Sunday, May 16, 2010

SQL Server Dynamic Management Views and Dynamic Management Functions

Here is list of Dynamic Management Views mostly used in day to day database administration.
 -- Page (sys.dm_exec_query_stats)
SELECT TOP 100 execution_count,
SUBSTRING(est.text, (eqs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(est.text)
ELSE eqs.statement_end_offset
END
- eqs.statement_start_offset)/2) + 1) as statement_text,
creation_time, last_execution_time
FROM sys.dm_exec_query_stats as eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) as est
ORDER BY execution_count DESC;  


-- Page (sys.dm_db_file_space_usage)
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128)
AS [version store space in MB]
FROM sys.dm_db_file_space_usage;  


-- Page (sys.dm_db_file_space_usage)
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128)
AS [user object space in MB]
FROM sys.dm_db_file_space_usage;  


-- Page (sys.dm_db_task_space_usage)
SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) AS allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) AS deallocated
FROM sys.dm_db_session_space_usage AS t1,
(SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id) AS t2
WHERE t1.session_id = t2.session_id
AND t1.session_id > 50
ORDER BY allocated DESC;  


-- Page (sys.dm_db_task_space_usage)
SELECT session_id,
SUM(internal_objects_alloc_page_count)
AS 'Internal obj alloc pg count',
SUM(internal_objects_dealloc_page_count)
AS 'Internal obj dealloc pg count',
SUM(user_objects_alloc_page_count)
AS 'User obj alloc pg count',
SUM(user_objects_dealloc_page_count)
AS 'User obj dealloc pg count'
FROM sys.dm_db_task_space_usage
WHERE session_id > 50
GROUP BY session_id
ORDER BY session_id;  


-- Page (sys.dm_exec_cached_plans)
SELECT usecounts, size_in_bytes, objtype,
(SELECT query_plan FROM sys.dm_exec_query_plan(cp.plan_handle))
AS QueryPlan
FROM sys.dm_exec_cached_plans cp
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY usecounts DESC;  


-- Page (sys.dm_exec_query_stats)
SELECT TOP 100
ecp.usecounts, ecp.cacheobjtype, ecp.size_in_bytes,
SUBSTRING(eqt.text,eqs.statement_start_offset/2,
(CASE
WHEN eqs.statement_end_offset = -1
THEN len(convert(NVARCHAR(MAX), eqt.text))*2
ELSE eqs.statement_end_offset
END - eqs.statement_start_offset)/2) AS statement,
eqs.plan_handle
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS eqt
INNER JOIN sys.dm_exec_cached_plans AS ecp
ON eqs.plan_handle=ecp.plan_handle
WHERE ecp.plan_handle=eqs.plan_handle
ORDER BY [usecounts] ASC;  


-- Page (sys.dm_exec_requests)
SELECT session_id, command, total_elapsed_time, status,
reads, writes, start_time, sql_handle
FROM sys.dm_exec_requests
WHERE session_id > 50
ORDER BY total_elapsed_time DESC;  


-- Page (sys.dm_exec_query_stats)
SELECT execution_count,
total_worker_time, total_physical_reads, total_logical_writes,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2 + 1 ,
((CASE
WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset
END) - statement_start_offset) / 2 + 1))
AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY execution_count DESC, total_worker_time DESC;  


-- Page (sys.dm_fts_index_population)
SELECT DB_NAME(database_id) AS database_name,
OBJECT_NAME(table_id) AS table_name, population_type_description,
status_description, start_time
FROM sys.dm_fts_index_population
ORDER BY start_time;  


-- Page (sys.dm_io_virtual_file_stats)
SELECT DB_NAME(database_id) AS database_name,
FILE_NAME(file_id) AS filename, num_of_reads, io_stall_read_ms,
CAST(io_stall_read_ms/(num_of_reads+1) AS NUMERIC(10,1))
AS 'avg_io_stall_read_ms',
num_of_writes, io_stall_write_ms,
CAST(io_stall_write_ms/(num_of_writes+1) AS NUMERIC(10,1))
AS 'avg_io_stall_write_ms',
(num_of_reads+num_of_writes) AS 'total_num_of_ios',
(io_stall_read_ms+io_stall_write_ms) AS 'io_stall_total_ms',
CAST((io_stall_read_ms+io_stall_write_ms)/(num_of_reads+num_of_writes+1)
AS NUMERIC(10,1)) AS 'avg_io_stall_total_ms'
FROM sys.dm_io_virtual_file_stats(NULL,NULL)
ORDER BY avg_io_stall_total_ms DESC;  


-- Page (sys.dm_db_index_operational_stats)
SELECT DB_NAME(database_id) AS 'db_name',
OBJECT_NAME(ios.object_id) AS 'obj_name',
i.name AS 'idx_name', i.index_id,
row_lock_count, row_lock_wait_count,
CAST(row_lock_wait_count/(row_lock_count+1)*100 AS NUMERIC(10,2))
AS '% blocked',
row_lock_wait_in_ms,
CAST (row_lock_wait_in_ms/(row_lock_wait_count+1) AS NUMERIC(10,2))
AS 'avg_row_lock_wait_in_ms'
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) ios,
sys.indexes i
WHERE OBJECTPROPERTY(ios.object_id, 'IsUserTable') = 1
AND i.object_id = ios.object_id
AND i.index_id = ios.index_id
ORDER BY row_lock_wait_count DESC;  


-- Page (sys.dm_db_index_usage_stats)
SELECT OBJECT_NAME(ios.object_id) AS 'obj_name',
ios.object_id, i.name AS 'idx_name', i.index_id,
(user_seeks + user_scans + user_lookups + user_updates)
AS 'total_usage_count',
user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats ios, sys.indexes i
WHERE database_id = db_id()
AND objectproperty(ios.object_id, 'IsUserTable') = 1
AND i.object_id = ios.object_id
AND i.index_id = ios.index_id
ORDER BY total_usage_count ASC;  


-- Page (sys.dm_db_index_usage_stats)
SELECT OBJECT_NAME(i.object_id) AS 'obj_name',
i.name AS 'idx_name', i.index_id
FROM sys.indexes i, sys.objects o
WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
AND o.object_id = i.object_id
AND i.index_id NOT IN (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE s.object_id = i.object_id
AND i.index_id=s.index_id
AND database_id = db_id() )
ORDER BY obj_name, i.index_id ASC;  


-- Page (sys.dm_db_missing_index_columns)
DECLARE @idx_handle INT;

SELECT @idx_handle = mid.index_handle
FROM sys.dm_db_missing_index_group_stats migs,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs.group_handle = mig.index_group_handle
AND mid.index_handle = mig.index_handle;

SELECT * FROM sys.dm_db_missing_index_columns(@idx_handle)
ORDER BY column_id;  


-- Page (sys.dm_db_missing_index_)
SELECT mid.*, migs.avg_total_user_cost, migs.avg_user_impact,
migs.last_user_seek, migs.unique_compiles
FROM sys.dm_db_missing_index_group_stats migs,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs.group_handle = mig.index_group_handle
AND mid.index_handle = mig.index_handle
ORDER BY migs.avg_user_impact DESC;  


-- Page
SELECT t1.name AS ServiceName, t3.name AS SchemaName, t2.name AS QueueName,
CASE WHEN t4.state IS NULL THEN 'Not available'
ELSE t4.state
END AS [Queue_State],
CASE WHEN t4.tasks_waiting IS NULL THEN '--'
ELSE CONVERT(VARCHAR, t4.tasks_waiting)
END AS tasks_waiting,
CASE WHEN t4.last_activated_time IS NULL THEN '--'
ELSE CONVERT(varchar, t4.last_activated_time)
END AS last_activated_time,
CASE WHEN t4.last_empty_rowset_time IS NULL THEN '--'
ELSE CONVERT(varchar,t4.last_empty_rowset_time)
END AS last_empty_rowset_time,
(SELECT COUNT(*) FROM sys.transmission_queue t6
WHERE (t6.from_service_name = t1.name)) AS TransMessageCount
FROM sys.services t1
INNER JOIN sys.service_queues t2 ON (t1.service_queue_id = t2.object_id)
INNER JOIN sys.schemas t3 ON (t2.schema_id = t3.schema_id)
LEFT OUTER JOIN sys.dm_broker_queue_monitors t4
ON (t2.object_id = t4.queue_id AND t4.database_id = DB_ID())
INNER JOIN sys.databases t5 ON (t5.database_id = DB_ID());  


-- Page (sys.dm_os_memory_objects)
SELECT type, SUM(pages_allocated_count * page_size_in_bytes)/1024 AS 'KB_Used'
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY KB_Used DESC;  


-- Page (sys.dm_os_performance_counters) 
DECLARE @Numerator FLOAT, @Denominator FLOAT; 
SET @Numerator = (
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'); 

SET @Denominator = (
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'); 

SELECT (@Numerator/@Denominator)*100 AS 'Cache_Hit_Ratio (%)';  

-- Page (sys.dm_os_schedulers)
SELECT scheduler_id, cpu_id, current_tasks_count,
runnable_tasks_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE scheduler_id <>
ORDER BY work_queue_count DESC, scheduler_id ASC;  


-- Page (sys.dm_os_waiting_tasks)
SELECT DB_NAME(resource_database_id) AS database_name,
resource_type, request_mode, request_session_id,
blocking_session_id, resource_associated_entity_id
FROM sys.dm_tran_locks AS dtl
INNER JOIN sys.dm_os_waiting_tasks AS dowt
ON dtl.lock_owner_address = dowt.resource_address; 


--Three key functions and views-namely,

sys.dm_db_index_physical_stats,
sys.dm_db_index_usage_stats, and
sys.dm_db_index_operational_stats

Above DMV's are available to help you understand whether your indexes are working as expected. They let you review the I/O and locking patterns of your indexes, and understand whether your indexes are being utilized by the query optimizer in a manner that does not lead to unnecessary contention in the database.
Dynamic Management Views and Functions, Find Most Expensive Queries Using DMV, A Microsoft SQL Server DMV, DMF, SQLServer Performance Monitoring using DMV and DMF, SQL DMVStats, SQL Server DMV Views, Free SQL Server DMV Starter Pack, dmv sql server 2005, dmv sql server 2008, dmv sql server 2008 list, dmv sql server 2008 r2, performance dmv sql server 2008, dmv sql server 2012, sql server central, error while executing dmv, dynamic management views ebook, what is dynamic management views in sql server, performance tuning with sql server dynamic management views download, performance tuning with sql server dynamic management views ebook, sql server dynamic management views poster, performance tuning with sql server dynamic management views free ebook, performance tuning with sql server dynamic management views pdf download, dynamic management views sql server 2012.

1 comments:

Post a Comment