Monitor Shrink Operations

analyze-shrink-performance.sql

DECLARE @WaitDuration varchar(12) = '00:01:00.00';
DECLARE @DatabaseName sysname = N'';

-------------------------------------------------------------------------------
-- Wait Stats: first snapshot
-------------------------------------------------------------------------------
DECLARE @SessionId int =
(
    SELECT  TOP (1) [session_id]
    FROM    sys.dm_exec_requests
    WHERE   database_id = DB_ID()
      AND   command IN ('DbccFilesCompact','DbccSpaceReclaim','DbccLOBCompact')
);

DROP TABLE IF EXISTS #wait1;
DROP TABLE IF EXISTS #wait2;

SELECT
        wait_type
,       waiting_tasks_count
,       wait_time_ms
,       signal_wait_time_ms
INTO    #wait1
FROM    sys.dm_exec_session_wait_stats
WHERE   [session_id] = @SessionId;

-------------------------------------------------------------------------------
-- Performance Counters: first snapshot
-------------------------------------------------------------------------------
DECLARE
    @StartTicks bigint,
    @EndTicks   bigint;

IF (@DatabaseName = N'') SET @DatabaseName = DB_NAME();

DROP TABLE IF EXISTS #pc1;
DROP TABLE IF EXISTS #pc2;

SELECT  @StartTicks = ms_ticks
FROM    sys.dm_os_sys_info;

SELECT
        counter_name
,       instance_name
,       cntr_value
,       cntr_type
INTO    #pc1
FROM    sys.dm_os_performance_counters
WHERE   instance_name = @DatabaseName
  AND   counter_name IN (
            N'Shrink Data Movement Bytes/sec'
        ,   N'DBCC Logical Scan Bytes/sec'
        ,   N'Log Bytes Flushed/sec'
        );

-------------------------------------------------------------------------------
-- Wait
-------------------------------------------------------------------------------
WAITFOR DELAY @WaitDuration;

-------------------------------------------------------------------------------
-- Wait Stats: second snapshot
-------------------------------------------------------------------------------
SELECT
        wait_type
,       waiting_tasks_count
,       wait_time_ms
,       signal_wait_time_ms
INTO    #wait2
FROM    sys.dm_exec_session_wait_stats
WHERE   [session_id] = @SessionId;

-------------------------------------------------------------------------------
-- Performance Counters: second snapshot
-------------------------------------------------------------------------------
SELECT  @EndTicks = ms_ticks
FROM    sys.dm_os_sys_info;

SELECT
        counter_name
,       instance_name
,       cntr_value
,       cntr_type
INTO    #pc2
FROM    sys.dm_os_performance_counters
WHERE   instance_name = @DatabaseName
  AND   counter_name IN (
            N'Shrink Data Movement Bytes/sec'
        ,   N'DBCC Logical Scan Bytes/sec'
        ,   N'Log Bytes Flushed/sec'
        );


-------------------------------------------------------------------------------
-- Report
-------------------------------------------------------------------------------
WITH top_waits AS
(
SELECT TOP (20)
        W2.wait_type
,       W2.waiting_tasks_count - COALESCE(W1.waiting_tasks_count, 0) AS waits
,       W2.wait_time_ms - COALESCE(W1.wait_time_ms, 0) AS wait_ms
,       W2.signal_wait_time_ms - COALESCE(W1.signal_wait_time_ms, 0) AS signal_wait_ms
FROM    #wait2 AS W2
LEFT
JOIN    #wait1 AS W1
  ON    W1.wait_type = W2.wait_type
WHERE   W2.wait_time_ms - COALESCE(W1.wait_time_ms, 0) > 0
ORDER BY wait_ms DESC
)
SELECT 
    wait_type
,   waits AS count_count
,   CONCAT(wait_ms / 86400000, '.', STUFF(CONVERT(char(12), DATEADD(MILLISECOND, wait_ms % 86400000, 0), 114), 9, 1, '.')) AS wait_duration
,   CONCAT(signal_wait_ms / 86400000, '.', STUFF(CONVERT(char(12), DATEADD(MILLISECOND, signal_wait_ms % 86400000, 0), 114), 9, 1, '.')) AS signal_wait_duration
FROM    top_waits;

SELECT
        P2.counter_name
--,       P2.cntr_type
--,       P2.cntr_value - P1.cntr_value AS raw_delta
,       CAST(
            (P2.cntr_value - P1.cntr_value)
            / 1048576.0
            / NULLIF((@EndTicks - @StartTicks) / 1000.0, 0)
        AS decimal(18,3)) AS [MiB/sec]
FROM    #pc1 AS P1
JOIN    #pc2 AS P2
  ON    P2.counter_name = P1.counter_name
 AND    P2.instance_name = P1.instance_name;

interactive-shrink-monitor.sql

SELECT
    R.[session_id]
,   R.command
,   CAST(R.percent_complete AS decimal(5,2)) AS pct_complete
,   R.[status]
,   R.wait_type
,   R.wait_resource
,   CONCAT(R.total_elapsed_time / 86400000, ' days ', CONVERT(char(8), DATEADD(MILLISECOND, R.total_elapsed_time % 86400000, 0), 108)) AS elapsed
,   CONCAT(R.estimated_completion_time / 86400000, ' days ', CONVERT(char(8), DATEADD(MILLISECOND, R.estimated_completion_time % 86400000, 0), 108)) AS est_left
,   R.reads
,   R.writes
FROM    sys.dm_exec_requests AS R
WHERE   R.command IN ('DbccFilesCompact','DbccSpaceReclaim','DbccLOBCompact');
--WHERE R.command LIKE 'Dbcc%';

SELECT
    [name] AS logical_name
,   CAST([size] / 128.0 AS decimal(10,1)) AS size_mb
,   CAST(FILEPROPERTY([name], 'SpaceUsed') / 128.0 AS decimal(10,1)) AS used_mb
,   CAST(([size] - FILEPROPERTY([name], 'SpaceUsed')) / 128.0 AS decimal(10,1)) AS free_mb
FROM sys.database_files
WHERE type_desc <> 'LOG'
ORDER BY [name];
--ORDER BY [size] DESC;
--ORDER BY free_mb DESC;