Build your professional network on facebook via our app Go to app
 
<< Prev  6 of 6 in Topic 
Topic : SQL Server 2005/2008 Profiler
  Rate : 
 
DMV’s in SQL Server 2005 by Atul Kumar, Project Lead, Satyam Computers

DMV’s in SQL Server 2005

By : Atul Kumar, Project Lead, Satyam Computers
Industry : IT Services Functional Area : Application Software
Keywords :

SQL Server 2005

SQL

DMV

2005

Activity:  6 comments  2324 views  last activity : 10 22 2011 17:17:39 +0000
 Refer 11
Share
 
 
 
Dynamic Management Views 
The dynamic management views (DMV’s) in SQL Server 2005 are designed to give DBA a window into what's going on inside SQL Server. They can provide information on what's currently happening inside the server as well as the objects it's storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000.   The DMV’s are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. The DMV’s are newly introduced in SQL Server 2005 gives database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMV’s are designed to be used instead of system tables and various other functions provided in SQL Server 2000. Few of the frequently used DMV’s are explained below.There are two (2) types of DMV’s namely: Server-scoped DMV -- Stored in Master database;
Database-scoped DMV -- Specific to each database

DMV’s have been broken up into twelve categories: 
Ø  Common Language Runtime Related Dynamic Management Views
Ø  I/O Related Dynamic Management Views and Functions
Ø  Database Mirroring Related Dynamic Management Views
Ø  Query Notifications Related Dynamic Management Views
Ø  Database Related Dynamic Management Views
Ø  Replication Related Dynamic Management Views
Ø  Execution Related Dynamic Management Views and Functions
Ø  Service Broker Related Dynamic Management Views
Ø  Full-Text Search Related Dynamic Management Views
Ø  SQL Operating System Related Dynamic Management Views
Ø  Index Related Dynamic Management Views and Functions
Ø  Transaction Related Dynamic Management Views and Functions

 Getting Started All the DMV’s exits in SYS schema and its name start with DM_. So when you need to query a DMV then you should prefix the view name with SYS and you need to have the required privilege. To query a Server scoped DMV the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV the user must have SELECT privilege on VIEW DATABASE STATE as: Ex:            GRANT VIEW SERVER STATE to             
GRANT VIEW DATABASE STATE to  

Now if you need to see the total physical memory of the SQL Server machine; then execute the below TSQL command:- 
SELECT (Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_MB FROM sys.dm_os_sys_info Permission to Execute DMV [Security] 
To query a Server scoped DMV the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV the user must have SELECT privilege on VIEW DATABASE STATE as: GRANT VIEW SERVER STATE to GRANT VIEW DATABASE STATE to  If you want to deny a user permission to query certain DMV’s you can use the DENY command to restrict access to specific DMV. 
Sessions The DMV that gives us information on each session is “dm_exec_sessions”. Selecting from sys.dm_exec_sessions is similar to running sp_who2 or selecting from sysprocesses. It lists one row per session.  Note: as said above when any of the dynamic management views or functions are used it needs to be qualified with the sys schema as

sys.dm_exec_sessions SELECT            session_id,            login_name,            last_request_end_time,            cpu_timeFROM            sys.dm_exec_sessions WHERE            session_id >= 51Note: Session_ID >= 51
is given so that we need NOT extract information related to “sa” account
 Session_id     login_name                           last_request_end_time   cpu_time51                    CT\svc-hou150ntah3c1       2007-11-13 08:25:24.520        052                   
CT\!sogg                               2007-11-13 08:26:09.237        6253                   
CT\svc-thpsqlsrv                 2007-11-09 12:42:41.280        12554                   
CT\!sogg                               2007-11-13 08:26:06.877        058                   
CT\svc-thpsqlsrv                 2007-11-13 05:00:00.240        060                   
CT\pmuc                              2007-11-12 15:42:47.417        63 If the query is run WITHOUT where clause that is: 
SELECT            session_id,            login_name,            last_request_end_time,            cpu_timeFROM            sys.dm_exec_sessions  
The O/P looks like below: 
Session_id     login_name                            last_request_end_time   cpu_time1                      sa                                            NULL                                     01                     
sa                                            NULL                                     051                   
CT\svc-hou150ntah3c1       2007-11-13 08:25:24.520        052                   
CT\!sogg                               2007-11-13 08:26:09.237        6253                   
CT\svc-thpsqlsrv                 2007-11-09 12:42:41.280        12554                   

The view also returns an extra twenty five (25) columns or so but this is a good place to start. The session_id is basically the SPID that we're used to seeing. In SQL Server 2000 selecting @@SPID returns the "server process identifier" or SPID. In SQL Server 2005 selecting @@SPID returns the "session ID of the current user process". The view also returns session-specific information such as the ANSI NULL settings, reads, writes and other set-able session objects. 
Connections For those sessions that come from outside SQL Server (session_id >= 51) we can also look at the connection information. We'll query sys.dm_exec_connections for this information. This view returns one row for each connection. 
SELECT            connection_id,          session_id,            client_net_address,            auth_schemeFROM            sys.dm_exec_connections 
Connection_id                                                session_id       client_net_address   auth_scheme 05006B62-0725-415B-859E-854FED54796B             51           146.36.99.207                      NTLMA
03CDFFD-6018-417E-A579-A6A863714D43        52           146.36.198.119                   NTLM
21B68032-7351-4AB7-8ABB-7063CC81B1E7           54           146.36.198.119                NTLM  

There are about fifteen other columns, for the time being the focus is on the above columns. Notice that the IP address of the client is listed as well as the authentication scheme. One of the things I've always wanted from SQL Server is an easy way to see the IP address for each connection. 
Requests In order to know what each connection is actually doing the DMV sys.dm_exec_requests is used. This lists each request that is executing within SQL
Server. SELECT            session_id,            status,            command,            sql_handle,            database_idFROM            sys.dm_exec_requestsWHERE            session_id >= 51  
session_id status     command   sql_handle                                         database_id
54           running SELECT 0x02000000FD37800AE6DCE50917C7FF9B65B745ED60C7EC5E   1 
In this case there is only one running query. The first (#54) is my select from sys.dm_exec_requests. Second, and so on are queries running from a different connection. We can see that it's current running a WAITFOR command. This view has a number of other interesting columns including the start time, plan_handle (hash map of the cached query plan), wait type information, transaction information, reads, writes and connection specific settings. Let's see what we can find out about the other query that's running. SQL Text sys.dm_exec_sql_text is a dynamic management function that returns the text of a SQL statement given a SQL handle. Fortunately we just happen to have a SQL handle from our query of sys.dm_exec_requests. To see the SQL text that's currently executing in session #54 we can use this query: 
SELECT                     st.textFROM            sys.dm_exec_requests rCROSS APPLY             sys.dm_exec_sql_text(sql_handle) AS stWHERE            r.session_id = 56 
TextSELECT            *FROM            DBNAME.dbo.tablename WAITFOR DELAY '00:00:10' 
Notice that we used a CROSS APPLY to execute the function for each row returned by the view. 
Security In order to query these views a user needs specific permissions granted. To view the server-wide DMV’s a user must be granted the VIEW SERVER STATE on the server. After running the following query as administrator: GRANT VIEW SERVER STATE to [ct\sogg] I was able to query the DMV’s when logged in as a regular user. To query database specific DMV’s a user must be granted the VIEW DATABASE STATE permission in each specific database. If you want to deny a user permission to query certain DMV’s you can use the DENY command and reference those specific views. And remember it's always better to grant permission to roles instead of individual users. Some of the DMV’s which can be used frequently to understand the current behavior of SQL Server is below: 
1. SQL Server related [Hardware Resources] DMV
2. Database related DMV
3. Index related DMV
4. Execution related DMV 1. SQL Server related DMV This section details the DMV’s associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.This section covers DMV’s related to OS, Disk and Memory a. sys.dm_os_sys_info This view returns the information about the SQL Server machine, available resources and the resource consumption. This view returns information’s like:- 1. CPU Count: Number of logical CPUs in the server2. Hyperthread-ratio: Ratio of logical and physical CPU’s3. Physical_memory_in_bytes: Amount of physical memory available4. Virtual_memory_in_bytes: Amount of virtual memory available5. Bpool_commited: Commited physical memory in buffer pool6. OS_Priority_class: Priority class for SQL Server process7. Max_workers_thread: Maximum number of workers which can be created b. sys.dm_os_hosts This view returns all the hosts registered with SQL Server 2005. This view also provides the resouces used by each host. 1. Name: Name of the host registered2. Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]3. Active_tasks_count: Number active tasks host a placed4. Active_ios_count: I/O requests from host waiting c. sys.dm_os_schedulers Sys.dm_os_schedulers view will help you identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values then there is a symptom of CPU bottleneck.  SELECT scheduler_id,current_tasks_count,runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 The above query will lists all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler. d. sys.dm_io_pending_io_requests This dynamic view will return the I/O requests pending in SQL Server side. It gives you information’s like:- 1. Io_type: Type of pending I/O request2. Io_pending: Indicates whether the I/O request is pending or has been completed by Windows3. Scheduler_address: Scheduler on which this I/O request was issued. e. sys.dm_io_virtual_file_stats This view returns I/O statistics for data and log files [MDF and LDF file]. This view is one of the commonly used views and will help you to identify I/O file level. This will return information’s like: 1. Sample_ms: Number of milliseconds since the instance of SQL Server has started2. Num_of_reads: Number of reads issued on the file3. Num_of_bytes_read: Total number of bytes read on this file4. Io_stall_read_ms: Total time, in milliseconds, that the users waited for reads issued on the file5. Num_of_writes: Number of writes made on this file6. Num_of_bytes_written: Total number of bytes written to the file7. Io_stall_write_ms: Total time, in milliseconds, that users waited for writes to be completed on the file8. Io_stall: Total time, in milliseconds, that users waited for I/O to be completed9. Size_on_disk_bytes: Number of bytes used on the disk for this file.f. sys.dm_os_memory_clerks This DMV will help how much memory SQL Server has allocated through AWE. SELECT SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb] FROM sys.dm_os_memory_clerks The Same DMV can be used to get the memory consumption by internal components of SQL Server 2005. SELECT TOP 10 type, SUM(single_pages_kb) as [SPA Mem, Kb] FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY SUM(single_pages_kb) DESC g. sys.dm_os_ring_buffers This DMV uses RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state. SELECT Record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' The output of the above query will be in XML format. The output will help you detecting any low memory notification. RING_BUFFER_OOM: Ring buffer_oom contains records indicating server out-of-memory conditions. SELECTrecord FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_OOM' 2. Database Related DMV This section details the DMV’s associated with SQL Server Databases. This DMV’s will help to identify database space usages, Partition usages, Session information usages, etc... a. sys.dm_db_file_space_usage This DMV provides the space usage information of TEMPDB database. b. sys.dm_db_session_space_usage This DMV provides the number of pages allocated and de-allocated by each session for the database c. sys.dm_db_partition_statsThis DMV provides page and row-count information for every partition in the current database. The below Query shows all counts for all partitions of all indexes and heaps in the MSDB database USE MSDBGOSELECT * FROM sys.dm_db_partition_stats; The following query shows all counts for all partitions of Backup set table and its indexes USE MSDBGOSELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('backupset'); d. sys.dm_os_performance_counters Returns the SQL Server / Database related counters maintained by the server.The below sample query uses the dm_os_performance_counters DMV to get the Log file usage for all databases in KB. SELECT instance_name,cntr_value 'Log File(s) Used Size (KB)'FROM sys.dm_os_performance_counters WHERE counter_name = 'Log File(s) Used Size (KB)' 3. INDEX Related DMV This section details the DMV’s associated with SQL Server Databases. This DMV’s will help to identify database space usages, Partition usages, Session information usages, etc... a. sys.dm_db_index_usage_stats This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index. SELECT object_id, index_id, user_seeks, user_scans, user_lookups FROM sys.dm_db_index_usage_stats ORDER BY object_id, index_id All indexes which are not been used sofar in as database can be identified using below Query:- SELECT object_name(i.object_id), i.name, s.user_updates, s.user_seeks, s.user_scans, s.user_lookupsfrom sys.indexes i left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = 5where objectproperty(i.object_id, 'IsIndexable') = 1 ands.index_id is null or(s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)order by object_name(i.object_id) Note: replace the Database_id with the database you are looking 

4. Execution Related DMV Execution related DMV’s will provide information’s regarding sessions, connections, and various requests which are coming in to SQL Server. a.       sys.dm_exec_sessions This DMV will give information on each session connected to SQL Server. This DMV is similar to running sp_who2 or querying Master..sysprocesses table.  SELECTsession_id,login_name,last_request_end_time,cpu_timeFROM sys.dm_exec_sessionsWHERE session_id >= 51 – All user Sessions b. sys.dm_exec_connections This DMV shows all the connection to SQL Server. The query below uses sys.dm_exec_connections DMV to get connection information. This view returns one row for each user connection (Sessionid > =51). SELECTconnection_id,session_id,client_net_address,auth_schemeFROM sys.dm_exec_connections c. sys.dm_exec_requests This DMV will give details on what each connection is actually performing in SQL Server.  SELECTsession_id,status,command,sql_handle,database_idFROM sys.dm_exec_requestsWHERE session_id >= 51 d. sys.dm_exec_sql_text This dynamic management function returns the text of a SQL statement given a SQL handle. SELECT st.textFROMsys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stWHERE r.session_id = 51 

Conclusion Dynamic Management views (DMV) and Dynamic Management Functions (DMF) in SQL Server 2005 gives a transparent view of what is going on inside various areas of SQL Server. By using them we will be able to query the system for information about its current state in a much more effective and provide solutions much faster. DMV’s can be used to performance tune and troubleshooting server and queries. 
go to top
 
6 comments on "DMV’s in SQL Server 2005 "
Sort by: Most Recent Top Rated
  Commented by  LAXMAN KESHWALA, Freelancer, Telecom/ISP    | 10 22 2011 17:17:39 +0000 
All these matter is related to internet server provider so you have to contact with him.
  Commented by  Sandeep Kumar, DBA, VIT-INDIA    | 10 21 2011 12:29:01 +0000 
I m not understand what r u saying
  Commented by  Arunkumar, Project Associate - Technical, Mindlogicx Infotech Limited    | 11 18 2010 12:45:33 +0000 
Cant understand what u say...Try to give it as a neat presentation.
  Commented by  Dinesh, Team Leader -(Technical), Avery    | 01 22 2010 08:14:51 +0000 
topic is looking interested but it will be better if the things can be shown in summarize way to get maximum understanding.
  Commented by  Abhishek Tiwari, Network Admin/System Admin, STPL INC.    | 08 07 2008 05:13:20 +0000 
what is this dear.. please expalin in a proper way.
  Commented by  Mahendra Sharma, Software Developer, SRM Techsol Pvt. Ltd.    | 07 31 2008 07:05:32 +0000 
This is not clear much
Add your comment on "DMV’s in SQL Server 2005 "

Rate:
Submit
 Found the article "DMV’s in SQL Server 2005 "  interesting ?  Share with your connections and communities  
Leading recruitment from Bangalore
  • Create a confidential Career Profile and Resume/C.V. online
  • Get advice for planning their career and for marketing of experience and skills
  • Maximize awareness of and access to the best career opportunities
Viewers also viewed
I am associate of prajapati association and i am working for BJP from year 2005 to till date....
 
0 referals 3 arguments, 111 views
Though media vs Self
 
157 referals 105 arguments, 3005 views
more...  
Recent Knowledge (61)
Why is using email marketing services sometimes a better option than direct mail? There are many...
 
0 referals 4 comments, 221 views
The following is a reproduction of from an article I wrote in 2004. 1.  Harnessing the vast...
 
735 referals 18 comments, 325 views
CORRUPTION INSTITUTE OF INDIA (CII) (World's best C- School on the World Corruption Index...
 
423 referals 4 comments, 100 views
more...  
Sponsored Jobs
More From Author
Anyone statement which cause some uncomfort to common man or mass of the people, nothing wrong in saying Sorry. She is good atleast said sorry. Raj Thakre, Rahul Gandhi, Digvijay Singh and many other politicians never say sorry for their mistakes.
Thanks for sharing the article. Good one
@Munshi: Please don't blame RSS for everything. The sounth India treats all north Indians as aliens. Tamilnadu people know Hindi language but feel shame to speak. They treat any others as outsiders... This all haterated are growing for the benifits of...
more...  
  • Popular Knowledge
  •  
  •  
  • Popular Jobs
  •