Learn T-SQL With Erik: AT TIME ZONE Performance
Chapters
- 00:00:00 – Introduction
- 00:01:48 – Timezone Performance Issues
- 00:03:59 – Query Plan Analysis and Data Types
- 00:06:27 – Conclusion
Full Transcript
Erik Darling here with Darling Data, and in this video, we’re going to talk about mostly AT TIME ZONE performance, but also some other interesting stuff about AT TIME ZONE. I’m going to be real honest with you, I hate timezone stuff. I hate all of it. I’ve never taken naturally to it. It’s all too weird, and managing it is just such a nightmare. in UTC. Store someone’s timezone, I guess, and deal with it from there. As soon as you have to start mixing things, it gets awful. The absolute pits. Down in the video description, you will find all sorts of helpful links. You can hire me for consulting. You can buy my training. You can even buy the full version of the training that you’re seeing in this video with the coupon code down below. You can become a supporting member of the channel if you feel that the content that you’re getting here is worth $4 a month. And of course, you can continue to ask me office hours questions, and please do always like, subscribe, tell a friend, maybe even several friends if you’re poly-friendulous or something. If you’re in the market for free SQL Server monitoring, you can get mine. Totally free, open source, no email, no phone home, no weirdness. Just a bunch of awesome data collectors getting all the important stuff from your SQL Server, and giving you all the information you need to fix your, well, find and fix your performance problems. Recently launched, recently got some AG monitoring in there, and also recently went Enterprise Edition, and we’ve now got a headless Windows service. You no longer have to worry about not monitoring stuff if the app I still have those. I still have that version, but it’s a headless Windows service backed by a Postgres database. You can monitor hundreds and hundreds of servers for free with it. Gosh, is it pretty darn good. All right, let’s, let’s T-SQL ourselves here. So, at time zone has a lot of weird performance problems. Not weird, very obvious ones once you start looking at it. Basically, it’s bad if you do something like this, and it’s good if you do something like this.
Right? Do, don’t do your date math on your at time zone on a column, because you will beat the crap out of your server. Right? So, I’ve got query plans turned on, and I’m going to run both of these. And I suppose the fun part is that until they finish, we won’t know. We won’t know which one is our problem here. I guess we will, because the results, I lied. So, in this query right here, we are having our where clause where creation date at time zone, Eastern Standard Time is greater than D. And I’ve got even got a recompile hint on here. So, SQL Server, there’s no statistical mystery there. And in this one, I’ve changed it so that I’m just using a time zone that sort of reverses the math, right? So, the minus five UTC to plus five, or actually, depending on what time of year it is, I guess it could be fewer or more hours. But the first query that we see scans the entire clustered index takes 10 seconds doing that, right? And part of that 10 second scan is at time zone calling out to, like, Windows to figure out time zone stuff. Everything, every time is a Windows API call. Right? So, like, it’s tremendously slow there.
And this one is better, but it’s not the plan shape that we want. And it’s not the plan shape that we want, because we were slack with data types, right? Because when you add time zone something, what happens? You don’t, like, so, the creation date column in the comments table is a date time. But when you do this, you add a time zone to something, guess what happens? That’s not a date time anymore. So, we get this query plan shape that I’ve talked about before that happens when you are slack with your data types, right?
So, you would want to write your query like this, and you would want to convert the result to a date time, and then you get the nice simple seek plan like this, right? So, if we just converted to date time without the switch offset here, we’d lose the offset information and the time range, right? And we don’t want to do that, because then we’d get wrong results back.
So, the wrong result would look like this, right? Because if we just say convert date time at time zone this, we lose this, right? That’s the five hours that we want from there.
But if we do the switch offset, use the switch offset function, then we keep that, right? Switch offset adjusts the time zone offset while maintaining the point in time that your data is in. The underlying UTC stuff all stays the same, and it’s really useful for normalizing data to UTC or displaying your user’s local time zone, I guess.
You can even combine it with two date time offset when you need to attach an offset to a date time value, right? So, for our purposes and illustrating that a little bit, this is what we started with, right? If we send that, if we say, if we just add time zone that, then we get the time zone added, which takes five hours, right?
And we switch the offset, we get the five hours in here, and then when we convert that to a date time, right? We lose the time zone portion from these two, then we get the correct time from that. So, just a couple notes about that stuff.
I’ve got a few more videos that will go over time zone things and hopefully help you, because I have just struggled mightily with time zone stuff over the years, getting it to work correctly and getting it to perform well.
So, hopefully, the upcoming videos will also help you if you are also struggling with those things. All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.