How do I change the system-wide default value of MAXRECURSION?
By default it is 100, but I need to increase it to something like 1000.
I am unable to use query hints since I'm using a program that takes my query and executes it for me and I can't get around this limitation, unfortunately.
However, I do have admin rights on the server instance. I've poked around in the server facets, but I don't see anything there related to query options or recursion. I assume there has to be a place somewhere where I can update the system-wide default.
Any ideas?
You can check the collation for each local in the following url:
Best Answer
If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides.
There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form using the built-in procedure sys.sp_get_query_template, and then create a TEMPLATE and/or OBJECT/SQL plan guide.
See the documentation for further information:
Plan guides will need to be revalidated whenever the application code changes, and when SQL Server is patched or upgraded. This should just be part of your normal testing cycle.
Note that plan guide validation using sys.fn_validate_plan_guide may incorrectly report a failure if the guided statement references a temporary table. See this question:
Plan guide validation with fn_validate_plan_guide gives false positives
The Plan Guide Successful and Plan Guide Unsuccessful Profiler and Extended Events classes can also be used to monitor plan guide applications.
Connect was retired before the product improvement suggestion Allow MAXRECURSION limit values other than 100 for views and UDFs by Steve Kass was implemented. If you would like to take it up with Microsoft now, see the options at SQL Server help and feedback.