jump to navigation

Looking into new and enhanced DMVs and DMFs in SQL Server 2012 July 19, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags: , ,
trackback

In this post I would like to demonstrate with a few examples how to use the new DMOs (Dynamic Management Objects – DMVs & DMFs) that are available to us with the RTM version of SQL Server 2012.Some of them were present in SQL Server 2008 R2 SP1  but I do not know how many people have noticed their existence.

DMO’s were first introduced in SQL Server 2005 and were immediately very popular with developers and administrators.They give us a great insight on what is going on inside  SQL Server.It would be very difficult otherwise to get  all this information so easily. They have great information on sessions,plan cache,memory,threads,database connections,waits,latches e.t.c.We can get all this information without placing a heavy burden on the server.

All of them start with dm_  in the name.They are all stored in the sys schema.

You can use any edition of SQL Server of 2012 you want for this example. SQL Server Express edition will do just fine.Download the Express edition, here.

Connect to the instance of the SQL Server.

The first DMF I will be looking into is sys.dm_os_volume_stats. This function returns several important information about the physical disk drive the specified database and its respective files are stored in SQL Server 2012.It returns information such as: Available free space on the volume,mount point at which the volume is rooted,whether the volume supports sparse files or not,whether the volume is currently marked as read only or not,whether the volume is currently compressed or not, e.t.c

As input parameters we need to provide the Database Id and the File Id values.

In an new query window type and execute the following code.Note the results.


USE AdventureWorks2012;
GO

SELECT name,total_bytes,available_bytes,volume_mount_point,file_system_type,is_compressed
FROM sys.database_files df
CROSS APPLY sys.dm_os_volume_stats(db_id(),df.file_id)

The next DMV is sys.dm_os_windows_info.It information about Windows Operating System.Type and execute the code below. Note the results.


USE MASTER;
GO
SELECT * FROM sys.dm_os_windows_info;

The next one I want to talk about is (sys.dm_server_services) and returns the list of SQL Server’s windows services.Type and execute the code below.Note the results.


USE MASTER;
GO
SELECT * FROM sys.dm_server_services

The next one I want to talk about is (sys.dm_server_memory_dumps) and returns  information about each memory dump file generated by SQL Server.

Type and execute the code below.Note the results.


USE MASTER;
GO
SELECT * FROM sys.dm_server_memory_dumps;

The next one I want to talk about (sys.dm_server_registry) and returns configuration and installation information stored in the registry for the current SQL Server instance.

Type and execute the code below.Note the results.


USE MASTER;
GO
SELECT * FROM sys.dm_server_registry;

The next one I want to talk about  is  (sys.dm_exec_query_stats). This is not a new one but it has changed in this version of SQL Server. 4 new columns have been added.

  •     total_rows –  This column returns the total number of rows returned by the query
  •     last_rows –    This column returns the total number of rows returned by last execution of the query
  •     min_rows –   This column returns the minimum number of rows query returned over the number of times that the plan has executed since it was last compiled
  •     max_rows –  This column returns the maximum number of rows query returned over the number of times that the plan has executed since it was last compiled

I will be using AdventureWorks2012 for this example. I run the first query below. I note that it returns 655 rows.

Then in the second query, I am returning old and new columns from the sys.dm_exec_query_stats for the query where the total number of rows is 655.

Type and execute the code below.


USE AdventureWorks2012;
GO

SELECT        Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderDetail.UnitPrice, Sales.SalesOrderDetail.OrderQty
FROM          Sales.SalesOrderHeader INNER JOIN
Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
WHERE Sales.SalesOrderHeader.ShipDate > '2008-07-29'
ORDER by UnitPrice

SELECT execution_count,total_logical_reads,total_logical_writes,total_physical_reads,total_worker_time,total_rows,last_rows,min_rows,max_rows
FROM sys.dm_exec_query_stats
WHERE total_rows = 655

There are many great posts on the web about DMVs but one post I really like is Glen Berry’s , A DMV per day series.

Hope it helps!!!!

Comments»

1. Dot Net Rules : Looking into new and enhanced DMVs and DMFs in SQL Server 2012 - July 20, 2012

[…] in SQL Server 2008 R2 SP1  but I do not know how many people have noticed their existence. (read more) Share Posted: Παρασκευή, 20 Ιουλίου 2012 4:39 μμ από το μέλος […]

2. Looking into the contents of Plan Cache in SQL Server « DOT NET RULES - August 9, 2012

[…] that has been added to SQL Server since 2005.I feel exactly the same way.Please have a look at this post of mine that I talk about new DMVs and DMFs in SQL Server 2008 SP1 and SQL Server […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: