SQL Server:
Performance Troubleshooting
I don't claim to be awesome at troubleshooting but wanted to document all the tips and tricks I am learning along the way.
Need to attach multiple databases?? |
|
Needed to attach several hundred DBs. Didn't want to code that by hand. This is a handy query using XML Path.
SELECT 'sp_attach_db '+DB_NAME(mf.database_id)+', '+
SUBSTRING(( SELECT ', ' +''''+ b.physical_name+''''
FROM sys.master_files b
WHERE b.database_id = mf.database_id
FOR
XML PATH('')
), 3, 1000) AS Result
FROM sys.master_files mf JOIN sys.databases db ON mf.database_id = db.database_id
WHERE db.name IN ('<enter your database names>'
)
GROUP BY mf.database_id
SELECT 'sp_attach_db '+DB_NAME(mf.database_id)+', '+
SUBSTRING(( SELECT ', ' +''''+ b.physical_name+''''
FROM sys.master_files b
WHERE b.database_id = mf.database_id
FOR
XML PATH('')
), 3, 1000) AS Result
FROM sys.master_files mf JOIN sys.databases db ON mf.database_id = db.database_id
WHERE db.name IN ('<enter your database names>'
)
GROUP BY mf.database_id
Find TempDB contention |
|
Got a chance to work with Bob Taylor, Microsoft Certified Architect and he showed me a quick way to find TempDB contention.
SELECT *
FROM sys.dm_os_waiting_tasks
where resource_description like '2:%'
2 represents DBID for TempDB and then looking for pages that are a waiting task resource description
SELECT *
FROM sys.dm_os_waiting_tasks
where resource_description like '2:%'
2 represents DBID for TempDB and then looking for pages that are a waiting task resource description
Find Cost of Queries in Plan Cache |
|
Find the cost of queries in the cache. Got it from Jonathan Kehayias. Click on the link below.
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
Does making a change to Max Server Memory Flush the Procedure Cache? |
|
Try this. Check the plan_count after the reconfigure. Procedure Cache is wiped!!
select count(*) as Plan_Count
From sys.dm_exec_cached_plans
;
GO
EXEC sys.sp_configure N'max server memory (MB)', N'10240'
GO
RECONFIGURE WITH OVERRIDE
GO
select count(*) as Plan_Count
From sys.dm_exec_cached_plans
select count(*) as Plan_Count
From sys.dm_exec_cached_plans
;
GO
EXEC sys.sp_configure N'max server memory (MB)', N'10240'
GO
RECONFIGURE WITH OVERRIDE
GO
select count(*) as Plan_Count
From sys.dm_exec_cached_plans
PLE and Buffer Cache Hit/Ratio Queries |
|
Queries to find PLE and Buffer Cache Hit/Ratio
PLE
SELECT cntr_value AS [Page Life Expectancy],object_name,counter_name
from sys.dm_os_performance_counters
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = N'Page life expectancy';
Cache Hit/Ratio
SELECT ( a.cntr_value * 1.0 / b.cntr_value ) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN ( SELECT cntr_value ,
object_name
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = 'SQLServer:Buffer Manager'
) b ON a.object_name = b.object_name
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.object_name = 'SQLServer:Buffer Manager'
PLE
SELECT cntr_value AS [Page Life Expectancy],object_name,counter_name
from sys.dm_os_performance_counters
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = N'Page life expectancy';
Cache Hit/Ratio
SELECT ( a.cntr_value * 1.0 / b.cntr_value ) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN ( SELECT cntr_value ,
object_name
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = 'SQLServer:Buffer Manager'
) b ON a.object_name = b.object_name
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.object_name = 'SQLServer:Buffer Manager'
Find out stuff about your Queries |
|
Using query stats to return info about my queries
SELECT TOP 100 QS.sql_handle, QS.plan_handle, QS.execution_count, ST.text, QP.query_plan
FROM SYS.dm_exec_query_stats QS
CROSS APPLY SYS.dm_exec_sql_text(QS.SQL_HANDLE) ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) QP
ORDER BY QS.execution_count DESC
SELECT TOP 100 QS.sql_handle, QS.plan_handle, QS.execution_count, ST.text, QP.query_plan
FROM SYS.dm_exec_query_stats QS
CROSS APPLY SYS.dm_exec_sql_text(QS.SQL_HANDLE) ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) QP
ORDER BY QS.execution_count DESC
Best Explanation of Parameterization with Examples
How to find number of Single-Use Plans in the Plan Cache |
|
Do you have plan cache bloat? This might help.
The OBJTYPE of 'prepared' means parameterized queries.
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx
DECLARE @single float
DECLARE @total float
SELECT @single = COUNT(*)
FROM sys.dm_exec_cached_plans
WHERE objtype IN ('Adhoc', 'Prepared') AND usecounts = 1
SELECT @total = COUNT(*)
FROM sys.dm_exec_cached_plans
SELECT CAST(@single AS VARCHAR(10))+' / '+CAST(@total AS VARCHAR(10)) 'Single Use AdHoc Query Plans' , (@single/@total)*100 'Percent of Single Use plans in Plan Cache'
The OBJTYPE of 'prepared' means parameterized queries.
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx
DECLARE @single float
DECLARE @total float
SELECT @single = COUNT(*)
FROM sys.dm_exec_cached_plans
WHERE objtype IN ('Adhoc', 'Prepared') AND usecounts = 1
SELECT @total = COUNT(*)
FROM sys.dm_exec_cached_plans
SELECT CAST(@single AS VARCHAR(10))+' / '+CAST(@total AS VARCHAR(10)) 'Single Use AdHoc Query Plans' , (@single/@total)*100 'Percent of Single Use plans in Plan Cache'
Log_ReUse says REPLICATION!! but I am not using Replication... |
|
So my database that is in simple mode has a log that is 16GBs. What!!!!!!! There are no open transactions. Log Reuse says replication. I check to see if there was any replication some idiot with SA perms set up and that didn't know about.
well here is the solution..
Select DATABASEPROPERTYEX ( db_name() , 'IsPublished' )
As I thought there was not any replication setup. So run sp_removedbreplication
select name, log_reuse_wait_desc, recovery_model_desc from sys.databases
EXEC sp_removedbreplication <dbname>
dbcc shrinkfile('<log file name>',1024)
dbcc sqlperf(logspace)
A Colleague showed me this ...
<use dbname>
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
GO
EXEC sp_replflush;
GO
checkpoint
well here is the solution..
Select DATABASEPROPERTYEX ( db_name() , 'IsPublished' )
As I thought there was not any replication setup. So run sp_removedbreplication
select name, log_reuse_wait_desc, recovery_model_desc from sys.databases
EXEC sp_removedbreplication <dbname>
dbcc shrinkfile('<log file name>',1024)
dbcc sqlperf(logspace)
A Colleague showed me this ...
<use dbname>
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
GO
EXEC sp_replflush;
GO
checkpoint
Find Query Times with a Server Side Trace |
|
So capture only Statement Start and Statement complete and then put that info into temp tables and use the EXCEPT operation to determin if queries are completing.
Sean McCowan explains this in detail.
http://www.midnightdba.com/DBARant/new-video-find-query-timeouts-in-profiler/
--query start
select TextData
into #QueryStart
from fn_trace_gettable('C:\Users\mdspain\Documents\MyTrace\Timeout.trc',default)
where EventClass = 44
--query completed
select TextData
into #QueryStop
from fn_trace_gettable('C:\Users\mdspain\Documents\MyTrace\Timeout.trc',default)
where EventClass = 45
select CAST(TextData as nvarchar(max)) as TextData
into #startfinal
from #Querystart
select CAST(TextData as nvarchar(max)) as TextData
into #stopfinal
from #QueryStop
select TextData
from #startfinal
except
select TextData
from #stopfinal
Find the tables used in a Stored Procedure and other stuff!! |
|
When I started a new gig, I had to do a lot of troubleshooting of SPs. Wanted to build a way to find out as much info about the tables used in the SP. Here is what I came up with.
CREATE PROCEDURE Find_SP_Info @sp_name VARCHAR(200)
AS
--find all tables associated with SP and put into temp table and return index and stat info including update stats and reindex statements
IF OBJECT_ID('TEMPDB..#sp_tables') IS NOT NULL
DROP TABLE #sp_tables
IF OBJECT_ID('TEMPDB..#index_info') IS NOT NULL
DROP TABLE #index_info
IF OBJECT_ID('TEMPDB..#stats') IS NOT NULL
DROP TABLE #stats
CREATE TABLE #index_info
(Dbname VARCHAR(100),
Tablename VARCHAR(200),
IndexName VARCHAR(200),
IndexDescription VARCHAR(50),
Avg_Index_Frag FLOAT,
IndexPageCount INT,
Rows INT,
Table_SizeKB INT,
Table_SizeGB float
)
CREATE TABLE #Stats
(Stats_id int,
SchemaName varchar(10),
TableName VARCHAR(200),
Stats_Name VARCHAR(200),
last_updated DATETIME2,
rows INT,
rows_sampled INT,
Percentage_sampled NUMERIC,
Percent_changed NUMERIC,
Modification_counter varchar(20)
)
SELECT p.name AS SP_name, t.name AS table_name,t.object_id AS table_ob_id
INTO #sp_tables
FROM sys.sql_expression_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.referencing_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_id
WHERE p.name = @sp_name
ORDER BY SP_name, table_name
--use sp_tables in cursor to return data regarding indexs and stats for tables in SP
DECLARE @table_ob_id INT
DECLARE @table_name VARCHAR(200)
DECLARE sp_info CURSOR FAST_FORWARD READ_ONLY FOR
SELECT table_name,table_ob_id FROM #sp_tables
OPEN sp_info
FETCH NEXT FROM sp_info INTO @table_name, @table_ob_id
WHILE @@FETCH_STATUS = 0
BEGIN
/************************
cursor logic
Loop through each index and stat metadata and insert into temp tables
************************/
INSERT INTO #index_info
( Dbname ,
Tablename ,
IndexName ,
IndexDescription ,
Avg_Index_Frag ,
IndexPageCount ,
Rows ,
Table_SizeKB ,
Table_SizeGB
)
SELECT DB_NAME(ips.database_id) AS DBName ,
t.name AS table_name ,
i.name AS INDEX_name ,
ips.index_type_desc ,
ips.avg_fragmentation_in_percent ,
ips.page_count ,
p.rows ,
a.total_pages * 8 AS table_size_kb,
a.total_pages * 8/1024.0 AS table_size_gb
FROM sys.dm_db_index_physical_stats(DB_ID(), @table_ob_id /*change to variable*/, NULL, NULL,
'sampled') ips
JOIN sys.indexes i ON i.index_id = ips.index_id
AND i.object_id = ips.object_id
JOIN sys.tables t ON t.object_id = ips.object_id
JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
INSERT INTO #Stats
( Stats_id ,
SchemaName ,
TableName ,
Stats_Name ,
last_updated ,
rows ,
rows_sampled ,
Percentage_sampled ,
Percent_changed ,
Modification_counter
)
SELECT sp.stats_id ,
sc.name AS SchemaName ,
OBJECT_NAME(s.object_id) AS tablename ,
s.name AS stats_name ,
sp.last_updated ,
sp.rows ,
sp.rows_sampled ,
CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
CASE WHEN sp.modification_counter = 0 THEN 0
ELSE CAST(sp.modification_counter AS NUMERIC)
/ CAST(sp.rows AS NUMERIC) * 100
END AS Percent_changed ,
CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
JOIN sys.tables t ON t.object_id = s.object_id
JOIN sys.schemas sc ON sc.schema_id = t.schema_id
WHERE s.object_id = object_id(@table_name)
FETCH NEXT FROM sp_info INTO @table_name, @table_ob_id
END
CLOSE sp_info
DEALLOCATE sp_info
/************************
Return data to user
Creating Update Stats stmts
Creating Reorg and Rebuild index stmts
************************/
SELECT *
FROM #sp_tables;
SELECT * ,
CASE WHEN Avg_Index_Frag > 10
AND Avg_Index_Frag < 30
THEN 'Alter Index ' + IndexName + ' on ' + Tablename
+ ' Reorganize'
WHEN Avg_Index_Frag >= 30
THEN 'Alter Index ' + IndexName + ' on ' + Tablename
+ ' Rebuild with (Online = ON)'
END AS FragStmt
FROM #index_info
WHERE Avg_Index_Frag > 10;
SELECT * ,
'Update Statistics ' + SchemaName + '.' + TableName + '(' + Stats_Name
+ ')' + ' with Sample 25 Percent;' AS UpdateStats_Stmt
FROM #Stats
WHERE Modification_counter <> 'No Modifications'
AND Percent_changed > 20;
CREATE PROCEDURE Find_SP_Info @sp_name VARCHAR(200)
AS
--find all tables associated with SP and put into temp table and return index and stat info including update stats and reindex statements
IF OBJECT_ID('TEMPDB..#sp_tables') IS NOT NULL
DROP TABLE #sp_tables
IF OBJECT_ID('TEMPDB..#index_info') IS NOT NULL
DROP TABLE #index_info
IF OBJECT_ID('TEMPDB..#stats') IS NOT NULL
DROP TABLE #stats
CREATE TABLE #index_info
(Dbname VARCHAR(100),
Tablename VARCHAR(200),
IndexName VARCHAR(200),
IndexDescription VARCHAR(50),
Avg_Index_Frag FLOAT,
IndexPageCount INT,
Rows INT,
Table_SizeKB INT,
Table_SizeGB float
)
CREATE TABLE #Stats
(Stats_id int,
SchemaName varchar(10),
TableName VARCHAR(200),
Stats_Name VARCHAR(200),
last_updated DATETIME2,
rows INT,
rows_sampled INT,
Percentage_sampled NUMERIC,
Percent_changed NUMERIC,
Modification_counter varchar(20)
)
SELECT p.name AS SP_name, t.name AS table_name,t.object_id AS table_ob_id
INTO #sp_tables
FROM sys.sql_expression_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.referencing_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_id
WHERE p.name = @sp_name
ORDER BY SP_name, table_name
--use sp_tables in cursor to return data regarding indexs and stats for tables in SP
DECLARE @table_ob_id INT
DECLARE @table_name VARCHAR(200)
DECLARE sp_info CURSOR FAST_FORWARD READ_ONLY FOR
SELECT table_name,table_ob_id FROM #sp_tables
OPEN sp_info
FETCH NEXT FROM sp_info INTO @table_name, @table_ob_id
WHILE @@FETCH_STATUS = 0
BEGIN
/************************
cursor logic
Loop through each index and stat metadata and insert into temp tables
************************/
INSERT INTO #index_info
( Dbname ,
Tablename ,
IndexName ,
IndexDescription ,
Avg_Index_Frag ,
IndexPageCount ,
Rows ,
Table_SizeKB ,
Table_SizeGB
)
SELECT DB_NAME(ips.database_id) AS DBName ,
t.name AS table_name ,
i.name AS INDEX_name ,
ips.index_type_desc ,
ips.avg_fragmentation_in_percent ,
ips.page_count ,
p.rows ,
a.total_pages * 8 AS table_size_kb,
a.total_pages * 8/1024.0 AS table_size_gb
FROM sys.dm_db_index_physical_stats(DB_ID(), @table_ob_id /*change to variable*/, NULL, NULL,
'sampled') ips
JOIN sys.indexes i ON i.index_id = ips.index_id
AND i.object_id = ips.object_id
JOIN sys.tables t ON t.object_id = ips.object_id
JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
INSERT INTO #Stats
( Stats_id ,
SchemaName ,
TableName ,
Stats_Name ,
last_updated ,
rows ,
rows_sampled ,
Percentage_sampled ,
Percent_changed ,
Modification_counter
)
SELECT sp.stats_id ,
sc.name AS SchemaName ,
OBJECT_NAME(s.object_id) AS tablename ,
s.name AS stats_name ,
sp.last_updated ,
sp.rows ,
sp.rows_sampled ,
CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
CASE WHEN sp.modification_counter = 0 THEN 0
ELSE CAST(sp.modification_counter AS NUMERIC)
/ CAST(sp.rows AS NUMERIC) * 100
END AS Percent_changed ,
CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
JOIN sys.tables t ON t.object_id = s.object_id
JOIN sys.schemas sc ON sc.schema_id = t.schema_id
WHERE s.object_id = object_id(@table_name)
FETCH NEXT FROM sp_info INTO @table_name, @table_ob_id
END
CLOSE sp_info
DEALLOCATE sp_info
/************************
Return data to user
Creating Update Stats stmts
Creating Reorg and Rebuild index stmts
************************/
SELECT *
FROM #sp_tables;
SELECT * ,
CASE WHEN Avg_Index_Frag > 10
AND Avg_Index_Frag < 30
THEN 'Alter Index ' + IndexName + ' on ' + Tablename
+ ' Reorganize'
WHEN Avg_Index_Frag >= 30
THEN 'Alter Index ' + IndexName + ' on ' + Tablename
+ ' Rebuild with (Online = ON)'
END AS FragStmt
FROM #index_info
WHERE Avg_Index_Frag > 10;
SELECT * ,
'Update Statistics ' + SchemaName + '.' + TableName + '(' + Stats_Name
+ ')' + ' with Sample 25 Percent;' AS UpdateStats_Stmt
FROM #Stats
WHERE Modification_counter <> 'No Modifications'
AND Percent_changed > 20;
Set Quoted_Identifiers is being changed and how to find that |
|
Customer is having SPs with XML in them bomb. Quoted_Identifiers is being changed some how. Here is a query to find out the connection setting for a session regarding Quoted_Identifiers
SELECT DB_NAME(s.database_id) AS DBName,
s.host_name ,
s.program_name ,
s.login_name,
s.quoted_identifier,
s.client_interface_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE DB_NAME(s.database_id) = '<Database Name>'
SELECT DB_NAME(s.database_id) AS DBName,
s.host_name ,
s.program_name ,
s.login_name,
s.quoted_identifier,
s.client_interface_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE DB_NAME(s.database_id) = '<Database Name>'
Extended Events to find a specific Stored Procedure |
|
I need to find a specific stored procedure and the parameters it is using at execution time. I have been trying to move away from Profiler and get with it regarding using Extended Events. Using the RPC_started and RPC_completed events and filtering on the object_name you can capture the events.
CREATE EVENT SESSION [XE_Stored_Procedure_Search] ON SERVER
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.rpc_starting (
ACTION ( sqlserver.database_name, sqlserver.session_id, sqlserver.sql_text )
WHERE ( [sqlserver].[like_i_sql_unicode_string]([object_name],
N'%stored procedure name%')
AND [sqlserver].[database_name] = N'your database'
) )
WITH ( MAX_MEMORY = 4096 KB ,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
MAX_DISPATCH_LATENCY = 30 SECONDS ,
MAX_EVENT_SIZE = 0 KB ,
MEMORY_PARTITION_MODE = NONE ,
TRACK_CAUSALITY = OFF ,
STARTUP_STATE = ON );
GO
CREATE EVENT SESSION [XE_Stored_Procedure_Search] ON SERVER
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.rpc_starting (
ACTION ( sqlserver.database_name, sqlserver.session_id, sqlserver.sql_text )
WHERE ( [sqlserver].[like_i_sql_unicode_string]([object_name],
N'%stored procedure name%')
AND [sqlserver].[database_name] = N'your database'
) )
WITH ( MAX_MEMORY = 4096 KB ,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
MAX_DISPATCH_LATENCY = 30 SECONDS ,
MAX_EVENT_SIZE = 0 KB ,
MEMORY_PARTITION_MODE = NONE ,
TRACK_CAUSALITY = OFF ,
STARTUP_STATE = ON );
GO
Application Timeout Tutorial |
|
Here is a good tutorial on understanding application timeouts.
Extended Event Session to see when plans are removed from the Plan Cache |
|
Here is great example and tutorial on how to see the behavior of your plan cache and also when plans are removed using Extended Events..
Scripted Extended Events Session to find RPC events |
|
I have been trying to push myself to use Extended Events. Here is a scripted Extended Events session for RPC events that will run for a specified number of seconds. Here are some of the parameters. At the end of the execution time, this process then queries the Extended event file.
Here is a link to a good page regarding shredding XEvent XML
https://itsalljustelectrons.blogspot.com/2019/02/shredding-xml-data-from-extended-events.html
/***************************************************************
***************************************************************/
--This will name your Extended Event session
DECLARE @filter VARCHAR(100);
SET @filter = 'all_RPC_queries';
--Define the Database you want to capture
DECLARE @database VARCHAR(100);
SET @database = '<your db name>';
--How long do you want it to run
DECLARE @run_time_seconds SMALLINT;
SET @run_time_seconds = 15;
--if you want to return both RPC_Started and RPC_Completed set this variable to zero
DECLARE @rpcStartset BIT = 0
--scripts starts below
******************************************************************************************
******************************************************************************************
SET NOCOUNT ON;
USE Qfiniti_platform;
DECLARE @filter VARCHAR(100);
SET @filter = 'all_RPC_queries';
DECLARE @database VARCHAR(100);
SET @database = '<your db name>';
DECLARE @run_time_seconds SMALLINT;
SET @run_time_seconds = 15;
/*
if you only want to return RPC Completed then set @rpcStartSet = 0
*/
DECLARE @rpcStartset BIT = 0
DECLARE @rpcStart VARCHAR(400)
IF @rpcStartset = 1
BEGIN
set @rpcStart = ',
ADD EVENT sqlserver.rpc_starting (
ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.sql_text, sqlserver.username )
Where [sqlserver].[database_name] = N' + '''' + @database + '''' + ')'
END
ELSE
BEGIN
SET @rpcStart = ''
END
DECLARE @cmd VARCHAR(MAX);
IF ( SELECT COUNT(*)
FROM sys.server_event_sessions
WHERE name = 'sp_search_' + @filter
) > 0
BEGIN
DECLARE @session VARCHAR(100);
SET @session = 'DROP EVENT SESSION ' + 'sp_search_' + @filter
+ ' ON SERVER';
EXECUTE (@session);
PRINT 'Dropped Extended Event Session..' + 'sp_search_' + @filter;
END;
PRINT 'Creating Extended Event Session';
SET @cmd = 'CREATE EVENT SESSION [sp_search_' + @filter + '] ON SERVER
ADD EVENT sqlserver.rpc_completed ( SET collect_statement = ( 1 )
ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.sql_text, sqlserver.username )
Where [sqlserver].[database_name] = N' + '''' + @database + ''')'+@rpcstart+'
ADD TARGET package0.asynchronous_file_target
(SET filename =' + '''' + 'c:\sp_capture.xel' + '''' + ',
metadatafile = ' + '''' + 'c:\sp_capture.xem' + '''' + ',
max_file_size=5,
max_rollover_files=0) --resets files at creation
WITH ( MAX_MEMORY = 4096 KB ,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
MAX_DISPATCH_LATENCY = 5 SECONDS ,
MAX_EVENT_SIZE = 0 KB ,
MEMORY_PARTITION_MODE = NONE ,
TRACK_CAUSALITY = OFF ,
STARTUP_STATE = ON);';
--PRINT @cmd
EXEC (@cmd);
DECLARE @start VARCHAR(200);
SET @start = 'ALTER EVENT SESSION sp_search_' + @filter + CHAR(13)
+ ' ON SERVER STATE = Start';
--PRINT @start
EXEC(@start);
DECLARE @i INT = 1;
WHILE ( 1 = 1 )
BEGIN
IF @i <= @run_time_seconds
BEGIN
PRINT 'waiting ' + CAST(@i AS VARCHAR(3)) + '/'
+ CAST(@run_time_seconds AS VARCHAR(3));
WAITFOR DELAY '00:00:01';
SET @i = @i + 1;
END;
ELSE
BEGIN
PRINT 'Run time completed';
BREAK;
END;
END;
PRINT 'Stop Extended Event Session' + ' sp_search_' + @filter;
DECLARE @stop VARCHAR(200);
SET @stop = 'ALTER EVENT SESSION sp_search_' + @filter + CHAR(13)
+ ' ON SERVER STATE = Stop';
--PRINT @stop
EXEC (@stop);
PRINT 'Drop Extended Event Session' + ' sp_search_' + @filter;
DECLARE @drop VARCHAR(200);
SET @drop = 'DROP EVENT SESSION sp_search_' + @filter + ' ON SERVER;';
--PRINT @drop
EXEC (@drop);
PRINT 'Query event session XML';
WITH events_cte
AS ( SELECT DATEADD(mi,
DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]',
'datetime2')) AS [event time] ,
xevents.event_data.value('(event/action[@name="database_name"]/value)[1]',
'nvarchar(max)') AS [database_name] ,
xevents.event_data.value('(event/@name)[1]',
'varchar(100)') AS event_type ,
xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]',
'nvarchar(128)') AS [client_app_name] ,
xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]',
'nvarchar(max)') AS [client_host_name] ,
xevents.event_data.value('(event/action[@name="username"]/value)[1]',
'nvarchar(max)') AS [user] ,
xevents.event_data.value('(event/data[@name="object_name"]/value)[1]',
'nvarchar(max)') AS sp_name ,
xevents.event_data.value('(event/data[@name="statement"]/value)[1]',
'nvarchar(max)') AS sp_statement ,
xevents.event_data.value('(event/data[@name="duration"]/value)[1]',
'bigint') AS [duration_ms] ,
xevents.event_data.value('(event/data[@name="cpu_time"]/value)[1]',
'bigint') AS [cpu_time_ms] ,
xevents.event_data.value('(event/data[@name="physical_reads"]/value)[1]',
'bigint') AS [physical_reads] ,
xevents.event_data.value('(event/data[@name="logical_reads"]/value)[1]',
'bigint') AS [logical_reads] ,
xevents.event_data.value('(event/data[@name="write"]/value)[1]',
'bigint') AS [writes] ,
xevents.event_data.value('(event/data[@name="row_count"]/value)[1]',
'bigint') AS [row_count]
FROM sys.fn_xe_file_target_read_file('C:\sp_capture*.xel',
'C:\sp_capture*.xem',
NULL, NULL)
CROSS APPLY ( SELECT CAST(event_data AS XML) AS event_data
) AS xevents
)
SELECT events_cte.[event time] ,
events_cte.database_name ,
events_cte.sp_name ,
events_cte.[user] ,
events_cte.event_type ,
events_cte.sp_statement ,
ISNULL(CAST(events_cte.logical_reads AS VARCHAR(100)), '--') AS logical_reads ,
ISNULL(CAST(events_cte.physical_reads AS VARCHAR(100)), '--') AS physical_reads ,
ISNULL(CAST(events_cte.writes AS VARCHAR(100)), '--') AS Writes ,
CASE WHEN events_cte.event_type = 'rpc_starting' THEN '--'
WHEN events_cte.event_type = 'rpc_completed'
THEN CAST(events_cte.row_count AS VARCHAR(20))
END AS Row_count ,
CASE WHEN events_cte.event_type = 'rpc_starting' THEN '--'
ELSE CAST(ps.execution_count AS VARCHAR(100))
END AS execution_count ,
CASE WHEN events_cte.event_type = 'rpc_completed'
THEN CAST(ps.last_elapsed_time / 1000.0 AS VARCHAR(100))
ELSE '--'
END AS last_elapsed_time_millisecond ,
CASE WHEN events_cte.event_type = 'rpc_starting' THEN '--'
WHEN events_cte.event_type = 'rpc_completed'
THEN CAST(( ps.total_elapsed_time / ps.execution_count
/ 1000.0 ) AS VARCHAR(100))
END AS AVG_Execution_time_millisecond ,
CASE WHEN events_cte.event_type = 'rpc_completed'
THEN qp.query_plan
ELSE CAST('see below' AS CHAR(9))
END AS query_plan
FROM events_cte
JOIN sys.procedures p ON p.name = events_cte.sp_name
JOIN sys.dm_exec_procedure_stats ps ON ps.object_id = p.object_id
AND p.name = OBJECT_NAME(ps.object_id)
JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = ps.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY [event time] ,
[event_type] DESC;
Here is a link to a good page regarding shredding XEvent XML
https://itsalljustelectrons.blogspot.com/2019/02/shredding-xml-data-from-extended-events.html
/***************************************************************
***************************************************************/
--This will name your Extended Event session
DECLARE @filter VARCHAR(100);
SET @filter = 'all_RPC_queries';
--Define the Database you want to capture
DECLARE @database VARCHAR(100);
SET @database = '<your db name>';
--How long do you want it to run
DECLARE @run_time_seconds SMALLINT;
SET @run_time_seconds = 15;
--if you want to return both RPC_Started and RPC_Completed set this variable to zero
DECLARE @rpcStartset BIT = 0
--scripts starts below
******************************************************************************************
******************************************************************************************
SET NOCOUNT ON;
USE Qfiniti_platform;
DECLARE @filter VARCHAR(100);
SET @filter = 'all_RPC_queries';
DECLARE @database VARCHAR(100);
SET @database = '<your db name>';
DECLARE @run_time_seconds SMALLINT;
SET @run_time_seconds = 15;
/*
if you only want to return RPC Completed then set @rpcStartSet = 0
*/
DECLARE @rpcStartset BIT = 0
DECLARE @rpcStart VARCHAR(400)
IF @rpcStartset = 1
BEGIN
set @rpcStart = ',
ADD EVENT sqlserver.rpc_starting (
ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.sql_text, sqlserver.username )
Where [sqlserver].[database_name] = N' + '''' + @database + '''' + ')'
END
ELSE
BEGIN
SET @rpcStart = ''
END
DECLARE @cmd VARCHAR(MAX);
IF ( SELECT COUNT(*)
FROM sys.server_event_sessions
WHERE name = 'sp_search_' + @filter
) > 0
BEGIN
DECLARE @session VARCHAR(100);
SET @session = 'DROP EVENT SESSION ' + 'sp_search_' + @filter
+ ' ON SERVER';
EXECUTE (@session);
PRINT 'Dropped Extended Event Session..' + 'sp_search_' + @filter;
END;
PRINT 'Creating Extended Event Session';
SET @cmd = 'CREATE EVENT SESSION [sp_search_' + @filter + '] ON SERVER
ADD EVENT sqlserver.rpc_completed ( SET collect_statement = ( 1 )
ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.sql_text, sqlserver.username )
Where [sqlserver].[database_name] = N' + '''' + @database + ''')'+@rpcstart+'
ADD TARGET package0.asynchronous_file_target
(SET filename =' + '''' + 'c:\sp_capture.xel' + '''' + ',
metadatafile = ' + '''' + 'c:\sp_capture.xem' + '''' + ',
max_file_size=5,
max_rollover_files=0) --resets files at creation
WITH ( MAX_MEMORY = 4096 KB ,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
MAX_DISPATCH_LATENCY = 5 SECONDS ,
MAX_EVENT_SIZE = 0 KB ,
MEMORY_PARTITION_MODE = NONE ,
TRACK_CAUSALITY = OFF ,
STARTUP_STATE = ON);';
--PRINT @cmd
EXEC (@cmd);
DECLARE @start VARCHAR(200);
SET @start = 'ALTER EVENT SESSION sp_search_' + @filter + CHAR(13)
+ ' ON SERVER STATE = Start';
--PRINT @start
EXEC(@start);
DECLARE @i INT = 1;
WHILE ( 1 = 1 )
BEGIN
IF @i <= @run_time_seconds
BEGIN
PRINT 'waiting ' + CAST(@i AS VARCHAR(3)) + '/'
+ CAST(@run_time_seconds AS VARCHAR(3));
WAITFOR DELAY '00:00:01';
SET @i = @i + 1;
END;
ELSE
BEGIN
PRINT 'Run time completed';
BREAK;
END;
END;
PRINT 'Stop Extended Event Session' + ' sp_search_' + @filter;
DECLARE @stop VARCHAR(200);
SET @stop = 'ALTER EVENT SESSION sp_search_' + @filter + CHAR(13)
+ ' ON SERVER STATE = Stop';
--PRINT @stop
EXEC (@stop);
PRINT 'Drop Extended Event Session' + ' sp_search_' + @filter;
DECLARE @drop VARCHAR(200);
SET @drop = 'DROP EVENT SESSION sp_search_' + @filter + ' ON SERVER;';
--PRINT @drop
EXEC (@drop);
PRINT 'Query event session XML';
WITH events_cte
AS ( SELECT DATEADD(mi,
DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]',
'datetime2')) AS [event time] ,
xevents.event_data.value('(event/action[@name="database_name"]/value)[1]',
'nvarchar(max)') AS [database_name] ,
xevents.event_data.value('(event/@name)[1]',
'varchar(100)') AS event_type ,
xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]',
'nvarchar(128)') AS [client_app_name] ,
xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]',
'nvarchar(max)') AS [client_host_name] ,
xevents.event_data.value('(event/action[@name="username"]/value)[1]',
'nvarchar(max)') AS [user] ,
xevents.event_data.value('(event/data[@name="object_name"]/value)[1]',
'nvarchar(max)') AS sp_name ,
xevents.event_data.value('(event/data[@name="statement"]/value)[1]',
'nvarchar(max)') AS sp_statement ,
xevents.event_data.value('(event/data[@name="duration"]/value)[1]',
'bigint') AS [duration_ms] ,
xevents.event_data.value('(event/data[@name="cpu_time"]/value)[1]',
'bigint') AS [cpu_time_ms] ,
xevents.event_data.value('(event/data[@name="physical_reads"]/value)[1]',
'bigint') AS [physical_reads] ,
xevents.event_data.value('(event/data[@name="logical_reads"]/value)[1]',
'bigint') AS [logical_reads] ,
xevents.event_data.value('(event/data[@name="write"]/value)[1]',
'bigint') AS [writes] ,
xevents.event_data.value('(event/data[@name="row_count"]/value)[1]',
'bigint') AS [row_count]
FROM sys.fn_xe_file_target_read_file('C:\sp_capture*.xel',
'C:\sp_capture*.xem',
NULL, NULL)
CROSS APPLY ( SELECT CAST(event_data AS XML) AS event_data
) AS xevents
)
SELECT events_cte.[event time] ,
events_cte.database_name ,
events_cte.sp_name ,
events_cte.[user] ,
events_cte.event_type ,
events_cte.sp_statement ,
ISNULL(CAST(events_cte.logical_reads AS VARCHAR(100)), '--') AS logical_reads ,
ISNULL(CAST(events_cte.physical_reads AS VARCHAR(100)), '--') AS physical_reads ,
ISNULL(CAST(events_cte.writes AS VARCHAR(100)), '--') AS Writes ,
CASE WHEN events_cte.event_type = 'rpc_starting' THEN '--'
WHEN events_cte.event_type = 'rpc_completed'
THEN CAST(events_cte.row_count AS VARCHAR(20))
END AS Row_count ,
CASE WHEN events_cte.event_type = 'rpc_starting' THEN '--'
ELSE CAST(ps.execution_count AS VARCHAR(100))
END AS execution_count ,
CASE WHEN events_cte.event_type = 'rpc_completed'
THEN CAST(ps.last_elapsed_time / 1000.0 AS VARCHAR(100))
ELSE '--'
END AS last_elapsed_time_millisecond ,
CASE WHEN events_cte.event_type = 'rpc_starting' THEN '--'
WHEN events_cte.event_type = 'rpc_completed'
THEN CAST(( ps.total_elapsed_time / ps.execution_count
/ 1000.0 ) AS VARCHAR(100))
END AS AVG_Execution_time_millisecond ,
CASE WHEN events_cte.event_type = 'rpc_completed'
THEN qp.query_plan
ELSE CAST('see below' AS CHAR(9))
END AS query_plan
FROM events_cte
JOIN sys.procedures p ON p.name = events_cte.sp_name
JOIN sys.dm_exec_procedure_stats ps ON ps.object_id = p.object_id
AND p.name = OBJECT_NAME(ps.object_id)
JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = ps.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY [event time] ,
[event_type] DESC;
Read the SQL Server Error Log |
|
Had a customer with an 11GB SQL Server Error Log file and it wouldn't pull up in SSMS. Also need to get the customer to cycle their error log.
Here is a way to read that log if you have too. The parameter 0 returns current log.
CREATE TABLE #errorlog
(logdate DATETIME2,
processinfo VARCHAR(20),
errortext VARCHAR(2000))
INSERT INTO #errorlog
( logdate, processinfo, errortext )
EXEC xp_readerrorlog 0
SELECT * FROM #errorlog
or if you want to check for just a specific error you can do this.
xp_readerrorlog 0, 1, "Login","failed"
How to do this with powershell.
$server = get-item sqlserver:\sql\$env:computername\default
$server.readerrorlog() | where {$_.text -match 'error'}
Here is how to bounce the SQL Server Error Log
EXEC sp_cycle_errorlog
Here is a way to read that log if you have too. The parameter 0 returns current log.
CREATE TABLE #errorlog
(logdate DATETIME2,
processinfo VARCHAR(20),
errortext VARCHAR(2000))
INSERT INTO #errorlog
( logdate, processinfo, errortext )
EXEC xp_readerrorlog 0
SELECT * FROM #errorlog
or if you want to check for just a specific error you can do this.
xp_readerrorlog 0, 1, "Login","failed"
How to do this with powershell.
$server = get-item sqlserver:\sql\$env:computername\default
$server.readerrorlog() | where {$_.text -match 'error'}
Here is how to bounce the SQL Server Error Log
EXEC sp_cycle_errorlog
TempDB spills |
|
Had some issue with TempDB spills today. Here is an easy to understand explanation of TempDB spills. Click on the link below.
http://rusanu.com/2011/10/19/understanding-hash-sort-and-exchange-spill-events/
http://rusanu.com/2011/10/19/understanding-hash-sort-and-exchange-spill-events/
Deadlocks and Stuff!!! |
|
Here is some info you might find handy when encountering and trying to troubleshoot deadlocks.
You can enable trace flags to write DeadLock info to the SQL Server Log. Its not real pretty but, its better than nothing.
-1 turns on the trace flag globally for all sessions. If you want these to always be on, consider adding the trace flags as startup parameters.
DBCC TraceON(1222,-1) --This will list by process and resource
DBCC TraceON()1204,-1) --This will list the information by node
If you want to use Extended Events, you can use either the system_health session or you can create you own session looking to capture the "xml_deadlock_report".
To return the deadlock graph here is a query to get that from the system_health file. You possibly need to make changes to the sys.fn_xe_file_target_read_file file path.
Here are two queries.........................
--Query #1
SELECT
CONVERT(XML, event_data).query('/event/data/value/child::*') ,
CONVERT(XML, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]',
'datetime') AS Execution_Time
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel',
NULL, NULL, NULL)
WHERE object_name LIKE 'xml_deadlock_report'
--Query #2 I can't remember where I got this query. I didn't write it as I suck with XPath and XQuery.
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
DeadlockEventXML.value('(//process/inputbuf)[1]','nvarchar(max)') AS Victim_Query,
DeadlockEventXML.value('(//process/inputbuf)[2]','nvarchar(max)') AS Query_That_Lived,
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
DeadlockEventXML
FROM
(
SELECT
XEvent.query('.') AS DeadlockEventXML,
Data.TargetData
FROM
(
SELECT
CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS DeadlockInfo
Something else super cool that Brent Ozar blogged about is using SentryOne Plan Explorer to play back the DeadLock graph.
Save the XML returned from the above query as an .xdl file and then open with SentryOne Plan Explorer.
Here is more info on this. Click the link below.
https://www.brentozar.com/archive/2019/02/you-know-what-your-deadlock-graphs-need-animation/
Also check out the video below to better understand the deadlock info return to the log
You can enable trace flags to write DeadLock info to the SQL Server Log. Its not real pretty but, its better than nothing.
-1 turns on the trace flag globally for all sessions. If you want these to always be on, consider adding the trace flags as startup parameters.
DBCC TraceON(1222,-1) --This will list by process and resource
DBCC TraceON()1204,-1) --This will list the information by node
If you want to use Extended Events, you can use either the system_health session or you can create you own session looking to capture the "xml_deadlock_report".
To return the deadlock graph here is a query to get that from the system_health file. You possibly need to make changes to the sys.fn_xe_file_target_read_file file path.
Here are two queries.........................
--Query #1
SELECT
CONVERT(XML, event_data).query('/event/data/value/child::*') ,
CONVERT(XML, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]',
'datetime') AS Execution_Time
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel',
NULL, NULL, NULL)
WHERE object_name LIKE 'xml_deadlock_report'
--Query #2 I can't remember where I got this query. I didn't write it as I suck with XPath and XQuery.
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
DeadlockEventXML.value('(//process/inputbuf)[1]','nvarchar(max)') AS Victim_Query,
DeadlockEventXML.value('(//process/inputbuf)[2]','nvarchar(max)') AS Query_That_Lived,
DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
DeadlockEventXML
FROM
(
SELECT
XEvent.query('.') AS DeadlockEventXML,
Data.TargetData
FROM
(
SELECT
CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS DeadlockInfo
Something else super cool that Brent Ozar blogged about is using SentryOne Plan Explorer to play back the DeadLock graph.
Save the XML returned from the above query as an .xdl file and then open with SentryOne Plan Explorer.
Here is more info on this. Click the link below.
https://www.brentozar.com/archive/2019/02/you-know-what-your-deadlock-graphs-need-animation/
Also check out the video below to better understand the deadlock info return to the log
SP Times and other Metadata including query plan |
|
Find the execution times of SPs in a DB
SELECT OBJECT_NAME(object_id, database_id) AS [OBJECT_NAME] ,
last_elapsed_time / 1000000.0 AS last_elapsed_sec ,
( total_elapsed_time / execution_count ) / 1000000.0 AS AVG_Execution_sec ,
max_elapsed_time / 1000000.0 AS max_elapsed_sec ,
execution_count ,
last_physical_reads ,
last_logical_reads ,
max_logical_reads ,
max_logical_writes ,
cached_time ,
last_execution_time ,
QueryPlans.query_plan ,
sqltext.text
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = '<database name>'
AND OBJECT_NAME(object_id, database_id) = '<sp name>'
SELECT OBJECT_NAME(object_id, database_id) AS [OBJECT_NAME] ,
last_elapsed_time / 1000000.0 AS last_elapsed_sec ,
( total_elapsed_time / execution_count ) / 1000000.0 AS AVG_Execution_sec ,
max_elapsed_time / 1000000.0 AS max_elapsed_sec ,
execution_count ,
last_physical_reads ,
last_logical_reads ,
max_logical_reads ,
max_logical_writes ,
cached_time ,
last_execution_time ,
QueryPlans.query_plan ,
sqltext.text
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = '<database name>'
AND OBJECT_NAME(object_id, database_id) = '<sp name>'
vCPUs Hot-added but SQL can not see them |
|
Server was having CPU issues. It was pegged at 100%. So we added 2 additional vCPUs. But we noticed SQL wasn't using the CPUs via the below Query.
SELECT scheduler_id, cpu_id, status, is_online
FROM sys.dm_os_schedulers
Found that you need to run
RECONFIGURE
GO
to basically reload the configuration settings for SQL.
Once reconfigure is executed you will this in sys.dm_os_schedulers
SELECT scheduler_id, cpu_id, status, is_online
FROM sys.dm_os_schedulers
Found that you need to run
RECONFIGURE
GO
to basically reload the configuration settings for SQL.
Once reconfigure is executed you will this in sys.dm_os_schedulers