Experienced DBAs know that auto-update-stats is not sufficient to keep statistics updated on large tables. This is doubly true for tables where the DML activity is near one end of the clustered key.
There are some common workarounds:
Not content with any of these, I’ve gone ahead and built my own script:
Now I can run this script on a near-constant schedule, and it will update each table as soon as a configurable percentage of rows or number of rows have changed. You can also configure the sampling percentages. I think it is The New Hotness very useful.
Coming up next: A version that compensates for filtered indexes & filtered statistics.Permalink
“I’d like to introduce a man with a lot of charm, talent, and wit. Unfortunately, he couldn’t be here tonight, so instead . . .” – Melvin Helitzer
My name is Dev Nambi, and this my blog on databases, data architecture, and solving interesting puzzles. In my short career I’ve grown from watching databases and paging DBAs, to being a pseudo-DBA myself, to writing the code that makes the databases tick. My next step is to become more involved in the SQL Server community. Depending on who I’m talking to, that’s either cause for celebration or agreat reason to join a 12-step program.
“Insanity - a perfectly rational adjustment to an insane world.”
I love working with the people. My mentors and peers are some of the best in the business, including the architect David Denz, the famous Kendra Little (@Kendra_Little) of LittleKendra.com, the infamous Crys Manson (@crysmanson) of Crysmanson.com, the classy Yanni Robel (@YanniRobel) of SqlScribbles.com, the hilarious Argenis Fernandez (@afernandez) of sqlps.com, and the Über-nerd Andrew Rogers (@sqlshaman).
I love thorny problems, and big challenges. My biggest joys are performance tuning and figuring out database architecture for large systems.Permalink