Thursday, February 16, 2017

A tool to Optimize SQL Server Indexes

SQL Server Management Studio has a nifty tool that allows you to setup daily maintenance plans that run a CHECKDB and will also rebuild your indexes. Great. But, those index rebuilds called by the maintenance tools have a drawback. A big, log growing, disk hogging drawback.

It wants to do full rebuilds of all indexes. And these processes are logged. Low fragmentation? No problem - full rebuild. Large table? No problem - full rebuild.  Low on disk space on your server, and you really do not want your log file to grow. Problem!


Years ago, in my days as a DBA, I created a process to review all the indexes for fragmentation, and then do targeted updates. But, that was years ago when we all carried flip-phones. Fortunately, Ola Hallengren has released a complete set of index optimization tools that work with current versions of SQL Server.

You can find them here:

And, if you need some additional suggestions on the best way to adjust all the optional parameters, start with the post at Brent Ozar:  Tweaking the Defaults for Ola Hallengren’s Maintenance Scripts.

And did I consider digging out my old index optimization scripts? After looking over the work Ola Hallengren did - not a chance. 

No comments: