May 24, 2010 at 6:08 pm
I had a situation today where a developer came to me to ask me why a query was running extremely slow. So slow it ran for minutes before he quit the query. After examining the execution plan of the SP I noticed that it was using an incorrect index. I modified the query and this time added an index hint to see if it would return results faster. Sure enough. It did. 2 seconds.
This sounds like a statistics issue but I'm hesitant to update them as it's a fairly large table and I don't want to impact production operations. The table sees only inserts and selects. No updates or deletes. How can I check the statistics of a table to determine if they are current or accurate? Is this a different issue that was simply masked by the addition of an index hint?
Any assistance would be greatly appreciated.
May 24, 2010 at 7:44 pm
You can run the following to identify when the statistics were last updated:
SELECT *, stats_date(object_id, stats_id) FROM sys.stats;
To find out the sampling rate for the statistics, you can run the following:
DBCC SHOW_STATISTICS('name of statistics');
If the statistics were last updated using auto update - then the rows sampled will be a lot less than the actual rows in the table. Whether or not a full scan will be beneficial will depend upon the actual distribution of the data in the columns that are defined. If there is an even distribution, then the default sampling rate may be correct.
Updating statistics will not block reads or writes, but it could block schema modifications. It could have a performance impact on a heavily used system, so I would recommend running after hours or during a slower utilization time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 25, 2010 at 8:43 am
1) this could be a cached plan that was great for the first execution but horrible for the next.
2) without the index hint, show the estimated query plan and check out the Estimated rows on various parts of the plan. Then run the query with the index hint and check out the Actual rows. If there is a large disparity it could be because of statistics freshness issues. But more likely it is simply because of variable usage and data skew. Dynamic SQL can help here, as can the WITH RECOMPILE query hint.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2010 at 8:52 am
It's interesting that you mention the WITH COMPILE option. I had to put that on a few months ago to get SP to use the right plan. I thought that it may have been a parameter sniffing issue, but all signs appear to be pointing to a statistics issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply