Running SQL faster

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.

  1. This is obviously undocumented and that means unsupported.
  2. It may not work on the next version of SQL.
  3. 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.

31 Comments

  1. Jason Brimhall

    Dang it. I was so sure this was going to work.

    Reply
  2. Edward Anil Joseph

    “run queries faster”? πŸ™‚
    That one was hilarious.
    Check out google’s new 2010 April fool’s day special at http://goo.gl/dA5y

    Reply
  3. willben

    LOL, brilliant.

    Reply
  4. Laerte Junior

    Gail, excllent tip. Can you explain what is 101010 ?

    Reply
    1. Gail

      Thanks, glad you all liked it.

      Laerte, 101010 is 42 expressed in binary.

      Reply
  5. AndrewJacksonZA

    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.

    Reply
  6. sunil

    Nice one!! πŸ™‚

    Reply
  7. Falk

    There’s another good hint:

    With the parameter “run queries much faster” you’ll get a much better performance…;)

    Reply
  8. Meir

    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”… πŸ™‚

    Reply
  9. Sudeepta

    Nice one… Second twist for the day.. My sis gave the first this morning.

    Reply
  10. Laerte Junior

    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..

    Reply
  11. h0b0

    ROFL

    Reply
  12. sanjeev

    Hahahahahaha…haha

    great sense……..of….

    i started thinking on it……..then quickly see the calendar..and then smiling…………

    Reply
  13. Vinicio

    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…

    Reply
  14. Grant Fritchey

    I’m so confused now. I thought the undocumented setting was just called “Run faster” is this one new with R2?

    Reply
  15. EL_oh_EL

    If my company found out this code snippet then they wont need me anymore! LOL

    Reply
  16. John Pertell

    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!

    Reply
  17. Steve Cullen

    Very nice!

    Reply
  18. not amused

    Retards! Now everytime I search for ‘SQL’ and ‘Faster’ I’ll have to weed through this lame attempt at humor.

    Reply
  19. Lynn Pettis

    Obviously someone doesn’t have a sense of humor…

    Reply
  20. Grant Fritchey

    Lighten up Francis!

    “not amused” should try to develop an epidermis.

    Reply
  21. Jason Brimhall

    Not amused does need to grow an epidermis. Everybody knows you should be searching on “SQL” and “TurboSpeed” anyway.

    Reply
  22. OwiseOne

    LOL….I was LMAO on the ‘run queries faster’. That’s awesome!!!

    Reply
  23. Seth Phelabaum

    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.

    Reply
  24. GSquared

    “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!

    Reply
  25. Gail

    Well, if you want to use a 10-year old version of SQL, you should expect that some stuff just doesn’t work right. πŸ˜‰

    Reply
  26. Jeff Moden

    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.

    Reply
  27. Roy Ernest

    Gail, That cracked me up… Extreme override. That was a good touch. πŸ™‚

    Reply
  28. wBob

    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

    Reply
  29. tosscrosby

    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.

    Reply
  30. Bru Medishetty

    Nice one Gail ! Very funny.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *