January 12, 2012 at 8:13 am
Hi,
Hopefully someone can advise and make things a bit clearer for me.
I have a database with three particularly large tables which are possibly the cause of some maintenance jobs overrunning.
Looking at the last updated statistics info from one of them, I can see that for instance there are 20 indexes which took about 30 secs for their stats updates, while the 90 or so _WA* columns took approx three minutes each.
Is there anything inherently different about a columns Vs indexes when it comes to the time taken for stats updates to complete? I could see why a clustered index may allow a faster scan, but the non-clustered seem to be the same in terms of timing in the example mentioned above.
Thanks
January 12, 2012 at 9:26 am
I don't know why that would be different. The only explanation I can think of is that the statistics on the indexes are being updated by an index rebuild before the statistics update happens, which would mean less work to do in the Statistics Update job.
I'd also look at why you have all those system generated column statistics. You might be missing some indexes and/or have poorly defined indexes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 12, 2012 at 9:43 am
Hi Jack,
I don't think there is any reindexing going on first, since the update stats script checks first to ensure it only runs where last update > 24 hours ago. Even still, regardless of what causes the stats to update ('UPDATE STATISTICS' or reindexing) the timings would still be the same?
Are all the systems generated column stats potentially historical and so no longer valid? Could this be an idea opportunity to just run update stats at index level? Perhaps prior to that, could I somehow delete the _WA* entries and if they are still needed then they will be auto created and be visible soon enough anyway??
The more I think about this subject the more complicated it becomes!
January 12, 2012 at 11:16 am
In general, most of the time, I run update stats on each individual index, not the table. I leave the table stats to fend for themselves. If I really, really need one, I'll probably create an index. Otherwise, the general sampling automated updates that they're still subject to will probably be good enough.
----------------------------------------------------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
January 12, 2012 at 12:48 pm
another reason to switch to a more intelligent solution in stead of a maintenance plan.
there are a couple of alternatives at SSC.
Ola Hallengren has a very nice one at http://ola.hallengren.com/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2012 at 2:41 am
i have not confirmed it yet (still awaiting access to production :angry:) but i am guessing that the issue may be related to the indexes being on typically integer data types, and the other columns possibly being varchar/float. would that result in different times? it still doesn't explain why sql wants to have stats on these other columns as i am not aware of our applications using them in any particular queries
January 16, 2012 at 6:55 am
rarara (1/16/2012)
i have not confirmed it yet (still awaiting access to production :angry:) but i am guessing that the issue may be related to the indexes being on typically integer data types, and the other columns possibly being varchar/float. would that result in different times? it still doesn't explain why sql wants to have stats on these other columns as i am not aware of our applications using them in any particular queries
SQL Server creates statistics on columns used in WHERE clauses and JOIN ON clauses so at some point the column(s) these statistics are on were used in this manner and SQL Server created the statistics.
You can explicitly drop them if you really believe that they aren't needed, but if they are there will be a performance hit as SQL Server will have to create them again.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply