sp_QuickieStore: Time Zones, Time Zones, Time Zones
Video Summary
In this video, I delve into the intricacies of searching Query Store data accurately in SQL Server. After receiving feedback from the Darling Data community, particularly a valued member named Rick, I realized that query store data is stored in UTC but isn’t being searched correctly due to time zone issues. This led me to develop SP_QuickieStore, which not only displays all query store dates in your local time but also allows you to search through query store data using the correct UTC time. The process involved a lot of trial and error, especially with understanding how SQL Server handles date and time functions, as well as dealing with the limitations of older SQL Server versions. Despite these challenges, I hope this script makes it easier for everyone to work with Query Store data accurately.
Full Transcript
Erik Darling here with Darling Data. And I’m a bit of a mess. It’s been a week. A lot going on. Busy, busy, busy. Getting a lot of things ready. Getting a lot of work done. And working on my free open source scripts that I provide to you, the SQL community writ large. I’m not sure what that means. I saw it in a book recently and I really liked the way it sounded writ large. But one thing that I’ve been putting a lot of work into recently is SP underscore QuickieStore because it was brought to my attention by a valued member, the Darling Data community, a fellow named Rick, that I was not, that query store data is stored in UTC. But we were not searching in UTC values. And I know that probably sounds like a big gigantic deal, like a big oversight. But the query store GUI also doesn’t do that correctly. I profiled every single query from all of the views that you can run in query store. That’s the high variance, regress, all those things. And I’ve been doing that for a lot of time.
And this is what they all look like. I mean, this isn’t exactly what they all look like, but they all do roundabout the same thing where you see this where clause written completely backwards, where first execution time is not greater than the end time. And the last execution time is not less than the start time. But the parameter values that get passed in there are never converted to UTC for accurate searching. And, you know, I saw that every time I looked at what query store was running. And I thought, well, maybe something magical happens. Maybe this is the right way to do things because there’s not a lot out there about the right way to search query store. It’s not even talked about a whole lot. I think only Aaron Stellato has a couple blog posts about query store dates being stored in UTC. But as I was working on quickie store to do two things. One is display all of the query store dates to you in your local time. And two is to search through query store in UTC time.
What it became really hard to do was figure out a way for you to pass in dates and times without the offset and still search accurately. Now, if you’re just looking for a whole day of data, it might matter a little bit less. All right. If you were just searching for this whole span of data, it might not matter a little bit that the UTC offset is not precise. It might, depending on how far away, like the further away you get from UTC, obviously, the more it would matter. But, you know, if I am finding everything for the full day of June 27th, it’s a little bit less critical that, you know, I have everything to within UTC perfect time.
Now, where things get difficult is when, like I was saying, when you don’t pass in an offset, it becomes harder to search accurately. Now, sysdateTime, the function does not return with an offset, a time zone offset. So if I run this query and I look at the results, I’m going to keep this up here, we have the start date, which is, for me, right now, like accurate.
Right? That’s like the actual time, but SQL Server is inferring, it is implicitly casting my current time with UTC time. I can guarantee you that this time is correct for me here. You can see down in the, actually, you can’t see, it’s a little bit under my armpit, but right about there, it says that it’s 8 p.m.
And we ran that at 16.59, which is just about 8 p.m. So SQL Server is taking 8 p.m., thinking that it’s UTC time. And then when I say, let’s go back to Pacific Standard Time, I mean, we can go back seven hours from now, but that’s not right, because Pacific is four hours from Eastern, it’s seven hours from UTC, so that’s wrong.
And then when we, if we try to cast it back to UTC, it just goes back to my time, Eastern time. So that is a really difficult problem to solve, and I haven’t quite figured out a way to do that. Now, it all changes when you use sysDateTimeOffset, because this returns with the correct date time offset for where I am.
So my SQL Server is in Pacific time, so this is correct here. And when I go and search, and when I go and cast this at the time zone UTC, now the reason my SQL Server is in Pacific time is because I installed Windows on a VM, I didn’t really pay much attention, and then bingo, bango, many years later, I found out it was in Pacific time, so that was fun.
I guess I don’t care about my VMs all that much, but slightly embarrassing. But anyway, so my date time offset is here, right, this is correct from UTC, and when I cast that to UTC, I do get the correct date time where it is officially a little bit past tomorrow.
So it’s 1 a.m. UTC, which is 7 hours from Pacific and 4 hours from Eastern, and you can tell, again, by the little clock under my armpit where it says 8 o’clock and 4 hours, it’ll be, I actually know it’s 5 because of the daylight savings, but, you know.
These date functions aren’t daylight savings aware, either. So the picture gets even foggier as we go through. And I know, this is all very boring and confusing, and it doesn’t give you a lot of faith in SQL Server or Microsoft or the summer interns who write these date functions.
Now, SQL Server 2022 does introduce a couple things that would make life easier, but, of course, ain’t no one in the world using SQL Server 2022 in a widespread enough way for me to only write scripts that work with SQL Server 2022.
So here I am writing scripts that have to work for folks using Query Store going back to SQL Server 2016, which, you know, I’d rather be able to help them than not, but, you know, even if you were on 2019, this wouldn’t help.
These functions are so new that the SSMS parser does not recognize them. We get these wonderful little red squiggly lines here where SQL Server, or rather SSMS says, I don’t know who you are.
But if we had these available, then we could maybe do something to figure out which time zone people are in easily and use that to correct dates passed in without the time part. But even that would get really challenging for a number of reasons that, trust me, I’d bang my head off and I couldn’t quite figure out.
Now, older versions of SQL Server, you can read the registry with XP Reg Read, and you can also get Pacific Standard Time back. However, the last thing I want to have to deal with is explaining to someone why I need to read the registry in order for them to query Query Store data.
So this is out. There’s some XP Reg Read stuff that I wrote into the Blitz scripts that I often have to not use when working with clients because they’re like, get out of my registry.
I’m like, I’m just reading it. And they’re like, no. So I found myself in a tough situation. But in order to make it as easy on you as possible, you can at least use the DateTimeOffset function here to figure out what your DateTimeOffset is.
So when you want to search accurately through Query Store data, which SP underscore Quickie Store does, because in my where clauses, I cast my start date at UTC time so that my searches are accurate. So anytime that there’s a start date parameter in the Dynamic SQL, it’s cast a time zone UTC.
That’s up here. That’s up here, too, where you can see UTC all over the place. And, well, there’s this one up here, but that just goes back.
This one just uses sys UTC date time to go back and search there. So, you know, that’s about that. Maybe I should use sys date time offset for that.
I haven’t quite decided yet. We’ll figure it out. But I think sticking with UTC is the smart thing to do there. Anyway, when you want to search through Query Store to look for stuff, this is what you have to do.
Put in the date, you put in the time, and you put in the offset, which you can find out, again, by running this and looking at what the sys date time offset is. So if I search through Query Store using this, I get nothing on purpose. And I want you to see that I get nothing because I am searching accurately.
And when I expand my search by two minutes, I get back a whole bunch of stuff. And since I’m showing the, using the time zone parameter here, we’ll display times in the current time zone. You can see that information if you go over here.
We have the first execution time in your time zone. All right. That’s helpful. All right.
We can see that that is indeed the correct Pacific time offset for what I’m looking for. And we see the, well, I search on the last execution time. So that’s going to be this column.
But you can see all of that that happened at 5.04 falls into the window of 5 to 5.05 that I was looking for. I also show you the UTC times just in case you ever would need them. I, that was first suggested to me by the lovely and talented Sean Ghilardi.
And I said, hey, man, that’s weird. And then as I was troubleshooting this and I, and I was trying to really figure out the UTC thing, I was like, oh, yeah, I’m just going to show. So, so, so thank you.
Thank you, Sean Ghilardi for, for pointing that out that I should do that because it ended up being a very valuable troubleshooting step for me. And I decided to keep it in there just in case it might ever be a valuable troubleshooting step for you too. Cool.
So, the time zone parameter in SP Quickie Store does validate the time zone if, when you use it, if you don’t, if you leave it blank, it will default to UTC. It seems reasonable to me since everything is stored in UTC. But if you try to use a time zone that does not exist in the DMV sys.timezoneinfo, then I will yell at you and say, please check sys.timezoneinfo for a valid list.
Cool. So, if you ever need to learn about how to use any of the stored procedures that I write, you can use the help parameter.
The help parameter will describe all of the parameters that you can pass in and their various uses. I’ve shown this in other videos before and for some reason this mouse will not grab onto this thing where I want it to. So, we’re just going to have to deal with a little bit of a scroll bar because I don’t feel like making you sit here and watching me wrestle with trying to grab onto this thing and shrink it.
It is just not doing it. Oh, there it is. Nope, not happening.
Nope, not happening. Nope, not happening. Nope, not happening.
So, trying to make it as easy on you to troubleshoot this stuff as possible. And also this helpful note to use the sys.timezoneinfo DMV if you need to figure out what to pass in for a time zone above. I would I could do more to automate the timezone stuff but oops not Tim zone sister time zone info if you if you look at what’s in here there like I was like I told with the idea of being like okay well I could look up the sys date time offset of someone’s local time and I could go with that right but there are there are a lot of date time offsets that have the same value minus seven for a bunch of stuff minus an eight and nine for a bunch of stuff and then like you think about crossing day boundaries think about you know people who are on half hours and stuff and it just became incredibly difficult and really just all too much to bear but anyway spquickiestore now does what the query store DMVs do not do when it lets you search the correct UTC time for queries in query store it also display it also allows you to display times to you in the time zone of your choosing as long as that time zone exists in the sys.timezoneinfo DMV and uh well don’t we all feel like more complete people now at least I do anyway um it is nearly 8 15 p.m.
here scenic New York uh I’m gonna go watch baseball uh I wanted to get this video out because I would have just kept me up last night and uh you know I don’t like when things keep me up and when things keep me up I assume they’ve kept you up at some point maybe not to the same degree or maybe I just care too much it’s something I’ve frequently been accused of uh anyway uh thank you for watching hope you learned something hope you enjoyed yourselves if you decide that this video is worthy of your respect then give me the old thumbs up if you decide that you want to learn more about how arduous sql server is uh you can subscribe to my channel and uh of course you can get all of my open source scripts over on github that’s about it thank you for watching
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.