Should You Use Index Hints?

Published on: 2018-07-31

Watch this week’s post on YouTube

One of the things that the SQL Server query optimizer does is determine how to retrieve the data requested by your query.

Usually it does a pretty good job, which is a great because if it didn’t then we’d be spending most of our days programming sorting and joining algorithms instead of having fun actually working with our data.

Sometimes the query optimizer has a lapse in judgement and createds a less-than-efficient plan, requiring us to step in and save the day.

Index Hints Give You Control

One way to “fix” a poor performing plan is to use an index hint.  While we normally have no control over how SQL Server retrieves the data we requested, an index hint forces the  query optimizer to use the index specified in the hint to retrieve the data (hence, it’s really more of a “command” than a “hint”).

Sometimes when I feel like I’m losing control I like using an index hint to show SQL Server who’s boss.  I occasionally will also use index hints when debugging poor performing queries because it allows me to confirm whether using an alternate index would improve performance without having to overhaul my code or change any other settings.

…But Sometimes That’s Too Much Power

While I like using index hints for short-term debugging scenarios, that’s about the only time they should be used because they can create some pretty undesirable outcomes.

For example, let’s say I have this nice simple query and index here:

This index was specifically created for a different query running on the Posts table, but it will also get used by the simple query above.

Executing this query without any hints causes SQL Server to use it anyway (since it’s a pretty good index for the query), and we get decent performance: only 1002 logical reads.

I wish all of my execution plans were this simple.

Let’s pretend we don’t trust the SQL Server optimizer to always choose this index, so instead we force it to use it by adding a hint:

With this hint, the index will perform exactly the same: 1002 logical reads, a good index seek, etc…

But what happens if in the future a better index gets added to the table?

If we run the query WITHOUT the index hint, we’ll see that SQL Server actually chooses this new index because it’s smaller and we can get the data we need in only 522 logical reads:

This execution plan looks the same, but you’ll notice the smaller, more data dense index is being used.

If we had let SQL Server do it’s job, it would have given us a great performing query!  Instead, we decided to intervene and hint (ie. force) it to use a sub-optimal index.

Things Can Get Worse

The above example is pretty benign – sure, without the hint SQL Server would have read about half as many pages, but this isn’t a drastic difference in this scenario.

What could be disastrous is if because of the hint, the query optimizer decides to make a totally different plan that isn’t nearly as efficient.  Or if one day someone drops the hinted index, causing the query with the hint to down right fail:

Index hints  can be nice to use in the short-term for investigating, testing, and debugging.  However, they are almost never the correct long-term solution for fixing query performance.

Instead, it’s better to look for the root-cause of a poor performing query: maybe you need to rebuild stats on an index or determine if the cardinality estimator being used is not ideal.  You might also benefit from rewriting a terribly written query.

Any of these options will likely help you create a better, long-term, flexible solutions rather than forcing SQL Server to use the same hard-coded, potentially sub-optimal index forever.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Why Is My VARCHAR(MAX) Variable Getting Truncated?

Published on: 2018-05-15

Watch this week’s episode on YouTube.

Sometimes SQL Server doesn’t do what you tell it to do.

Normally that’s ok – SQL is a declarative language after all, so we’re supposed to tell it what we want it to do, not how we want it done.

And while that’s fine for most querying needs, it can become really frustrating when SQL Server decides to completely disregard what you explicitly asked it to do.

Why Is My VARCHAR(MAX) Truncated to 8000 Characters?

A prime example of this is when you declare a variable as VARCHAR(MAX) because you want to assign a long string to it.  Storing values longer than 8000 characters long is the whole point of VARCHAR(MAX), right?

If we look at the above query, I would expect my variable @dynamicQuery to be 8001 characters long; it should be 8000 letter ‘a’s followed by a single letter ‘b’.  8001 characters total, stored in a VARCHAR(MAX) defined variable.

But does SQL Server actually store all 8001 characters like we explicitly asked it to?

No:

First we can see that the LEN() of our variable is only 8000 – not 8001 – characters long!

Copying and pasting our resulting value into a new query window also shows us that there is no character ‘b’ at position 8001 like we expected.

The Miserly SQL Server

The reason this happens is that SQL Server doesn’t want to store something as VARCHAR(MAX) if none of the variable’s components are defined as VARCHAR(MAX).  I guess it doesn’t want to store something in a less efficient way if there’s no need for it.

However, this logic is flawed since we clearly DO want to store more than 8000 characters.  So what can we do?

Make Something VARCHAR(MAX)

Seriously, that’s it.  You can do something like CAST the single character ‘b’ as VARCHAR(MAX) and your @dynamicQuery variable will now contain 8001 characters:

But casting a single character as VARCHAR(MAX) isn’t very intuitive.

Instead, I recommend casting a blank as VARCHAR(MAX) and prefixing it to the start of your variable string.  Leave yourself a comment for the future and hopefully you’ll remember why this superfluous looking piece of code is needed:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

AT TIME ZONE: The easy way to deal with time zones and daylight savings time

Published on: 2018-03-27

Many people think daylight savings time was created to help align the hours that the sun is up with our waking hours so farmers and school children didn’t have to be outside in the dark.  Or something like that.

Well let me fill you in on a little secret – daylight savings time was actually created by a government works project to ensure that programmers could forever write tedious conditional logic in their date-based queries to handle the date time conversions surrounding time zones and daylight savings time.

While these types of datetime conversions have historically been a pain, SQL Server 2016 introduced the AT TIME ZONE feature makes these types of conversions a breeze.

Defining Time Zone

Let’s say you have a datetime value that you know is encoded in UTC (if you don’t know what timezone your data was originally encoded in you’re out of luck):

Besides naming convention, there’s nothing that tells us that our datetime is in UTC.  But if we know that to be the case, we can use AT TIME ZONE to add an UTC offset to the timestamp to make it official:

See that +00:00  at the end of our value?  That’s our time zone offset – it’s basically telling us how many hours and minutes away from UTC our date is stored in.  With this offset in place, our UTC datetime isn’t encoded only by the variable name – it’s actually encoded in the data itself.

Converting Time Zones

Now that we have a well-defined UTC datetime, we can use AT TIME ZONE to easily convert our data to different time zones.

For example, if we want to convert our UTC datetime to Eastern Standard Time, we can tack on  AT TIME ZONE 'Eastern Standard Time' :

The time portion of our datetime is now showing 7am with the offset indicating we are 4 hours behind UTC.  Easy time zone conversions, yes!

But What About Our Farmer Friends?

So AT TIME ZONE makes it easy to convert between time zones – but how does it handle daylight savings time conversions?

Well, on March 11, 2018 I lost an hour of my life when daylight savings time kicked in at 2am.  Does AT TIME ZONE recognize this theft?

YES! Say so long to conditional conditions that subtract an amount of time based on the date; AT TIME ZONE handles the switch to DST with ease as noted by the offset change from -05:00  to -04:00 .

And just like that, programming for time zone and day light savings time logic just became a little easier.

But I Don’t Live In The Eastern Standard Time Zone!

No problem, in addition to AT TIME ZONE, SQL Server 2016 added this nifty table that will give you the names of all of the time zones it supports:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!