Query Store is a configuration that is enabled per database, and the plans and stats for queries executed in that database are stored in the database itself. So what does query store do when a query spans more than one database?
Does it record the execution stats in all databases involved or does it store them in one based on some criteria (e.g. the one where the most work occurs)? Or does it somehow proportion them out between the databases?
This was relevant as it crossed my mind that if it records them in multiple database then my query in the above post could be double counting.
Time to test and find out.
I created three databases, Fred, Bert and Ernie. Then a table called Fred in database Fred, and a table called Bert in database Bert. In table Fred I created a bunch of records, then in table Bert I created a much bigger bunch of records:
DROP DATABASE IF EXISTS Fred;
DROP DATABASE IF EXISTS Bert;
DROP DATABASE IF EXISTS Ernie;
CREATE DATABASE Fred;
CREATE DATABASE Bert;
CREATE DATABASE Ernie;
USE Fred;
CREATE TABLE dbo.Fred(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, FredText NVARCHAR(500));
INSERT INTO dbo.Fred(FredText)
SELECT a.name + b.name
FROM sys.objects a, sys.objects b;
USE Bert;
CREATE TABLE dbo.Bert(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, BertText NVARCHAR(500));
INSERT INTO dbo.Bert(BertText)
SELECT a.name + b.name + c.name
FROM sys.objects a, sys.objects b, sys.objects c;
Then I turned on Query Store for all three databases:
USE MASTER;
ALTER DATABASE Fred SET query_store = ON;
ALTER DATABASE Bert SET query_store = ON;
ALTER DATABASE Ernie SET query_store = ON;
Once that was done I concocted a horrible query that was bound to be horrendously slow – so I knew it would be easy to find when I queried the Query Store runtime stats:
SET STATISTICS IO ON
SELECT TOP 100000 *
FROM Fred.dbo.Fred f
INNER JOIN Bert.dbo.Bert b
ON b.BertText LIKE '%' + f.FredText + '%';
I turned STATISTICS IO on so I could see how much work was happening in each database.
I ran the query first in a query window pointing at the Fred database, then I ran my query store query from the previous post (Capture the most expensive queries across your SQL Server using Query Store) to see what had been captured. I made it slightly easier for myself by adding an additional where clause to the cursor so that it only looked at these databases:
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT [name]
FROM sys.databases
WHERE is_query_store_on = 1
AND name IN ('Fred','Bert','Ernie');
I cleared down Query Store for all the databases:
USE MASTER;
ALTER DATABASE Fred SET QUERY_STORE CLEAR;
ALTER DATABASE Bert SET QUERY_STORE CLEAR;
ALTER DATABASE Ernie SET QUERY_STORE CLEAR;
So you can see most of the work occurs in the Bert database, a little in Fred, and none in Ernie.
Now let’s see what query store captured when I ran the query pointing at database Fred:
And pointing at database Bert:
And pointing at database Ernie:
You can see that the figures get recorded against whichever database you are pointing at – regardless of where the data being accessed resides. I left the “TotalLogicalReads %” in the above screen shots so you can see I’m not hiding anything.
This has a few implications. First, I’m happy because it means my “Expensive queries” script isn’t double counting.
Second though, as you can’t turn on query store on in any of the system databases, you won’t be able to capture details for any queries executed with those as the context. That includes ad-hoc queries where the user may connect to master, but execute queries against your other databases.
Fortunately (because Query Store would be pretty pointless if it did) this doesn’t apply to stored procedures.
I’m going to wrap my horrible query into a stored procedure, and deploy it into database Ernie:
USE Ernie;
CREATE PROCEDURE dbo.Horrible
AS
BEGIN
SELECT TOP 100000 *
FROM Fred.dbo.Fred f
INNER JOIN Bert.dbo.Bert b
ON b.BertText LIKE '%' + f.FredText + '%';
END;
Now I clear my Query Stores for the three database one last time. Then I’ll called the stored procedure from database Fred:
USE Fred;
EXEC Ernie.dbo.Horrible;
Here’s what I get from query store now:
So, Query Store logs the execution against database Ernie – where the stored procedure resides, rather than Fred – where it was called from, or Bert – where most of the work was done.
I hope you’ll trust me enough on that that I don’t have to demonstrate all the other combinations!
I’m a big fan of using queries based on the dynamic management view sys.dm_exec_query_stats to identify the most resource hungry queries across a SQL instance. There are lots of versions of this sort of query around the place if you google for “Top 20 queries”.
That approach has some limitations though. First, it is cleared out every time an instance restarts, and second it only keeps figures for currently cached plans, so when a query recompiles, data is lost.
The DMVs provided by SQL Server Query Store solve these issues, as data is persisted in the database over time, so nothing is lost on restarts etc. And one extra thing you gain by using the Query Store DMVs is that you can slice by time interval, for instance if you want to look at the before and after states relating to a change that has been made – or you want to look at an interval where performance degradation has been reported.
Some time ago I wrote a query store version of the “Top 20 queries” query that will produce a ranked list of your most expensive queries – and I’ve ended up using this a lot.
The only downside of using the DMVs for Query Store is that they are per database whereas dm_exec_query_stats is a view across the whole instance. So I had to use a cursor and a temp table, populating the temp table for each database in turn.
Here’s the query:
--Gather and report on most resource hungry queries
DECLARE @Reportinginterval int;
DECLARE @Database sysname;
DECLARE @StartDateText varchar(30);
DECLARE @TotalExecutions decimal(20,3);
DECLARE @TotalDuration decimal(20,3);
DECLARE @TotalCPU decimal(20,3);
DECLARE @TotalLogicalReads decimal(20,3);
DECLARE @SQL varchar(MAX);
--Set Reporting interval in days
SET @Reportinginterval = 1;
SET @StartDateText = CAST(DATEADD(DAY, -@Reportinginterval, GETUTCDATE()) AS varchar(30));
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT [name]
FROM sys.databases
WHERE is_query_store_on = 1;
--Temp table to store the results
DROP TABLE IF EXISTS #Stats;
CREATE TABLE #Stats (
DatabaseName sysname,
SchemaName sysname NULL,
ObjectName sysname NULL,
QueryText varchar(1000),
TotalExecutions bigint,
TotalDuration decimal(20,3),
TotalCPU decimal(20,3),
TotalLogicalReads bigint
);
OPEN curDatabases;
FETCH NEXT FROM curDatabases INTO @Database;
--Loop through the datbases and gather the stats
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
USE [' + @Database + ']
INSERT intO #Stats
SELECT
DB_NAME(),
s.name AS SchemaName,
o.name AS ObjectName,
SUBSTRING(t.query_sql_text,1,1000) AS QueryText,
SUM(rs.count_executions) AS TotalExecutions,
SUM(rs.avg_duration * rs.count_executions) AS TotalDuration,
SUM(rs.avg_cpu_time * rs.count_executions) AS TotalCPU,
SUM(rs.avg_logical_io_reads * rs.count_executions) AS TotalLogicalReads
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
LEFT JOIN sys.objects o
ON q.OBJECT_ID = o.OBJECT_ID
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE rsi.start_time > ''' + @StartDateText + '''
GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000)
OPTION(RECOMPILE);';
EXEC (@SQL);
FETCH NEXT FROM curDatabases INTO @Database;
END;
CLOSE curDatabases;
DEALLOCATE curDatabases;
--Aggregate some totals
SELECT
@TotalExecutions = SUM(TotalExecutions),
@TotalDuration = SUM (TotalDuration),
@TotalCPU = SUM(TotalCPU),
@TotalLogicalReads = SUM(TotalLogicalReads)
FROM #Stats
--Produce output
SELECT TOP 20
DatabaseName,
SchemaName,
ObjectName,
QueryText,
TotalExecutions,
CAST((TotalExecutions/@TotalExecutions)*100 AS decimal(5,2)) AS [TotalExecutions %],
CAST(TotalDuration/1000000 AS decimal(19,2)) AS [TotalDuration(s)],
CAST((TotalDuration/@TotalDuration)*100 AS decimal(5,2)) AS [TotalDuration %],
CAST((TotalDuration/TotalExecutions)/1000 AS decimal(19,2)) AS [AverageDuration(ms)],
CAST(TotalCPU/1000000 AS decimal(19,2)) [TotalCPU(s)],
CAST((TotalCPU/@TotalCPU)*100 AS decimal(5,2)) AS [TotalCPU %],
CAST((TotalCPU/TotalExecutions)/1000 AS decimal(19,2)) AS [AverageCPU(ms)],
TotalLogicalReads,
CAST((TotalLogicalReads/@TotalLogicalReads)*100 AS decimal(5,2)) AS [TotalLogicalReads %],
CAST((TotalLogicalReads/TotalExecutions) AS decimal(19,2)) AS [AverageLogicalReads]
FROM #Stats
--Order by the resource you're most interested in
--ORDER BY TotalExecutions DESC
--ORDER BY TotalDuration DESC
ORDER BY TotalCPU DESC
--ORDER BY TotalLogicalReads DESC
DROP TABLE #Stats;
The script limits itself to looking at databases where query store is enabled.
If you want to bring back more results you can just change the TOP statement, and if you want to look at the results ordered by a different resource (e.g. Reads) then just make sure the relevant ORDER BY clause is uncommented. With other small modifications I find this script useful in a myriad of scenarios. I hope you find it useful too.
Introduced with SQL 2016, Query Store was, probably without doubt, the most anticipated and talked out new feature. In this post we’ll just take a brief look at it, what it is, how you set it running, and what you can use it for. This will be a fairly brief overview – you’d need a book to cover it in detail – but hopefully this will give you a flavour of how useful this will be and how to get started.
What it does, at a base level, is actually quite simple. It just stores information relating to query execution over time.
That information consists of two things: • Execution Plans – the execution plans generated for each query are stored in the query store, and if the plan changes the new plan is also stored. • Performance metrics – information such as CPU consumption, reads and writes, are captured and stored for each query.
This information is aggregated over intervals (default is one hour) so you can see how query performance changes over time.
This isn’t earth shatteringly new, you can already query to find out the execution plan for a query and you can also query to find aggregated performance metrics related to a given query.
The difference is that now a history can be maintained without implementing additional monitoring. Previously the performance metrics would be aggregated as a single total since the last restart of the SQL instance – and would be cleared at the next restart. Now they are persisted and time-sliced so you can actually measure change over time.
The simple activity of storing old execution plans is also profound for performance troubleshooting. Anyone who’s worked with large scale production data will have experienced the issue when a function that was working fine, fairly suddenly starts to develop performance problems.
A common cause of this is what’s known as “plan regression”. Basically this is where the execution plan has changed – and the new one’s just not as good as the old one for most executions. Previously you might suspect this was the cause of an issue you were seeing, but there was no way to easily prove it, now you can use query store to view and compare the old and new plans to verify this. You can even with a click or two force the query to go back to using the old (better) plan – though we hope people won’t overuse this and will try to at least delve into the cause and think about resolving it. There is usually a reason SQL thought the new plan would be better – and a particular plan may work for now but may not always be the best plan in the future as your data changes.
Let’s have a look at these features in little more detail.
Enabling Query Store Query store is a database level configuration. It’s important to understand that, and that the information stored is actually stored within system tables in the database. That means that if you backup and restore the database, the information is retained. Also very importantly, the information is stored asynchronously – so there shouldn’t be any performance impact on the executed queries themselves. There will of course be some overall server overhead at the point the data does get saved, but that shouldn’t be too significant in most cases.
You can enable Query Store for a database through T-SQL (or in your source code) or through the GUI in SSMS. I just want to demonstrate doing this through the GUI so you can see some of the options. Right-click on the database, select properties, and then select the Query Store page all the way at the bottom:
Above you can see Query Store enabled for the WideWorldImporters database, with all default settings.
The first setting is “Operation Mode”. By default this is set to “Off”. To enable Query Store and get it running for a particular database you change it to “Read Write”. Job Done.
The Data Flush interval is how often the query store data gets written to disk – remembered I said this was asynchronous.
The Statistics Collection interval determines the size of the time slices that your query performance metrics get aggregated into.
Then we have some stuff about data retention. It’s worth noting that if your query store fills up and nothing is happening to clear it out then it flips to Read-Only mode and won’t store any more data until space is freed up. The default amount of space set for it is 100MB – that’s not a lot of space so I really can’t see any justification from that point of view for not enabling this feature.
Leaving the “Size Based Cleanup Mode” set to Auto should make sure that old data gets purged if the query store starts to fill up. Above that is the “Query Store Capture Mode” – if you leave that to AUTO it will ignore infrequent queries or those with negligible overhead.
The last setting “Stale Query Threshold” is how long it keeps data for in days. So 30 days for default. I can see it being useful to up this significantly it we want to use Query Store to be able to monitor performance over a long period, but it may depend on how much space Query Store wants to consume for your database – remember the default is 100MB but you can up that to whatever you like.
At the bottom of the properties page you can also see some nice pie charts that show how much of a proportion of your database Query Store has allocated, and how much of that space it is using.
So that’s Query store set up and configured, let’s have a look at a few of the things it gives us.
Query Store in Action and Forcing a Plan I’ve set up Query Store as above in a copy of the WideWorldImporters databases on a SQL 2016 instance. I’ve created a stored procedure that I’m running every two seconds and I’ve set the Statistics Collection Interval in Query Store to 1 minute (rather than an hour) so that I can get some figures and graphs out fairly quickly.
Under the database in SSMS, there is now a Query Store folder where some built in reports reside:
For the sake of this blog post I’m just going to look at a couple of these. Let’s open the “Top Resource Consuming Queries” Report:
You can see a few things here. On the top left is a bar chart of the most expensive queries (you’ll notice one large one and the rest are negligible in comparison – the large one’s my query). You can configure whether you want to look by CPU or Logical Reads amongst other options and whether you want to look at averages, or maximums or minimums. Basically there a whole bunch of ways you can configure your view.
I’ll be honest that I struggled with some of these built-in Query Store reports to get them to show me what I wanted, so expect a bit of playing around to figure things out if you are using this feature.
In the bar chart, the bar highlighted in green is the currently selected query, on the right we can then see a scatter graph of the execution figures for this query across our statistics intervals (remember I’ve set it to have intervals of 1 minute). You can see I’m looking at average logical reads. You will also see that this query was ticking along nicely until about 14:05 when something happened (that was me!) and the logical reads for each execution of the query suddenly shot up. The blobs on the scatter graph have also changed colour at this point and that represents that the query is now using a new execution plan.
Next to this graph is a key telling us which plan each colour of blob represents and if you click on the plan in the key that you want, the plan itself is displayed in the bottom pane. At the moment I’m looking at the original plan (Plan 1). You will notice that the title specifies that it is “not forced”, you’ll also notice a button to the right of the title that gives us the option to “Force Plan”. Let’s just hold off a minute before we do that.
Before we change anything to try and fix the issue with this query, let’s look at the “Regressed Queries” report. This is pretty similar, but you may use it from a different direction. I.e. it may not be one your most expensive queries that has started going bad, so if you look in the Regressed Queries report it will focus on ones for which the execution plan has changed in the interval you are looking at. Again I found it a little challenging to get this report to show me the query I was interested in, some playing around can be required:
You can see here that I have just one big fat bar on my bar chart – as I only have one regressed query in the interval (last 30 minutes) I chose to look at. This can make it easier to identify queries suffering this issue.
I’m going to go back the the previous Resource Consumers report and try and fix the problem. Now, in reality I know what I did and why the query went bad. It was the result of something caused parameter sniffing, which is where, if a stored procedure recompiles, the execution plan that is formed may be different depending on the parameters it is executed with. Basically it forms the best plan for the parameters supplied – but that might not be the best plan for all sets of parameters. In this case I forced the stored procedure to form a plan that was going to be expensive in most cases. More on that in my next set of performance tuning workshops.
That issue would be best fixed in the code of the stored procedure, but in production, turning around a fix may take days and we have the problem right now. So let’s use the Force Plan functionality to fix the symptom – just for the moment.
I select the plan I want, and I click the “Force Plan” button. The effect is immediate and I notice it within minutes because my statistics collection interval is so small. I’ll let it run for a bit and then show you the new graph:
You can see the query has now returned back to healthy (quick) execution. Note the Orange blobs all now have a tick over them to denote that this plan is now forced.
Comparing Plans
A related feature in SQL 2016 is the ability to compare two execution plans to see what’s changed. I haven’t found this that amazing myself when I’ve looked at it, but that’s mainly due to the natural limitations – if two plans are significantly different then something that highlights the differences is just going to highlight the whole thing. However it can be useful at least to have both plans on screen at the same time so you can go back and forth easily.
You don’t need to do this through Query Store – if you right-click on any Execution Plan in SSMS there is now the “Compare ShowPlan” option, and as long as you have the plan you want to compare against saved as a file then you can go ahead. Note that one good thing is that this is an SSMS feature, so as long as you have SSMS 2016 or higher you can use it to compare plans from on earlier versions of SQL Server.
With Query Store you can compare plans directly from the Store. If we go back to one of the reports above, the plans are listed in the key for the scatter graph. You can select more than one by using Shift+Click. Then you can click the button in the toolbar above the scatter graph which has the ToolTip “Compare the Plans for the selected query in separate window.”
Let’s do that for the two plans formed for our query above. The resulting view shows us two views side by side. It may be useful to look at these separately so they fit better on this page. On the left we have:
The area highlighted in Red is where the tool had identified that the two plans are the same. The rest it is not so sure about. All the same it’s a nice visual view just to be able to see what both plans are doing. On the right hand side of the screen you then get this view:
This shows us a comparison of the properties of whichever operator is selected in each plan – note this need not be equivalent operator in each plan. You can select the Nested Loop operator in the top and the Index Scan operator in the bottom and it will show you those – though the comparison may not be that meaningful!
So, this is kind of a useful tool, but don’t expect it to magically solve the process of comparing plans for you. In general too much changes from one plan to the next for it to be that simple – but this may help – a bit…
Query Store Catalog Views
Like everything else in SQL Server – all the information you can access through the GUI in SSMS, is available directly through the use of system catalogs and views. So if you want to examine the information in a way that isn’t supported by the built in reports then you can just go ahead and write your own queries.
Query store is a great feature. It’s not rocket-science but it is very useful. In particular it massively aids the investigation of production issues, saving time for those troubleshooting them at exactly the point they need time saving – right when everything’s hitting the fan.
It is also very useful for monitoring performance over time and being able to keep ahead of scalability issues with specific queries.
The methods for forcing a plan are also excellent for quick fixes – but try not to overuse them. It is possible to force plans in earlier versions of SQL – but tricky, so people usually just fixed the code. Forcing plans can end up being a case of treating the symptoms rather than the cause – and can lead to other problems later on.
This is something I touched on back in 2017 a little after the Live Query Statistics feature was introduced with SQL 2016, but I was using the functionality this morning and felt like it was worth a reminder.
You can use Live Query Stats to check on the progress of an executing query – and you can do it through the GUI in SSMS.
I created a long running query for a demo, and after 15 minutes I was still waiting for it to finish. That was a bit longer than I intended. Should I kill it – or did I just need to wait a few more minutes for it to complete.
You can check this quickly via the Activity Monitor:
Find the query you are interested in in the processes list:
Right-click and select “Show Live Execution Plan”. That will show you something like this:
I can see from this that my query is about 83% complete, so maybe I’ll just wait a little longer. Note that this is a live view, so the numbers keep updating. If I want I can watch the progress.
This is against a SQL 2019 instance and is the out of the box behaviour. Before SQL 2019 you had to enable trace flag 7412 if you wanted this to work:
One of the powerful aspects of Query Store is the ability to directly query the DMVs for details of historical executions and performance.
A key view for this is sys.query_store_runtime_stats (but also sys.query_store_runtime_stats_interval).
If you’re querying these views to look at what was happening during a particular time period then it’s important to understand that the dates and times are stored according to UTC time (which is equivalent to GMT with no adjustment for daylight savings times).
You can see this if you look at a few rows from the view:
SELECT runtime_stats_id, first_execution_time, last_execution_time
FROM sys.query_store_runtime_stats;
Though it will be more obvious to you if you’re in a time zone other than the UK.
The datetimes are stored as DATETIMEOFFSET which you can see from the +00:00 at the end of each entry. DATETIMEOFFSET allows you to store both the datetime as well as the timezone being used.
This means that if you’re querying against these columns you need to convert the values you are looking for to UTC first. You can do that my making sure you use GETUTCDATE() instead of GETDATE(), or if you are using specific local times you can use the AT TIME ZONE function e.g.
SELECT CAST('2019-08-21 11:50:40.400 +9:00' AS datetimeoffset) AT TIME ZONE('UTC');
I’m a big fan of storing all datetimes as the UTC version. It avoids things going out of sequence due to daylight saving changes – and can be helpful in mitigating problems when you have application users in multiple countries accessing the same database.
I’ll admit I might be biased though as – being based in the UK – UTC is the same as my local time half the year.
I had a server that looked like it had been suffering from memory contention. I wanted to see what queries were being run that had high memory requirements. The problem was that it wasn’t happening right now – I needed to be able to see what had happened over the last 24 hours.
Enter Query Store. In the run-time stats captured by Query Store are included details relating to memory.
I decided to use the column max_query_max_used_memory from sys.query_store_runtime_stats. In books online this is defined as:
Maximum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval.
Here’s the script, it collates figures across all databases that have Query Store enabled and returns the top 50 queries with the highest memory grants. This is looking over the last 24 hours, but you can easily modify that to look at details for any interval you are interested in:
--Gather and report on most memory hungry queries
DECLARE @Reportinginterval int;
DECLARE @Database sysname;
DECLARE @StartDateText varchar(30);
DECLARE @TotalExecutions decimal(20,3);
DECLARE @TotalDuration decimal(20,3);
DECLARE @TotalCPU decimal(20,3);
DECLARE @TotalLogicalReads decimal(20,3);
DECLARE @SQL varchar(MAX);
--Set Reporting interval in days
SET @Reportinginterval = 1;
SET @StartDateText = CAST(DATEADD(DAY, -@Reportinginterval, GETUTCDATE()) AS varchar(30));
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT [name]
FROM sys.databases
WHERE is_query_store_on = 1
AND state_desc = 'ONLINE';
--Temp table to store the results
DROP TABLE IF EXISTS #Stats;
CREATE TABLE #Stats (
DatabaseName sysname,
SchemaName sysname NULL,
ObjectName sysname NULL,
QueryText varchar(1000),
MaxMemoryGrantMB decimal(20,3)
);
OPEN curDatabases;
FETCH NEXT FROM curDatabases INTO @Database;
--Loop through the datbases and gather the stats
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
USE [' + @Database + ']
INSERT INTO #Stats
SELECT
DB_NAME(),
s.name AS SchemaName,
o.name AS ObjectName,
SUBSTRING(t.query_sql_text,1,1000) AS QueryText,
(MAX(rs.max_query_max_used_memory)/128) AS MaxMemoryMB
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
LEFT JOIN sys.objects o
ON q.OBJECT_ID = o.OBJECT_ID
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE rsi.start_time > ''' + @StartDateText + '''
GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000)
OPTION(RECOMPILE);'
EXEC (@SQL);
FETCH NEXT FROM curDatabases INTO @Database;
END;
CLOSE curDatabases;
DEALLOCATE curDatabases;
--Report Results
SELECT TOP 50
DatabaseName,
SchemaName,
ObjectName,
QueryText,
MaxMemoryGrantMB
FROM #Stats
WHERE QueryText not like 'INSERT INTO #Stats%' --Exclude current query
ORDER BY MaxMemoryGrantMB DESC;
DROP TABLE #Stats;
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.
I’d just completed by post on CMS when I realised I’ve blogged about a few of my favourite tools in the past and that this would be a good opportunity to share them again in a quick post. So, here’s my second of two T-SQL Tuesday posts.
First we have these two great tools:
Statistics Parser – If you do performance tuning and you’re not using this then you’re missing out. Formats and summarises the results of your STATISTICS IO and TIME commands. Big shout to Richie Rump for creating this – I use it so much I just need to type “st” in my browser search and it comes straight up!
Live Query Stats – Watch an animated execution plan while your query executes. I’ll admit I’ve not used this a lot but I still think it’s really cool tool.
And then a few different ones around the same tool:
Query Store – track query performance and plan changes over time as well as forcing SQL to use the plan you want. I’m lucky to have quite a bit of stuff on SQL 2016 and Query Store is great! I was just using it minutes ago to look at how a performance fix put in last week has improved things.
Clone Database with Query Store – If you want to see how your queries would behave in your production environment then Clone Database copies out the stats used to generate the plan. With SQL 2016 you can also a copy of your query store data so you can query away at it happily in your dev environment.
Query Store is a configuration that is enabled per database, and the plans and stats for queries executed in that database are stored in the database itself. So what does query store do when a query spans more than one database?
Does it record the execution stats in all databases involved or does it store them in one based on some criteria (e.g. the one where the most work occurs)? Or does it somehow proportion them out between the databases?
This was relevant as it crossed my mind that if it records them in multiple database then my query in the above post could be double counting.
Time to test and find out.
I created three databases, Fred, Bert and Ernie. Then a table called Fred in database Fred, and a table called Bert in database Bert. In table Fred I created a bunch of records, then in table Bert I created a much bigger bunch of records:
DROP DATABASE IF EXISTS Fred;
DROP DATABASE IF EXISTS Bert;
DROP DATABASE IF EXISTS Ernie;
CREATE DATABASE Fred;
CREATE DATABASE Bert;
CREATE DATABASE Ernie;
USE Fred;
CREATE TABLE dbo.Fred(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, FredText NVARCHAR(500));
INSERT INTO dbo.Fred(FredText)
SELECT a.name + b.name
FROM sys.objects a, sys.objects b;
USE Bert;
CREATE TABLE dbo.Bert(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, BertText NVARCHAR(500));
INSERT INTO dbo.Bert(BertText)
SELECT a.name + b.name + c.name
FROM sys.objects a, sys.objects b, sys.objects c;
Then I turned on Query Store for all three databases:
USE MASTER;
ALTER DATABASE Fred SET query_store = ON;
ALTER DATABASE Bert SET query_store = ON;
ALTER DATABASE Ernie SET query_store = ON;
Once that was done I concocted a horrible query that was bound to be horrendously slow – so I knew it would be easy to find when I queried the Query Store runtime stats:
SET STATISTICS IO ON
SELECT TOP 100000 *
FROM Fred.dbo.Fred f
INNER JOIN Bert.dbo.Bert b
ON b.BertText LIKE '%' + f.FredText + '%';
I turned STATISTICS IO on so I could see how much work was happening in each database.
I ran the query first in a query window pointing at the Fred database, then I ran my query store query from the previous post (Capture the most expensive queries across your SQL Server using Query Store) to see what had been captured. I made it slightly easier for myself by adding an additional where clause to the cursor so that it only looked at these databases:
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT [name]
FROM sys.databases
WHERE is_query_store_on = 1
AND name IN ('Fred','Bert','Ernie');
I cleared down Query Store for all the databases:
USE MASTER;
ALTER DATABASE Fred SET QUERY_STORE CLEAR;
ALTER DATABASE Bert SET QUERY_STORE CLEAR;
ALTER DATABASE Ernie SET QUERY_STORE CLEAR;
So you can see most of the work occurs in the Bert database, a little in Fred, and none in Ernie.
Now let’s see what query store captured when I ran the query pointing at database Fred:
And pointing at database Bert:
And pointing at database Ernie:
You can see that the figures get recorded against whichever database you are pointing at – regardless of where the data being accessed resides. I left the “TotalLogicalReads %” in the above screen shots so you can see I’m not hiding anything.
This has a few implications. First, I’m happy because it means my “Expensive queries” script isn’t double counting.
Second though, as you can’t turn on query store on in any of the system databases, you won’t be able to capture details for any queries executed with those as the context. That includes ad-hoc queries where the user may connect to master, but execute queries against your other databases.
Fortunately (because Query Store would be pretty pointless if it did) this doesn’t apply to stored procedures.
I’m going to wrap my horrible query into a stored procedure, and deploy it into database Ernie:
USE Ernie;
CREATE PROCEDURE dbo.Horrible
AS
BEGIN
SELECT TOP 100000 *
FROM Fred.dbo.Fred f
INNER JOIN Bert.dbo.Bert b
ON b.BertText LIKE '%' + f.FredText + '%';
END;
Now I clear my Query Stores for the three database one last time. Then I’ll called the stored procedure from database Fred:
USE Fred;
EXEC Ernie.dbo.Horrible;
Here’s what I get from query store now:
So, Query Store logs the execution against database Ernie – where the stored procedure resides, rather than Fred – where it was called from, or Bert – where most of the work was done.
I hope you’ll trust me enough on that that I don’t have to demonstrate all the other combinations!
I’m a big fan of using queries based on the dynamic management view sys.dm_exec_query_stats to capture the most resource hungry queries across a SQL instance.
That approach has some drawbacks though. First, it is cleared out every time an instance restarts, and second it only keeps figures for currently cached plans, so when a query recompiles, data is lost.
It was some time ago I blogged about SQL Server Query Store but one of the great features of it is that performance stats are stored in the database, so they aren’t lost in either of the above scenarios.
So I wanted to write a query store equivalent that will produce a ranked list of your most expensive queries.
The only downside is that the DMVs for Query Store are per database whereas dm_exec_query_stats is a view across the whole instance. So I had to use a cursor and a temp table, populating the temp table for each database in turn.
Anyway, enough preamble – here’s the query:
--Gather and report on most resource hungry queries
DECLARE @Reportinginterval int;
DECLARE @Database sysname;
DECLARE @StartDateText varchar(30);
DECLARE @TotalExecutions decimal(20,3);
DECLARE @TotalDuration decimal(20,3);
DECLARE @TotalCPU decimal(20,3);
DECLARE @TotalLogicalReads decimal(20,3);
DECLARE @SQL varchar(MAX);
--Set Reporting interval in days
SET @Reportinginterval = 1;
SET @StartDateText = CAST(DATEADD(DAY, -@Reportinginterval, GETUTCDATE()) AS varchar(30));
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT [name]
FROM sys.databases
WHERE is_query_store_on = 1;
--Temp table to store the results
DROP TABLE IF EXISTS #Stats;
CREATE TABLE #Stats (
DatabaseName sysname,
SchemaName sysname NULL,
ObjectName sysname NULL,
QueryText varchar(1000),
TotalExecutions bigint,
TotalDuration decimal(20,3),
TotalCPU decimal(20,3),
TotalLogicalReads bigint
);
OPEN curDatabases;
FETCH NEXT FROM curDatabases INTO @Database;
--Loop through the datbases and gather the stats
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
USE [' + @Database + ']
INSERT intO #Stats
SELECT
DB_NAME(),
s.name AS SchemaName,
o.name AS ObjectName,
SUBSTRING(t.query_sql_text,1,1000) AS QueryText,
SUM(rs.count_executions) AS TotalExecutions,
SUM(rs.avg_duration * rs.count_executions) AS TotalDuration,
SUM(rs.avg_cpu_time * rs.count_executions) AS TotalCPU,
SUM(rs.avg_logical_io_reads * rs.count_executions) AS TotalLogicalReads
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
LEFT JOIN sys.objects o
ON q.OBJECT_ID = o.OBJECT_ID
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE rsi.start_time > ''' + @StartDateText + '''
GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000)
OPTION(RECOMPILE);';
EXEC (@SQL);
FETCH NEXT FROM curDatabases INTO @Database;
END;
CLOSE curDatabases;
DEALLOCATE curDatabases;
--Aggregate some totals
SELECT
@TotalExecutions = SUM(TotalExecutions),
@TotalDuration = SUM (TotalDuration),
@TotalCPU = SUM(TotalCPU),
@TotalLogicalReads = SUM(TotalLogicalReads)
FROM #Stats
--Produce output
SELECT TOP 20
DatabaseName,
SchemaName,
ObjectName,
QueryText,
TotalExecutions,
CAST((TotalExecutions/@TotalExecutions)*100 AS decimal(5,2)) AS [TotalExecutions %],
CAST(TotalDuration/1000000 AS decimal(19,2)) AS [TotalDuration(s)],
CAST((TotalDuration/@TotalDuration)*100 AS decimal(5,2)) AS [TotalDuration %],
CAST((TotalDuration/TotalExecutions)/1000 AS decimal(19,2)) AS [AverageDuration(ms)],
CAST(TotalCPU/1000000 AS decimal(19,2)) [TotalCPU(s)],
CAST((TotalCPU/@TotalCPU)*100 AS decimal(5,2)) AS [TotalCPU %],
CAST((TotalCPU/TotalExecutions)/1000 AS decimal(19,2)) AS [AverageCPU(ms)],
TotalLogicalReads,
CAST((TotalLogicalReads/@TotalLogicalReads)*100 AS decimal(5,2)) AS [TotalLogicalReads %],
CAST((TotalLogicalReads/TotalExecutions) AS decimal(19,2)) AS [AverageLogicalReads]
FROM #Stats
--Order by the resource you're most interested in
--ORDER BY TotalExecutions DESC
--ORDER BY TotalDuration DESC
ORDER BY TotalCPU DESC
--ORDER BY TotalLogicalReads DESC
DROP TABLE #Stats;
The script limits itself to looking at databases where query store is enabled.
If you want to bring back more results you can just change the TOP statement, and if you want to look at the results ordered by a different resource (e.g. Reads) then just make sure the relevant ORDER BY clause is uncommented.
I consider this something that will evolve over time, so if you have any suggestions of things you think I should change or add then let me know.
