March 24, 2014 at 2:47 pm
Hello All,
Problem:
I have created a test scenarion where the statement from MSDN is being reflected in my environment. I have a stored proc which hits a table called Test1. It takes Name as a parameter and returns the Primary Key Coulumn(Id) along with name column itself from the Test1 table for that Name. It uses an Index IX_name on the Name column. I have verified the plan in the Plan cache (sys.dm_exec_proc_stats)/Actula Execution plan and it does use IX_name index in its plan. No other index, No key lookups just one operatior is being used and that is Index Seek.
I did some delets/Inserts/Updates for few rows from Test1 and update the stats to reflect that change in the histogram. Now I run the profiler capture SP:Starting, SP:Recompile, SQL:Recompile, SP:Completed events with a filter on that database. I see the executions(SP:Starting, SP:Recomplie) but I do not see the Recomiple happening even after the stats update (I have verified the stats update using DBCC SHOW_STATISTICS and I see the Histogarm properly updated). According to the MSDN article (http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx) SP plan will be recomplied when the stats are updated on the table which SP refrences. In that case why am I not able to see that happening in my environment. I donot see SP:Recomplie event firing. I have observerd the Actual execution plan the Estimates are skewed from Actuals. When I run the SQL in the SP as an Adhoc I see proper Estimates/Actual Row counts in the Adhoc plan. Why is it happening? Is it a version issue? But the documentaion is for 2008 R2. Please help.
Info:
SQL Server Version : SQL Server 2008R2
Product Level: SP2
Datbase Setting: All are in default settings
Auto Create Statistics : True
Auto Update Statistics : True
Auto Update Statistics Async : False
Regards,
Nawaz.
March 25, 2014 at 11:10 am
Hello,
Can anybody help me with this?
Regards,
Nawaz.
March 25, 2014 at 11:54 am
Hi There,
From Microsoft
"As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization occurs automatically the first time a stored procedure is run after SQL Server is restarted. It also occurs if an underlying table that is used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not occur until the next time that the stored procedure is run after SQL Server is restarted."
If you want the plan to recompile, say, if you are using variable parameters you will need to add OPTION(RECOMPILE) to the end of your script.
Kenda Little has a 30 min video on it here:
http://www.brentozar.com/archive/2012/06/use-abuse-of-recompile-sql-server-video/
March 25, 2014 at 4:00 pm
Hi David,
Thanks for the reply. I am aware of the fact that any DDL changes to the underlaying tables, or modifications to the SP itself causes the SP plan to be removed from cache (Whichj is not my case).
Similarly, I was under a notion that when the Stats for the underlaying tables is updated the plan would be recompiled. This is as per MSDN documentation (http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx).
If you want the plan to recompile, say, if you are using variable parameters you will need to add OPTION(RECOMPILE) to the end of your script.
I do not want my SP to be recompiled every time it runs. Hence, I do not want to use OPTION(RECOMPILE). I want it to be recompiled only when stats are updated. Which happens once every week.
So, if SPs will not be recompiled when stats updated how do we overcome the problem. Should we clear the plan cahe every time stasts are updated? So that a new plan is crated. I have not seen such a recomedation any where else.
Regards,
Nawaz.
March 26, 2014 at 2:13 am
Hi,
Clearing the plan cache would be a bad idea, your performance would be degraded as the first execution of the SP would have to workout the best plan.
What has prompted the question, are you seeing performance degrade over time for the SP?
There is another article here:
http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
We may need someone who knows the database engine better than I to answer this.. 🙂
March 26, 2014 at 10:32 am
Thank you David,
There is another article here:
http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
You are pointing me to the same article that I did in my previous posts.
What has prompted the question, are you seeing performance degrade over time for the SP?
Yes, I am seeing a degraded performance in our system dude to outdated stats. And when I update them I do not see the plans being recompiled with proper cardinality. That is the reason I am here.
January 13, 2015 at 11:23 am
Did you ever get an answer on this? I am seeing a similar behavior. For example:
1.) Table has few rows
2.) query table (plan gets compiled)
3.) insert millions of rows
4.) update statistics
5.) query table
the query in step 5 uses the plan from step 2, which should have been invalidated when the stats were updated in step 4. The result is a very long running query. Once the query is recompiled, it gets a good plan.
Anyone else seen this behavior? If it matters the query is using sp_executesql to do a select.
January 13, 2015 at 11:34 am
Figured this out... the problem is a missing statistic. Stat updates will cause recompiles for plans that use those statistics. If stats are updated and the plan persists, it must not be using any of the stats on that table. Confirmed the missing statistic with DTA.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply