Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 1: Using Dynamic Management Objects

Chapter 1: Using Dynamic Management Objects

• Unless you are the system administrator of the server, you will need to be granted one or two privileges to be able to use the DMOs: VIEW SERVER STATE and VIEW DATABASE STATE … P21.
• This chapter is just an introduction and a very general view of what will be explained in the next chapters.

Statistics About Queries Ran On SQL Server Since Last Restart

SELECT

       SUBSTRING(dest.TEXT,

                 (deqs.statement_start_offset/2)+1,(

CASE deqs.statement_end_offset WHEN -1 THEN

                        DATALENGTH(dest.TEXT)

                  ELSE deqs.statement_end_offset

                  END - deqs.statement_start_offset)/2) + 1)

AS statement_text, 

deqs.last_execution_time ,deqs.execution_count,deqs.last_worker_time as [Last CPU time],

deqs.last_physical_reads , deqs.last_logical_reads,deqs.last_logical_writes,

deqs.last_elapsed_time

   FROM sys.dm_exec_query_stats AS deqs

            CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Note:
■ Logical Reads — Represents the number of pages read from the data cache.
■ Physical Reads — If the required page is not in cache, it will be read from disk.
■ Elapsed Time  — CPU time + waits