Query data from a SQL trace file directly.
SELECT TextData, StartTime, Duration
FROM fn_trace_gettable('X:\Path\To\TraceFile.trc', default)
WHERE EventClass = 41;
For a complete list of eventclasses you can use the query below.
SELECT * FROM sys.trace_events
The query below returns a few frequently used counters. Page life expectancy, Lazy writes/sec, Target Server Memory, Total Server Memory and Processor Utilizaton. If exist PAGEIOLATCH waiting tasks are returned.
SET QUOTED_IDENTIFIER ON
-- New temp table...
CREATE TABLE #tPrefCounters ([Name] nvarchar(40),[Value] int,[Info] nvarchar(50) )
GO
-- Prepare statements...
DECLARE @H1 int
EXEC sp_prepare @H1 OUTPUT, N'@P1 nvarchar(40), @P2 nvarchar(50)',
N'SELECT TOP(1) @P1 AS Name, cntr_value AS Value, @P2 AS Info FROM [master].[sys].[dm_os_performance_counters] WHERE counter_name = @P1', 1
-- Execute handles and fill #tPrefCounters table...
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Page life expectancy', N'Should be -gt 300 sec per 4GB mem'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Lazy writes/sec', N'(Sample #1)'
WAITFOR DELAY '00:00:01'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Lazy writes/sec', N'(Sample #2) Should not climb much (low or zero)'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Target Server Memory (KB)', N'Optimal memory under current load'
INSERT INTO #tPrefCounters EXEC sp_execute @H1, N'Total Server Memory (KB)', N'Memory used by instance, should be close to target'
-- Add CPU Utilization...
INSERT INTO #tPrefCounters
SELECT TOP(1) 'Processor Utilization' AS Name,
x.xrecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS Value,
'% CPU Utilization' AS Info
FROM ( SELECT [timestamp], CAST(record AS XML) AS xrecord FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' ) AS x
ORDER by x.[timestamp] DESC
-- Add PAGEIOLATCH wait stats...
INSERT INTO #tPrefCounters
SELECT top(5) wait_type AS Name, wait_duration_ms AS Value, 'Long waits may indicate disk io problems'
FROM [master].[sys].[dm_os_waiting_tasks] WHERE wait_type LIKE 'PAGEIOLATCH%' ORDER BY wait_duration_ms DESC
-- Unprepare statements...
EXEC sp_unprepare @H1
-- Display prefered counters...
SELECT * FROM #tPrefCounters
GO
-- Remove old temp table...
DROP TABLE #tPrefCounters
GO
Met de onderstaande oplossing kun je snel de CPU Utilization van de laatste 5 minuten ophalen (per minuut weergegeven).
SET QUOTED_IDENTIFIER ON
SELECT TOP(5) x.xrecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS CPUUTIL
FROM ( SELECT [timestamp], CAST(record AS XML) AS xrecord FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' ) AS x
ORDER by x.[timestamp] DESC
Met onderstaande query kun je het Query Execution Plan bekijken voor een lopende (probleem)query op basis van de session_id.
USE [master] SELECT * FROM sys.dm_exec_query_plan ( (SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = 53) ) GO
Ook handig is om performance gegevens te tonen in het messages scherm m.b.t. de query. Met onderstaande query kun je deze opties aanzetten binnen een sessie.
SET STATISTICS [TIME|IO] [ON|OFF];