January 6, 2015 at 9:01 am
I ran Glenn berry's query and saw I have a big table 40 million rows. that has only 1 clustered index and the index hasn't been updated for more than 1 year.
My question are:
how do I find when and how often index were used.
Can I just update statistics manually on the index?
If I do, will there be bad impact?/If I don't, what are damages?
please have your inputs.
January 6, 2015 at 10:57 am
You can look at sys.dm_db_index_usage_stats to see how, or if, the index is being accessed. Just note that the statistics are reset if the server is restarted, or the database is detached or shutdown, so they won't be a perfect record.
As far as updating the statistics, if the data hasn't changed, shouldn't the statistics be up to date? That assumes the statistics were updated when the data was loaded. If not, yeah, I'd do a manual update. But, if the data is not changing, you only need to do this one time. If the data doesn't change, neither will the statistics about the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2015 at 1:10 pm
For a table that size, and esp. one that apparently static, I'd make sure the statistics were based on the entire table rather than just a small sampling.
You can see the statistics row count by reviewing the output from:
DBCC SHOW_STATISTICS ( '<table_name>' , '<clus_index_name>' )
If the "Rows Sampled" is significantly less than "Rows", then update stats with a fullscan:
UPDATE STATISTICS <table_name> ( ... ) WITH FULLSCAN
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2015 at 6:14 am
Thank you Grant and Scott.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply