TSQL Scripts for Troubleshooting Common Issues with SQL Server

[EstherXinMSFT]

This blog provides TSQL samples for troubleshooting common SQL Server issues as listed below. You can modify the parameters (i.e. database name, table name, keyword name, duration, etc.) based on customized environment settings and requirements. (Test these scripts before implementing in PROD environment. Please be aware of all potential risks for implementing the script in your PROD environment.)

1. Performance Troubleshooting(blocking, high CPU, memory, idle, query execution)

2. Deadlock and Database Object ID mapping

3. HA (Clustered SQL Server)

4. Backup & Transaction Log Related Issue

5. Query Store (QDS)

6. Database Encryption (TDE)

7. Tool (Profiler trace and X-event)

Performance Troubleshooting

=========================

(blocking, high CPU, memory, idle, query execution)

       1.List all active sessions and its queries that contain your target table name or specific TSQL structure (input your target key word)

SELECT sqltext.TEXT,

req.session_id,

req.status,

req.command,

req.cpu_time,req.database_id,

req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where sqltext.text like '%Your Target Key Word%'

        2.List all sleeping user sessions that have been idle for over 15 minutes with detailed queries (You can customize the session’s status and idle time)

SELECT CURRENT_TIMESTAMP as currenttime, datediff(minute,last_batch,GETDATE()) as 'idletime_in_minute' ,sp.status,sp.spid,sp.login_time,sp.program_name,sp.hostprocess,sp.loginame,text FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS QT where sp.status = 'sleeping' and datediff(minute,last_batch,GETDATE()) >15 and spid>50

         3.List top 10 high CPU queries that currently running in this SQL instance

SELECT s.session_id,r.status,r.blocking_session_id 'Blk by',r.wait_type,wait_resource,r.wait_time / (1000 * 60) 'Wait M',r.cpu_time,r.logical_reads,r.reads,r.writes,r.total_elapsed_time / (1000 * 60) 'Elaps M',Substring(st.TEXT,(r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset WHEN -1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text,

Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,r.command,s.login_name,

s.host_name,s.program_name,s.last_request_end_time,s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time desc

        4. List top 10 high memory usage queries that currently running in this SQL instance

SELECT mg.session_id,mg.granted_memory_kb,mg.requested_memory_kb,mg.ideal_memory_kb,mg.request_time,mg.grant_time,mg.query_cost,mg.dop,st.[TEXT],qp.query_plan

FROM sys.dm_exec_query_memory_grants AS mg CROSS APPLY sys.dm_exec_sql_text(mg.plan_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp ORDER BY mg.required_memory_kb DESC

       5. List detailed memory usage for each memory clerk

DBCC MEMORYSTATUS

      6. List memory usage for plan cache and its maximum size based on current setting. By removing the WHERE condition, you will get a full list for all memory cache clerks’ information

select name, type, buckets_count

from sys.dm_os_memory_cache_hash_tables

where name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' ,'Extended Stored Procedures')

select name, type, pages_kb, entries_count

from sys.dm_os_memory_cache_counters

where name IN ( 'SQL Plans' , 'Object Plans' ,  'Bound Trees' ,'Extended Stored Procedures')

       7. List progressive (on-going) execution plan for a specific session (Starting with SQL 2016)

• For SQL 2016 and 2017, please first run below TSQL in the query session of which the execution plan you wish to extract later

                  set statistics profile on

• Run below query to extract the on-going execution plan for your target session (input the SPID in the bracket)

        SELECT * FROM sys.dm_exec_query_statistics_xml(59);

       8.List all block header queries that currently detected in this SQL instance

declare @blocker varchar(100),@sql varchar(100)

print convert(varchar(20), getdate(),120)

   select distinct blocked into #blocker from sysprocesses where blocked <> 0

   DECLARE blocker CURSOR FOR

   select spid from sysprocesses where spid in (select * from #blocker) and blocked = 0

   OPEN blocker

   FETCH NEXT

      FROM blocker

      INTO @blocker

   WHILE @@FETCH_STATUS = 0

   BEGIN     

       set @sql='DBCC inputbuffer(' + @blocker + ')'

       execute (@sql)

    set @sql= 'select * from sysprocesses where spid=' + @blocker

    execute (@sql)

          FETCH NEXT

         FROM blocker

         INTO @blocker

   END

   CLOSE blocker

   DEALLOCATE blocker

   drop table #blocker

      9.Kill all sleeping sessions that has been idle over 1 hour (You can customize the idle duration)

DECLARE @user_spid INT

DECLARE CurSPID CURSOR FAST_FORWARD

FOR

SELECT SPID

FROM master.dbo.sysprocesses (NOLOCK)

WHERE spid>50

AND status='sleeping' -- only sleeping threads

AND DATEDIFF(HOUR,last_batch,GETDATE())>=1 -- thread sleeping for 1 hours

AND spid<>@@spid -- ignore current spid

OPEN CurSPID

FETCH NEXT FROM CurSPID INTO @user_spid

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT 'Killing '+CONVERT(VARCHAR,@user_spid)

EXEC('KILL '+@user_spid)

FETCH NEXT FROM CurSPID INTO @user_spid

END

CLOSE CurSPID

DEALLOCATE CurSPID

GO

       10.Kill all block headers. This script will continuously scan every 5 seconds for all block header sessions and kill all block headers

Use master

go

while 1=1

Begin

declare @blocker varchar(100),@sql varchar(100)

print convert(varchar(20), getdate(),120)

   select distinct blocked into #blocker from sysprocesses where blocked <> 0

   DECLARE blocker CURSOR FOR

   select spid from sysprocesses where spid in (select * from #blocker) and status='sleeping'

   OPEN blocker

   FETCH NEXT

      FROM blocker

      INTO @blocker

   WHILE @@FETCH_STATUS = 0

   BEGIN     

       set @sql='DBCC inputbuffer(' + @blocker + ')'

       execute (@sql)

    set @sql= 'kill ' + @blocker

    execute (@sql)

          FETCH NEXT

         FROM blocker

         INTO @blocker

   END

    CLOSE blocker

   DEALLOCATE blocker

   drop table #blocker

waitfor delay '0:0:05'

End

Deadlock and Database Object ID mapping

===================================

Below are 3 examples to map the issued object based on the ID to its source database, index or schema. 

           1. Key type wait resource

waitresource=KEY: 6:12345678990 (987654321a9b)

• database_id = 6
• hobt_id = 12345678990
• hash value = (987654321a9b)

• We can use below TSQL to check the database name based on DATABASE ID

select db_name(6)

• Use below TSQL to check the schema, object, and index details related to this key

USE DatabaseName

GO

SELECT

   sc.name as schema_name,

    so.name as object_name,

    si.name as index_name

FROM sys.partitions AS p

JOIN sys.objects as so on

    p.object_id=so.object_id

JOIN sys.indexes as si on

    p.index_id=si.index_id and

    p.object_id=si.object_id

JOIN sys.schemas AS sc on

    so.schema_id=sc.schema_id

WHERE hobt_id = 12345678990

         2. Object Wait resource type

waitresource=OBJECT: 6:1234567890:4 

• database_id = 6
• Object ID = 1234567890

We can use below TSQL to check the specific object name based on the waitresource details

select OBJECT_NAME(1234567890,6) 

         3. Page Wait Resource Type

waitresource=“PAGE: 6:3:70133 ” = Database_Id : FileId : PageNumber

• database_id=6
• data_file_id = 3
• page_number = 70133

HA (Clustered SQL Server)

===================

1. List the latest error encountered by local AG replica for connection timeout

select r.replica_server_name, r.endpoint_url,

rs.connected_state_desc, rs.last_connect_error_description,

rs.last_connect_error_number, rs.last_connect_error_timestamp

from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r

on rs.replica_id=r.replica_id

where rs.is_local=1

2. List the current redo rate, redo queue size, log send rate, log send queue size for current AG replica

SELECT CURRENT_TIMESTAMP as currenttime,drs.last_commit_time,ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs.is_primary_replica, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id

3. Restart endpoint
   • Check SQL AG endpoint name

SELECT * FROM sys.endpoints

• Restart AG endpoint on this local replica

ALTER ENDPOINT <Your AG Endpoint Name> STATE=STOPPED 

ALTER ENDPOINT <Your AG Endpoint Name> STATE=STARTED

Backup & Transaction Log Related Issue

================================

1. List all backup history and backup file details for the past 7 days (You can modify the date)

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date,

CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date

2. List all databases’ data and log file path and logical name

USE master;

SELECT name 'Logical Name', physical_name 'File Location' FROM sys.master_files;

3. List each database’ transaction log size usage and space

DBCC SQLPERF(LOGSPACE)

4. List VLF size and counts for all databases as well as detailed size for your target database

select * from sys.dm_db_log_info(5); /*input here your DB ID to get detailed size for existing VLFs on this database*/

SELECT [name], s.database_id,

COUNT(l.database_id) AS 'VLF Count',

SUM(vlf_size_mb) AS 'VLF Size (MB)',

SUM(CAST(vlf_active AS INT)) AS 'Active VLF',

SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',

COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',

SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'

FROM sys.databases s

CROSS APPLY sys.dm_db_log_info(s.database_id) l

GROUP BY [name], s.database_id

ORDER BY 'VLF Count' DESC

GO

5. For the purpose of shrinking transaction log file, check the log_reuse_wait_desc type for all database

select name,database_id,log_reuse_wait, log_reuse_wait_desc from sys.databases

Please refer this link for how to fix each of the log_reuse_wait_desc type when it is not “NOTHING”  https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transac...

6. Database Corruption Issue

Force repair of database with REPAIR_ALLOW_DATA_LOSS option (data loss expected and sometimes can cause more damage. Please refrain from using this method unless as for a last resort. More details for fixing database corruption, please refer this link https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=...)

Alter database YourDBName set single_user with rollback immediate;

DBCC CHECKDB('YourDBName', REPAIR_ALLOW_DATA_LOSS);

Alter database YourDBName set multi_user with rollback immediate;

Query Store (QDS)

=================

1.List a database’s query store status

select * from sys.database_query_store_options

 2. List all databases that have query store configured

select name as 'DATABASE NAME',

CASE is_query_store_on when 1 then 'ENABLED'

else 'OTHER' END AS 'QUERY STORE STATE'

from sys.databases

where is_query_store_on = 1

order by 1 ;

Database Encryption (TDE)

======================

1. Check if the DMK exist in master database

USE master

GO

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'

2. Check if the certificate is created in master database. A certificate is equivalent to an asymmetric key

USE master

GO

select * from sys.certificates

3. Check if the database is encrypted/progress (encryption_state = 3 encrypted; =2 in progress)

USE master

GO

SELECT db_name(database_id) [TDE Encrypted DB Name], c.name as CertName, encryptor_thumbprint , dek.*     FROM sys.dm_database_encryption_keys dek     INNER JOIN sys.certificates c on dek.encryptor_thumbprint = c.thumbprint

4. Check if the DMK in the master database is now encrypted by the SMK (is_master_key_encrypted_by_server = 1)

select is_master_key_encrypted_by_server, * from sys.databases

where database_id = db_id(N'master')

Tool (Profiler trace and X-event)

=======================

1. Import SQL Profiler trace into SQL Server database tables

USE DatabaseName

GO

select * into Sample from fn_trace_gettable('c:\trace\YourFolderToStoreTheTrace\YourTraceFile.trc',default) where eventclass in (10, 12)

For event class ID, check the list in this link https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setevent...

2. Use XELoader to process X-event and import the data into SQL database table for data analysis purpose.

• XELoader can be downloaded from this opensource link

https://github.com/suresh-kandoth/XELoader

• Use CMD script to load the X-event files into SQL Server database

Sample script:

C:\XELoader\6.2>XELoader.exe -D"C:\Users\yixin\Desktop\xeloader\xevent" -SYourServerName\InstanceName -dYourDBName

• Use TSQL query to check aggregated data result

Sample TSQL:

SELECT sum(c_duration) as SUM_DURAION,sum(c_signal_duration) as SUM_SIGNAL_DURATION,c_wait_type from [xel].[wait_info] group by c_wait_type order by SUM_DURAION desc

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.