Query Store Training – Portugal

I am so excited to announce that I am presenting a full day of Query Store Training, in-person, this September in Lisbon, Portugal! The SQLskills team will be in London for two weeks in September for a set of Immersion Events (IEPTO1, IEAzure, IECAG, and IEPTO2). After I’ve finished my teaching for IEPTO2 I’m heading over to Lisbon for a full day (Friday, September 21, 2018)  on Query Store in advance of SQLSaturday Portugal.

This workshop has continued to evolve since its first inception at the PASS Summit last fall – specifically, I’ve added more content around performance and workload analysis, but every time I get asked a new question that I think is relevant or interesting, it gets added into a slide or demo. You can read the full abstract below, and can purchase your ticket here, and if you have any questions about the workshop please email me or post a comment!

*Note: If you’re interested in the Immersion Events listed above, please know that we probably won’t offer them in Europe again until 2020, so if you’re interested please talk to your manager and get signed up. We would love to see you!

Using Query Store to Easily Troubleshoot and Stabilize Your Workload

– Have you upgraded to SQL Server 2016 or higher, but still have databases using the old Cardinality Estimator?
– Do you know that you have queries with inconsistent performance, but you’re just not sure how to find them, or fix them, quickly?
– Are you tired of flailing around in SQL Server, querying DMV after DMV to figure out the *real* problem with performance?

Query Store can help.

We’ll cover Query Store end-to-end in this full day workshop built using real-world examples based on customer issues resolved over the last two years. You’ll understand how to configure it, what data it captures, and how to use it to analyze performance, find regressions, and force plans. The demos will teach you how to find common patterns in query performance using T-SQL, and how to understand your workload.

This class is applicable for those running SQL Server 2016 or higher (or planning to upgrade), or Azure SQL Database, and will provide practical and applicable information you can use whether you’re a new or veteran DBA, a developer that has to troubleshoot query performance, or an application administrator just trying to keep the system afloat. You’ll learn how to find and leverage important information in Query Store to make solving common performance problems easier the moment you walk back into the office.

Monitoring Space Used by Query Store

Last week I presented a session on Query Store and when talking about the settings I mentioned that monitoring space used by Query Store is extremely important when you first enable it for a database.  Someone asked me how I would do that and as I provided an explanation I realized that I should document my method…because I give the same example every time and I would be nice to have the code.

For those of you not familiar with the Query Store settings, please check out my post which lists each one, the defaults, and what I would recommend for values and why.  When discussing MAX_STORAGE_SIZE_MB, I mention monitoring via sys.database_query_store_options or Extended Events.  As much as I love Extended Events, there isn’t an event that fires based on a threshold exceeded.  The event related to size is query_store_disk_size_over_limit, and it fires when the space used exceeds the value for MAX_STORAGE_SIZE_MB, which is too late.  I want to take action before the maximum storage size is hit.

Therefore, the best option I’ve found is to create an Agent job which runs on a regular basis (maybe every four or six hours initially) that checks current_storage_size_mb in sys.database_query_store_options and calculates the space used by Query Store as a percentage of the total allocated, and then if that exceeds the threshold you set, send an email.  The code that you can put into an Agent job is below.  Please note you want to make sure the job runs in the context of the user database with Query Store enabled (as sys.database_query_store_options is a database view), and configure the threshold to a value that makes sense to your MAX_STORAGE_SIZE_MB.  In my experience, 80% has been a good starting point, but feel free to adjust as you see fit!

Once your Query Store size has been tweaked and stabilized, I would leave this job in place as a safety to alert you should anything change (e.g. someone else changes a Query Store setting which indirectly affects the storage used).

/* Change DBNameHere as appropriate */
USE [DBNameHere]

/* Change Threshold as appropriate */
DECLARE @Threshold DECIMAL(4,2) = 80.00
DECLARE @CurrentStorage INT
DECLARE @MaxStorage INT

SELECT @CurrentStorage = current_storage_size_mb, @MaxStorage = max_storage_size_mb
FROM sys.database_query_store_options

IF (SELECT CAST(CAST(current_storage_size_mb AS DECIMAL(21,2))/CAST(max_storage_size_mb AS DECIMAL(21,2))*100 AS DECIMAL(4,2))
FROM sys.database_query_store_options) >= @Threshold
BEGIN

     DECLARE @EmailText NVARCHAR(MAX) = N'The Query Store current space used is ' + CAST(@CurrentStorage AS NVARCHAR(19)) + 'MB
     and the max space configured is ' + CAST(@MaxStorage AS NVARCHAR(19)) + 'MB,
     which exceeds the threshold of ' + CAST(@Threshold AS NVARCHAR(19) )+ '%.
     Please allocate more space to Query Store or decrease the amount of data retained (stale_query_threshold_days).'

     /* Edit profile_name and recipients as appropriate */
     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQL DBAs',
     @recipients = 'DBAs@yourcompany.com',
     @body = @EmailText,
     @subject = 'Storage Threshold for Query Store Exceeded' ;
END