Uncategorized

Get adhoc query plans without reuse

9. 3. 2018

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

Read more
Uncategorized

Get CPU usage from Ring Buffer

9. 3. 2018
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,…
Uncategorized

Batch Requests/sec

9. 3. 2018

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…

Read more
Uncategorized

Get I/O statistics for data and log files

9. 3. 2018
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] +…
Uncategorized

Wait Statistics Overview

9. 3. 2018
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’,…
Uncategorized

Statistics overview with modifications, rows and age

9. 3. 2018

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],…

Read more