Tag Archives: dmv

How to: Verifying CPU Pressure via Signal Wait Time

Use the following query to verify the CPU pressure via signal wait time through MS SQL Server Management Studio.

USE master
SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats


How to: Find Last Restart of SQL Server

Method-1: Checking SQL Server Error Log
First time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

Method-2: Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Note: Method-2 was removed by Microsoft due to internal formatting issue. Internally the SQL start time is stored as tick count from machine boot (displayed in the sqlserver_start_time_ms_ticks column). During the conversion from tick count to a SQL Datetime the precision of the conversion is rounded off at 1 second rather than 1 ms. According to Micrsoft, this issue will be addressed in a future release of SQL Server. (Source: http://connect.microsoft.com/SQLServer/feedback/details/367663/sqlserver-start-time-not-persistent)

Method-3: Start time of the Default Trace
The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

Method-4: Creating date of tempdb
The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.
SELECT create_date FROM sys.databases
WHERE name = ‘tempdb’

What is: DMV (Dynamic Management Views) and Functions

Dynamic Management View (DMV) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

DMVs and functions are divided into two types:

  • Server-Scope. These require VIEW SERVER STATE permission on the server.
  • Database-Scope. These require VIEW DATABASE STATE permission on the database.

For the record, there are a total of 136 DMVs and functions available in SQL Server 2008 R2. Let execute the T-SQL scripts below to obtain the list.

--Get a list of grouped DMVs
	name as [DMV/DMF Name],
	type_desc as [Type],
	[GroupName] =
		WHEN name LIKE 'dm_exec%' THEN 'Query/Session Execution'
		WHEN name LIKE 'dm_os%' THEN 'SQL Server Operating System'
		WHEN name LIKE 'dm_io%' THEN 'Disk I/O'
		WHEN name LIKE 'dm_tran%' THEN 'Database Transactions and locks'
		WHEN name LIKE 'dm_db_%index%' THEN 'Indexes'
		WHEN name LIKE 'dm_db%' THEN 'Database and database objects'
		WHEN name LIKE 'dm_db_mirror%' THEN 'Database Mirroring'
		WHEN name LIKE 'dm_resou%' THEN 'Resource Governor'
		WHEN name LIKE 'dm_clr%' THEN 'CLR based'
FROM sys.system_objects
WHERE name LIKE 'dm[_]%'

The DMVs and functions have been organized into several categories, please visit BOL for more information.

Also, you can download the detailed system views map from Microsoft download, which shows the key views involved and relationship between them.