A couple of weeks ago I attended a training session that was taught by Tim Chapman(t). Parameter-Sniffing was part of the agenda and when we were asked to share our thoughts and ideas of how to tackle it , I suggested that if you know very well the distribution of the data and you cannot afford to recompile the plan every time, then you can add a single comment to generate a new plan and such plan will be reused.
I thought most of the people there would know about this and I was going to get a lot feedback and a couple of good reasons of why they do not use it, but it was the other way around, most of them did not know about it.
The lab
We are going to be working with a single table, one stored procedure and a simple query to retrieve the plans from cache, scripts are as follows:
Here is our stored procedure, please noted that I am using Dynamic SQL:
This is the query that we are going to use to retrieve the plans from cache.
Le'ts review our data distribution.
Results are shown below:
Executing this stored procedure with Score=1 produces the following results, please note that we have to clear the cache for this execution.
200 rows were returned and new plan was created, this plan is complied for Score=1, please see below.
Messages tab will show the query that was executed.
Execution plan: Index Seek therefore a Nested Loops is required due to the Key Lookup to get the "AboutMe" field.
Executing this stored procedure with any other value will produces the same results, since the plan will be pulled from cache. Let's try with a different value.
799K rows were returned and plan was pulled from cache, note that Query Hash and Query Plan Hash are the same as the previous execution.
Messages tab will show the query that was executed. (Same query)
Execution plan: Index Seek therefore a Nested Loops is required due to the Key Lookup to get the "AboutMe" field... Not good, we should be scanning , but parameter-sniffing is doing it's job.
The trick
Let's modify the stored procedure, we are going to add a comment and ,if we are lucky enough, SQL Server will hear our prayers.
Executing this stored procedure with Score=1 produces the following results, please note that we have to clear the cache for this execution.
200 rows were returned and new plan was created, this plan is complied for Score=1, please see below.
Messages tab will show the query that was executed. (Now with our comment)
Execution Plan: It seems that our prayers are working out :)
If we run the stored procedure now using Score =6, we get the same execution plan since it was reused from cache, so our comment it's working wonders, pleas see below.
Executing this stored procedure with Score=4 produces the following results.
799K rows were returned and new plan was created note that Query Hash is the same as the previous execution, but now we have a new Query Plan Hash.
Messages tab will show the query that was executed. (Now with our comment)
Execution Plan: It seems that our prayers are working out :)
The magic is here:
Basically, a comment is persuading SQL Server to create a new plan and this is because the query changed and SQL Server has to recompile in order to assure a better plan :).
If you have any questions or suggestion, please let me know in the comments.
Enjoy!!
Kommentare