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 Responses to “Running SQL faster”

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

    Jason Brimhall said this on April 1st, 2010 at 08:19

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

    Edward Anil Joseph said this on April 1st, 2010 at 09:09

  3. LOL, brilliant.

    willben said this on April 1st, 2010 at 09:48

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

    Laerte Junior said this on April 1st, 2010 at 09:58

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

    AndrewJacksonZA said this on April 1st, 2010 at 10:05

  6. Nice one!! :-)

    sunil said this on April 1st, 2010 at 11:20

  7. Thanks, glad you all liked it.

    Laerte, 101010 is 42 expressed in binary.

    Gail said this on April 1st, 2010 at 11:22

  8. There’s another good hint:

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

    Falk said this on April 1st, 2010 at 11:45

  9. 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”… :-)

    Meir said this on April 1st, 2010 at 11:50

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

    Sudeepta said this on April 1st, 2010 at 12:05

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

    Laerte Junior said this on April 1st, 2010 at 12:30

  12. ROFL

    h0b0 said this on April 1st, 2010 at 13:00

  13. Hahahahahaha…haha

    great sense……..of….

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

    sanjeev said this on April 1st, 2010 at 14:16

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

    Vinicio said this on April 1st, 2010 at 14:47

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

    Grant Fritchey said this on April 1st, 2010 at 14:51

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

    EL_oh_EL said this on April 1st, 2010 at 14:55

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

    John Pertell said this on April 1st, 2010 at 15:29

  18. Very nice!

    Steve Cullen said this on April 1st, 2010 at 17:46

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

    not amused said this on April 1st, 2010 at 18:12

  20. Obviously someone doesn’t have a sense of humor…

    Lynn Pettis said this on April 1st, 2010 at 18:55

  21. Lighten up Francis!

    “not amused” should try to develop an epidermis.

    Grant Fritchey said this on April 1st, 2010 at 19:01

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

    Jason Brimhall said this on April 1st, 2010 at 20:07

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

    OwiseOne said this on April 1st, 2010 at 22:50

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

    Seth Phelabaum said this on April 2nd, 2010 at 01:54

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

    GSquared said this on April 2nd, 2010 at 14:24

  26. Well, if you want to use a 10-year old version of SQL, you should expect that some stuff just doesn’t work right. ;-)

    Gail said this on April 2nd, 2010 at 14:36

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

    Jeff Moden said this on April 8th, 2010 at 22:51

  28. Gail, That cracked me up… Extreme override. That was a good touch. :-)

    Roy Ernest said this on April 13th, 2010 at 19:41

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

    wBob said this on April 23rd, 2010 at 14:15

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

    tosscrosby said this on April 30th, 2010 at 21:34

  31. Nice one Gail ! Very funny.

    Bru Medishetty said this on May 24th, 2010 at 22:23

Leave a Reply