The first time the procedure executes, the resulting execution plan is cached, and subsequent executions of the procedure make use of this cached plan. Go Here is the resulting execution plan for both executions: Where ProductID = sp_executesql INT', Now you can execute the procedure and pass through two different parameters: dbcc freeproccache - clear all existing plans from cacheĮxec parameter_sniffing_dynamic_sql = 897Įxec parameter_sniffing_dynamic_sql = 870 Set = 'select SalesOrderId, SalesOrderDetailID, CarrierTrackingNumber create procedure parameter_sniffing_dynamic_sql int) as
EARMAN PRIMER ON DETERMINISM DOWNLOAD
If you don’t have a copy of AdventureWorks you can download it from Microsoft. In short, yes, and here is an example using the AdventureWorks sample database to illustrate this and what you can do about it.įirst, create a simple procedure that selects from the SalesOrderDetail table in AdventureWorks. Can parameter sniffing affect dynamic SQL?
Parameter sniffing itself isn’t a problem, that’s just how SQL Server works, but we only really talk about it in the context of it causing a problem due to the uneven distribution of values in the underlying data. Problems occur when the distribution of values in the tables is wildly uneven so that a good plan for one set of parameters becomes terrible when re-used for another set. Creating an execution plan can be expensive so SQL Server tries to re-use them whenever possible. When SQL Server first executes a piece of code, it uses (sniffs) any parameter values to generate an execution plan which is then cached. The statement executed in 2 seconds and the execution plan was completely different to the one generated by the sp_executesql call.Īll this information is highly suggestive that parameter sniffing was causing a problem, a common SQL Server DBA scenario. We isolated the slow performance to a single dynamic SQL statement that was being executed with sp_executesql, pulled out the dynamic SQL string and executed it manually. However, it was executing in under 2 seconds on previous days and with other parameter combinations. We were given a set of parameters with which to reproduce the problem and when we executed the procedure, it took 1 minute and 44 seconds.
Recently, one of our Managed Database Service customers asked us to investigate a stored procedure that was timing out, preventing hundreds of users from getting data.
0 kommentar(er)
