As an administrator its 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 use 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
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 its 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
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
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
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
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 its 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.
Bandagi!