SQL Server – Some commonly used DMV/DMFs by most of the DBAs

There are more than 150 DMV/DMFs spread across 20 some categories in 2012, and no one remember all these by heart.  Below are some commonly used DMV/DMFs by most the DBAs.


— Execution related DMV and DMF

— Current Connections (User Processes only)
SELECT * FROM sys.dm_exec_connections

— Active Sessions
SELECT * FROM sys.dm_exec_sessions

— Currently executing requests
SELECT * FROM sys.dm_exec_requests

— Very Important: Query Statistics
SELECT * FROM sys.dm_exec_query_stats

— Cached Plans
SELECT * FROM sys.dm_exec_cached_plans

— Very Important: Query Optimizer details
SELECT * FROM sys.dm_exec_query_optimizer_info

— Very Important: Query Plan, This is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_query_plan (T1.plan_handle )

— Very Important: SQL Statement, this is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_SQL_text (T1.SQL_handle )

— Index related Dynamic Management Objects
— Index Usability
SELECT * FROM sys.dm_db_index_usage_stats

— Missing Indexes
SELECT * FROM sys.dm_db_missing_index_details

— Physical Stats, this is DMF, requires DBID, ObjectID, IndexID, PartitionID and Mode
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(‘TESTDB‘), OBJECT_ID(‘EMPLOYEE‘), NULL, NULL , NULL) WHERE avg_fragmentation_in_percent >30

— SQL Server Operating system related Dynamic Management Objects

— Wait Stats
SELECT * FROM sys.dm_os_wait_stats

— Scheduler
SELECT * FROM sys.dm_os_schedulers

— Waiting/ Suspended Tasks
SELECT * FROM sys.dm_os_waiting_tasks WHERE Session_ID > 50

— Performance Counters
SELECT * FROM sys.dm_os_performance_counters

— Cluster Nodes Details
SELECT * FROM sys.dm_os_cluster_nodes



Rebuild or Reorganize

In OLTP database applications, Indexes are subjected to data modification, which makes indexes fragmented (gaps in data pages).

Gaps in data pages can reduce the number of rows that can be stored in SQL Server’s data cache, leading to increased disk I/O. Logical fragmentation can cause extra disk activity, as the disk subsystem has to work harder to find the data on disk and move it to the data cache. The only way to remove wasted space and logical fragmentation is to rebuild or reorganize the indexes on a regular basis.


Rebuild: physically drop and rebuilds indexes from scratch. This removes index fragmentation and updates statistics at the same time. If you use this option, you do not want to run the Reorganize Index or the Update Statistics task, as doing so would be redundant.



Reorganize Index is more of a gentle reshuffling of the leaf-level pages of an index, such that the physical ordering matches the logical ordering, and wasted space is minimized.



Difference between Rebuild and Reorganize:


Reorganize Index Rebuild Index
Performs a less thorough index defragmentation than Rebuild Index Virtually all wasted free space and logical fragmentation is removed.
If an index does not have any fragmentation, then it is not reorganized, saving resources. All indexes are rebuilt from scratch, whether they need it or not.
Does not require long blocking locks. Requires potentially long blocking locks that prevent users from accessing the indexes being rebuilt.
An online task that allows users to access the database during the task. A task that should be performed offline, though with the Enterprise Edition of SQL Server, you can use the online version of rebuilding an index.
Generally takes longer to run than the Rebuild Index Task. Generally runs faster than the Reorganize Index Task.
Uses less disk space than the Rebuild Index Task. Uses more disk space than the Reorganize Index Task.
Uses less space in the transaction log than the Rebuild Index Task. More space is required in the transaction log than the Reorganize Index task.
Index and column statistics must be updated separately. This adds to the administration hassle. Index and column statistics are automatically updated as part of this step, using the FULLSCAN option.




Tracking Index Fragmentation

By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases.


Sample Query to check Fragmentation in a Database:


Sample Output:



Recommended Operation based on Fragmentation Percent:

avg_fragmentation_in_percent value Corrective statement

* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online.

If the fragmenation % is between 5% and 30%, then Index Reorganize is sufficient. However, If it exceeds 30%, then index Rebuild is recommended.


Index Maintenance frequency and Time

It is difficult to give general advise on how frequently index maintenance (Rebuild/Reorganise/Update Statistics) should be performed, as it depends on the nature of data, indexes and query which used them. However few considerations/recommendations are as below.


* If your indexes fragment rapidly and you have maintenance window every night, then Rebuild indexes every night. There is no harm in doing so.

* If it can’t be run nightly, then at a minimum, perform this activity weekly.

* If you don’t have maintenance window long enough weekly, then consider alternatives :

— Rebuild Indexes Online (only available in Enterprise Edition)

— Reorganize indexes and then update indexes



 –Hope this was helpful!!!!!!