I have attended the idera sponsored webinar on 'Top 10 Most Useful SQL Server DMVs' by Greg Robidoux
Here are my learnings of that webcast
What are DMVsDynamic Management Views are views and functions introduced in sql server 2005 for monitoring and tuning sql server performance.
Dynamic Management Objects (DMOs)Dynamic Management Views (DMVs) -- can select like a view
Dynamic Management Functions(DMFs) --Requires input parameters like a function
When and Why use them
Provides information that was not available in previous version of sql server
Provides a simpler way to query the data just like any other view versus using DBCC commands or system stored procedures
Types of DMVs- change data capture
- common language runtime
- database mirroring
- database
- execution
- full-text search
- I/O
- Index
- Object
- Query notifications
- Replication
- Resource governor
- SQL Operating System
Get a list of all DMOs
select name, type_descfrom sys.all_objects where name like 'dm%' order by name
Permissions
Server scoped-- view server state
Database scoped--view database state
Deny takes prescedencedeny state or deny select on an object
People should have sys admin privileges
Grant permissions
grant view server state to loginname
grant view database state to user
deny view server state to loginname
deny view database state
must create user in master first
Specific types of DMVs
- database
- execution
- IO
- Index
- SQL operatng system
Database for page and row count
select object_name(object_id) as objname, * from sys.dm_db_partition_stats order by 1
Tips 1851 -- mssqltips.com
Execution--- (when sql server is restart everything is reset)
sys.dm_exec_sessions-- info about all active user connections and internal tasks
sys.dm_exec_connections-- info about connections established
sys.dm_exec_requests-- info about each request that is executing (including all system processes)
Tips 1811, 1817, 1829, 1861
Execution--- Query plans
sys.dm_exec_sql_text--returns text of sql batch
sys.dm_exec_query_plan--returns showplan in xml
select * from sys.dm_exec_query_stats-- returns stats for cached query plans sys.dm_exec_cached_plans--each query plan that is cached
Exection -- example
select * from dm_exec_connections cross apply
sys.dm_exec_sql_text(most_recent_sql_handle)
select * from dm_exec_requests cross apply
sys.dm_exec_sql_text(sql_handle)
Select T.[text],p.[query_plan], s.[program_name],s.host_name, s.client_interface_name, s.login_name, r.* from sys_dm_exec_requests r inner join sys.dm_exec_sessions S ONs.session_id = r.session_idcross apply sql_text cross apply sys.dm_execsql_query_plan
select usecounts, cacheobjype, objtype, text from sys.dm_exec_cached_planscross apply dm_exec_sql_text(plan_handle)where usecounts > 1 order by use counts desc
IO
select * sys.dm_io_pending_io_requests can be run when you think that io can be a bottleneck select * from sys.dm_io_virtual_file_stats (null,null)
select db_name(database_id), * from sys.dm_io_virtual_file_stats(null,null) --shows io stats for data and log files -- database id and
file id -- null returns all datadb_name is a funtion to return the name of the actual
database rather than database id
Index (when sql server is restart everything is reset)
sys.dm_dm_db_index_operational_stats (DMF) -- shows io, locking and access information such as inserts, deletes, updates
sys.dm_dm_db_index_physical_stats (DMF) -- shows index storage and fragmaentation info,
sys.dm_dm_db_index_usage_stats (DMV) -- shows how often indexes are used and for what type of SQL operation
Tips 1239, 1545, 1642, 1749, 1766, 1789
Index examples
select db_name(dtabase_id), object_name(), * from operation_stats(5,null,null,null)
parameters databaseid, objectid, indexid, partition number
select db_name(dtabase_id), object_name(), * from physical_stats(DB_ID(N'Northwind'),5,null,null,null, detailed)
parameters databaseid, objectid, indexid, partition number, mode
Missing indexes
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_groups
- sys.dm_db_missing_index_group_stats
- sys.dm_db_missing_index_columns
Tip 1634
SQL Operating system
sys.dm_os_schedulers-- information abt processors
sys.dm_os_sys_info-- info abt computer and abt resources available to and consumed by sql server
sys.dm_os_sys_memory-- how memory is used overall on the server, and how much memory is available.
sys.dm_os_wait_stats-- info abt all waitsDBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
Tips 1949
sys.dm_os_buffer_descriptors-- info abt all data pages that are currently in the sql server buffer pool
Tips 1181, 1187
memory use by database
memory use by table
