Dynamic Management Views in Azure SQL Database v12

When the Dynamic Management Views (DMV) were introduced back in SQL Server 2005, we got our hands on some absolutely wonderful tools that allowed us to observe much of the inner workings of SQL Server using the language we use most, T-SQL. Prior to the release of v12 of Azure SQL Database, there were about 148 DMVs that were in the Earthed version of SQL Server that were not in Azure SQL Database. There were only about 13 DMVs in Azure that were not in the Earthed product. Things have changed.

Along with the release of v12, a very large number of DMVs have been added to Azure SQL Database, approximately 106 that were not in previous version. There are, as of this writing, because things change fast in Azure, only 46 unique DMVs available on the Earthed version of SQL Server. There are 14 that are unique to Azure SQL Database v12. There are 166 DMVs in the Earthed version of SQL Server and 132 in Azure SQL Database (there is a small disparity between the actual counts and the unique counts).

However, this is not about numbers.

What it's about is the expanded capabilities that have been introduced in Azure SQL Database for gathering information about your databases. If you look at the DMVs that are still on the Earthed version, but not in Azure, we're talking about data that just isn't applicable to the Platform as a Service (PaaS) version of SQL Server that Azure SQL Database represents. We don't have Mirroring or Clusters in PaaS because the platform itself handles those types of functions. So missing those DMVs is absolutely not a hardship. 

From a traditional query tuning point of view, we've long had most of the DMVs that we need. You've been able to query sys.dm_exec_requests to see the queries currently running on your database for years. You could also join this to sys.dm_exec_sql_text or sys.dm_exec_query_plan to pull the appropriate information out of the DMVs within Azure SQL Database. Most of the fundamentals needed for checking on and monitoring the system have been in place and available. But now we have a lot more.

Examples of New Monitoring Available in v12

Just a small example to start. Keeping on eye on the size of transactions and monitoring your transaction log usage is important in SQL Server. It's now possible to get a look at the log file by using the DMV sys.dm_db_log_space_usage (a DMV introduced in SQL Server 2012) within Azure SQL Database. You can run this:

SELECT DB_NAME(ddlsu.database_id) AS DatabaseName,

database_id,

CAST((ddlsu.total_log_size_in_bytes / 1048576.0) AS DECIMAL(10, 1))

AS LogSizeMB,

CAST((ddlsu.used_log_space_in_bytes / 1048576.0) AS DECIMAL(10, 1))

AS LogSpaceUsedMB,

CAST(ddlsu.used_log_space_in_percent AS DECIMAL(10, 1)) AS PercentLogSpace

FROM sys.dm_db_log_space_usage AS ddlsu;

You can adjust that as needed to get GB for larger databases as needed.

Throttling can be an issue within Azure SQL Database if you have performance problems on your system. So gaining an understanding of long running queries is a must. Introduced in SQL Server 2014, dm_exec_query_profiles will show you the status of your query within the execution plan. It will show the number of rows processed within an operator, the number of rewinds or rebinds that have been necessary, in short, the status of the query within the active live execution. And this is now available in Azure SQL Database as well:

SELECT dest.text,

deqpl.query_plan,

deqp.physical_operator_name,

deqp.row_count

FROM sys.dm_exec_query_profiles AS deqp

CROSS APPLY sys.dm_exec_sql_text(deqp.sql_handle) AS dest

CROSS APPLY sys.dm_exec_query_plan(deqp.plan_handle) AS deqpl;

For very short transactions, sys.dm_exec_query_profiles just won't work that well, if at all. You have to be capturing an execution plan at the same time as you're running the query. This DMV is intended for troubleshooting those really bad queries, the ones that run long and may even be getting throttled by Azure SQL Database. The output from a poorly performing query I have looks like this:

Every time I rerun the query, you'll see the row_count values changing in the operators. But knowing how this works isn't important. What's important is that it's available in Azure SQL Database. We are absolutely reaching parity with the earthed version of SQL Server on all the common tasks and most of the uncommon ones that that you need DMVs to support.

I can keep going with example after example of the types of monitoring that is now available because of the v12 update. Let me finish with just one more. 

Priot to the release of v12, one of the easiest ways to tell if a DMV would not be included in Azure was to look at the name. If it started out 'sys.dm_os_' then it was absolutely not going to be included in Azure SQL Database. Not anymore. There are now 35, from sys.dm_os_buffer_descriptors to sys.dm_os_workers. All of this means that I can write queries that do things like this:

WITH RingBuffer

AS (SELECT CAST(dorb.record AS XML) AS xRecord,

dorb.timestamp

FROM sys.dm_os_ring_buffers AS dorb

WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

)

SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)')

AS RmNotification,

xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint')

AS IndicatorsProcess,

xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint')

AS IndicatorsSystem,

DATEADD(ss,

(-1 * ((dosi.cpu_ticks / CONVERT (FLOAT, (dosi.cpu_ticks

/ dosi.ms_ticks)))

- rb.timestamp) / 1000), GETDATE()) AS RmDateTime,

xr.value('(MemoryNode/TargetMemory)[1]', 'bigint')

AS TargetMemory,

xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint')

AS ReserveMemory,

xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint')

AS CommitedMemory,

xr.value('(MemoryNode/SharedMemory)[1]', 'bigint')

AS SharedMemory,

xr.value('(MemoryNode/PagesMemory)[1]', 'bigint')

AS PagesMemory,

xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint')

AS MemoryUtilization,

xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint')

AS TotalPhysicalMemory,

xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')

AS AvailablePhysicalMemory,

xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint')

AS TotalPageFile,

xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint')

AS AvailablePageFile,

xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint')

AS TotalVirtualAddressSpace,

xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]',

'bigint') AS AvailableVirtualAddressSpace,

xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]',

'bigint') AS AvailableExtendedVirtualAddressSpace

FROM RingBuffer AS rb

CROSS APPLY rb.xRecord.nodes('Record') record (xr)

CROSS JOIN sys.dm_os_sys_info AS dosi

ORDER BY RmDateTime DESC;

I'm now able to look into the Ring Buffers within Azure to see if I'm hitting memory issues. I'm also going to have access to all the other information that gets exposed by the ring buffers.

Monitoring Unique to Azure SQL Database

It's not just about being able to do the same things you can already do on the earthed product. You also need to be very specifically focused on some of the unique information needed within Azure itself. For example, one of the best ways to understand specifically what is going wrong on a server is to look at sys.dm_os_wait_stats. On Azure, we have another DMV, sys.dm_db_wait_stats. That shows the waits specific to my PaaS database and no others. The output should look familiar:

Although, this does bring up an interesting question. Prior to v12, this was how you monitored wait statistics in Azure. But, now, sys.dm_os_wait_stats is available. My immediate question was, of course, are they the same. I ran this query:

SELECT *

FROM sys.dm_os_wait_stats AS dows

WHERE dows.wait_type = 'PAGEIOLATCH_SH';

SELECT *

FROM sys.dm_db_wait_stats AS ddws

WHERE ddws.wait_type = 'PAGEIOLATCH_SH';

The results are here:

No. I don't actually know why they are different. Although, max_wait_time_ms is identical between both DMVs. This is something I'll try to find out more about. For now, stick to the Azure specific VM, sys.dm_db_wait_stats.

Another unique feature to Azure SQL Database is the ability to easily set up GeoLocation for your databases. You have a number of options to ensure that your data will be protected and available, not just locally to your own data center, but around the globe. However, wouldn't you like to keep an eye on all this? Of course you would. That's why you're going to use DMVs like sys.dm_operation_stats and others. 

Conclusion

With the addition of the VMs to Azure SQL Database v12, we now have an extremely rich environment for monitoring the status of our databases running on that platform. We can do everything we need to do when compared to the earthed product. We can also do more where there is unique functionality within Azure SQL database. Once more, the addition of all this functionality removes barriers for entry into the new world that Azure SQL Database represents.