Find timeouts using Query Store

 Kendra Little has a great blog post that explains a lot about timeouts, how to create one on purpose, and various ways to find them. She includes a simple query to get the essential information about each timeout from Query Store.

However, I often find myself in the "too much is never enough" mode, and have been using a more complex script that provides significantly more detail. Kendra's post inspired me to Optimize my script, clean it up a bit, and post it here.

/* Find timeouts using Query Store.sql

Purpose:        This will get data from Query Store about statement timeouts. Two results sets are provided,
                one with statement text and plans, and one without. The latter is for easy sharing (pasting 
                into Excel, etc.).
Author:         Mark Freeman (@m60freeman)
Last Edited:    2022-03-11
Instructions:   Provide a value for @QueryHash or set it to NULL to revieve data for all statements, then 
                execute.
Compatibility:  SQL Server 2016+, Azure SQL Database
License:        This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 
                Unported License.
                    http://creativecommons.org/licenses/by-nc-sa/3.0/

References:
    https://blogs.technet.microsoft.com/dataplatform/2017/03/02/query-store-how-it-works-how-to-use-the-new-query-store-dmvs
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql
    https://sqlperformance.com/2017/11/waits-2/wait-statistics-query-store
    https://davegugg.wordpress.com/2015/01/29/creating-a-comma-separated-list-with-for-xml-path-and-stuff
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @QueryHash  UNIQUEIDENTIFIER    --= 0xE99B152E01DBEF83
;

CREATE TABLE #Result (
    ExecutionType           NVARCHAR(60)        NULL,
    FirstExecutionTime      DATETIMEOFFSET(7)   NOT NULL,
    LastExecutionTime       DATETIMEOFFSET(7)   NOT NULL,
    LastQueryExecutionTime  DATETIMEOFFSET(7)   NOT NULL,
    IntervalStart           DATETIMEOFFSET(7)   NOT NULL,
    IntervalEnd             DATETIMEOFFSET(7)   NOT NULL,
    MaxDurationSecs         DECIMAL(18, 4)      NOT NULL,
    AvgDurationSecs         DECIMAL(18, 4)      NOT NULL,
    ExecutionCount          BIGINT              NOT NULL,
    ObjectName              NVARCHAR(128)       NULL,
    QueryHash               BINARY(8)           NOT NULL,
    QueryId                 BIGINT              NOT NULL,
    StatementHandle         VARBINARY(44)       NOT NULL,
    StatementType           VARCHAR(6)          NULL,
    StmtText                NVARCHAR(MAX)       NOT NULL,
    QueryPlan               XML                 NOT NULL,
    QueryPlanHash           BINARY(8)           NOT NULL,
    QueryParameterization   NVARCHAR(60)        NOT NULL,
    IsTrivial               BIT                 NOT NULL,
    IsParallel              BIT                 NOT NULL,
    IsForced                BIT                 NOT NULL,
    MaxLogicalReads         BIGINT              NOT NULL,
    AvgLogicalReads         INT                 NOT NULL,
    MaxLogicalWrites        BIGINT              NOT NULL,
    AvgLogicalWrites        INT                 NOT NULL,
    Waits                   NVARCHAR(MAX)       NOT NULL,
    PRIMARY KEY (   IntervalEnd DESC, 
                    LastQueryExecutionTime DESC, 
                    MaxLogicalReads DESC, 
                    StatementHandle, 
                    QueryPlanHash)
);

WITH Query AS (
    SELECT  LastQueryExecutionTime  =   q.last_execution_time
            ,QueryParameterization  =   q.query_parameterization_type_desc
            ,QueryPlan              =   CAST(p.query_plan AS XML)
            ,IsTrivial              =   p.is_trivial_plan
            ,IsParallel             =   p.is_parallel_plan
            ,IsForced               =   p.is_forced_plan
            ,ObjectName             =   OBJECT_NAME(q.[object_id])
            ,QueryHash              =   q.query_hash
            ,QueryId                =   q.query_id
            ,StatementHandle        =   t.statement_sql_handle
            ,StmtText               =   t.query_sql_text
            ,QueryPlanHash          =   p.query_plan_hash
            ,QueryPlanId            =   p.plan_id
      FROM  sys.query_store_query_text t
            JOIN sys.query_store_query q   ON t.query_text_id = q.query_text_id
            JOIN sys.query_store_plan p    ON q.query_id = p.query_id
     WHERE  t.query_sql_text    NOT LIKE 'ALTER INDEX %'
       AND  t.query_sql_text    NOT LIKE 'SELECT StatMan%'
       AND  t.query_sql_text    NOT LIKE '%encrypted%'
       AND  t.query_sql_text    NOT LIKE '(@plan_handle%'
       AND  t.query_sql_text    NOT LIKE 'WITH tempdbObjects%'
       AND  t.query_sql_text    NOT LIKE 'SELECT SUM(reserved_page_count)%'
       AND  t.query_sql_text    NOT LIKE 'SELECT secondary_lag_seconds%'
       AND  q.query_hash        =   ISNULL(@QueryHash, q.query_hash)
)
INSERT  INTO #Result WITH (TABLOCK)
SELECT  ExecutionType               =   rs.execution_type_desc
        ,FirstExecutionTime         =   rs.first_execution_time
        ,LastExecutionTime          =   rs.last_execution_time
        ,q.LastQueryExecutionTime
        ,IntervalStart              =   i.start_time
        ,IntervalEnd                =   i.end_time
        ,MaxDurationSecs            =   CAST(ROUND(rs.max_duration / 1000000.0, 4) AS DECIMAL(18, 4))
        ,AvgDurationSecs            =   CAST(ROUND(rs.avg_duration / 1000000.0, 4) AS DECIMAL(18, 4))
        ,ExecutionCount             =   rs.count_executions
        ,q.ObjectName
        ,q.QueryHash
        ,q.QueryId
        ,q.StatementHandle
        ,StatementType              =   CASE
                                            WHEN CHARINDEX(N'MERGE ',  q.StmtText) > 0 THEN 'MERGE'
                                            WHEN CHARINDEX(N'INSERT ', q.StmtText) > 0 THEN 'INSERT'
                                            WHEN CHARINDEX(N'UPDATE ', q.StmtText) > 0 THEN 'UPDATE'
                                            WHEN CHARINDEX(N'DELETE ', q.StmtText) > 0 THEN 'DELETE'
                                            WHEN CHARINDEX(N'SELECT ', q.StmtText) > 0 THEN 'SELECT'
                                        END
        ,q.StmtText       
        ,q.QueryPlan
        ,q.QueryPlanHash  
        ,q.QueryParameterization
        ,q.IsTrivial
        ,q.IsParallel
        ,q.IsForced  
        ,MaxLogicalReads            =   rs.max_logical_io_reads
        ,AvgLogicalReads            =   CAST(ROUND(rs.avg_logical_io_reads, 0) AS INT)
        ,MaxLogicalWrites           =   rs.max_logical_io_writes
        ,AvgLogicalWrites           =   CAST(ROUND(rs.avg_logical_io_writes, 0) AS INT)
        ,w.Waits
  FROM  Query q
        JOIN sys.query_store_runtime_stats rs           ON q.QueryPlanId = rs.plan_id
        JOIN sys.query_store_runtime_stats_interval i   ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
        OUTER APPLY ( 
        SELECT  Waits   =   STUFF(  (   SELECT  ', ' + CONVERT(VARCHAR, wait_category_desc) + ': '
                                                + CONVERT(VARCHAR, total_query_wait_time_ms) + 'ms'
                                          FROM  (   SELECT  ws.wait_category_desc, ws.total_query_wait_time_ms
                                                      FROM  sys.query_store_wait_stats ws
                                                     WHERE  ws.runtime_stats_interval_id    = rs.runtime_stats_interval_id
                                                       AND  ws.plan_id                      = rs.plan_id
                                                       AND  rs.execution_type               = ws.execution_type
                                                ) WaitValues
                                         ORDER  BY total_query_wait_time_ms DESC
                                           FOR  XML PATH('')
                                    ), 1, 2, ''
                            ) 
        ) w
 WHERE  rs.execution_type       =   3
--   AND  rs.first_execution_time >=   '2020-12-14 00:00:00' --DATEADD(HOUR, -12, GETDATE())
--   AND  rs.last_execution_time  <=   '2020-12-21 23:23:23' --DATEADD(HOUR, -12, GETDATE())
OPTION  (MAXDOP 1); /* To minimize the load when run in production */

SELECT  * 
  FROM  #Result
 ORDER  BY IntervalEnd DESC, LastQueryExecutionTime DESC, MaxLogicalReads DESC, StatementHandle, QueryPlanHash;

/* No statement text or plans, for easy sharing */

SELECT  ExecutionType      
        ,ObjectName            
        ,QueryHash             
        ,QueryPlanHash         
        ,QueryId               
        ,FirstExecutionTime    
        ,LastExecutionTime     
        ,LastQueryExecutionTime
        ,IntervalStart         
        ,IntervalEnd           
        ,MaxDurationSecs       
        ,AvgDurationSecs       
        ,ExecutionCount		   
        ,MaxLogicalReads       
        ,AvgLogicalReads       
        ,MaxLogicalWrites      
        ,AvgLogicalWrites      
        ,Waits
  FROM  #Result
 ORDER  BY IntervalEnd DESC, LastQueryExecutionTime DESC, MaxLogicalReads DESC, StatementHandle, QueryPlanHash;

DROP TABLE #Result;

Configure Database Mail

When it comes to monitoring servers and software, I like to have them notify me of issues rather than my having to periodically ask them how they are doing. The foundation of that is being able to have them send me email.

SQL Server has a feature called Database Mail that works very well for this purpose, but it can be a bit difficult to configure correctly, especially if you try to do it manually. I will show several ways to make use of Database Mail in future posts, but in this post I will show how to get it configured correctly. The necessary steps are:

1. Enable Database Mail and SQL Agent: This sets the features so they can be used, as they are generally disabled by default.
2. Setup Database Mail
1. Create a Database Mail profile: A profile is a collection of mail accounts, and it must exist before we can add an account to it.
2. Create a Database Mail account: This is the account we are going to use for sending email; it contains all the information about the SMTP server and the address to be used as the sender.
3. Add the account to the profile: The account cannot be used unless it is a member of a profile.
4. Grant the DBMailUsers role access to the profile: This provides the security right that is needed and also makes the profile the default.
3. Send a test message: At this point, Database Mail should be ready to send a message and this will confirm that it works properly. Depending on your mail client, you may need to refresh your InBox view or give it a command to fetch new mail to avoid waiting for the standard interval for checking if new mail has arrived.
4. Configure SQL Agent to use the profile and operator: Without this step, SQL Agent cannot use Database Mail. Unfortunately, it requires that the SQL Agent service be restarted. I didn't add the restart to the script as it is likely that security would prevent it from working in many environments. You can use SSMS (right-click on SQL Agent in Object Explorer and select Restart), SQL Server Configuration Manager, or the Windows Services Control Panel applet to restart the service manually.

The script below is what I use to accomplish all of this except the service restart. Just substitute your own values in the block of SET statements near the top and execute it. Each of these values is described in the script.

/* Configure Database Mail.sql

Purpose:        Configures Database Mail and sends a test message
Author:         Mark Freeman
Last Edited:    2011-11-20
Instructions:   Update the values in the block of SET statements as appropriate,
                 then execute.
References:     Adapted from:
                 http://www.kannade.com/tech/database-mail-and-sql-agent-mail-setup-using-script-t30.html and
                 http://www.mssqltips.com/sqlservertip/1736/sql-server-database-mail-and-sql-agent-mail-setup-by-using-a-script/
Compatibility:  SQL Server 2005 and newer
License:        This work is licensed under a Creative Commons
                 Attribution-NonCommercial-ShareAlike 3.0 Unported License.
                 http://creativecommons.org/licenses/by-nc-sa/3.0/
*/

SET NOCOUNT ON
USE [master]

DECLARE @ProfileName                    SYSNAME,
        @SmtpServer                     SYSNAME,
        @OperatorName                   SYSNAME,
        @ToEmail                        SYSNAME,
        @RecipientDomainName            SYSNAME,
        @SenderDomainName               SYSNAME,
        @ReplyToEmail                   SYSNAME,
        @SmtpUserName                   NVARCHAR(128),
        @SmtpPassword                   NVARCHAR(128),
        @SmtpPort                       INT,
        @SmtpUseDefaultCredentials      BIT,
        @SmtpEnableSsl                  BIT;

-- Please update the values in the section below as needed:
SET @ProfileName                = 'MyProfile';          /* This could be your company name,
                                                            domain name, or something
                                                            generic such as
                                                            'Database Mail Profile' */
SET @SmtpServer                 = 'smtp.mydomain.com';  /* Usually something like
                                                            'smtp.mydomain.com' */
SET @SmtpPort                   = 25;                   /* This is the standard port,
                                                            but yours may differ */
SET @RecipientDomainName        = 'mydomain.com';     /* This may differ from the domain
                                                            of the sender if, for example,
                                                            you arehaving servers at other
                                                            companies send you emails */
SET @SenderDomainName           = @RecipientDomainName; /* This is usually the domain of the
                                                            SMTP server */
SET @SmtpUserName               = NULL;                 /* Only needed if your SMTP server 
                                                            requires authentication */
SET @SmtpPassword               = NULL;                 /* Only needed if your SMTP server 
                                                            requires authentication */
SET @SmtpUseDefaultCredentials  = 0                     /* Change to 1 to use the
                                                            credentials of the SQL Server
                                                            Database Engine and ignore the 
                                                            user name and password
                                                            settings */
SET @SmtpEnableSsl              = 0;                    /* Change to 1 if your SMTP server
                                                            uses SSL encryption */
SET @ToEmail                    = 'dba' + @RecipientDomainName;
SET @ReplyToEmail               = 'sqlserver' + @SenderDomainName;
-- Please update the values in the section above as needed


-- Enable Database Mail and SQL Agent
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
EXEC sp_configure 'Agent XPs',1;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;

-- Setup Database Mail
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile)
    PRINT 'Database Mail already configured';
ELSE BEGIN
    DECLARE @FromEmail  SYSNAME,
            @FromName   SYSNAME;
    SELECT  @FromEmail  = REPLACE(@@SERVERNAME,'\','_') + @SenderDomainName,
            @FromName   = 'SQL Server ' + @@SERVICENAME;

    -- Create a Database Mail profile
    EXEC msdb.dbo.sysmail_add_profile_sp
        @profile_name = @ProfileName
        ,@description = 'Mail profile setup for email from this SQL Server';

    -- Create a Database Mail account, assuming Windows Authentication
    EXEC msdb.dbo.sysmail_add_account_sp
        @account_name              = @ToEmail
        ,@description              = 'Mail account for use by database processes'
        ,@email_address            = @FromEmail
        ,@replyto_address          = @ReplyToEmail
        ,@display_name             = @FromName
        ,@mailserver_name          = @SmtpServer
        ,@mailserver_type          = 'SMTP'
        ,@port                     = @SmtpPort
        ,@username                 = @SmtpUserName
        ,@password                 = @SmtpPassword
        ,@use_default_credentials  = @SmtpUseDefaultCredentials
        ,@enable_ssl               = @SmtpEnableSsl;

    -- Add the account to the profile
    EXEC msdb.dbo.sysmail_add_profileaccount_sp
        @profile_name              = @ProfileName
        ,@Account_name             = @ToEmail
        ,@sequence_number          = 1;

    -- Grant access to the profile to the DBMailUsers role
    EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @profile_name              = @ProfileName
        ,@is_default               = 1;
END

-- Send a test message
DECLARE @MessageText VARCHAR(100);
SET @MessageText = 'Test from SQL Server ' + @@SERVICENAME;
EXEC msdb..sp_send_dbmail
    @profile_name                  = @ProfileName
    ,@recipients                   = @ToEmail
    ,@subject                      = @MessageText
    ,@body                         = @MessageText;

-- Create operator
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysoperators WHERE name = @ToEmail)
    EXEC msdb.dbo.sp_add_operator
        @name                      = @ToEmail 
        ,@enabled                  = 1 
        ,@email_address            = @ToEmail;

EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator = @ToEmail;

-- Configure SQL Agent to use the profile and operator
USE [msdb];
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1;
EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    ,N'UseDatabaseMail'
    ,N'REG_DWORD'
    ,1;
EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    ,N'DatabaseMailProfile'
    ,N'REG_SZ'
    ,@ProfileName;
PRINT '**** You must manually restart the Service: SQL Server Agent ('
    + @@SERVICENAME + ')';

Finding Foreign Keys without Indexes

I have seen a number of blog posts on this topic that go into detail as to why it is important to have such indexes and sometimes provide helpful queries. Examples would include:
    http://www.brentozar.com/archive/2011/09/indexing-for-deletes/
    http://michaeljswart.com/2009/12/finding-your-missing-keys/

However, I wanted to take it a step further. The following query not only finds the Foreign Keys without an associated Index, it also provides the command needed to create each index in both XML and text formats.

If you click on the XML result, SSMS will open a new window containing the command already nicely formatted for you to copy into your source control system of preference, or into a new SSMS window so you can execute it. Of course, the query as shown uses my preferred naming and formatting standards for the indexes, but it should be easy enough to modify it to use your own.

/* What indexes are needed for my foreign keys.sql

Purpose:    Find foreign keys without an index and the command needed to create the index.
            Note that if these aren't showing up in the missing_index* views you might not
            think that you need them, but not having them will cause some deletes from
            referenced tables to perform full table scans. For example, without the
            appropriate foreign key index, deleting a customer would result in a full scan
            of the invoice table to ensure that the customer PK does not exist in any
            invoice record.

Author:     Mark Freeman (@m60freeman)

Changes:    2011-09-01  Created
            2011-11-20  Eliminated matches to index where the referenced column is not the
                         first column in the index, added comment and brackets in Potential
                         DDL, and added handling of objects with the same name in multiple
                         schemas
*/
SELECT      rc.Constraint_Schema                            AS Base_Schema
            ,ccu.Table_Name                                 AS Base_Table
            ,ccu.Column_Name                                AS Base_Column
            ,ccu.Constraint_Name                            AS Foreign_Key
            ,ccu2.Constraint_Schema                         AS Referenced_Schema
            ,ccu2.Table_Name                                AS Referenced_Table
            ,ccu2.Column_Name                               AS Referenced_Column
            ,'IX_' + ccu.Table_Name + '_' + ccu.Column_Name AS Potential_Index_Name
            ,CAST('<?query --' + CHAR(13) +
                'CREATE INDEX [IX_' +
                ccu.Table_Name + '_' + ccu.Column_Name +
                ']' + CHAR(13) +
                CHAR(9) + '-- Needed to support ' +
                ccu.Constraint_Name + CHAR(13) +
                CHAR(9) + 'ON [' + rc.Constraint_Schema +
                '].[' + ccu.Table_Name + ']' + CHAR(13) +
                CHAR(9) + '(' + CHAR(13) +
                CHAR(9) + CHAR(9) + '[' + ccu.Column_Name +
                ']' + CHAR(13) + CHAR(9) + ');'
                + CHAR(13) + ' --?>' AS XML)             AS Potential_DDL_XML
            ,'CREATE INDEX [IX_' + ccu.Table_Name + '_' +
                ccu.Column_Name + ']' + ' ON [' +
                rc.Constraint_Schema + '].[' +
                ccu.Table_Name + ']' +  '([' +
                ccu.Column_Name +
                ']); -- Needed to support '
                + ccu.Constraint_Name                       AS Potential_DDL
FROM        information_schema.referential_constraints rc
            JOIN information_schema.constraint_column_usage ccu
                ON rc.CONSTRAINT_NAME           = ccu.CONSTRAINT_NAME
                    AND rc.CONSTRAINT_SCHEMA    = ccu.CONSTRAINT_SCHEMA
            JOIN information_schema.constraint_column_usage ccu2
                ON rc.UNIQUE_CONSTRAINT_NAME    = ccu2.CONSTRAINT_NAME
                    AND rc.CONSTRAINT_SCHEMA    = ccu2.CONSTRAINT_SCHEMA
WHERE       NOT EXISTS (
                SELECT  1
                FROM    sys.indexes i
                        JOIN sys.index_columns ic
                            ON  ic.[object_id]  = i.[object_id]
                                AND ic.index_Id = i.index_Id
                        JOIN sys.columns c
                            ON  c.[object_id]   = ic.[object_id]
                                AND c.column_id = ic.column_id
                        JOIN sys.objects o
                            ON  o.[object_id]   = c.[object_id]
                        JOIN sys.schemas s
                            ON  s.[schema_id]   = o.[schema_id]
                WHERE   c.name                      = ccu.COLUMN_NAME
                  AND   OBJECT_NAME(c.[object_id])  = ccu.TABLE_NAME
                  AND   s.name                      = ccu.TABLE_SCHEMA
                   AND   ic.key_ordinal              = 1
                )
AND         ccu.Table_Name NOT LIKE 'aspnet_%'
ORDER BY    ccu2.Constraint_Schema,
            ccu2.Table_Name,
            rc.Constraint_Schema,
            ccu.Table_Name,
            ccu.Column_Name;