Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Plan Not Being Recomplied After Even After Statistics Update Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 2:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303
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.
Post #1554219
Posted Tuesday, March 25, 2014 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303
Hello,

Can anybody help me with this?

Regards,
Nawaz.
Post #1554599
Posted Tuesday, March 25, 2014 11:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 61, Visits: 513
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/


============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1554620
Posted Tuesday, March 25, 2014 4:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303
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.
Post #1554711
Posted Wednesday, March 26, 2014 2:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 61, Visits: 513
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.. :)


============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1554813
Posted Wednesday, March 26, 2014 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303
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.


Post #1555075
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse