In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.
Today we are going to take a look at Dynamic Management Views. Dynamic Management Views is one of my favorite things that they have added to the SQL Server product. Instead of running all kinds of stored procedures, dbcc commands and selects from table, you can get all that information now from the Dynamic Management Views, all you need to know is what view will get you the information you need.
Let’s take a look at some examples. This query below will give me the top 50 most executed statements in stored procedures
SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,s2.objectid,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
and exists (SELECT 1 FROM sys.procedures s
WHERE s.is_ms_shipped = 0
and s.name = x.ProcName )
ORDER BY execution_count DESC
Here is some sample output
proc count objectid statement last_execution_time ---------------------------------------------------------------------------------------------- usp_AddLast 42757230 517576882 UPDATE dbo.LastSome 2010-04-14 14:15:33.433 usp_AddLast 42757230 517576882 IF EXISTS( SELECT 2010-04-14 14:15:33.433 usp_Update 20290 725577623 update t set pclose 2010-04-14 14:15:33.433 usp_Update 20288 725577623 update t set pclose 2010-04-14 14:15:33.453 usp_GetLast3 3188 501576825 SELECT distinct l.Sy 2010-04-14 14:14:33.350 usp_Historical 168 1029578706 select * from Histor 2010-04-14 14:02:08.190 usp_AddLast 3 517576882 INSERT dbo.Sometable 2010-04-07 08:42:57.040 usp_GetLast2 3 965578478 SELECT l.Symbol, q 2010-04-06 16:14:49.523 usp_Historical2 2 1045578763 select SomeNumber,G 2010-04-14 12:10:48.860 usp_GetLast 1 901578250 SELECT distinct l.Sy 2010-02-12 09:11:59.840
As you can see the first two rows are for the same stored procedure, what if you only want to know the procedure names? You can use the following query for that, I grouped them by name and then used the max count of the statement itself as the execution count, you could also use SUM instead of MAX. If you have a lot of if else conditions then max might not give you the whole picture.
SELECT TOP 50 * FROM
(SELECT OBJECT_NAME(s2.objectid) AS ProcName,
MAX(execution_count) AS execution_count,s2.objectid,
MAX(last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
GROUP BY OBJECT_NAME(s2.objectid),s2.objectid) x
WHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
AND EXISTS (SELECT 1 FROM sys.procedures s
WHERE s.is_ms_shipped = 0
AND s.name = x.ProcName )
ORDER BY execution_count DESC
Here is the output
proc count objectid last_execution_time ---------------------------------------------------------------------- usp_AddLast 42667941 517576882 2010-04-14 14:08:48.287 usp_Update 20263 725577623 2010-04-14 14:08:48.307 usp_GetLast3 3180 501576825 2010-04-14 14:07:10.513 usp_Historical 168 1029578706 2010-04-14 14:02:08.190 usp_GetLast2 3 965578478 2010-04-06 16:14:49.523 usp_Historical2 2 1045578763 2010-04-14 12:10:48.860 usp_GetLast 1 901578250 2010-02-12 09:11:59.840
Imagine doing stuff like this in the SQL Server 2000 days…..better get profiler and some traces running.
What if you want to know the stored procedures with the highest average CPU time in SQL Server? That is pretty easy as well, here is the query for that
SELECT TOP 50 * FROM
(SELECT OBJECT_NAME(s2.objectid) AS ProcName,
SUM(s1.total_worker_time/s1.execution_count) AS AverageCPUTime,s2.objectid,
SUM(execution_count) AS execution_count
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
GROUP BY OBJECT_NAME(s2.objectid),objectid) x
WHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
AND EXISTS (SELECT 1 FROM sys.procedures s
WHERE s.is_ms_shipped = 0
AND s.name = x.ProcName )
ORDER BY AverageCPUTime DESC
Output
proc AverageCPUTime objectid execution_count ---------------------------------------------------------------------- usp_Update 17152 725577623 41074 usp_GetLast3 333 965578478 3 usp_GetLast2 145 501576825 3237 usp_Historical 70 1029578706 170 usp_AddLast 36 517576882 87154735 usp_GetLast 0 901578250 1 usp_Historical2 0 1045578763 2
Wow, that usp_Update proc really sucks 🙂
Here is another one of my favorite queries. How long will the database restore take?
Run the query below and you will know
SELECT
d.PERCENT_COMPLETE AS [%Complete],
d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
d.ESTIMATED_COMPLETION_TIME/60000 AS TimeRemainingMin,
d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
d.ESTIMATED_COMPLETION_TIME*0.00000024 AS TimeRemainingHours,
s.TEXT AS Command
FROM sys.dm_exec_requests d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)AS s
WHERE d.COMMAND LIKE 'RESTORE DATABASE%'
ORDER BY 2 DESC, 3 DESC
For all the sessions that are connected, what state are they in? The query below will give you a quick count
SELECT COUNT(*) AS StatusCount,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
WHEN 'Preconnect ' THEN 'Session is in the Resource Governor classifier'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
GROUP BY status
Just a quick count of all the transaction isolation levels
SELECT COUNT(*),CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
GROUP BY transaction_isolation_level
To see what the SET options are that you are using in your connection, use the following query, leave out the WHERE clause if you want to know it for all connections. The query returns pretty much what DBCC USERINFO returns but you can run this for all connected sessions in one shot
SELECT @@SPID AS SPID,
text_size,
language,
lock_timeout,
date_first,
date_format,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Here is another one where you had to run performance counter back in the 2000 days. This query will get you the page life expectancy for your server
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'
Here is another short one, what account are my services running under?
SELECT distinct servicename,
service_account,status_desc
FROM sys.dm_server_services
Output
servicename service_account status_desc SQL Server (MSSQLSERVER) NT ServiceMSSQLSERVER Running SQL Server Agent (MSSQLSERVER) NT ServiceSQLSERVERAGENT Stopped
I only listed a handful of Dynamic Management Views, SQL Server 2008 R2 has 135 Dynamic Management Views, SQL Server 2012 as of CTP 3 has 174 Dynamic Management Views
Here is how you can get a list of all of them
SELECT * FROM master.sys.sysobjects
WHERE name like 'dm[_]%'
The list below links to Books On Line for related Dynamic Management Views, if you want to know about mirroring Dynamic Management Views then click on the Database Mirroring Related Dynamic Management Views link. I would say, go to each section and maybe spend a week on it…by the middle of spring you should be the Dynamic Management Views master 🙂
Change Data Capture Related Dynamic Management Views
I/O Related Dynamic Management Views and Functions
Change Tracking Related Dynamic Management Views
Object Related Dynamic Management Views and Functions
Common Language Runtime Related Dynamic Management Views
Query Notifications Related Dynamic Management Views
Database Mirroring Related Dynamic Management Views
Replication Related Dynamic Management Views
Database Related Dynamic Management Views
Resource Governor Dynamic Management Views
Execution Related Dynamic Management Views and Functions
Service Broker Related Dynamic Management Views
Extended Events Dynamic Management Views
SQL Server Operating System Related Dynamic Management Views
Full-Text Search Related Dynamic Management Views
Transaction Related Dynamic Management Views and Functions
Index Related Dynamic Management Views and Functions
Security Related Dynamic Management Views
Filestream-Related Dynamic Management Views (Transact-SQL)
That is all for today, come back tomorrow for the next part in this series