Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« SQL Advent 2011 Day 23: OBJECT_DEFINITION

SQL Advent 2011 Day 21: TRY CATCH »

comments

SQL Advent 2011 Day 22: Dynamic Management Views

by SQLDenis on Dec 22, 2011 in categories Data Modelling & Design

Instapaper

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

tsql

Line number Off | Hide | Select all

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.

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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?

tsql

Line number Off | Hide | Select all

SELECT distinct servicename,
service_account,status_desc
FROM sys.dm_server_services

Output

servicename			service_account			status_desc
SQL Server (MSSQLSERVER)	NT Service\MSSQLSERVER		Running
SQL Server Agent (MSSQLSERVER)	NT Service\SQLSERVERAGENT	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

tsql

Line number Off | Hide | Select all

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 :-)

That is all for today, come back tomorrow for the next part in this series

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings

Article Views: 728 views

1 comment

Comment from: Naomi Nosonovsky [Member]

I am getting wrong results with the the first query:

siriussp_GetQtyRemaining_By_LessonType 45928 39671189 SET @DBVersion = (select top(1) [ServerVersion] from [dbo].[ServerUpgradeHistory] ORDER BY [UpgradeID] DESC) 2011-12-24 20:49:56.030

------------
However, this stored procedure doesn't have such query in it and it surely not executed that many times.

12/24/11 @ 19:44

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

SQL Advent 2011 Day 22: Dynamic Management Views

About the Author

1 comment

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

SQL Advent 2011 Day 22: Dynamic Management Views

About the Author

1 comment

Leave a comment