December 17, 2009 at 8:47 pm
How do you identify which table has the max stats that is causing statistics job to run long? also is there a way to tell some stats for a table are unnecessary? Any more info may help understand.
December 18, 2009 at 1:01 am
Not quite sure I understand your question. You can use profiler to see what a stats update job is running and how long the various steps take.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2009 at 7:28 am
All stats for a table were necessary at one time or another or they wouldn't be there. It is possible though that system generated statistics on columns without indexes may have only been used once. Generally though, it's better to leave these in place or identify why they were created and possibly create or modify an index to satisfy the need of the statistic, not to drop them.
----------------------------------------------------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
December 18, 2009 at 8:16 am
autostats is turned on. How would you know which stats are USELESS and could be dropped? How to identify which update stats is taking the longest time ?
December 18, 2009 at 8:43 am
lakb200 (12/18/2009)
autostats is turned on. How would you know which stats are USELESS and could be dropped?
If they are automatically created statistics then, virtually by definition, they are not useless. SQL created them because it needed them. If you drop them, SQL will just recreate them next time it needs them.
Why do you want to drop them anyway. They take very little storage space. In general, stats updates are fast compared to other maintenance like an index rebuild.
How to identify which update stats is taking the longest time ?
I answered that.
You can use profiler to see what a stats update job is running and how long the various steps take.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2009 at 8:46 am
Gail already told you how to see what's taking the longest.
Identifying which stats are useless is difficult. You'll have to monitor the servers for a long period of time and collect data on which queries are called in order to determine which stats may or may not be used. It's not easy. There's no way that I'm aware of to simply look at statistics and see if they've been used recently. Instead you have to look at the queries to determine if they need statistics or not.
Remember, you can't drop statistics that are part of an index.
----------------------------------------------------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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply