Monthly Archives: August 2012

What a statistics problem can look like

You are a busy production OLTP SQL server and your owner has decided that its only necessary back you up and re-org your heavily fragmented indexes. That’s it, he is a minimalist and his ideological ways work well for months…


Then along comes Martha and her HR report. The plan cache is bloated with these pesky OLTP sql plans and Martha’s report needs more buffer pool and a big fat sql plan. (Martha’s not fat just her plan) So her plan and buffer pool requirements evict your very important OLTP plans. Re-evaluating plans once in a while is fine, part of normal memory management is to clean up old unused stuff. By “clean up stuff” I mean use the special sauce algorithms inside the sql server optimizer to decide what needs to go. This is much more complicated than a LIFO or FIFO system.

The data in these tables have changed so much you are not sure what to do. Optimization is taking forever so you throw a new sql plan in place and go about your business.

This optimization process didn’t have enough current data(a.k.a. statistics) so you got a crappy plan for a query that is run over, and over, and over again. The query that used to bring back data with 24 logical reads now needs 24,000 logical reads. But guess what, your optimizer is so confident in it’s nerdy mathematical algorithms that it doesn’t bother to re-compile. So what do you do? Start your CPU insanity workout until your owner fixes your statistics which forces a re-compile of the crappy plan.

1 Comment

Posted by on August 22, 2012 in SQL Admin, SQL Dev