Introduction
As an administrator it's your responsibility to monitor your server on regular intervals and Dynamic Management Views and Dynamic Management Functions play vital role in providing help to ease the monitoring process.
The DMV and DMF returns server state information that can be used to monitor server health, performance bottleneck and issues.
To find out what sort of DMV and DMF in SQLServer, issue this query in SQL Window
| SQL |
Use master
Go
Select name
From sys.all_objects
Where name like 'dm%
Order by name
Go
|
This will list a number of them, the naming convention help you in identifying what each of them is meant for.
As a hint it's another way to view the Performance Monitor counter through T-SQL.
Let say you as administrator wants to view the Operating System Performance Counter, simply issue this query and you will come to know the performance counter
| SQL |
Select object_name, counter_name, instance_name, cntr_value
From sys.dm_os_performance_counters
Where counter_name IN
('buffer cache hit ratio',
'user connections',
'transactions',
'average wait time (ms)',
'transactions/sec',
'errors/sec',
'target server memory (kb)',
'total server memory (kb)')
Order by object_name, counter_name
Go
|
Similarly, by issuing this query you can have a similar display as of the Activity Monitor
| SQL |
Select session_id, login_name, cpu_time, memory_usage, status
From sys.dm_exec_sessions
Go
|
Further, if you like to view what has actually executed for a particular session you can issue this statement
| SQL |
Select T.text
From sys.dm_exec_requests R
cross apply sys.dm_exec_sql_text(sql_handle) As T
Where (R.session_id = 57)
Go
|
And for those who wants to see everything from single statement can rely on the below SQL
| SQL |
Select T.text
From sys.dm_exec_requests R
cross apply sys.dm_exec_sql_text(sql_handle) As T
Where R.session_id
IN
(Select session_id From sys.dm_exec_sessions)
|
So,you've seen that how easy it would be to perform Performance Monitoring but in reality it's not as easy as it sounds, so far Microsoft has rewarded you DMV and DMF for your patience but solving out the issues by reading these counters is another big challenge.