主页 / dba / 问题 / 187446
Accepted
John Eisbrener
Asked: 2017-10-03 05:57:58 +0800 CST

SQL Server：识别最接近数据文件末尾的对象

  • 772

长话短说

我正在寻找一种方法来有效地识别最靠近SQL Server数据文件末尾的对象。这种方法需要保持对大型数据文件的性能。

到目前为止我所拥有的

以下查询使用 SQL 2012 附带的未记录的动态管理函数：sys.dm_db_database_page_allocations; 这个 DMF 提供了一个粗略的DBCC IND命令等价物。

以下查询标识给定数据文件中的最后一个对象（警告：不要对大于 25 GB 的数据库运行此查询，除非您想在某个时候取消它）：

-- Return object with highest Extent Page ID
SELECT   files.name as logical_file_name
        , files.physical_name as physical_file_name
        , OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS object_name
        , alloc.*
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, NULL) alloc
    INNER JOIN sys.database_files files
        ON alloc.extent_file_id = files.file_id
WHERE is_allocated = 1
    AND files.name = 'Logical_FileName'
ORDER BY files.name , files.physical_name, extent_page_id DESC

这种方法有什么问题

正如上面的警告所暗示的那样，随着数据库大小的增加，此查询将运行得更慢，因为该函数实际上是为查看特定对象而不是查看有问题的特定数据文件的有针对性的方法而设计的。当像我一样传递NULL参数时，此函数可能会在后台遍历数据库中的所有对象并吐出组合输出。这完成了我需要的，但它以一种非常蛮力的方式完成，不适合优化。

我要的是什么

我希望有一种方法可以遍历GAM、SGAM和/或IAM 链，以快速识别给定数据文件末尾的对象。我假设我必须将这种方法从 TSQL 推到 PowerShell 之类的东西，然后返回使用DBCC PAGE调用或类似的东西，遍历页面分配映射以查找给定数据文件的最后一个对象是什么。...我希望有人已经将这些代码放在一起，或者比我更了解这些结构和/或这些未记录程序的输出。

为什么我需要这个？

这是许多人不可避免会问的问题，所以我只是开门见山地回答一下。我一直在做的项目是让遗留系统加快速度，但是在将一堆堆表整合在一起之后（由于其他原因这是一场噩梦），我现在在我的数据中留下了一堆可用空间文件。我想将这个空间释放回操作系统，但是，将对象迁移到不同数据文件的传统方法在这个阶段不可行，因为我没有足够的可用空间在系统上使用（直到我能够从此数据文件中释放更多空间）。

我采取了禁用文件增长并DBCC SHRINKFILE TRUNCATEONLY每晚运行命令来释放数据文件末尾的所有打开页面的方法，但这是一个缓慢而艰巨的过程，可能会经常发生，也可能不会发生。我希望确定文件末尾的对象是什么，以便我可以手动重建它们并在更快的时间表中释放空间。

总之

有没有办法快速识别位于给定数据文件末尾的对象的名称？我现在使用的方法不能满足我的需求，我愿意使用任何可用的方法。

sql-server datafile

2 个回答

  1. Best Answer

    我认为这会做到。此代码主要执行以下操作：

    1. 从文件中的最后一个 GAM 间隔检索最高分配的页面 ID
    2. 从文件中的最后一个 SGAM 间隔检索最高分配的页面 ID
    3. 比较两个值以找到最高的页面
    4. 从上次分配的页面中识别最后一个 ObjectId（表）
    5. 识别在对象上定义的索引及其分区
    6. 提供一个DBCC SHRINKFILE命令，该命令仅将文件末尾的剩余空白释放回操作系统（应该是立即执行的）并且实际上等同于DBCC SHRINKFILE使用TRUNCATEONLY

    它嵌套在一个游标中，该游标遍历数据库中数据文件的页面 ID，并根据我的本地化测试快速执行。我还添加了功能来识别数据文件的末尾是否被表或索引未保留的页面占用，例如 IAM 或 PFS 页面。

    SET NOCOUNT ON;

-- Create Temp Table to push DBCC PAGE results into
CREATE TABLE #dbccPage_output(
      ID                INT IDENTITY(1,1)
    , [ParentObject]    VARCHAR(255)
    , [Object]          VARCHAR(255)
    , [Field]           VARCHAR(255)
    , [Value]           VARCHAR(255)
)
GO

-- Variables to hold pointer information for traversing GAM and SGAM pages
DECLARE @GAM_maxPageID INT, @SGAM_maxPageID INT, @maxPageID INT,
        @GAM_page INT, @SGAM_page INT
DECLARE @stmt VARCHAR(2000)

-- Final Output Table
DECLARE @myOutputTable TABLE
(
      LogicalFileName   VARCHAR(255)
    , ObjectID          BIGINT
    , IndexID           BIGINT
    , PartitionID       BIGINT
    , MaxPageID         BIGINT
)

-- Cursor to iterate through each file
DECLARE cursorFileIds CURSOR
FOR
        SELECT file_id, size
        FROM sys.database_files
        WHERE type = 0

-- Variable to hold fileID
DECLARE @fileID INT, @size INT, @interval INT

-- Inject the data into the cursor
OPEN cursorFileIds
FETCH NEXT FROM cursorFileIds
INTO @fileID, @size

-- Enter the While Loop.  This loop will end when the
--  end of the data injected into the cursor is reached.
WHILE @@FETCH_STATUS = 0
BEGIN
        -- Truncate table (mainly used for 2nd pass and forward)
        TRUNCATE TABLE #dbccPage_output

        -- Referenced if we need to step back a GAM/SGAM interval
        STEPBACK:

        -- # of pages in a GAM interval
        SET @interval = @size / 511232
        -- Set GAM Page to read
        SET @GAM_page = CASE @interval WHEN 0 THEN 2 ELSE @interval * 511232 END
        -- Set SGAM page to read (always the next page after the GAM)
        SET @SGAM_page = CASE @interval WHEN 0 THEN 3 ELSE (@interval * 511232) + 1 END

        -- Search Last GAM Interval page
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(@GAM_page AS VARCHAR(20)) + ', 3) WITH TABLERESULTS, NO_INFOMSGS' -- GAM on Primary Datafile
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Get Last Allocated Page Number
        SELECT TOP 1
                @GAM_maxPageID = REVERSE(SUBSTRING(REVERSE(Field), CHARINDEX(')', REVERSE(Field)) + 1, CHARINDEX(':', REVERSE(Field)) - CHARINDEX(')', REVERSE(Field)) - 1))
        FROM #dbccPage_output
        WHERE [Value] = '    ALLOCATED'
        ORDER BY ID DESC

        -- Truncate Table
        TRUNCATE TABLE #dbccPage_output

        -- Search Last SGAM Interval page
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(@SGAM_page AS VARCHAR(20)) + ', 3) WITH TABLERESULTS, NO_INFOMSGS' -- SGAM on Primary Datafile
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Get Last Allocated Page Number
        SELECT TOP 1
                @SGAM_maxPageID = REVERSE(SUBSTRING(REVERSE(Field), CHARINDEX(')', REVERSE(Field)) + 1, CHARINDEX(':', REVERSE(Field)) - CHARINDEX(')', REVERSE(Field)) - 1))
        FROM #dbccPage_output
        WHERE [Value] = '    ALLOCATED'
        ORDER BY ID DESC

        -- Get highest page value between SGAM and GAM
        SELECT @maxPageID = MAX(t.value)
        FROM (VALUES (@GAM_maxPageID), (@SGAM_maxPageID)) t(value)

        TRUNCATE TABLE #dbccPage_output

        -- Check if GAM or SGAM is last allocated page in the chain, if so, step back one interval
        IF(@maxPageID IN (@GAM_page, @SGAM_page))
        BEGIN
            SET @size = ABS(@size - 511232)
            GOTO STEPBACK
        END

        -- Search Highest Page Number of Data File
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(CASE WHEN @maxPageID = @SGAM_maxPageID THEN @maxPageID + 7 ELSE @maxPageID END AS VARCHAR(50)) + ', 1) WITH TABLERESULTS, NO_INFOMSGS' -- Page ID of Last Allocated Object
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Capture Object Name of DataFile
        INSERT INTO @myOutputTable
        SELECT (SELECT name FROM sys.database_files WHERE file_id = @fileID) AS LogicalFileName
            , CASE WHEN (SELECT [Value] FROM #dbccPage_output WHERE Field = 'm_type') IN ('1', '2') THEN -- If page type is data or index
                        CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: ObjectId') AS BIGINT)
                   ELSE CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'm_type') AS BIGINT)
              END AS ObjectID
            , NULLIF(CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: IndexId') AS BIGINT), -1) AS IndexID
            , NULLIF(CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: PartitionId') AS BIGINT), 0) AS PartitionID
            , @maxPageID + 7 AS MaxPageID

        -- Reset Max Page Values
        SELECT @GAM_maxPageID = 0, @SGAM_maxPageID = 0, @maxPageID = 0

     -- Traverse the Data in the cursor
     FETCH NEXT FROM cursorFileIds
     INTO @fileID, @size
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE cursorFileIds
DEALLOCATE cursorFileIds

-- page type values pt. 1: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back/
-- page type values pt. 2: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
-- ObjectIDs of either 0 or 99: https://www.sqlskills.com/blogs/paul/finding-table-name-page-id/
-- Output Object Closest to the End
SELECT  t.LogicalFileName
    ,   CAST(CASE WHEN t.IndexID IS NULL THEN 
                CASE t.ObjectID
                    WHEN 0 THEN  '>>No MetaData Found<<'  -- This isn't m_type, rather ObjectID
                    WHEN 1 THEN  '>>Data Page<<'
                    WHEN 2 THEN  '>>Index Page<<'
                    WHEN 3 THEN  '>>Text Mix Page<<'
                    WHEN 4 THEN  '>>Text Tree Page<<'
                    WHEN 6 THEN  '>>DCM Page<<<'
                    WHEN 7 THEN  '>>Sort Page<<'
                    WHEN 8 THEN  '>>GAM Page<<'
                    WHEN 9 THEN  '>>SGAM Page<<'
                    WHEN 10 THEN '>>IAM Page<<'
                    WHEN 11 THEN '>>PFS Page<<'
                    WHEN 13 THEN '>>Boot Page<<'
                    WHEN 15 THEN '>>File Header Page<<'
                    WHEN 16 THEN '>>Diff Map Page<<'
                    WHEN 17 THEN '>>ML Map Page<<'
                    WHEN 18 THEN '>>Deallocated DBCC CHECKDB Repair Page<<'
                    WHEN 19 THEN '>>Temporary ALTER INDEX Page<<'
                    WHEN 20 THEN '>>Pre-Allocated BULK LOAD Page<<'
                    WHEN 99 THEN '>>Possible Page Corruption/Run DBCC CHECKDB<<'  -- This isn't m_type, rather ObjectID
                    ELSE CAST(t.ObjectID AS VARCHAR(50))
                END
            ELSE QUOTENAME(OBJECT_SCHEMA_NAME(t.ObjectID)) + '.' + QUOTENAME(OBJECT_NAME(t.ObjectID)) END AS VARCHAR(250)) AS TableName
    ,   QUOTENAME(i.name) AS IndexName
    ,   p.partition_number AS PartitionNumber
    ,   'DBCC SHRINKFILE(' + t.LogicalFileName + ', ' + CAST(CEILING((t.MaxPageID + 8) * 0.0078125) AS VARCHAR(50)) + ')' AS ShrinkCommand_Explicit
    ,   'DBCC SHRINKFILE(' + t.LogicalFileName + ', TRUNCATEONLY)' AS ShrinkCommand_TRUNCATEONLY
FROM @myOutputTable t
    LEFT JOIN sys.indexes i
        ON t.ObjectID = i.object_id
        AND t.IndexID = i.index_id
    LEFT JOIN sys.partitions p
        ON t.ObjectID = p.object_id
        AND t.PartitionID = p.partition_id

-- Cleanup
DROP TABLE #dbccPage_output
GO
    • 3

  2. 以下代码检查每个数据库页面，从最高页码到最低页码，看它是否已分配。一旦找到第一个分配的页面，它就会显示与该页面关联的对象。它不能保证工作，因为最后分配的页面可能不引用实际对象；但是它应该在大多数时间都有效。 

    SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#dbcrep', N'U') IS NOT NULL
DROP TABLE #dbcrep;
CREATE TABLE #dbcrep
(
        ParentObject VARCHAR(128)
        , [Object] VARCHAR(128)
        , [Field] VARCHAR(128)
        , VALUE VARCHAR(2000)
);
DECLARE @cmd nvarchar(max);
DECLARE @PageNum int;
DECLARE @PageCount int;
DECLARE @FileID int;
DECLARE @Status varchar(2000);

SET @FileID = 1;

SET @PageCount = (
    SELECT df.size
    FROM sys.database_files df
    WHERE df.file_id = @FileID
    );
SET @PageNum = @PageCount - 1;
WHILE @PageNum > 0
BEGIN
    SET @cmd = N'DBCC PAGE (''' + DB_NAME() + N''', ' + CONVERT(nvarchar(20), @FileID) + N', ' + CONVERT(nvarchar(20), @PageNum) + N', 0) WITH TABLERESULTS, NO_INFOMSGS;';
    DELETE FROM #dbcrep;
    INSERT INTO #dbcrep (ParentObject, [Object], [Field], [VALUE])
    EXEC sys.sp_executesql @cmd;
    SELECT @Status = VALUE
    FROM #dbcrep
    WHERE ParentObject = 'PAGE HEADER:'
        AND Object = 'Allocation Status'
        AND Field LIKE 'GAM %';
    SET @PageNum -= 1;
    PRINT @Status;
    IF @Status <> 'NOT ALLOCATED' BREAK
END

SELECT ObjectName = s.name + N'.' + o.name
    , d.*
FROM #dbcrep d
    LEFT JOIN sys.all_objects o ON d.VALUE = o.object_id
    LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE ParentObject = 'PAGE HEADER:'
    AND Object = 'Page @0x00000001BA28E000'
    AND Field = 'Metadata: ObjectId';

    我们获取当前数据库中给定 file_id 的分配页数，然后使用循环检查每个页DBCC PAGE，将输出保存到临时表中。然后连接临时表sys.all_objects以获得页面分配给的对象的名称。

    在我的测试装置中，我看到以下结果：

    ╔════════════════════╦══════════════╦═════════════ lu
║ 对象名称 ║ 父对象 ║ 对象 ║ 字段 ║ 值 ║
╠════════════════════╬══════════════╬═════════════ ══════════║
║ dbo.EmptyDatabases ║ 页眉：║ 页@0x00000001BA28E000 ║ 元数据：ObjectId ║ 1938105945 ║
╚════════════════════╩══════════════╩═════════════ ══════════║

    #dbcrep临时表包含以下详细信息：

    ╔══════════════╦══════════════════════════╦═══════ lu
║ 父对象 ║ 对象 ║ 字段 ║ 值 ║
╠══════════════╬══════════════════════════╬═══════ lu
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bpage ║ 0x00000001BA28E000 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bhash ║ 0x0000000000000000 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bpageno ║ (1:42743) ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bdbid ║ 7 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ 参考 ║ 0 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bcputicks ║ 0 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bsampleCount ║ 0 ║
║ 缓冲器： ║ BUF @0x0000000200E95B80 ║ bUse1 ║ 10982 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bstat ║ 0x9 ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ 博客 ║ 0x2121215a ║
║ 缓冲区： ║ BUF @0x0000000200E95B80 ║ bnext ║ 0x0000000000000000 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_pageId ║ (1:42743) ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_headerVersion ║ 1 ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_type ║ 20 ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_typeFlagBits ║ 0x0 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_level ║ 0 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_flagBits ║ 0x204 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_objId (AllocUnitId.idObj) ║ 227 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_indexId (AllocUnitId.idInd) ║ 256 ║
║页眉：║页面@0x00000001BA28E000║元数据：AllocUnitId║72057594052804608║
║ 页眉：║ 页@0x00000001BA28E000 ║ 元数据：PartitionId ║ 72057594043301888 ║
║ 页眉：║ 页 @0x00000001BA28E000 ║ 元数据：IndexId ║ 1 ║
║ 页眉：║ 页@0x00000001BA28E000 ║ 元数据：ObjectId ║ 1938105945 ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_prevPage ║ (0:0) ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_nextPage ║ (0:0) ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ pminlen ║ 8 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_slotCnt ║ 0 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_freeCnt ║ 8096 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_freeData ║ 96 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_reservedCnt ║ 0 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_lsn ║ (321:6718:151) ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_xactReserved ║ 0 ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_xdesId ║ (0:0) ║
║ 页眉： ║ 页 @0x00000001BA28E000 ║ m_ghostRecCnt ║ 0 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ m_tornBits ║ 1253867700 ║
║ 页眉： ║ 页@0x00000001BA28E000 ║ 数据库片段 ID ║ 1 ║
║ 页眉： ║ 分配状态 ║ GAM (1:2) ║ 已分配 ║
║ 页眉： ║ 分配状态 ║ SGAM (1:3) ║ 未分配 ║
║ 页眉： ║ 分配状态 ║ PFS (1:40440) ║ 0x0 0_PCT_FULL ║
║ 页眉： ║ 分配状态 ║ 差异 (1:6) ║ 未更改 ║
║ 页眉： ║ 分配状态 ║ ML (1:7) ║ NOT MIN_LOGGED ║
╚══════════════╩══════════════════════════╩═══════ lu
    • 2

相关问题