Home > DMO, SQL Saturday, SQL Server > Guest Blogger – Jes Borland

Guest Blogger – Jes Borland

I saw Jes give this presentation at SQL Saturday last year. She was the first person to make me think I could write my own blog. She has recently joined Brent Ozar PLF. You can find the link to her blog below and I recommend it highly. She is a constant source of inspiration and I am very happy to have her post here.

My Favorite DMOs

First, let me thank Frank for letting me do a guest post here! I’ve gotten to know Frank over the past couple of years through SQL Saturday events and user group meetings. I was very excited to hear he’d started blogging! You can follow my blogs regularly at LessThanDot.com.

When I read his blog about sys.dm_os_performance_counters, I started thinking about my favorite DMOs (dynamic management objects) in SQL Server. If you are not familiar with them, they are views and functions that can be queried, like any other object, to return information about your servers and database. They were first introduced in SQL Server 2005, and have been expanded and improved in each version since. They cover a wide range of topics, from database information to what is currently happening on the server. They are an invaluable tool for me, and many other DBAs.

Here are my top five DMOs!

sys.dm_exec_requests – This returns a row for each request that is executing on an instance of SQL Server. There is a wealth of information here that can help you identify processes, troubleshoot problems, and more.

Using the status column will allow you to filter on specific statues, like Running or Sleeping. The database_id column can help you identify what is being run in a single database. The blocking_session_id is useful for determining what session may be blocking another. If there is a system wait, the wait_type column can identify what type it is. Other helpful information like cpu_time, reads, writes, and total_elapsed_time will tell you how long the request has been running.

sys.dm_exec_sessions – This returns a row for each session on a SQL Server instance. It can be helpful in identifying how many sessions are open and which clients are connected.

Session_id will identify a particular session. The status of the session can be identified using status. You can tell how long the session has been active using cpu_time and total_elapsed_time.   The resources used can be determined by using reads and writes.

sys.dm_exec_sql_text – Using this DMF, you can see what SQL text was executed. You can use sys.dm_exec_requests to find a specific sql_handle to pass in. You’ll be able to see the dbid, the ID of the database the statement is running in (or was compiled in). The text column shows the exact query.

You should be using this instead of the old function, fn_get_sql, which Microsoft states will be deprecated in a future version of SQL Server.

Here is a script I refer to as my “activity” script. It uses sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_sql_text to show user activity on an instance. You can use this as a base to filter further, by database, CPU, reads, or writes.

SELECT r.session_id,
db_name(r.database_id) as dbname,
GETDATE() as CurrentTime,
s. as sql_text,
p.query_plan as query_plan,
FROM sys.dm_exec_requests r
inner join sys.dm_exec_sessions se on r.session_id = se.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) s
outer apply sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.session_id <> @@spid
    and se.is_user_process=1
ORDER BY r.cpu_time DESC;

sys.dm_db_index_physical_stats – This invaluable DMV gives you size and fragmentation information about indexes. This can be helpful in identifying one index that needs to be reorganized or rebuilt, or put into a script that runs weekly to determine the percentage of fragmentation and reorganize or rebuild anything above a certain threshold. It’s been a frequently-used tool in my DBA toolbox.

You pass arguments such as database_id, object_id, index_id, partition_number, and mode in. The information returned will be very helpful. Index_type_desc will tell you what type of index you’re working with. Page_count will give you the total count of index or data pages. The avg_fragmentation_in_percent tells you the logical fragmentation (out-of-order pages) percent; fragment_count will tell you the number of fragments.

This query will use sys.dm_db_index_physical_stats to retrieve information about all indexes on all objects in a database. You can filter down using different arguments into the function, such as a specific database, object, or even index. By filtering on indexes with a specific fragmentation percent or higher, you can figure out which to rebuild or reorganize, as an example.

SELECT ips.[object_id] AS ObjectId,
ss.name AS SchemaName,
so.name AS TableName,
ips.index_id AS IndexId,
si.name AS IndexName,
si.allow_page_locks AS AllowPageLocks,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') AS ips
join sys.objects AS so ON ips.[object_id] = so.[object_id]
join sys.schemas AS ss ON ss.[schema_id] = so.[schema_id]
join sys.indexes AS si ON ips.[object_id] = si.[object_id] and ips.index_id = si.index_id
ORDER BY so.name,
avg_fragmentation_in_percent DESC;

sys.dm_os_wait_stats – When determining “Why is my query/session/server running slow?”, are you turning to wait stats to answer that question? They are one of several tools available for troubleshooting issues on a SQL instance. As always, having a baseline to work off of is not only useful, but necessary. Ted Krueger blogged about one way to do this, and Erin Stellato also has a post with a great list of resources.

When querying sys.dm_os_wait_stats, the wait_type column will tell you what the wait type is. The list of types can be found here. Wait_time_ms will be the total wait time in milliseconds for all tasks that have had this wait type. Wait_tasks_count tells you how many tasks have encountered this wait type. Note: these statistics are reset each time SQL Server restarts! This means that the numbers you are seeing are cumulative since the last restart. A large number isn’t necessarily bad – something a baseline will be able to tell you.

This query will show you the waits on an instance, ordered by the type that has the longest accumulated wait time. It could be sorted and filtered further, on specific wait types or total wait times.

SELECT wait_type,
wait_time_ms - signal_wait_time_ms as total_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY max_wait_time_ms DESC

DMOs: A Must-Have Piece of Knowledge

This is only a small taste of the many DMOs offered. They exist for Service Brokers, replication, CLR, database mirroring, Filestream, full-text search, and much more. A full list can be found here.

I consider DMOs an essential part of administering SQL Server. If you are administering one or one hundred servers, become familiar with the many DMVs offered, and how they can help you. They are incredibly flexible and will give you insight into many facets of your server.

  1. Andy P
    April 19, 2012 at 16:05

    Thanks Jes! Very much enjoyed reading this post, and I have saved these scripts for future reference. Not too much or too little detail, and (as usual) it’s lead me to more blog posts!


  2. April 19, 2012 at 16:22

    excellent http://dbasqlserver.wordpress.com/

  1. No trackbacks yet.

Leave a Reply

%d bloggers like this: