August 1, 2011 at 9:03 pm
brian118 (8/1/2011)
Found this white paper. related to SQL Server 2000.To avoid long term maintenance of unused statistics, SQL Server 2000 ages the automatically created statistics (only those that are not a byproduct of the index creation). After several automatic updates the column statistics are dropped rather than updated. If they are needed in the future, they may be created again. There is no substantial cost difference between statistics that are updated and created. The aging does not affect user-created statistics.
Not sure if it still applies to SQL Server 2008 since I have another white paper on Statistics and there is no mention of this behaviour.
Yes it used to work that way, but no longer. I don't recall exactly when the change was made, but it sounds right to me that it would have changed between 2000 and 2005 RTM.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 1, 2011 at 11:57 pm
SQLkiwi (8/1/2011)
brian118 (8/1/2011)
Found this white paper. related to SQL Server 2000.To avoid long term maintenance of unused statistics, SQL Server 2000 ages the automatically created statistics (only those that are not a byproduct of the index creation). After several automatic updates the column statistics are dropped rather than updated. If they are needed in the future, they may be created again. There is no substantial cost difference between statistics that are updated and created. The aging does not affect user-created statistics.
Not sure if it still applies to SQL Server 2008 since I have another white paper on Statistics and there is no mention of this behaviour.
Yes it used to work that way, but no longer. I don't recall exactly when the change was made, but it sounds right to me that it would have changed between 2000 and 2005 RTM.
Thanks Paul, it was the answer I was hoping for, however now I back to my original mystery, what happened to those auto-created stats after the DB being restored to a new instance? Well I guess I have to keep on digging!
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 2, 2011 at 1:54 am
some drop'm after a restore. I don't advise it, but it happens.
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
August 2, 2011 at 6:07 am
SQLkiwi (8/1/2011)
brian118 (8/1/2011)
Found this white paper. related to SQL Server 2000.To avoid long term maintenance of unused statistics, SQL Server 2000 ages the automatically created statistics (only those that are not a byproduct of the index creation). After several automatic updates the column statistics are dropped rather than updated. If they are needed in the future, they may be created again. There is no substantial cost difference between statistics that are updated and created. The aging does not affect user-created statistics.
Not sure if it still applies to SQL Server 2008 since I have another white paper on Statistics and there is no mention of this behaviour.
Yes it used to work that way, but no longer. I don't recall exactly when the change was made, but it sounds right to me that it would have changed between 2000 and 2005 RTM.
So Paul, how does it work now? I've downloaded the book but I,m not done reading it atm so you can reffer me to RTFM if the answer's in there ;-).
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply