Article
One Community, One World
Windows Commnication Foundation Addressing
by [lnkAuthor]  on 
  PageViews   |    Add to Fav   |     Send to Friend   |    Download Code
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.
Comments
By Mohamed Atia on 7/21/2011
wow