While working on performance monitoring , one of my forum member Cadavre, introduced me about DMV's. These are really handy. You can extract system stats directly from dmv. Remember that dmv's scope is limited to instance. If you will restart instance , system will wipe older and collects stats after restart.
Microsoft introduced dynamic
management views (DMV) and functions (DMF) with SQL Server 2005. So what are
DMV's and DMF's? How do you use these dynamic management objects? DMVs and DMFs
are a mechanism to allow you to look at the internal workings of SQL Server
using TSQL. They allow you an easy method for monitoring what SQL Server is
doing and how it is performing. They replace the need to query the system
tables or using other awkward methods of retrieving system information that you
had to use with SQL Server 2000.
Some examples
=========================================
--–Queries taking longest elapsed time:
==========================================
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds ,
qs.total_elapsed_time / 1000000.0 AS total_seconds ,
qs.execution_count ,
SUBSTRING(qt.text, qs.statement_start_offset / 2,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS individual_query ,
o.name AS object_name ,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC ;
===============================
---–Queries doing most I/O:
===============================
SELECT TOP 10
( total_logical_reads + total_logical_writes ) / qs.execution_count AS average_IO ,
( total_logical_reads + total_logical_writes ) AS total_IO ,
qs.execution_count AS execution_count ,
SUBSTRING(qt.text, qs.statement_start_offset / 2,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS indivudual_query ,
o.name AS object_name ,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC ;
====================================
SELECT TOP 20
SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) ,
qs.execution_count ,
qs.total_logical_reads ,
qs.last_logical_reads ,
qs.total_logical_writes ,
qs.last_logical_writes ,
qs.total_worker_time ,
qs.last_worker_time ,
qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
qs.last_execution_time ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
--ORDER BY qs.total_logical_writes DESC -- logical writes
--ORDER BY qs.total_worker_time DESC -- CPU time
--ORDER BY total_elapsed_time_in_S DESC -- time
Friday, February 22, 2013
Thursday, February 21, 2013
Read MSSQL 2005 trace Table
while working on a major project, I was assigned to track database activity. Objective was to find long running queries. It seen easy but i was not so proficient in MSSQL profiler :(
I have created a template and applied dbname and userName filter as required.
Performance
After running trace,Now I have data in my table ,next step is to query that data.I found it is difficult for new user to extract data.So I thought why not to share it with others.
--Definitation of column used in query
--CPU: Amount of CPU time (in milliseconds) used by the event.But when store to table/file it will be
--10^3
--Duration:Amount of time (in milliseconds) taken by the event.But when store to table/file it will be
--micro second 10^6
--Writes:Number of physical disk writes performed by the server on behalf of the event.
--Reads:Number of logical disk reads performed by the server on behalf of the event.
--Event Class:Type of event class captured.
--TextData:Text value dependent on the event class captured in the trace. However, if you are tracing a parametrized query, the variables will not be displayed with data values in the TextData column.
--Query to read trace table
SELECT
COUNT(*) AS TotalExecutions ,
EventClass ,
CAST(TextData AS NVARCHAR(4000)) AS query ,
SUM(Duration) / 1000000 AS DurationTotal_Sec ,
--SUM(Duration) / 1000000/COUNT(*) AS DurationTotal_per_call_Sec ,
SUM(CPU)/ 1000 AS CPUTotal_Sec ,
SUM(Reads) AS ReadsTotal ,
SUM(Writes) AS WritesTotal
FROM tmptracetable -- table that contains the trace results
--WHERE eventclass IN ( 12 ) --batch completed
--WHERE eventclass IN ( 41 ) --stmt completed
GROUP BY EventClass ,
CAST(TextData AS NVARCHAR(4000))
--un comment required order clause as per your needs
ORDER BY DurationTotal_sec DESC
--Order by ReadsTotal desc
--ORDER BY WritesTotal DESC
--ORDER BY CPUTotal_Sec DESC
--ORDER BY DurationTotal_per_call_Sec DESC
In trace table you will have event class id, if you want to know event name to filter your trace search, use the
following query
-- Query to view event class name
SELECT DISTINCT
TE.name ,
t.eventclass
FROM tmptracetable T -- table that contains the trace results
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
--WHERE t.eventclass IN (12,41)
ORDER BY t.eventclass
/*
name eventclass
SQL:BatchCompleted 12
SQL:BatchStarting 13
SQL:StmtStarting 40
SQL:StmtCompleted 41
Showplan All 97
Showplan XML 122
Performance statistics 165
*/
and rest is ongoing :)
I have created a template and applied dbname and userName filter as required.
Performance
- Performance Statistics
- ShowPlanAll
- ShowPlanXML
- SQL batch completed
- Sql batchStarting
- Sql StmtCompleted
- Sql StmtStarting
After running trace,Now I have data in my table ,next step is to query that data.I found it is difficult for new user to extract data.So I thought why not to share it with others.
--Definitation of column used in query
--CPU: Amount of CPU time (in milliseconds) used by the event.But when store to table/file it will be
--10^3
--Duration:Amount of time (in milliseconds) taken by the event.But when store to table/file it will be
--micro second 10^6
--Writes:Number of physical disk writes performed by the server on behalf of the event.
--Reads:Number of logical disk reads performed by the server on behalf of the event.
--Event Class:Type of event class captured.
--TextData:Text value dependent on the event class captured in the trace. However, if you are tracing a parametrized query, the variables will not be displayed with data values in the TextData column.
--Query to read trace table
SELECT
COUNT(*) AS TotalExecutions ,
EventClass ,
CAST(TextData AS NVARCHAR(4000)) AS query ,
SUM(Duration) / 1000000 AS DurationTotal_Sec ,
--SUM(Duration) / 1000000/COUNT(*) AS DurationTotal_per_call_Sec ,
SUM(CPU)/ 1000 AS CPUTotal_Sec ,
SUM(Reads) AS ReadsTotal ,
SUM(Writes) AS WritesTotal
FROM tmptracetable -- table that contains the trace results
--WHERE eventclass IN ( 12 ) --batch completed
--WHERE eventclass IN ( 41 ) --stmt completed
GROUP BY EventClass ,
CAST(TextData AS NVARCHAR(4000))
--un comment required order clause as per your needs
ORDER BY DurationTotal_sec DESC
--Order by ReadsTotal desc
--ORDER BY WritesTotal DESC
--ORDER BY CPUTotal_Sec DESC
--ORDER BY DurationTotal_per_call_Sec DESC
In trace table you will have event class id, if you want to know event name to filter your trace search, use the
following query
-- Query to view event class name
SELECT DISTINCT
TE.name ,
t.eventclass
FROM tmptracetable T -- table that contains the trace results
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
--WHERE t.eventclass IN (12,41)
ORDER BY t.eventclass
/*
name eventclass
SQL:BatchCompleted 12
SQL:BatchStarting 13
SQL:StmtStarting 40
SQL:StmtCompleted 41
Showplan All 97
Showplan XML 122
Performance statistics 165
*/
and rest is ongoing :)
Subscribe to:
Posts (Atom)