查询 sys.dm_db_log_info() 函数同时减少到最大值

窗口函数是您需要的。LAG并将LEAD告诉前一行和下一行的值是什么，NULL如果没有这样的行（换句话说，第一行或最后一行），将返回。

SELECT
  ddli.vlf_begin_offset,
  ddli.vlf_sequence_number,
  ddli.vlf_active,
  ddli.vlf_status,
  ddli.vlf_first_lsn
FROM  (
    SELECT *,
      prev_active = LAG (vlf_active) OVER (ORDER BY vlf_begin_offset),
      next_active = LEAD(vlf_active) OVER (ORDER BY vlf_begin_offset)
    FROM sys.dm_db_log_info(DB_ID()) ddli
) ddli
WHERE (
      ddli.vlf_active = 1
   OR ddli.prev_active IS NULL
   OR ddli.prev_active = 1
   OR ddli.next_active IS NULL
   OR ddli.next_active = 1
)
ORDER BY
   ddli.vlf_begin_offset;

如果使用 and 的参数，可以简化default逻辑LEADLAG

SELECT
  ddli.vlf_begin_offset,
  ddli.vlf_sequence_number,
  ddli.vlf_active,
  ddli.vlf_status,
  ddli.vlf_first_lsn
FROM  (
    SELECT *,
      prev_active = LAG (vlf_active, 1, 1) OVER (ORDER BY vlf_begin_offset),
      next_active = LEAD(vlf_active, 1, 1) OVER (ORDER BY vlf_begin_offset)
    FROM sys.dm_db_log_info(DB_ID()) ddli
) ddli
WHERE 1 IN (ddli.vlf_active, ddli.prev_active, ddli.next_active)
ORDER BY
   ddli.vlf_begin_offset;

另一种选择是在前行和后行上使用条件COUNT或, 窗口化。SUM您仍然需要LEAD LAG或ROW_NUMBER识别第一行和最后一行，因此它不会给您带来太多好处。

SELECT
  ddli.vlf_begin_offset,
  ddli.vlf_sequence_number,
  ddli.vlf_active,
  ddli.vlf_status,
  ddli.vlf_first_lsn
FROM  (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY vlf_begin_offset),
      next_active = LEAD(vlf_active) OVER (ORDER BY vlf_begin_offset),
      any_active = COUNT(NULLIF(vlf_active, 0)) OVER (ORDER BY vlf_begin_offset ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
    FROM sys.dm_db_log_info(DB_ID()) ddli
) ddli
WHERE ddli.rn = 1
   OR ddli.any_active > 0
   OR ddli.next_active IS NULL
ORDER BY
   ddli.vlf_begin_offset;

要求

使用您的样本数据：

DECLARE @LogInfo table
(
    vlf_begin_offset bigint NULL UNIQUE CLUSTERED,
    vlf_sequence_number bigint NULL,
    vlf_active bit NOT NULL,
    vlf_status integer NULL,
    vlf_first_lsn nvarchar(24) NULL
);

INSERT @LogInfo
VALUES
    (     8192, 253978, 0, 0, N'00000000:00000000:0000'),
    (   262144, 253979, 0, 0, N'00000000:00000000:0000'),
    (   516096, 253980, 0, 0, N'00000000:00000000:0000'),
    (   770048, 253977, 0, 0, N'00000000:00000000:0000'),
    (  1048576, 253981, 0, 0, N'00000000:00000000:0000'),
    ( 17563648, 253982, 0, 0, N'00000000:00000000:0000'),
    ( 34078720, 253983, 1, 2, N'0003e01f:00000010:0001'),
    ( 50593792, 253970, 0, 0, N'00000000:00000000:0000'),
    ( 67108864, 253971, 0, 0, N'00000000:00000000:0000'),
    ( 75497472, 253972, 0, 0, N'00000000:00000000:0000'),
    ( 83886080, 253973, 0, 0, N'00000000:00000000:0000'),
    ( 92274688, 253974, 0, 0, N'00000000:00000000:0000'),
    (100663296, 253975, 0, 0, N'00000000:00000000:0000'),
    (109051904, 253976, 0, 0, N'00000000:00000000:0000'),
    (117440512,      0, 0, 0, N'00000000:00000000:0000'),
    (236978176,      0, 0, 0, N'00000000:00000000:0000'),
    (356515840,      0, 0, 0, N'00000000:00000000:0000'),
    (476053504,      0, 0, 0, N'00000000:00000000:0000'),
    (595591168,      0, 0, 0, N'00000000:00000000:0000'),
    (715128832,      0, 0, 0, N'00000000:00000000:0000'),
    (834666496,      0, 0, 0, N'00000000:00000000:0000'),
    (954204160,      0, 0, 0, N'00000000:00000000:0000');

你想看：

• 偏移顺序的第一行
• 偏移顺序的最后一行
• active = 1 的任何行
• 偏移顺序中的下一行处于活动状态的任何行 = 1
• 偏移顺序中前一行处于活动状态的任何行 = 1

解决方案

我们可以使用最多覆盖三行的偏移排序窗口框架来完成所有这些操作：前一行、当前行和下一行。

第一行和最后一行的帧中只有两行。第一行缺少前一行，最后一行缺少后续行。无论活动值如何，我们总是对这些行感兴趣。其他有趣的行将在其框架的行中具有 active = 1。

注意：如果只有一个 VLF，则该帧将只有一行，但该行必须处于活动状态。活动行将始终是“有趣的”。

另一种表达方式是说我们对两者都感兴趣：

• 三行框架中只有两行的行
• 任何帧行中活动 = 1 的行

我们可以将其封装在以下逻辑中：

SELECT
    Q1.vlf_begin_offset,
    Q1.vlf_sequence_number,
    Q1.vlf_active,
    Q1.vlf_status,
    Q1.vlf_first_lsn
FROM 
(
    SELECT 
        LI.*,
        is_interesting =
            IIF
            (
                -- First & last rows have only two in frame
                2 =
                COUNT_BIG(*) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                -- Any row in frame is active
                OR 1 =
                MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                1,
                0
            )
    FROM @LogInfo AS LI
) AS Q1
WHERE
    Q1.is_interesting = 1
ORDER BY
    Q1.vlf_begin_offset;

结果：

有摘要记录

生成问题中显示的隐藏记录的摘要只会稍微困难一些。我们首先需要一种方法来识别我们想要跳过的连续行组。

这可以通过在当前行之前并包括当前行的偏移排序帧中求和感兴趣的行数来完成：

SELECT
    Q1.*,
    grp = 
        SUM(Q1.is_interesting) OVER (
            ORDER BY Q1.vlf_begin_offset 
            ROWS UNBOUNDED PRECEDING)
FROM 
(
    SELECT 
        LI.*,
        is_interesting =
            IIF
            (
                2 =
                COUNT_BIG(*) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                OR 1 =
                MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                1,
                0
            )
    FROM @LogInfo AS LI
) AS Q1
ORDER BY
    Q1.vlf_begin_offset;

结果：

所需的输出不显示无意义的行的数据，因此用空值替换它们：

SELECT
    vlf_begin_offset = IIF(Q1.is_interesting = 1, Q1.vlf_begin_offset, NULL),
    vlf_sequence_number = IIF(Q1.is_interesting = 1, Q1.vlf_sequence_number, NULL),
    vlf_active = IIF(Q1.is_interesting = 1, Q1.vlf_active, NULL),
    vlf_status = IIF(Q1.is_interesting = 1, Q1.vlf_status, NULL),
    vlf_first_lsn = IIF(Q1.is_interesting = 1, Q1.vlf_first_lsn, NULL),
    Q1.is_interesting,
    grp = 
        SUM(Q1.is_interesting) OVER (
            ORDER BY Q1.vlf_begin_offset 
            ROWS UNBOUNDED PRECEDING)
FROM 
(
    SELECT 
        LI.*,
        is_interesting =
            IIF
            (
                2 =
                COUNT_BIG(*) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                OR 1 =
                MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                1,
                0
            )
    FROM @LogInfo AS LI
) AS Q1
ORDER BY
    Q1.vlf_begin_offset;

结果：

我们需要总结的所有不感兴趣的行现在在它们的组中都是相同的。我们可以删除重复项并使用 plain 计算行数GROUP BY。

最终查询

最后润色会生成自定义的偏移列文本，并确保行以正确的顺序出现：

SELECT
    vlf_begin_offset =
        IIF
        (
            Q2.is_interesting = 1,
            CONVERT(sql_variant, Q2.vlf_begin_offset),
            CONCAT('(', COUNT_BIG(*), ' res clipped)')
        ),
    Q2.vlf_sequence_number,
    Q2.vlf_active,
    Q2.vlf_status,
    Q2.vlf_first_lsn
FROM 
(
    SELECT 
        vlf_begin_offset = IIF(Q1.is_interesting = 1, Q1.vlf_begin_offset, NULL),
        vlf_sequence_number = IIF(Q1.is_interesting = 1, Q1.vlf_sequence_number, NULL),
        vlf_active = IIF(Q1.is_interesting = 1, Q1.vlf_active, NULL),
        vlf_status = IIF(Q1.is_interesting = 1, Q1.vlf_status, NULL),
        vlf_first_lsn = IIF(Q1.is_interesting = 1, Q1.vlf_first_lsn, NULL),
        Q1.is_interesting,
        grp = 
            SUM(Q1.is_interesting) OVER (
                ORDER BY Q1.vlf_begin_offset 
                ROWS UNBOUNDED PRECEDING)
    FROM 
    (
        SELECT 
            LI.*,
            is_interesting =
                IIF
                (
                    2 =
                    COUNT_BIG(*) OVER (
                        ORDER BY LI.vlf_begin_offset 
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                    OR 1 =
                    MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                        ORDER BY LI.vlf_begin_offset 
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                    1,
                    0
                )
        FROM @LogInfo AS LI
    ) AS Q1
) AS Q2
GROUP BY 
    Q2.vlf_begin_offset,
    Q2.vlf_sequence_number,
    Q2.vlf_active,
    Q2.vlf_status,
    Q2.vlf_first_lsn,
    Q2.is_interesting,
    Q2.grp
ORDER BY
    Q2.grp,
    Q2.is_interesting DESC;

结果：

用 DMV 调用替换对表变量的单个引用，以在您的服务器上试用它。