Collect and Query Cumulative Statistics from DMVs/DMFs

-- http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

-- http://gallery.technet.microsoft.com/ScriptCenter/en-us/site/search?f%5B0%5D.Type=Tag&f%5B0%5D.Value=SQL%20Server%202005

USE master

DROP DATABASE MSCSSDMV

CREATE DATABASE MSCSSDMV

USE MSCSSDMV

--Use the following query to list all the schedulers

--and look at the number of runnable tasks.

SELECT scheduler_id ,

current_tasks_count ,

runnable_tasks_count

INTO DMV_dm_os_schedulers

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

The query against sys.dm_exec_query_stats is an efficient way to determine which query is using the most cumulative CPU.

SELECT highest_cpu_queries.* ,

q.dbid ,

q.objectid ,

q.number ,

q.encrypted ,

q.[text]

INTO DMV_Top50_CPU_Commands

FROM ( SELECT TOP 50

qs.*

FROM sys.dm_exec_query_stats qs

ORDER BY qs.total_worker_time DESC

) AS highest_cpu_queries

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q

ORDER BY highest_cpu_queries.total_worker_time DESC

--Gives you the top 25 stored procedures that have been recompiled.

SELECT TOP 25

sql_text.text ,

sql_handle ,

plan_generation_num ,

execution_count ,

dbid ,

objectid

INTO DMV_Top25_Recompile_Commands

FROM sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text

WHERE plan_generation_num > 1

ORDER BY plan_generation_num DESC

-- sys.dm_os_wait_stats

SELECT *

INTO DMV_dm_os_wait_stats

FROM sys.dm_os_wait_stats

--Top 50 IO contributer.

SELECT TOP 50

( total_logical_reads / execution_count ) AS avg_logical_reads ,

( total_logical_writes / execution_count ) AS avg_logical_writes ,

( total_physical_reads / execution_count ) AS avg_phys_reads ,

execution_count ,

statement_start_offset AS stmt_start_offset ,

statement_end_offset AS stmt_end_offset ,

sql_handle ,

SUBSTRING(sql_text.text, ( statement_start_offset / 2 ),

CASE WHEN ( statement_end_offset - statement_start_offset )

/ 2 <= 0 THEN 64000

ELSE ( statement_end_offset - statement_start_offset )

/ 2

END) AS exec_statement ,

sql_text.text ,

plan_handle ,

plan_text.*

INTO DMV_Top50_IO

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS plan_text

ORDER BY ( total_logical_reads + total_logical_writes ) / execution_count DESC

--Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

SELECT *

INTO DMV_dm_db_index_operational_stats

FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);

--Calculate Average Stalls per database file

SELECT database_id ,

file_id ,

io_stall_read_ms ,

num_of_reads ,

CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS 'avg_read_stall_ms' ,

io_stall_write_ms ,

num_of_writes ,

CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS 'avg_write_stall_ms' ,

io_stall_read_ms + io_stall_write_ms AS io_stalls ,

num_of_reads + num_of_writes AS total_io ,

CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads

+ num_of_writes ) AS NUMERIC(10,

1)) AS 'avg_io_stall_ms'

INTO DVM_File_Average_Stalls

FROM sys.dm_io_virtual_file_stats(NULL, NULL)

ORDER BY avg_io_stall_ms DESC

-- Tempdb.

-- An overview query.

SELECT SUM(user_object_reserved_page_count) * 8 AS user_objects_kb ,

SUM(internal_object_reserved_page_count) * 8 AS internal_objects_kb ,

SUM(version_store_reserved_page_count) * 8 AS version_store_kb ,

SUM(unallocated_extent_page_count) * 8 AS freespace_kb

INTO DMV_Tempdb_Overall_Usage

FROM sys.dm_db_file_space_usage

WHERE database_id = 2

-- Determine tempdb space used by Task

SELECT t1.session_id ,

( t1.internal_objects_alloc_page_count + task_alloc ) AS allocated ,

( t1.internal_objects_dealloc_page_count + task_dealloc ) AS deallocated

INTO DMV_Tempdb_Usage_By_Session

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 t2.session_id > 50

ORDER BY allocated DESC

-- The following DMV query can be used to get useful information about the index usage for all objects in all databases.

SELECT *

INTO DMV_dm_db_index_usage_stats

FROM sys.dm_db_index_usage_stats

ORDER BY database_id ,

object_id ,

index_id

CREATE TABLE [dbo].[DMV_Buffer_Counts_By_Objects]

(

[database_id] [INT] NULL ,

[db] [NVARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL ,

[object_id] [INT] NOT NULL ,

[index_id] [INT] NOT NULL ,

[buffer_count] [INT] NULL

)

ON [PRIMARY]

USE [MSCSSDMV]

/****** Object: Table [dbo].[chenTable_Index_Access_Type] Script Date: 01/18/2007 15:45:10 ******/

CREATE TABLE [dbo].[Table_Index_Access_Type]

(

[database_id] [INT] NULL ,

[db] [NVARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL ,

[object] [INT] NOT NULL ,

[index_id] [INT] NOT NULL ,

[user reads] [BIGINT] NULL ,

[system reads] [BIGINT] NULL ,

[user writes] [BIGINT] NOT NULL ,

[system writes] [BIGINT] NOT NULL

)

ON [PRIMARY]

CREATE TABLE [dbo].[Table_Index_Access_Type_2]

(

[database_id] [INT] NULL ,

[db] [NVARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL ,

[object] [INT] NOT NULL ,

[index_id] [INT] NOT NULL ,

[usage_reads] [BIGINT] NULL ,

[operational_reads] [BIGINT] NULL ,

[range_scan_count] [BIGINT] NOT NULL ,

[singleton_lookup_count] [BIGINT] NOT NULL ,

[usage writes] [BIGINT] NOT NULL ,

[operational_leaf_writes] [BIGINT] NULL ,

[leaf_insert_count] [BIGINT] NOT NULL ,

[leaf_update_count] [BIGINT] NOT NULL ,

[leaf_delete_count] [BIGINT] NOT NULL ,

[operational_leaf_page_splits] [BIGINT] NOT NULL ,

[operational_nonleaf_writes] [BIGINT] NULL ,

[operational_nonleaf_page_splits] [BIGINT] NOT NULL

)

ON [PRIMARY]

CREATE TABLE [dbo].[Table_Index_names]

(

[database_id] [INT] NULL ,

[db] [NVARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL ,

[Object] [INT] NOT NULL ,

[Index_Id] [SMALLINT] NULL ,

[TableName] [sysname] COLLATE Latin1_General_CI_AS

NOT NULL ,

[IndexName] [sysname] COLLATE Latin1_General_CI_AS

NULL ,

[Rows] [INT] NULL

)

ON [PRIMARY]

DECLARE @name NVARCHAR(100)

DECLARE @cmd NVARCHAR(1200)

DECLARE dbnames CURSOR

FOR

SELECT name

FROM master.dbo.sysdatabases

OPEN dbnames

FETCH NEXT FROM dbnames INTO @name

WHILE @@fetch_status = 0

BEGIN

SET @cmd = 'insert into Table_Index_names select db_id(''' + @name

+ '''), ''' + @name

+ ''',

a.id as ObjectId, a.indid as IndexId, b.name as TableName, a.name as IndexName, a.rows as Rows from ['

+ @name + '].dbo.sysindexes a inner join [' + @name

+ '].dbo.sysobjects b on a.id = b.id'

EXEC (@cmd)

--- Determine the data pages by tables

SET @cmd = 'insert into DMV_Buffer_Counts_By_Objects

select b.database_id, db=db_name(b.database_id)

,p.object_id

,p.index_id

,buffer_count=count(*)

from ' + @name + '.sys.allocation_units a, ' + @name

+ '.sys.dm_os_buffer_descriptors b, ' + @name + '.sys.partitions p

where a.allocation_unit_id = b.allocation_unit_id

and a.container_id = p.hobt_id

and b.database_id = db_id(''' + @name + ''')

group by b.database_id,p.object_id, p.index_id

order by b.database_id, buffer_count desc'

EXEC (@cmd)

-- Determine Index Cost Benefits

--- sys.dm_db_index_usage_stats

SET @cmd = 'insert into Table_Index_Access_Type select db_id('''

+ @name + '''), ''' + @name + ''',

''object'' = object_id,index_id '

+ ',''user reads'' = user_seeks + user_scans + user_lookups '

+ ',''system reads'' = system_seeks + system_scans + system_lookups '

+ ',''user writes'' = user_updates '

+ ',''system writes'' = system_updates '

+ 'from sys.dm_db_index_usage_stats '

+ 'where database_id = db_id (''' + @name + ''') '

+ 'order by ''user reads'' desc '

EXEC (@cmd)

SET @cmd = 'insert into Table_Index_Access_Type_2 select db_id('''

+ @name + '''), ''' + @name + ''',

''object''=o.object_id, o.index_id '

+ ', ''usage_reads''=user_seeks + user_scans + user_lookups '

+ ', ''operational_reads''=range_scan_count + singleton_lookup_count '

+ ', range_scan_count ' + ', singleton_lookup_count '

+ ', ''usage writes'' = user_updates '

+ ', ''operational_leaf_writes''=leaf_insert_count+leaf_update_count+ leaf_delete_count '

+ ', leaf_insert_count,leaf_update_count,leaf_delete_count '

+ ', ''operational_leaf_page_splits'' = leaf_allocation_count '

+ ', ''operational_nonleaf_writes''=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count '

+ ', ''operational_nonleaf_page_splits'' = nonleaf_allocation_count '

+ +'from sys.dm_db_index_operational_stats (db_id(''' + @name

+ '''),NULL,NULL,NULL) o ' + ',sys.dm_db_index_usage_stats u '

+ 'where u.database_id = db_id (''' + @name + ''') and

u.object_id = o.object_id and u.index_id = o.index_id '

+ 'order by operational_reads desc, operational_leaf_writes, operational_nonleaf_writes '

EXEC (@cmd)

FETCH NEXT FROM dbnames INTO @name

END

CLOSE dbnames

DEALLOCATE dbnames

-- Determine CPU Resources Required for Optimization

Select * from sys.dm_exec_query_optimizer_info

where counter in ('optimizations','elapsed time','trivial plan','tables','insert stmt','update stmt','delete stmt')

SELECT *

INTO DMV_dm_exec_query_optimizer_info

FROM sys.dm_exec_query_optimizer_info

-- Retrieve Statements with the Highest Plan Re-Use Counts

SELECT TOP 100

qs.sql_handle ,

qs.plan_handle ,

cp.cacheobjtype ,

cp.usecounts ,

cp.size_in_bytes ,

qs.statement_start_offset ,

qs.statement_end_offset ,

qt.dbid ,

qt.objectid ,

qt.text ,

SUBSTRING(qt.text, qs.statement_start_offset / 2,

( CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset ) / 2) AS statement

INTO DMV_Top100_Reuse_Plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle = cp.plan_handle

WHERE cp.plan_handle = qs.plan_handle

--and qt.dbid = db_id()

ORDER BY [dbid] ,

[usecounts] DESC

--Retrieve Statements with the Lowest Plan Re-Use Counts

SELECT TOP 100

cp.cacheobjtype ,

cp.usecounts ,

size = cp.size_in_bytes ,

stmt_start = qs.statement_start_offset ,

stmt_end = qs.statement_end_offset ,

qt.dbid ,

qt.objectid ,

qt.text ,

SUBSTRING(qt.text, qs.statement_start_offset / 2,

( CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset ) / 2) AS statement ,

qs.sql_handle ,

qs.plan_handle

INTO DMV_Top100_NoReuse_Plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle = cp.plan_handle

WHERE cp.plan_handle = qs.plan_handle

AND qt.dbid IS NULL

ORDER BY [usecounts] ,

[statement] ASC

-- Retrieve Parallel Statements With the Highest Worker Time

SELECT TOP 100

qs.total_worker_time ,

qs.total_elapsed_time ,

SUBSTRING(qt.text, qs.statement_start_offset / 2,

( CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset ) / 2) AS query_text ,

qt.dbid ,

dbname = DB_NAME(qt.dbid) ,

qt.objectid ,

qs.sql_handle ,

qs.plan_handle

INTO DMV_HighCPU_Parallel_Statements

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qs.total_worker_time > qs.total_elapsed_time

ORDER BY qs.total_worker_time DESC

----------------------

--Indexes and Indexing

----------------------

-- Identify Missing Indexes

SELECT d.* ,

s.avg_total_user_cost ,

s.avg_user_impact ,

s.last_user_seek ,

s.unique_compiles

INTO DMV_Missing_Indexes

FROM sys.dm_db_missing_index_group_stats s ,

sys.dm_db_missing_index_groups g ,

sys.dm_db_missing_index_details d

WHERE s.group_handle = g.index_group_handle

AND d.index_handle = g.index_handle

ORDER BY s.avg_user_impact DESC

----------------------

--Ring Buffer

----------------------

DECLARE @runtime DATETIME

SET @runtime = GETDATE()

SELECT CONVERT (VARCHAR(30), @runtime, 121) AS runtime ,

DATEADD(ms, ring.[timestamp] - sys.ms_ticks, GETDATE()) AS record_time ,

ring.[timestamp] AS record_timestamp ,

sys.ms_ticks AS cur_timestamp ,

ring.*

INTO DMV_dm_os_ring_buffers

FROM sys.dm_os_ring_buffers ring

CROSS JOIN sys.dm_os_sys_info sys

----------------------

---Determine memory usage by connection

----------------------

SELECT TOP 200

( ( granted_query_memory * 8192 ) / 1024 ) AS GrantedMemoryInKb ,

s3.* ,

start_time ,

( SELECT SUBSTRING(text, statement_start_offset / 2,

( CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), text))

* 2

ELSE statement_end_offset

END - statement_start_offset ) / 2)

FROM sys.dm_exec_sql_text(sql_handle)

) AS query_text ,

SUBSTRING(text, 1, 200) AS Text_header

INTO Memory_Usage_By_Connection

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

INNER JOIN sys.dm_exec_sessions s3 ON s1.session_id = s3.session_id

WHERE granted_query_memory <> 0

ORDER BY granted_query_memory DESC

BACKUP DATABASE MSCSSDMV TO DISK = 'c:\MSCSSDMV.bak'

USE master