TSQL Tuesday

TSQLTuesday

It is TSQL Tuesday and I’m giving it my first shot. The topic is performance tuning and is graciously hosted by Russ Thomas  (@SQLJudo).

What is my “go BIG or flame out” trick. It’s none other than NOLOCK. It’s flames on sports cars. It’s the turbo button. It is the SQL developer fairy dust and unicorns. In somewhat recent pop culture it’s Tiger blood and Adonis DNA. It’s the Charlie Sheen of TSQL performance tuning.

cs1

It sounds great and is easy to implement. Who wouldn’t want to use that? Sure it’s a little risky, but what’s development without a bit of danger. Isn’t that why we got into the DBA business? A craving for addrenaline fueled risk

cs2

NOLOCK is the lovely table hint that doesn’t put shared locks on your tables. Great! It doesn’t stop other transactions from modifying data. Perfect! It increases concurrecy. What could be better! Like other vices it can get addictive.

cs3

Also, like other vices there are down sides. Without those locks, users could be reading data that is in the middle of being updated. Can the application handle that? Will they be reading incomplete data? Is that data being deleted and never should have existed in the first place? That’s NOLOCK for you. We don’t know!

cs4

But who am I to tell you not to use NOLOCK. These people are all far smarter and more qualified than I.

All I can do is end with a slightly altered Charlie Sheen quote, “I’m on a drug. It’s called NOLOCK. It’s not available because if you try it you will die. Your face will melt off and your children will weep over your exploded body.

cs5

Advertisements

One thought on “TSQL Tuesday

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s