Thursday, April 1, 2010

Boost the performance of your SQL Server

Today I discovered a fascinating, although also weird thing regarding SQL Server Standard Edition. We all know that SQL Server has many of undocumented commands. DBCC makes no difference here. You probably know it as the DBA’s all purpose tool, but what I found by coincidence really puzzled me when I came across it this afternoon.

So here’s the story. A customer of mine runs some SQL Server Standard Editions which – as you may know – has some limitations in comparison to the Enterprise Edition. Those limitations also include some features of the optimizer, e.g. regarding the use of Bitmap Filters, or Indexed Views. Today, just for fun, I entered and ran the following DBCC command:


And, believe it or not, all the limitations of the SQL Server Standard Edition went away instantly. It really behaves like it was the Enterprise Edition now. Amazing!

Of course, I can’t give any explanation what’s behind this. But hey, all that counts is, it finally works, right? So why not giving it a try? But be prepared that the command is undocumented and maybe not work as expected on all systems. But for me – it did! At least today…

Have fun!