(DIR) Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
(HTM) https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
(DIR) Return to: Scripts
*****************************************************
#Post#: 128--------------------------------------------------
SQL Server Trace Queries
By: srinivasma_exceldbp Date: December 30, 2014, 5:44 am
---------------------------------------------------------
/*Listing 1: The SlowQueries trace definition*/
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* */
/****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @DateTime DATETIME
SET @DateTime = '2012-08-10 21:00:34.207' --** change this
SET @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an
appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The
.trc extension
-- will be appended to the filename automatically. If you are
writing from
-- remote server to local drive, please use UNC path and make
sure server has
-- write access to your network share
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,
N'InsertFileNameHere',
@maxfilesize, @Datetime
IF ( @rc != 0 )
GOTO error
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
EXEC sp_trace_setfilter @TraceID, 10, 0, 7,
N'SQL Server Profiler -
05332afa-db8c-4ded-beca-38e2548e246b'
SET @bigintfilter = 1000000
EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT TraceID = @TraceID
GOTO finish
error:
SELECT ErrorCode = @rc
finish:
GO
/*Listing 2: The modified sp_trace_create section of the
SlowQueries trace definition*/
SET @DateTime = DATEADD(hh, 1, GETDATE())
SET @maxfilesize = 1024
EXEC @rc = sp_trace_create @TraceID OUTPUT, 2,
N'\\Server1\Share1\MyTrace',
@maxfilesize, @Datetime;
GO
/*Listing 3: Stopping and trace then closing it and deleting its
definition*/
exec sp_trace_setstatus @TraceID = 2, @status = 0; --stop
exec sp_trace_setstatus @TraceID = 2, @staus = 2;
--close/delete
GO
/*Listing 4: Retrieving trace details with fn_trace_getinfo*/
select * from fn_trace_getinfo(0);
GO
/*Listing 5: Importing trace data into a table*/
USE MyDatabase
GO
--import the first file (MyTrace.trc)
SELECT *
INTO MyTrace
FROM FN_TRACE_GETTABLE('\\Server1\Share1\MyTrace.trc', 1);
-- import all the files
SELECT *
INTO MyTrace
FROM FN_TRACE_GETTABLE('\\Server1\Share1\MyTrace.trc',
DEFAULT);
-- import a specifc number of files
SELECT *
INTO MyTrace
FROM FN_TRACE_GETTABLE('\\Server1\Share1\MyTrace.trc', 3);
GO
/*Listing 6: Batching trace file imports*/
SELECT *
INTO MyTrace
FROM FN_TRACE_GETTABLE('\\Server1\Share1\MyTrace.trc', 10);
INSERT INTO MyTrace
SELECT *
FROM FN_TRACE_GETTABLE('\\Server1\Share1\MyTrace_10.trc',
10);
GO
/*Listing 7: Indexing the MyTrace table*/
CREATE NONCLUSTERED INDEX idx_Duration ON MyTrace(Duration);
CREATE NONCLUSTERED INDEX idx_CPU ON MyTrace(CPU);
CREATE NONCLUSTERED INDEX idx_Reads ON MyTrace(Reads);
GO
/*Listing 8: Read-intensive queries in our sample workload*/
USE MyDatabase
GO
SELECT TOP 1000
e.name AS EventName ,
TextData ,
Reads ,
CPU ,
Duration / 1000 AS Duration_in_milliseconds ,
Writes ,
StartTime
FROM MyTrace t
INNER JOIN sys.trace_events e ON t.EventClass =
e.trace_event_id
ORDER BY Reads DESC;
GO
/*Listing 9: Which queries use the most CPU?*/
SELECT TOP 1000
( CPU * 100 ) / ( ( SELECT SUM(CPU)
FROM MyTrace
) * 1.0 ) AS PercentCPU ,
TextData
FROM MyTrace
ORDER BY PercentCPU DESC;
GO
/*Listing 10: CPU by Object accessed*/
DECLARE @TotalCPU BIGINT
SELECT @TotalCPU = SUM(CPU)
FROM MyTrace
SELECT TOP 1000
( CPU * 100 ) / ( @TotalCPU * 1.0 ) AS PercentCPU ,
ObjectName
FROM ( SELECT SUM(CPU) AS CPU ,
ObjectName
FROM MyTrace
GROUP BY ObjectName
) t
ORDER BY PercentCPU DESC;
GO
/*Listing 11: Grouping by TextData*/
DECLARE @TotalCPU BIGINT
SELECT @TotalCPU = SUM(CPU)
FROM MyTrace
SELECT TOP 1000
( CPU * 100 ) / ( @TotalCPU * 1.0 ) AS PercentCPU ,
TextData
FROM ( SELECT SUM(CPU) AS CPU ,
TextData
FROM ( SELECT CPU ,
CONVERT(NVARCHAR(MAX), TextData)
AS TextData
FROM MyTrace
) t1
GROUP BY TextData
) t2
ORDER BY PercentCPU DESC;
GO
/*Listing 12: Finding frequently executed queries*/
SELECT COUNT(*) AS ExecCount ,
ObjectName ,
AVG(Duration / 1000) AS AvgDuration
FROM MyTrace
WHERE ObjectName <> 'sp_reset_connection' -- connection
pooling
GROUP BY ObjectName
ORDER BY ExecCount DESC;
GO
/*Listing 13: Execute
sp_SQLskills_ConvertTraceToExtendedEvents*/
EXECUTE sp_SQLskills_ConvertTraceToExtendedEvents
@TraceID = 2,
@SessionName = 'XE_SlowQueries2',
@PrintOutput = 1,
@Execute = 0;
/*Listing 14: Output of
sp_SQLskills_ConvertTraceToExtendedEvents using MyTrace
definition*/
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name =
'XE_SlowQueries2')
DROP EVENT SESSION [XE_SlowQueries2] ON SERVER;
GO
CREATE EVENT SESSION [XE_SlowQueries2]
ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION
(
sqlserver.server_principal_name
, sqlserver.session_id
-- BinaryData not implemented in XE for this event
)
WHERE
(
duration >= 1000000
)
),
ADD EVENT sqlserver.sql_batch_completed(
ACTION
(
sqlserver.server_principal_name
, sqlserver.session_id
)
WHERE
(
duration >= 1000000
)
)
ADD TARGET package0.event_file
(
SET filename = '\\Server1\Share1\XE_SlowQueries2.xel',
max_file_size = 1024,
max_rollover_files = 0
)
/*Listing 15: Start XE_MyTrace*/
ALTER EVENT SESSION [XE_SlowQueries2]
ON SERVER
STATE = start;
/*Listing 16: Stop and delete XE_MyTrace*/
ALTER EVENT SESSION [XE_SlowQueries2] ON SERVER STATE = stop;
DROP EVENT SESSION [XE_SlowQueries2] ON SERVER;
*****************************************************