使用自定义函数和存储过程显示出自上次完备之后数据库的变化情况，有多少个区发生变化

使用自定义函数和存储过程显示出自上次完备之后数据库的变化情况，有多少个区发生变化

http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/

Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup?

No such code exists as far as I know – until now! I happened to read the thread while sitting in the airport in Washington D.C. on the way back from Iceland so I started playing around and this morning I completed the code.

The code below creates a function and a stored procedure. The basic idea behind the code is as follows:

For each online data file in the database
   For each GAM interval in the file
      Crack the DIFF map page using DBCC PAGE
      Interpret the DIFF bitmap to aggregate the changed extents
      Add the sum to the total changed extents for the database
   End
End
Report results

There's a function that I create in msdb call SQLskillsConvertToExtents that cracks some of the DBCC PAGE output, and the main procedure is calledsp_SQLskillsDIFForFULL and it created as a system object in master. I tried making it a table-valued function but you can't do things like INSERT-EXEC in a function, and that's required for processing the DBCC PAGE output. So – create your own wrapper function or whatever to use it. The interface/output is:

 

EXEC sp_SQLskillsDIFForFULL 'msdb';
GO

 

Total Extents Changed Extents Percentage Changed
————- ————— ———————-
102           56              54.9

I've tested it with databases with multiple files and up to around 700GB for a single file size. There's the potential for an issue with file sizes of 4TB and beyond (where PFS intervals and GAM intervals map to the same extent every 4TB or so, but I think it'll be ok and the position of the DIFF map in the extent won't change - if someone could test it with a 4+TB file I'd be grateful). It's been tested on SQL Server 2005 and 2008. It will not work on SQL Server 2000 – I'll do a 2000 version sometime soon.

Note that after doing a full backup you will never see Changed Extents equal to zero. It will always be 4 + (number of online data files – 1), and around 20 or so for msdb. This is because the extent containing the file header in each file is always marked as changed, as are three extents in the primary file containing the roots of some critical system tables.

Anyway – here it is. You can download it in a zip file from SQLskillsDIFForFULL.zip (2.65KB). Enjoy!

 

/*============================================================================
   File: SQLskillsDIFForFULL.sql

   Summary: This script creates a system-wide SP SQLskillsDIFForFILL that
   works out what percentage of a database has changed since the
   previous full database backup.

   Date: April 2008

   SQL Server Versions:
         10.0.1300.13 (SS2008 February CTP – CTP-6)
         9.00.3054.00 (SS2005 SP2)
——————————————————————————
   Copyright (C) 2008 Paul S. Randal, SQLskills.com
   All rights reserved.

   For more scripts and sample code, check out 
      http://www.sqlskills.com/

   You may alter this code for your own *non-commercial* purposes. You may
   republish altered code as long as you give due credit.

 

   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
   PARTICULAR PURPOSE.

============================================================================*/

– Create the function in MSDB
–
USE msdb;
GO

 

IF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'SQLskillsConvertToExtents')
   DROP FUNCTION SQLskillsConvertToExtents;
GO

 

– This function cracks the output from a DBCC PAGE dump
– of an allocation bitmap. It takes a string in the form
– "(1:8) – (1:16)" or "(1:8) -" and returns the number
– of extents represented by the string. Both the examples
– above equal 1 extent.
–

 

CREATE FUNCTION SQLskillsConvertToExtents (
   @extents VARCHAR (100))
RETURNS INTEGER
AS
BEGIN
   DECLARE @extentTotal   INT;
   DECLARE @colon         INT;
   DECLARE @firstExtent   INT;
   DECLARE @secondExtent  INT;

 

   SET @extentTotal = 0;
   SET @colon = CHARINDEX (':', @extents);

   – Check for the single extent case
   –
   IF (CHARINDEX (':', @extents, @colon + 1) = 0)
      SET @extentTotal = 1;
   ELSE
      – We're in the multi-extent case
      –
      BEGIN
      SET @firstExtent = CONVERT (INT,
         SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));
      SET @colon = CHARINDEX (':', @extents, @colon + 1);
      SET @secondExtent = CONVERT (INT,
         SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));
      SET @extentTotal = (@secondExtent - @firstExtent) / 8 + 1;
   END

   RETURN @extentTotal;
END;
GO

 

USE master;
GO

 

IF OBJECT_ID ('sp_SQLskillsDIFForFULL') IS NOT NULL
   DROP PROCEDURE sp_SQLskillsDIFForFULL;
GO

 

– This SP cracks all differential bitmap pages for all online
– data files in a database. It creates a sum of changed extents
– and reports it as follows (example small msdb):
– 
– EXEC sp_SQLskillsDIFForFULL 'msdb';
– GO
–
– Total Extents Changed Extents Percentage Changed
– ————- ————— ———————-
– 102           56              54.9
–
– Note that after a full backup you will always see some extents
– marked as changed. The number will be 4 + (number of data files – 1).
– These extents contain the file headers of each file plus the
– roots of some of the critical system tables in file 1.
– The number for msdb may be round 20.
–
CREATE PROCEDURE sp_SQLskillsDIFForFULL (
   @dbName VARCHAR (128))
AS
BEGIN
   SET NOCOUNT ON;

 

   – Create the temp table
   –
   IF EXISTS (SELECT * FROM msdb.sys.objects WHERE NAME = 'SQLskillsDBCCPage')
   DROP TABLE msdb.dbo.SQLskillsDBCCPage;

   CREATE TABLE msdb.dbo.SQLskillsDBCCPage (
      [ParentObject] VARCHAR (100),
      [Object]       VARCHAR (100),
      [Field]        VARCHAR (100),
      [VALUE]        VARCHAR (100));

   DECLARE @fileID         INT;
   DECLARE @fileSizePages  INT;
   DECLARE @extentID       INT;
   DECLARE @pageID         INT;
   DECLARE @DIFFTotal      INT;
   DECLARE @sizeTotal      INT;
   DECLARE @total          INT;
   DECLARE @dbccPageString VARCHAR (200);

   SELECT @DIFFTotal = 0;
   SELECT @sizeTotal = 0;

   – Setup a cursor for all online data files in the database
   –
   DECLARE files CURSOR FOR
      SELECT [file_id], [size] FROM master.sys.master_files
      WHERE [type_desc] = 'ROWS'
      AND [state_desc] = 'ONLINE'
      AND [database_id] = DB_ID (@dbName);

   OPEN files;

   FETCH NEXT FROM files INTO @fileID, @fileSizePages;

   WHILE @@FETCH_STATUS = 0
   BEGIN
      SELECT @extentID = 0;

      – The size returned from master.sys.master_files is in
      – pages – we need to convert to extents
      –
      SELECT @sizeTotal = @sizeTotal + @fileSizePages / 8;

      WHILE (@extentID < @fileSizePages)
      BEGIN
         – There may be an issue with the DIFF map page position
         – on the four extents where PFS pages and GAM pages live
         – (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
         – but I think we'll be ok.
         – PFS pages are every 8088 pages (page 1, 8088, 16176, etc)
         – GAM extents are every 511232 pages
         –
         SELECT @pageID = @extentID + 6;

         – Build the dynamic SQL
         –
         SELECT @dbccPageString = 'DBCC PAGE ('
            + @dbName + ', '
            + CAST (@fileID AS VARCHAR) + ', '
            + CAST (@pageID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';

         – Empty out the temp table and insert into it again
         –
         DELETE FROM msdb.dbo.SQLskillsDBCCPage;
         INSERT INTO msdb.dbo.SQLskillsDBCCPage EXEC (@dbccPageString);

         – Aggregate all the changed extents using the function
         –
         SELECT @total = SUM ([msdb].[dbo].[SQLskillsConvertToExtents] ([Field]))
         FROM msdb.dbo.SQLskillsDBCCPage
            WHERE [VALUE] = '    CHANGED'
            AND [ParentObject] LIKE 'DIFF_MAP%';

         SET @DIFFTotal = @DIFFTotal + @total;

         – Move to the next GAM extent
         SET @extentID = @extentID + 511232;
      END

      FETCH NEXT FROM files INTO @fileID, @fileSizePages;
   END;

   – Clean up
   –
   DROP TABLE msdb.dbo.SQLskillsDBCCPage;
   CLOSE files;
   DEALLOCATE files;

   – Output the results
   –
   SELECT
      @sizeTotal AS [Total Extents],
      @DIFFTotal AS [Changed Extents],
      ROUND (
         (CONVERT (FLOAT, @DIFFTotal) /
         CONVERT (FLOAT, @sizeTotal)) * 100, 2) AS [Percentage Changed];
END;
GO

 

– Mark the SP as a system object
–
EXEC sys.sp_MS_marksystemobject sp_SQLskillsDIFForFULL;
GO

 

– Test to make sure everything was setup correctly
–
EXEC sp_SQLskillsDIFForFULL 'msdb';
GO

df

/*============================================================================
   File: SQLskillsDIFForFULL.sql

   Summary: This script creates a system-wide SP SQLskillsDIFForFILL that
   works out what percentage of a database has changed since the
   previous full database backup.

   Date: April 2008

   SQL Server Versions:
         10.0.1300.13 (SS2008 February CTP – CTP-6)
         9.00.3054.00 (SS2005 SP2)
——————————————————————————
   Copyright (C) 2008 Paul S. Randal, SQLskills.com
   All rights reserved.

   For more scripts and sample code, check out 
      http://www.sqlskills.com/

   You may alter this code for your own *non-commercial* purposes. You may
   republish altered code as long as you give due credit.


   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
   PARTICULAR PURPOSE.

============================================================================*/

--– Create the function in MSDB
--–
USE msdb;
GO


IF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'SQLskillsConvertToExtents')
   DROP FUNCTION SQLskillsConvertToExtents;
GO


--– This function cracks the output from a DBCC PAGE dump
--– of an allocation bitmap. It takes a string in the form
--– "(1:8) – (1:16)" or "(1:8) -" and returns the number
--– of extents represented by the string. Both the examples
--– above equal 1 extent.
--–


CREATE FUNCTION SQLskillsConvertToExtents (
   @extents VARCHAR (100))
RETURNS INTEGER
AS
BEGIN
   DECLARE @extentTotal   INT;
   DECLARE @colon         INT;
   DECLARE @firstExtent   INT;
   DECLARE @secondExtent  INT;


   SET @extentTotal = 0;
   SET @colon = CHARINDEX (':', @extents);

   --– Check for the single extent case
   --–
   IF (CHARINDEX (':', @extents, @colon + 1) = 0)
      SET @extentTotal = 1;
   ELSE
      --– We're in the multi-extent case
      --–
      BEGIN
      SET @firstExtent = CONVERT (INT,
         SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));
      SET @colon = CHARINDEX (':', @extents, @colon + 1);
      SET @secondExtent = CONVERT (INT,
         SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));
      SET @extentTotal = (@secondExtent - @firstExtent) / 8 + 1;
   END

   RETURN @extentTotal;
END;
GO


USE master;
GO


IF OBJECT_ID ('sp_SQLskillsDIFForFULL') IS NOT NULL
   DROP PROCEDURE sp_SQLskillsDIFForFULL;
GO


--– This SP cracks all differential bitmap pages for all online
--– data files in a database. It creates a sum of changed extents
--– and reports it as follows (example small msdb):
--– 
--– EXEC sp_SQLskillsDIFForFULL 'msdb';
--– GO
--–
--– Total Extents Changed Extents Percentage Changed
--– ————- ————— ———————-
--– 102           56              54.9
--–
--– Note that after a full backup you will always see some extents
--– marked as changed. The number will be 4 + (number of data files – 1).
--– These extents contain the file headers of each file plus the
--– roots of some of the critical system tables in file 1.
--– The number for msdb may be round 20.
--–
CREATE PROCEDURE sp_SQLskillsDIFForFULL (
   @dbName VARCHAR (128))
AS
BEGIN
   SET NOCOUNT ON;


   --– Create the temp table
   --–
   IF EXISTS (SELECT * FROM msdb.sys.objects WHERE NAME = 'SQLskillsDBCCPage')
   DROP TABLE msdb.dbo.SQLskillsDBCCPage;

   CREATE TABLE msdb.dbo.SQLskillsDBCCPage (
      [ParentObject] VARCHAR (100),
      [Object]       VARCHAR (100),
      [Field]        VARCHAR (100),
      [VALUE]        VARCHAR (100));

   DECLARE @fileID         INT;
   DECLARE @fileSizePages  INT;
   DECLARE @extentID       INT;
   DECLARE @pageID         INT;
   DECLARE @DIFFTotal      INT;
   DECLARE @sizeTotal      INT;
   DECLARE @total          INT;
   DECLARE @dbccPageString VARCHAR (200);

   SELECT @DIFFTotal = 0;
   SELECT @sizeTotal = 0;

   --– Setup a cursor for all online data files in the database
   --–
   DECLARE files CURSOR FOR
      SELECT [file_id], [size] FROM master.sys.master_files
      WHERE [type_desc] = 'ROWS'
      AND [state_desc] = 'ONLINE'
      AND [database_id] = DB_ID (@dbName);

   OPEN files;

   FETCH NEXT FROM files INTO @fileID, @fileSizePages;

   WHILE @@FETCH_STATUS = 0
   BEGIN
      SELECT @extentID = 0;

      --– The size returned from master.sys.master_files is in
      --– pages – we need to convert to extents
      --–
      SELECT @sizeTotal = @sizeTotal + @fileSizePages / 8;

      WHILE (@extentID < @fileSizePages)
      BEGIN
         --– There may be an issue with the DIFF map page position
         --– on the four extents where PFS pages and GAM pages live
         --– (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
         --– but I think we'll be ok.
         --– PFS pages are every 8088 pages (page 1, 8088, 16176, etc)
         --– GAM extents are every 511232 pages
         --–
         SELECT @pageID = @extentID + 6;

         --– Build the dynamic SQL
         --–
         SELECT @dbccPageString = 'DBCC PAGE ('
            + @dbName + ', '
            + CAST (@fileID AS VARCHAR) + ', '
            + CAST (@pageID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';

         --– Empty out the temp table and insert into it again
         --–
         DELETE FROM msdb.dbo.SQLskillsDBCCPage;
         INSERT INTO msdb.dbo.SQLskillsDBCCPage EXEC (@dbccPageString);

         --– Aggregate all the changed extents using the function
         --–
         SELECT @total = SUM ([msdb].[dbo].[SQLskillsConvertToExtents] ([Field]))
         FROM msdb.dbo.SQLskillsDBCCPage
            WHERE [VALUE] = '    CHANGED'
            AND [ParentObject] LIKE 'DIFF_MAP%';

         SET @DIFFTotal = @DIFFTotal + @total;

         --– Move to the next GAM extent
         SET @extentID = @extentID + 511232;
      END

      FETCH NEXT FROM files INTO @fileID, @fileSizePages;
   END;

   --– Clean up
   --–
   DROP TABLE msdb.dbo.SQLskillsDBCCPage;
   CLOSE files;
   DEALLOCATE files;

   --– Output the results
   --–
   SELECT
      @sizeTotal AS [Total Extents],
      @DIFFTotal AS [Changed Extents],
      ROUND (
         (CONVERT (FLOAT, @DIFFTotal) /
         CONVERT (FLOAT, @sizeTotal)) * 100, 2) AS [Percentage Changed];
END;
GO


--– Mark the SP as a system object
--–
EXEC sys.sp_MS_marksystemobject sp_SQLskillsDIFForFULL;
GO


--– Test to make sure everything was setup correctly
--–
EXEC sp_SQLskillsDIFForFULL 'sss';
GO