Top Most Used Sql Server DMV's

TOP MOST USED SQL SERVER DMV’s(Dynamic Management Views) What are DMVs Dynamic 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 Permissions 1 select name, type_desc from sys.all_objects where name like 'dm%' order by name Server scoped -- view server state database scoped --view database state Deny takes prescedence deny 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 T 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) 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_id cross apply sql_text cross apply sys.dm_execsql_query_plan select usecounts, cacheobjype, objtype, text from sys.dm_exec_cached_plans cross apply dm_exec_sql_text(plan_handle) where usecounts > 1 order by use cuonts 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 data db_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 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, detaled) 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 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 waits DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) sys.dm_os_buffer_descriptors -- info abt all data pages that are currently in the sql server buffer pool