For this month’s T-SQL Tuesday Bert Wagner invites us to share SQL scripts we’ve written that we’d hate to live without.

Code You Would Hate To Live Without (T-SQL Tuesday #104 Invitation)

I don’t know that I’d go quite as far as “hate”, but I’ve been getting a lot of re-use out of a script I wrote and shared on this blog last year.

I’m lucky enough to have a number of SQL 2106 instances with Query Store enabled for the user databases, in particular that includes a performance test environment for one of the applications I work on.

In the past I’ve always been a sucker for those queries that you can use to find the most expensive queries on your SQL instance. Be that in terms of CPU, IO, or just longest running.

I wanted to use Query Store for an improved version of those scripts I’d been using, as Query Store retains statistics when a server restarts – or a plan gets cleared out of the cache.

With Query Store it’s easy enough to get figures per database, but I wanted to be able to view across all databases – and that was why I wrote the script.

Here’s the original post (and script):

Capture the most expensive queries across your SQL Server using Query Store

I find this useful as it is, but also I often cut it about to ask slightly different questions of the query store data.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.