Online Index Creation and Cruel Defaults

Have you ever been watching queries crawl, feeling sort of helpless because you can’t change them? And even if you could, it’s hitting a new custom column in an ISV table that no one bothered to tell you about. I’ve been there. I’ve been pomade deep there.

I’ve also had my Robot tell me: “there’s an index for that, Erik.”

And Robot was right! But how could I sneak an index on this 180 million row table without making things worse? It’s the middle of a production day. Slow is bad; blocked is worse.

Well, creating an index ONLINE just needs a lil’ lock at the beginning and then a lil’ lock at the end, and just like the Ghost of Milton Berle that watches you while you sleep, you’ll never know it was there.

But you have to specify the ONLINE option, even in Enterprise Edition, that your company lovingly spent your bonuses for the next decade to license. Here is an example that breaks contrived speed:

No Business Use Whatsoever

AdventureWorks went out of business because of queries like this.

Seems reasonable. What say you, execution plan?

>Implying I need an index

Hey, look, it’s that thing Robot told me about. Thanks, Robot.

Except unlike Robot, I have a feeling. Just one. And today that feeling cares about end user experience and satisfaction. I want to get this index into the party pronto. This is the index that SQL and Robot agree on. But c’mon, we both know there’s WAYYYY more stuff going on here.

To prove it, we can script out simple index out from the GUI.

04-Go To The GUI

And here’s what that gives us. Note that ONLINE = OFF here.

05-GUI Results

 

If we go back in and check out the options, we can turn ONLINE = ON here.

 

06-Back To The GUI

And now looking at how it scripts out, ONLINE = ON

07-Better GUI Results

Which means it’s totally safe to just roll this out any ol’ time.

Just kidding, follow change management procedures and TEST TEST TEST. Also remember that robots and missing index hints are sometimes just plain dumb. They’re not terribly aware of existing indexes, and they don’t consider the columns they’re asking you to index. I’ve seen SQL demand indexes on 4 – 5 (MAX) columns, or ask for just one column to be indexed that could easily be covered or included in another existing index.

If you want to see what’s up with your indexes, missing, existing, hypothetical, or somewhere in the middle of that Bizarre Love Triangle, click here to check out sp_BlitzIndex®.

Kendra says: I keep trying to use ONLINE=MOSTLY, because that seems accurate, but it never works.

Brent says: Once I got started typing out CREATE INDEX by hand, I fell into the rut of doing it every single time, and I forgot about all the cool options available in the GUI. I’m conditioned to just say ONLINE = ON, but there’s other cool options too – like 2014’s amazing performance improvements if you sort in tempdb.

Previous Post
Foreign Keys in SQL Server (video)
Next Post
Microsoft Partners with Taco Bell for Naming Rights

9 Comments. Leave new

  • First!

    Reply
  • Not in Standard Edition

    Reply
    • Erik Darling
      March 31, 2015 4:19 pm

      Hi Kenny! Nope, and to make matters even funnier, even your offline rebuilds won’t go parallel in SE.

      Reply
  • Noob question. ISV table?

    Reply
  • Good find, did not even know you can change the online flag from the GUI.
    Rebuilding an old index online can take quite a bit of extra disk space while doing the rebuild so make sure you have enough free space before you start.

    Reply
  • Holy smoke, I need some info on this topic, but this April Fools’ style by Brontosaurus (Brentozar – Brontosaur, gotcha) makes it so hard to focus. Comic Sans Serif, garish colours, animated gifs: brings back painful memories of HTML in the previous millennium. 🙂

    I’ll be back tomorrow. With a pair of sunglasses…

    Reply
  • Alex Friedman
    April 30, 2015 3:09 am

    > Kendra says: I keep trying to use ONLINE=MOSTLY, because that seems accurate, but it never works.

    LOL this is too true. There’s just no truth in keywords (e.g. NOLOCK, ROWLOCK, etc…).

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}