SQL Server Query Tuning Series - Troubleshooting Query or Command Timeout
SQL Server Query Tuning Series - Troubleshooting Query or Command Timeout #jbswiki #querytuning
Restoring Required Databases for Query Tuning Session - • Video
use [JBSWIKI]
go
/* Blocking script
DROP table Tbl1_Check_TimeOut
create table Tbl1_Check_TimeOut(sno int)
begin tran
insert into Tbl1_Check_TimeOut values (1)
commit tran
*/
CREATE PROCEDURE [dbo].[Check_TimeOut]
AS
select * from Tbl1_Check_TimeOut where sno=1
GO
-- Use Xevents to capture timeout
EXEC [dbo].[Check_TimeOut];
GO
-- Also available in sys.dm_exec_query_stats
SELECT [database_id] ,
OBJECT_NAME([object_id]) AS [object_name] ,
[max_elapsed_time] ,
[plan_handle] ,
[sql_handle]
FROM [sys].[dm_exec_procedure_stats]
WHERE [max_elapsed_time] > 9000000; -- microseconds
--Solution - Not a great solution, but app team is happy
ALTER PROCEDURE [dbo].[Check_TimeOut]
AS
select * from Tbl1_Check_TimeOut (NOLOCK) where sno=1
GO
-- Don't forget to change your execution-timeout back
-- to the original value!
EXEC [dbo].[Check_TimeOut];
GO
DROP PROCEDURE [Check_TimeOut]
GO
use tempdb
go
if not exists (select * from sys.objects where name = 'JB_xevents_attention')
create table JB_xevents_attention (id int identity primary key, object_name nvarchar(256), event_data xml)
go
if exists (select * from sys.objects where name = 'JB_xev_attention')
drop view JB_xev_attention
go
create view JB_xev_attention as
select event_data.value ('(event/@name)[1]', 'varchar(max)') as event_name,
event_data.value ('(event/@timestamp)[1]', 'datetime') as event_timestamp,
event_data.value ('(event/data[@name="duration"]/value)[1]', 'bigint') as [duration],
event_data.value ('(event/data[@name="request_id"]/value)[1]', 'int') as [request_id],
event_data.value ('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') as action_client_app_name,
event_data.value ('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') as action_client_hostname,
event_data.value ('(event/action[@name="database_name"]/value)[1]', 'nvarchar(max)') as action_database_name,
event_data.value ('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(max)') as action_nt_username,
event_data.value ('(event/action[@name="session_id"]/value)[1]', 'int') as action_session_id,
event_data.value ('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as action_sql_text
from JB_xevents_attention where object_name in ('attention')
go
if exists (select * from sys.server_event_sessions where name = 'JB_xevents_attention')
drop event session JB_xevents_attention on server
go
create event session JB_xevents_attention on server
add event sqlserver.attention (action (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text) where counter <= 100
/* client_app_name != 'Microsoft SQL Server Management Studio - Query' and counter <= 100 */)
add target package0.event_file (set filename = N'c:\xevents\JB_xevents_attention.xel')
with (event_retention_mode = ALLOW_SINGLE_EVENT_LOSS, memory_partition_mode = NONE)
go
/*
alter event session JB_xevents_attention on server state = start
go
--Execute your workload
alter event session JB_xevents_attention on server state = stop
go
truncate table JB_xevents_attention
go
insert into JB_xevents_attention select object_name, convert(xml, event_data) as event_data from sys.fn_xe_file_target_read_file('c:\xevents\JB_xevents_attention*.*', null, null, null)
go
select object_name, count(*) as count from JB_xevents_attention
group by object_name
order by count(*) desc, object_name
go
select * from JB_xev_attention where action_database_name='JBSWIKI'
go
*/ Disclaimer:
The views expressed on this Video are mine alone and do not reflect the views of my company or anyone else. All postings on this Video are provided “AS IS” with no warranties, and confers no rights.
