Have you ever had a query that runs frequently start to randomly go slower? We all have and without the proper tools and knowledge, this can be hard to figure out because there can be so many different reasons. In this blog post, our goal is to make your query optimization in SQL Server a lot better and easier for you.
How does a SQL Server query get proccessed?
This is a great question. All queries get put into a queue if they are not available to run. If there are any available schedulers then the query will start processed. We call this the RUNNING state. If the RUNNING query needs to pause to obtain any required resource like pages from disk or is blocked this query will have SUSPENDED state. Once the required pause is no longer needed, the query status will go back into the queue and its status will now be “RUNNABLE”. This queue is first in first out (FIFO) and once the query moves up the queue and a schedule is free the query is back in motion and has the “RUNNING” status again. This circle can be repeated multiple times until the query execution completes.
Hopefully, our flow process of the status of a query will make sense below.
How do we see the waits for my query?
Now that you understand how a query waits. Lets go ahead and look at how we recommend reviewing this data. Before SQL Server 2017 you had to a bit of work with extended events and dynamic management views. Now we can use our best friend, the query store.
In this video below you will learn how to figure out what waits occurred when your query was executed inside of SSMS without writing a single line of code.
What are your thoughts about getting quick access to waits going on for your queries? Got
Can I get the demo code?
We know some of you love to replay demos and we like to give you the code to do so when you have free time. If you have any questions with the demo below fill free to throw your questions our way.
USE [master];
GO
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 60,
QUERY_CAPTURE_MODE = ALL, /* For demo purposes.
most likely don't want to capture all in prod */
INTERVAL_LENGTH_MINUTES = 1);
GO
USE [WideWorldImporters];
GO
DBCC DROPCLEANBUFFERS; -- For demo to get PAGEIOLATCH waits
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL;
/* Verify Clean Starting Point */
select * from sys.query_store_plan
select * from sys.query_store_query
select * from sys.query_store_runtime_stats
select * from sys.query_store_wait_stats /* New in SQL 2017 */
GO
USE [WideWorldImporters];
GO
/* Load all records from disk into memoery
because we dropped clean buffers */
SELECT * FROM Sales.Invoices
/* 102 records */
SELECT * FROM Sales.Invoices
WHERE CustomerID = 832
/* Create locking example.
Begin explicit transaction
but don't commit or rollback yet */
BEGIN TRANSACTION
UPDATE Sales.Invoices
SET DeliveryInstructions = 'Data Error'
WHERE CustomerID = 832
-- Hold off on rolling back until after the block
/* Now back in another window,
wait a few seconds and then
execute the following statement */
USE [WideWorldImporters];
GO
SELECT COUNT(DeliveryInstructions)
FROM Sales.Invoices
WHERE CustomerID = 832
-- After a few seconds - roll back the other transaction
/* Now Rollback and show other window has data */
ROLLBACK TRANSACTION
/* Flushes the in-memory portion of the Query Store data to disk.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-flush-db-transact-sql?view=sql-server-2017
*/
USE [WideWorldImporters];
GO
EXECUTE sp_query_store_flush_db;
GO
/*
Look at all queries in QS using Sales.Invoices.
Notice our UPDATE is missing??
We Rolled it BACK
*/
SELECT [q].[query_id], *
FROM sys.query_store_query_text AS [t]
INNER JOIN sys.query_store_query AS [q]
ON [t].query_text_id = [q].query_text_id
WHERE query_sql_text LIKE '%Sales.Invoices%';
/* get the query_id from statement above
use it to get the plan_id so we can look at its waits */
declare @queryID bigint = 7
SELECT *
FROM sys.query_store_plan
WHERE query_id = @queryID;
/*Get all waits for that plan. */
begin
declare @planid int = 7
select * from sys.query_store_runtime_stats_interval
SELECT * --wait_category_desc, avg_query_wait_time_ms
FROM sys.query_store_wait_stats
WHERE plan_id = @planid AND
wait_category_desc NOT IN ('Unknown')
ORDER BY avg_query_wait_time_ms DESC;
end
GO
/* Make sure you filter for time intervals needed */
declare @planid int = 7
SELECT wait_category_desc, avg_query_wait_time_ms
FROM sys.query_store_wait_stats
WHERE plan_id = @planid AND
wait_category_desc NOT IN ('Unknown')
ORDER BY avg_query_wait_time_ms DESC;
GO
/* Look at all waits in system */
select * from sys.query_store_wait_stats
