Administrating the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 58

4. SQL Server 2008 R2 tools and system internal commands will be useful to identify

and narrow down the problem. The first source of information you need to obtain is
the resource usage such as executing threads and waits.

5. Using the sys.dm_os_wait_stats DMV, we can obtain the wait stats encountered

by threads that are executed. The aggregate result of this DMV will help to diagnose
the performance of specific queries and batches execution:
SELECT TOP 10
wait_type, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOTIN
('LAZYWRITER_SLEEP','SQLTRACE_BUFFER_FLUSH',
'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE',
'CHECKPOINT_QUEUE','CLR_AUTO_EVENT','WAITFOR',
'BROKER_TASK_STOP','SLEEP_TASK','BROKER_TO_FLUSH')
ORDER BY wait_time_ms DESC

In order to view the Server-scoped dynamic management views
and functions, the relevant login requires VIEW SERVER STATE
permission on the server. Similarly, for database-scoped dynamic
management views and functions, the login requires VIEW
DATABASE STATE permission on the database

How it works...

The sys.dm_exec_requests DMV returns information about each request that is executing
within SQL Server instance. The sys.dm_exec_requests DMV execution is a two-fold
process, which joins within another system catalog to obtain the information on a command
execution. The column names that are used in DMV are self-explanatory. Select the session
ID, status of the query, start time, command type, (such as SELECT/INSERT/UPDATE/
DELETE/DBCC/BACKUP DB) and actual text that is used in the execution.