It’s been a while, but I’m finally participating in T-SQL Tuesday again! This month Erik Darling (blog|twitter) is hosting. Ever looked at a piece of code and gotten excited or maybe a bit upset? I know I have, repeatedly. Well, that’s what Erik would like to hear about this month.
I’ve generally written about these as they happen so this is going to have to be something I’ve learned about more recently. Fortunately, a new job means learning lots of new stuff. Before I tell you what the code is I need to point something out. My memory is iffy at best. When I thought of what I wanted to write about I’d completely forgotten that Erik was hosting. So no, I’m not kissing up.
A very popular tool at my new office is sp_quickiestore, written by, you guessed it, Erik. Query Store is an awesome tool that stores information about query performance and usage. sp_quickiestore is a quick and easy tool to search through this information.
EXEC sp_quickiestore @database_name = 'DBName', @Start_Date = '7/8/2023';
In this particular case, I’m pulling just the queries that have been run so far today for a specific database. There are also options that let you specify an end date, the SP name, text in the query, etc. Basically, anything you can think of to get you to the data you want.
Then once there you have, right up front, the query text, query plan, and some other basic information. As you scroll right you get a list of the top waits, first and last execution times, duration, CPU time, etc, etc. Heck, it even tells you how much tempdb space the query used. (Which has come in way handier than I ever expected.)
In other words, if you are using Query Store, this stored procedure will let you slice, dice, chop, or julienne your data to make it quick and easy to get to and use.