Category Archives: Dynamic Management Views

Modified DMV’s in SQL Server 2008 R2 and SQL Server 2012

Dynamic Management Views October 11, 2013 1 Comment

In this post i will discuss about two dynamic management views that has been enhanced with new columns in SQL Server 2008 R2 and SQL Server 2012. The DMV’s are

1) sys.dm_exec_query_stats
2) sys.dm_exec_sessions

Let us begin with [sys.dm_exec_query_stats]
So what does this view reflect. It reflects performance data for stored query plans in SQL Server plan cache. The data is aggregated and is helpful for performance analysis. When a query plan is eliminated from the plan cache, the related row is also removed. In SQL Server 2008 R2 onwards, 3 new columns have been added to this DMV. They are as follows as explained on Technet.

a) last_rows: The number of rows returned by the last execution of the query.
b) min_rows: Minimum number of rows returned by the query since last recompilation of the query and execution of the plan.
c) max_rows: Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled.

The next dmv that was modified with new columns are [sys.dm_exec_sessions]
This view returns a row per authenticated session on SQL Server. It shows information about all the active user connections and internal tasks that are being performed. The new columns that have been added to this view are as follows:

a) Database_id: iD of the current database for each session.
b) authenticating_database_id:
i) When the source of the session is a contained database, then the value is the db id of the authenticating database.
ii) The value will be 1 if it is a server scoped principal i.e a windows or SQL login.
iii) This will be Null if the session is that of an internal task.
c) open_transaction_count: Number of open transactions per session.

Related articles
Advertisement

Find Top 5 executed queries ordered by execution count

Dynamic Management Views, Scripts September 21, 2013 1 Comment

I wrote this query that helps us find executed queries with most number of execution counts. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 SQLtxt.text AS 'SQL',
qstats.execution_count 
AS 'Execution Count',
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time,
GetDate()) AS 'Logical Writes Per Second',
qstats.total_physical_reads AS [Total Physical Reads],
qstats.total_worker_time/qstats.execution_count AS [Average WorkerTime],
qstats.total_worker_time AS 'Total Worker Time',
DATEDIFF(hour, qstats.creation_time,
GetDate()) AS 'TimeInCache in Hours'
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS SQLtxt
WHERE SQLtxt.dbid = db_id()
ORDER BY qstats.execution_count DESC
Related articles

Find Top 5 expensive Queries from a Write IO perspective

Dynamic Management Views, Scripts September 10, 2013 Comments: 2

Recently i wrote this query that helps us find the most expensive queries from a write IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 sqltxt.text AS ‘SQL’, qstats.total_logical_writes AS [Total Logical Writes],
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS ‘Logical Writes Per Second’,
qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time AS [Total Worker Time],
qstats.total_worker_time/qstats.execution_count AS [Average Worker Time],
qstats.total_physical_reads AS [Total Physical Reads],
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘TimeInCache in Hours’,
qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,
db_name(sqltxt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS sqltxt
WHERE sqltxt.dbid = db_id()
ORDER BY qstats.total_logical_writes DESC

Find Top 5 expensive Queries from a Read IO perspective

Dynamic Management Views, Scripts September 10, 2013 Leave a comment

The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 qt.text AS ‘SQL’, qstats.total_physical_reads AS ‘Total Physical Reads’, qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,    qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time/qstats.execution_count AS ‘Average Worker Time’,
qstats.total_worker_time AS ‘Total Worker Time’,
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘AgeInCache In Hours’,  db_name(qt.dbid) AS ‘Database Name’
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qstats.total_physical_reads DESC

New DMV’s in SQL Server 2008 R2 onwards

Dynamic Management Views August 15, 2013 Leave a comment

I came across these 2 new DMV’s in SQL server 2012 and found them available in R2 also. The DMV’s are very helpful and insightful. Below is a brief about the 3 new DMV’s.

1) SELECT * FROM sys.dm_server_registry:- Use this dynamic management view in SQL Server 2008 R2 SP1 and later versions to return information such as the SQL Server services that are available on the host machine or network configuration values for the instance of SQL Server.
MSDN Link: http://msdn.microsoft.com/en-us/library/hh204561.aspx

Registry

2) SELECT * FROM sys.dm_server_services:-Use this dynamic management view to report status information about SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server.
MSDN Link: http://technet.microsoft.com/en-us/library/hh204542.aspx

Services

%d bloggers like this: