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.
Dang it. I was so sure this was going to work.
“run queries faster”? 🙂
That one was hilarious.
Check out google’s new 2010 April fool’s day special at http://goo.gl/dA5y
LOL, brilliant.
Gail, excllent tip. Can you explain what is 101010 ?
Thanks, glad you all liked it.
Laerte, 101010 is 42 expressed in binary.
Bahaha! That one was really funny!
On a more serious note, thank you for including some real advice that readers can take away from reading this post. It’s nice to see real advice and not just a funny but useless post.
Nice one!! 🙂
There’s another good hint:
With the parameter “run queries much faster” you’ll get a much better performance…;)
first, i was surprised to see the “WITH EXTREME OVERRIDE” – only understood it’s April 1st when i got to the “run queries faster option”… 🙂
Nice one… Second twist for the day.. My sis gave the first this morning.
Heheheh.. I send to one friend, In Brasil today we call “Dia da Mentira” ..and he still trying “extreme override” and “Hide Oprtions”…hahahahaha….I am bad..
ROFL
Hahahahahaha…haha
great sense……..of….
i started thinking on it……..then quickly see the calendar..and then smiling…………
I almost fall for it. I was reading another article about the NULLs and SQL 11 and then this. At the beginning I thought It would be great to have a Nitro option to boost performance. Nice one…
I’m so confused now. I thought the undocumented setting was just called “Run faster” is this one new with R2?
If my company found out this code snippet then they wont need me anymore! LOL
Perfect! a co-worker asked me a few weeks ago what switch he should use to make SQL run faster. Now I have a documented source!
Very nice!
Retards! Now everytime I search for ‘SQL’ and ‘Faster’ I’ll have to weed through this lame attempt at humor.
Obviously someone doesn’t have a sense of humor…
Lighten up Francis!
“not amused” should try to develop an epidermis.
Not amused does need to grow an epidermis. Everybody knows you should be searching on “SQL” and “TurboSpeed” anyway.
LOL….I was LMAO on the ‘run queries faster’. That’s awesome!!!
This might be one of the first times I’ve had to disagree with you. I just ran this on my server and am seeing almost no difference in Query execution results. Man, NOTHING works on SQL 2000!
I can’t wait till we can upgrade to 2008 and use this and the ‘OPTIMIZE FOR NESTED CURSORS’ hint.
“Not amused”, and now every time I search for “retards”, your comment will come up. How … I can’t choose whether to type “ironic” or “appropriate”.
Good post, Gail. And I love Seth’s idea too. I know devs who need both of these options!
Well, if you want to use a 10-year old version of SQL, you should expect that some stuff just doesn’t work right. 😉
BWAAA-HAAA!!! Two pork chops up! Between this undocumented command and DBCC TIMEWARP for doing restores from tomorrow’s backups yesterday (well, except for Tuesdays), every DBA can now relax in the cooling comfort of the beer locker.
Gail, That cracked me up… Extreme override. That was a good touch. 🙂
Funny thing is people use sp_configure ‘max degree of parallelism’, 1; like this and expect queries to run faster! Maybe they should read this post for a reality check! No free lunch lol
Dang, I thought that idden feature wa akin to DBCC Timewarp. Seriously though, as soon as I saw “RECONFIGURE WITH EXTREME OVERRIDE”, I thought to myself, is this a joke – never once looking at the date of the post. Good article, a nice bit of a refresher.
Nice one Gail ! Very funny.
Gail your post are very useful, this is brilliant sp_Configure i was not aware of…You remind me back at The Microsoft HQ in Brynston…SQL Server Chalk and Talk…Very helpful tips..I will forever give you The MVP stamp…