Início / dba / Perguntas / 187446
Accepted
John Eisbrener
Asked: 2017-10-03 05:57:58 +0800 CST

SQL Server: identificando o objeto mais próximo do final de um arquivo de dados

  • 772

TL\DR

Estou procurando uma maneira de identificar com eficiência o objeto localizado mais próximo do final de um arquivo de dados do SQL Server . Essa abordagem precisa manter o desempenho em relação a arquivos de dados grandes.

O que eu tenho até agora

A consulta a seguir utiliza uma Função de Gerenciamento Dinâmico não documentada fornecida com o SQL 2012: sys.dm_db_database_page_allocations; este DMF fornece um equivalente aproximado do DBCC INDcomando.

A consulta a seguir identifica o último objeto em um determinado arquivo de dados ( Aviso: não execute isso em um banco de dados maior que 25 GB, a menos que você queira cancelá-lo em algum momento ):

-- 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

O que há de errado com essa abordagem

Como o Aviso acima indica, essa consulta será executada mais lentamente à medida que o tamanho do banco de dados aumenta porque a função é realmente projetada para uma abordagem direcionada para examinar um objeto específico e não um arquivo de dados específico em questão. Ao passar os NULLparâmetros como eu fiz, essa função provavelmente itera por todos os objetos dentro do banco de dados nos bastidores e cospe a saída combinada. Isso realiza o que eu preciso, mas o faz de uma maneira muito forçada que não se presta a otimizações.

O que estou pedindo

Espero que haja uma maneira de iterar pelas cadeias GAM, SGAM e/ou IAM para identificar rapidamente o objeto no final de um determinado arquivo de dados. Estou assumindo que tenho que empurrar essa abordagem para fora do TSQL para algo como o PowerShell e voltar a usar chamadas DBCC PAGE , ou algo dessa natureza, percorrendo mapas de alocação de página para descobrir qual é o último objeto de um determinado arquivo de dados. ... e espero que alguém já tenha lançado esse código ou conheça essas estruturas e/ou a saída desses procedimentos não documentados melhor do que eu.

Por que eu preciso disso?

Esta é a pergunta inevitável que muitos farão, então vou responder logo de cara. O projeto em que estou trabalhando é para acelerar um sistema legado, mas depois de consolidar várias tabelas de heap (o que foi um pesadelo por outros motivos), agora tenho muito espaço livre nos meus dados arquivos. Eu quero liberar esse espaço de volta para o sistema operacional, no entanto, a abordagem tradicional de migrar objetos para um arquivo de dados diferente não é viável neste estágio porque não tenho espaço livre suficiente para trabalhar no sistema (até que eu capaz de liberar mais espaço deste arquivo de dados).

Recorri a desabilitar o crescimento de arquivos e executar um DBCC SHRINKFILE TRUNCATEONLYcomando todas as noites para liberar todas as páginas abertas no final do arquivo de dados, mas esse é um processo lento e árduo que pode funcionar com tanta frequência quanto não. Espero identificar quais são os objetos no final do arquivo para que eu possa reconstruí-los manualmente e liberar espaço em um cronograma mais rápido.

Em suma

Existe uma maneira de identificar rapidamente o nome do objeto localizado no final de um determinado arquivo de dados? O método que estou empregando agora não está atendendo às minhas necessidades e estou aberto a usar qualquer abordagem disponível.

sql-server datafile

2 respostas

  1. Best Answer

    Eu acho que isso vai fazer isso. Esse código basicamente faz o seguinte:

    1. Recuperar o ID de página alocado mais alto do último intervalo GAM no arquivo
    2. Recupere o ID de página alocado mais alto do último intervalo SGAM no arquivo
    3. Compare dois valores para encontrar a página mais alta
    4. Identifique o último ObjectId (tabela) da última página alocada
    5. Identifique o índice definido no objeto, bem como sua partição
    6. Forneça um DBCC SHRINKFILEcomando que liberará apenas o espaço em branco restante no final do arquivo de volta ao sistema operacional (que deve ser imediato) e seja efetivamente equivalente ao DBCC SHRINKFILEusoTRUNCATEONLY

    Isso está aninhado em um cursor que itera por meio de IDs de página dos arquivos de dados no banco de dados e é executado rapidamente com base em meus testes localizados. Também adicionei funcionalidade para identificar se o final de um arquivo de dados está ocupado por páginas que não são reservadas por tabelas ou índices, como uma página do IAM ou 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. O código a seguir verifica cada página do banco de dados, do número de página mais alto ao mais baixo, para ver se ela está alocada. Depois de encontrar a primeira página alocada, ele mostra o objeto associado a essa página. Não é garantido que funcione, pois a última página alocada pode não fazer referência a um objeto real; no entanto, deve funcionar na maioria das vezes . 

    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';

    Obtemos o número de páginas alocadas para o file_id fornecido no banco de dados atual e, em seguida, usamos um loop para inspecionar cada página via DBCC PAGE, salvando essa saída em uma tabela temporária. A tabela temporária é então unida para sys.all_objectsobter o nome do objeto ao qual a página está alocada.

    No meu equipamento de teste, vejo os seguintes resultados:

    ╔════════════════════╦══════════════╦═════════════ ═════════════╦════════════════════╦══════╗══╦══════╗══╦══════╗══╦══════╗
║ ObjectName ║ ParentObject ║ Object ║ Field ║ VALUE ║
╠════════════════════╬══════════════╬═════════════ ═════════════╬════════════════════╬══════╣═╬══════╣═╬══════╣
║ dbo.EmptyDatabases ║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ Metadados: ObjectId ║ 1938105945 ║
╚════════════════════╩══════════════╩═════════════ ═════════════╩════════════════════╩════════╩══════╝═╩══════╝

    A #dbcreptabela temporária contém os seguintes detalhes:

    ╔══════════════╦══════════════════════════╦═══════ ════════════════════════╦═══════════════════════════════════╗
║ ParentObject ║ Object ║ Field ║ VALUE ║
╠══════════════╬══════════════════════════╬═══════ ════════════════════════╬══════════════════════════════════════
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bpage ║ 0x00000001BA28E000 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bhash ║ 0x0000000000000000 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bpageno ║ (1:42743) ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bdbid ║ 7 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ referências ║ 0 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bcputicks ║ 0 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bsampleCount ║ 0 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bUse1 ║ 10982 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ bstat ║ 0x9 ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║ blog ║ 0x2121215a ║
║ BUFFER: ║ BUF @0x0000000200E95B80 ║bnext ║ 0x0000000000000000 ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ m_pageId ║ (1:42743) ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ m_headerVersion ║ 1 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_type ║ 20 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_typeFlagBits ║ 0x0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_level ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_flagBits ║ 0x204 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_objId (AllocUnitId.idObj) ║ 227 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_indexId (AllocUnitId.idInd) ║ 256 ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ Metadados: AllocUnitId ║ 72057594052804608 ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ Metadados: PartitionId ║ 72057594043301888 ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ Metadados: IndexId ║ 1 ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ Metadados: ObjectId ║ 1938105945 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_prevPage ║ (0:0) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_nextPage ║ (0:0) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ pminlen ║ 8 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_slotCnt ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_freeCnt ║ 8096 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_freeData ║ 96 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_reservedCnt ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_lsn ║ (321:6718:151) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_xactReserved ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_xdesId ║ (0:0) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_ghostRecCnt ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x00000001BA28E000 ║ m_tornBits ║ 1253867700 ║
║ PAGE HEADER: ║ Página @0x00000001BA28E000 ║ DB Frag ID ║ 1 ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ GAM (1:2) ║ ALOCADA ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ SGAM (1:3) ║ NÃO ALOCADO ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ PFS (1:40440) ║ 0x0 0_PCT_FULL ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ DIFF (1:6) ║ NÃO ALTERADO ║
║ CABEÇALHO DA PÁGINA: ║ Status de Alocação ║ ML (1:7) ║ NÃO MIN_LOGGED ║
╚══════════════╩══════════════════════════╩═══════ ════════════════════════╩══════════════════════════════════════
    • 2

relate perguntas