![]() Obviously longer term solutions include figuring out why this was designed this way and looking at other possible options. This resulted in the reporting services query coming back in 35 seconds as opposed to timing out. In this case, adding WITH RECOMPILE to the stored procedure before the AS, tells SQL to recalculate the plan at each execution instead of cache and reuse a plan for every execution. This can be verified by looking at the xml showplan for the execution and seeing what the parameter is compiled for. various reasons this can happen even for the same proc) and 2.) The query plan used by SSRS is being compiled first with the first time an SSRS session ever executes the query (190001). Two issues at play here 1.) The sessions are getting different query plans (topic for another post. When the query was executed for 200809 from SQL Server Management Studio, it came back in about 30 seconds. When the Reporting Services query ran asking for 200809 it ran until it timed out, no results coming back. The problem was picked up during an execution after a restart. The procedure behind this report looks for any “invalid” (per the business rules that means less than 195001) date and issues a “RETURN”, returning control to the color. So this first execution gives an false year/month to search back until (190001). Using SQL Server Reporting Services, a workaround was used (this is the part I want to look into more) to basically get a blank report showing when a user first links to a report until they change parameters and run the report themselves. The following example causes stored procedures, triggers, and user-defined functions that act on the Sales.Customer table to be recompiled the next time that they are run.Haven’t looked into all of the details underneath but at the surface probably a good reminder of where WITH RECOMPILE can be helpful… Requires ALTER permission on the specified object. For more issues and discussion on this topic, see Resolving queries with parameter sensitive plan problems.Apply the WITH RECOMPILE option with a Query Store hint without making code changes.Apply the WITH RECOMPILE option with a plan guide.Append the WITH RECOMPILE option to the query, requiring a code change.In prior versions, instead of calling sp_recompile with each execution, consider: The feature Parameter Sensitive Plan optimization introduced in SQL Server 2022 (16.x) Preview attempts to mitigate this problem automatically. Recompiling a stored procedure with every execution is one of the less efficient ways to combat query plan issues caused by parameterization. Most commonly, automatic recompilation follows changes to the underlying cardinality estimate because of automatic or manual statistics updates. There are a variety of reasons the database engine may choose to recompile objects. SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous. Proactive execution of this stored procedure is usually unnecessary. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. Sp_recompile looks for an object in the current database only. ![]() Return Code ValuesĠ (success) or a nonzero number (failure) Remarks ![]() If object is the name of a table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they are run. If object is the name of a stored procedure, trigger, or user-defined function, the stored procedure, trigger, or function will be recompiled the next time that it is run. object is nvarchar(776), with no default. ![]() The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. Transact-SQL Syntax Conventions Syntax sp_recompile 'object' In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |