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:

Seems reasonable. What say you, execution plan?

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.

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

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

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

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.