April 26, 2011 at 12:05 pm
Hello,
I have a stored procedure which times out, but if i update the statistics and run the program it works fine
I analysed the execution plan and everything looks fine(other than some KeyLookup and RIDLookup which i can't modify much as the stored procedure is involved in update statement)
Also i update the statistics every morning at 4 am and the stored procedure is run every morning at 2 am.....Do u guys think i have update the statistics more than once everyday(like around 12 pm)
April 26, 2011 at 3:49 pm
For that one query, yeah, it sounds like it needs more up to date statistics.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2011 at 4:13 pm
1) what happens to the table during the day? that would determine if you need more frequent stats updates
2) could this be a simple case of parameter sniffing? updating stats clears out the plans so the next run you do could be fast simply due to that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 2, 2011 at 2:58 pm
Run your sql statement and obtain the *actual* exec plan.
In the exec plan identify the operator(s) with the largest cost, eg. clustered index scan.
Compare actual with estimated number of rows. If the actual number of rows is, say 1,000, and the estimated number of rows is 100,000, then statistics is definitely an issue.
If the actual and estimated number of rows are similar, then statistics is likely not an issue.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 2, 2011 at 7:07 pm
If that's the issue, you need to update the statistics prior to running the SP ?
BTW, how are you updating the statistics ?
Thank You,
Best Regards,
SQLBuddy
May 5, 2011 at 10:24 am
PradeepVallabh (4/26/2011)
I analysed the execution plan and everything looks fine(other than some KeyLookup and RIDLookup which i can't modify much as the stored procedure is involved in update statement)
I noticed that you have Lookups going on in the query. This makes me suspect parameter sniffing as mentioned by Kevin. The increase in performance may be coincidental to the update of statistics. The new plan may not do Lookups simply based on the parameters and may have a more efficient plan.
Key Lookups are only efficient on an indexes that are selective of less than 1% of the total records in the table. You could have a perfectly good plan doing Key Lookups on less than 1% of the table and then have a query that is trying to do a Key Lookup on over 50% of the table using the same plan. This would be a very bad plan.
Todd Fifield
May 5, 2011 at 11:21 am
This might very well explain your particular case
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply