Poor performance is not uncommon in most SQL Server environments. The data sizes are growing, the hardware isn’t and traditional methods of performance tuning are time-consuming and difficult.
So what’s the solution? Well, throwing hardware at the problem is an old favourite. There are few workloads that a nice 256-processor Itanium with a terabyte or two of memory won’t handle, but servers like that are a little on the expensive side and lots of money spent on expensive hardware means less that can be spent on annual bonuses.
There is another option, a hidden, undocumented option that can improve query performance, maybe a little, maybe substantially.
First thing that you need to do to get this one is to enable the hidden options in sp_configure. This is done much the same way as the advanced options.
exec sp_configure 'show hidden options', 1 RECONFIGURE WITH EXTREME OVERRIDE
Once that’s done, the undocumented option can be enabled.
exec sp_configure 'run queries faster', 101010 RECONFIGURE WITH EXTREME OVERRIDE
How much improvement this will give depends on the kind of queries being run. OLTP systems usually see a greater improvement than decision-support, unless there’s full text search or spatial queries, in which case there will likely be substantially less of a gain.
Now, there are a few things to consider.
- This is obviously undocumented and that means unsupported.
- It may not work on the next version of SQL.
- If you call support, disable the option first and don’t tell them you were running it!
Happy April Fools’ day.
Seriously now, there’s no options that, when enabled, makes SQL run queries faster. There is no silver bullet for performance problems, there is no one-size-fits-all fix.
Fixing performance problems involves finding the current bottleneck and removing it, then repeating that operation until performance is acceptable. It’s a complex area and there’s a lot to it. Simply throwing hardware at the problem may not produce much, if any, performance gain, especially if the hardware wasn’t the bottleneck.
If you have a query performance problem and don’t know where to start, ask on one of the SQL forums (like SQLServerCentral) if it’s not an urgent problem. If it is, or if there are serious problems, consider getting a consultant in to help out. One of the quickest ways to learn is to learn from someone who knows what they are doing.