Uncategorized

Get adhoc query plans without reuse

Transact-SQL SELECT cp.usecounts, text querytext, cp.size_in_bytes/1024 size, plan_handle FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cp.cacheobjtype = N'Compiled Plan' AND cp.objtype = N'Adhoc' AND cp.usecounts = 1 ORDER BY querytext GO 123456789 SELECT cp.usecounts, text querytext, cp.size_in_bytes/1024 size, plan_handleFROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE cp.cacheobjtype = N'Compiled Plan' AND   cp.objtype = N'Adhoc' AND   cp.usecounts = 1ORDER BY querytextGO
Uncategorized

Get CPU usage from Ring Buffer

Transact-SQL DECLARE @Now BIGINT SELECT @Now = ms_ticks FROM sys.dm_os_sys_info SELECT DATEADD(ms, -1 * ( @Now - [timestamp] ), GETDATE()) AS EventTime, SQLService, Idle, 100 - Idle - SQLService AS NonSqlProcesses FROM ( SELECT record.value('(./Record/@id)[1]', 'INT') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'INT') AS Idle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'INT') AS SQLService, timestamp FROM ( SELECT timestamp, CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%%' ) AS x ) AS y ORDER BY 1 DESC 123456789101112131415161718 DECLARE @Now BIGINTSELECT  @Now = ms_ticks FROM sys.dm_os_sys_info  SELECT  DATEADD(ms, -1 * ( @Now - [timestamp] ), GETDATE()) AS EventTime,        SQLService,         Idle,        100 - Idle - SQLService AS NonSqlProcessesFROM    ( SELECT    record.value('(./Record/@id)[1]', 'INT') AS record_id,                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'INT') AS Idle,                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'INT') AS SQLService,                     timestamp          FROM      ( SELECT    timestamp, CONVERT(XML, record) AS record                      FROM      sys.dm_os_ring_buffers                      WHERE     ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'                                AND record LIKE '%%'                    ) AS x        ) AS yORDER BY 1 DESC Listing Ring Buffer record types: Transact-SQL SELECT RecordType, COUNT(*) FROM ( SELECT CAST(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS RecordType FROM sys.dm_os_ring_buffers ) rb GROUP BY RecordType 1234567 SELECT RecordType, COUNT(*)FROM     (        SELECT CAST(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS RecordType        FROM sys.dm_os_ring_buffers    ) rbGROUP BY RecordType
Uncategorized

Batch Requests/sec

Transact-SQL DECLARE @Batches INT DECLARE @Delay CHAR(8) DECLARE @SecToCalc INT -- set seconds to calculate average SET @SecToCalc = 5 SET @Delay = '00:00:' + CAST(@SecToCalc AS CHAR(2)) SELECT @Batches = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM(counter_name) = 'Batch Requests/sec' WAITFOR DELAY @Delay SELECT ( cntr_value - @Batches ) / CAST(@SecToCalc AS FLOAT) AS 'Batch Requests/sec' FROM sys.dm_os_performance_counters WHERE RTRIM(counter_name) = 'Batch Requests/sec' GO 123456789101112131415161718192021 DECLARE @Batches INTDECLARE @Delay CHAR(8)DECLARE @SecToCalc INT -- set seconds to calculate averageSET @SecToCalc = 5 SET @Delay = '00:00:' + CAST(@SecToCalc AS CHAR(2)) SELECT     @Batches = cntr_value FROM sys.dm_os_performance_countersWHERE RTRIM(counter_name) = 'Batch Requests/sec' WAITFOR DELAY @Delay SELECT    ( cntr_value - @Batches ) / CAST(@SecToCalc AS FLOAT) AS 'Batch Requests/sec'FROM sys.dm_os_performance_countersWHERE RTRIM(counter_name) = 'Batch Requests/sec'GO
Uncategorized

Get I/O statistics for data and log files

Transact-SQL SELECT LEFT ([mf].[physical_name], 2) AS [DriveLetter], DB_NAME ([vfs].[database_id]) AS [Database], [mf].[physical_name] [PhysicalName], [vfs].[size_on_disk_bytes] / 1024 / 1024 [FileSizeMB], [vfs].[io_stall], [vfs].[io_stall_read_ms], CAST([io_stall_read_ms] / ( 1.0 + [num_of_reads] ) as numeric(10,1)) as 'avg_read_stall_ms', [vfs].[io_stall_write_ms], 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', [TotalLatency] = CASE WHEN [vfs].[num_of_reads] = 0 AND [vfs].[num_of_writes] = 0 THEN 0 ELSE [vfs].[io_stall] / ([vfs].[num_of_reads] + [vfs].[num_of_writes]) END, [ReadLatency] = CASE WHEN [vfs].[num_of_reads] = 0 THEN 0 ELSE [vfs].[io_stall_read_ms] / [vfs].[num_of_reads] END, [WriteLatency] = CASE WHEN [vfs].[num_of_writes] = 0 THEN 0 ELSE [vfs].[io_stall_write_ms] / [vfs].[num_of_writes] END, [vfs].[num_of_reads] [NumOfReads], [vfs].[num_of_bytes_read] [NumOfBytesRead], [AvgBytesPerRead] = CASE WHEN [vfs].[num_of_reads] = 0 THEN 0 ELSE [vfs].[num_of_bytes_read] / [vfs].[num_of_reads] END, [vfs].[num_of_writes] [NumOfWrites], [vfs].[num_of_bytes_written] [NumOfBytesWritten], [AvgBytesPerWrite] = CASE WHEN [vfs].[io_stall_write_ms] = 0 THEN 0 ELSE [vfs].[num_of_bytes_written] / [vfs].[num_of_writes] END, [AvgBytedPerIO] = CASE WHEN [vfs].[num_of_reads] = 0 AND [vfs].[num_of_writes] = 0 THEN 0 ELSE ([vfs].[num_of_bytes_read] + [vfs].[num_of_bytes_written]) / ([vfs].[num_of_reads] + [vfs].[num_of_writes]) END FROM [sys].[dm_io_virtual_file_stats] (NULL, NULL) [vfs] INNER JOIN [sys].[master_files] [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] ORDER BY [TotalLatency] DESC GO 123456789101112131415161718192021222324252627282930313233343536373839404142434445 SELECT LEFT ([mf].[physical_name], 2) AS [DriveLetter], DB_NAME ([vfs].[database_id]) AS [Database],    [mf].[physical_name] [PhysicalName], [vfs].[size_on_disk_bytes] / 1024 / 1024 [FileSizeMB], [vfs].[io_stall], [vfs].[io_stall_read_ms], CAST([io_stall_read_ms] / ( 1.0 + [num_of_reads] ) as numeric(10,1)) as 'avg_read_stall_ms', [vfs].[io_stall_write_ms], 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', [TotalLatency] = CASE WHEN [vfs].[num_of_reads] = 0 AND [vfs].[num_of_writes] = 0 THEN 0 ELSE [vfs].[io_stall] / ([vfs].[num_of_reads] + [vfs].[num_of_writes]) END,    [ReadLatency] = CASE WHEN [vfs].[num_of_reads] = 0 THEN 0 ELSE [vfs].[io_stall_read_ms] / [vfs].[num_of_reads] END,    [WriteLatency] = CASE WHEN [vfs].[num_of_writes] = 0 THEN 0 ELSE [vfs].[io_stall_write_ms] / [vfs].[num_of_writes] END,    [vfs].[num_of_reads] [NumOfReads], [vfs].[num_of_bytes_read] [NumOfBytesRead], [AvgBytesPerRead] = CASE WHEN [vfs].[num_of_reads] = 0 THEN 0 ELSE [vfs].[num_of_bytes_read] / [vfs].[num_of_reads] END,    [vfs].[num_of_writes] [NumOfWrites], [vfs].[num_of_bytes_written] [NumOfBytesWritten], [AvgBytesPerWrite] = CASE WHEN [vfs].[io_stall_write_ms] = 0 THEN 0 ELSE [vfs].[num_of_bytes_written] / [vfs].[num_of_writes] END,    [AvgBytedPerIO] = CASE WHEN [vfs].[num_of_reads] = 0 AND [vfs].[num_of_writes] = 0 THEN 0 ELSE ([vfs].[num_of_bytes_read] + [vfs].[num_of_bytes_written]) / ([vfs].[num_of_reads] + [vfs].[num_of_writes]) ENDFROM [sys].[dm_io_virtual_file_stats] (NULL, NULL) [vfs] INNER JOIN [sys].[master_files] [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id]ORDER BY [TotalLatency] DESCGO  
Uncategorized

Wait Statistics Overview

Transact-SQL WITH [WaitStats] AS ( SELECT [wait_type], [wait_time_ms] / 1000.0 [WaitTimeSec], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 [ResourcesWaitTimeSec], [signal_wait_time_ms] / 1000.0 [SignalWaitTimeSec], [waiting_tasks_count] [WaitingTasksCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() [WaitPercentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) [Rows], CASE WHEN [wait_type] IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN', N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE', N'TRACEWRITE', N'XE_DISPATCHER_WAIT', N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER', N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP' ) THEN 1 ELSE 0 END IsSystemWaitType FROM [sys].[dm_os_wait_stats] ) SELECT [ws1].[wait_type] AS [WaitType], CAST([ws1].[WaitTimeSec] AS DECIMAL(12, 2)) [WaitTimeSec], CAST([ws1].[ResourcesWaitTimeSec] AS DECIMAL(12, 2)) [ResourcesWaitTimeSec], CAST([ws1].[SignalWaitTimeSec] AS DECIMAL(12, 2)) [SignalWaitTimeSec], [ws1].[WaitingTasksCount], CAST([ws1].[WaitPercentage] AS DECIMAL(12, 2)) [WaitPercentage], CASE WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[WaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2)) ELSE 0 END [AvgWaitTimeSec], CASE WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[ResourcesWaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2)) ELSE 0 END [AvgResourcesWaitTimeSec], CASE WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[SignalWaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2)) ELSE 0 END [AvgSignalWaitTimeSec] FROM [WaitStats] AS [ws1] WHERE [ws1].[IsSystemWaitType] = 0 GROUP BY [ws1].[IsSystemWaitType], [ws1].[Rows], [ws1].[wait_type], [ws1].[WaitTimeSec], [ws1].[ResourcesWaitTimeSec], [ws1].[SignalWaitTimeSec], [ws1].[WaitingTasksCount], [ws1].[WaitPercentage] HAVING [ws1].[WaitTimeSec] > 0 ORDER BY [ws1].[WaitTimeSec] DESC GO 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647 WITH [WaitStats] AS( SELECT        [wait_type],        [wait_time_ms] / 1000.0 [WaitTimeSec],        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 [ResourcesWaitTimeSec],        [signal_wait_time_ms] / 1000.0 [SignalWaitTimeSec],        [waiting_tasks_count] [WaitingTasksCount],        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() [WaitPercentage],        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) [Rows], CASE WHEN [wait_type] IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN', N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE', N'TRACEWRITE', N'XE_DISPATCHER_WAIT', N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER', N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP' ) THEN 1 ELSE 0 END IsSystemWaitType    FROM [sys].[dm_os_wait_stats]    )SELECT    [ws1].[wait_type] AS [WaitType],    CAST([ws1].[WaitTimeSec] AS DECIMAL(12, 2)) [WaitTimeSec],    CAST([ws1].[ResourcesWaitTimeSec] AS DECIMAL(12, 2)) [ResourcesWaitTimeSec],    CAST([ws1].[SignalWaitTimeSec] AS DECIMAL(12, 2)) [SignalWaitTimeSec],    [ws1].[WaitingTasksCount],    CAST([ws1].[WaitPercentage] AS DECIMAL(12, 2)) [WaitPercentage],    CASE WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[WaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2)) ELSE 0 END [AvgWaitTimeSec],    CASE WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[ResourcesWaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2)) ELSE 0 END [AvgResourcesWaitTimeSec],    CASE WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[SignalWaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2)) ELSE 0 END [AvgSignalWaitTimeSec]FROM [WaitStats] AS [ws1]WHERE [ws1].[IsSystemWaitType] = 0GROUP BY [ws1].[IsSystemWaitType], [ws1].[Rows], [ws1].[wait_type], [ws1].[WaitTimeSec], [ws1].[ResourcesWaitTimeSec], [ws1].[SignalWaitTimeSec], [ws1].[WaitingTasksCount], [ws1].[WaitPercentage]HAVING [ws1].[WaitTimeSec] > 0ORDER BY [ws1].[WaitTimeSec] DESCGO  
Uncategorized

Statistics overview with modifications, rows and age

SELECT [sc].[name] [SchemaName], [t].[name] [TableName], [s].[name] [StatisticsName], [ddsp].[modification_counter] [Modifications], [ddsp].[rows] [Rows], [ddsp].[rows_sampled] [RowsSampled], DATEDIFF(DAY, STATS_DATE([ddsp].[object_id], [ddsp].[stats_id]), GETDATE()) [AgeDays] FROM [sys].[stats] [s] CROSS APPLY [sys].[dm_db_stats_properties]([s].[object_id], [s].[stats_id]) [ddsp] INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [s].[object_id] INNER JOIN [sys].[schemas] [sc] ON [sc].[schema_id] = [t].[schema_id] WHERE [sc].[name] <> 'sys' ORDER BY [SchemaName], [TableName], [StatisticsName] 1234567891011121314 SELECT               [sc].[name] [SchemaName], [t].[name] [TableName], [s].[name] [StatisticsName], [ddsp].[modification_counter] [Modifications], [ddsp].[rows] [Rows], [ddsp].[rows_sampled] [RowsSampled], DATEDIFF(DAY, STATS_DATE([ddsp].[object_id], [ddsp].[stats_id]), GETDATE()) [AgeDays]FROM [sys].[stats] [s] CROSS APPLY [sys].[dm_db_stats_properties]([s].[object_id], [s].[stats_id]) [ddsp] INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [s].[object_id] INNER JOIN [sys].[schemas] [sc] ON [sc].[schema_id] = [t].[schema_id]WHERE [sc].[name] <> 'sys' ORDER BY [SchemaName], [TableName], [StatisticsName]