none
How to know the record count in each data page? RRS feed

  • Question

  • Hi,

     

    I can list the data pages allocated to a table via DBCC IND. But the result does not including the number of records in each page. It is possible to use DBCC PAGE on each data page individually, but it will be time-consuming if there are thousands of pages. Is there are convenient way to obtain the record count  of each data page?

     

    Thanks

    Thursday, February 11, 2016 3:42 AM

Answers

  • The sys.dm_os_buffer_descriptors DMV has "row_count" at the data page level, but that only works for data pages that have been read into the Buffer Pool (i.e. memory). However, if you are on SQL Server 2012 or newer, then you can use the following query to automate the calling of DBCC PAGE. It was not stated how you planned on using this data so just adapt the query as appropriate (meaning: turn it into a stored procedure -- the top 3 variables would be the input params -- or insert it into an existing query so that you can JOIN to the info).

    PLEASE NOTE that the sys.dm_db_database_page_allocations DMV and DBCC PAGE are both undocumented.

    SET NOCOUNT ON;
    
    DECLARE	@DatabaseName sysname = N'{db_name}',
    		@SchemaAndTableName NVARCHAR(260) = N'{schema_name}.{table_name}',
    		@FileID INT = 1;
    
    DECLARE	@PageID INT,
    		@Query NVARCHAR(1000),
    		@ObjectID INT,
    		@IndexID INT;
    
    DECLARE @RowsPerDataPage TABLE (PageID INT NOT NULL PRIMARY KEY, NumRows INT NOT NULL);
    DECLARE @DbccPage TABLE
    (
    	[ParentObject]	NVARCHAR(255),
    	[Object]		NVARCHAR(255),
    	[Field]		NVARCHAR(255),
    	[VALUE]		NVARCHAR(255)
    );
    
    SET @ObjectID = OBJECT_ID(N'[' + @DatabaseName + N'].' + @SchemaAndTableName);
    
    SET @Query = CONCAT(N'SELECT @IndexID_tmp = si.index_id FROM [',
    				@DatabaseName,
    				N'].sys.indexes si WHERE si.[object_id] = ',
    				@ObjectID,
    				N' AND si.index_id < 2;');
    -- PRINT @Query; -- DEBUG
    EXEC sp_executesql
    	@Query,
    	N'@IndexID_tmp INT OUTPUT',
    	@IndexID_tmp = @IndexID OUTPUT;
    
    -- SELECT @ObjectID AS [ObjectID], @IndexID AS [IndexID]; -- DEBUG
    
    -- If using SQL Server 2005, 2008, or 2008 R2: replace 
    -- sys.dm_db_database_page_allocations with EXEC of:
    -- DBCC IND(db_name, table_name, 1);
    
    DECLARE page_ids CURSOR STATIC FORWARD_ONLY READ_ONLY LOCAL
    FOR 
    	SELECT alloc.allocated_page_page_id
    	FROM   sys.dm_db_database_page_allocations(
    			DB_ID(@DatabaseName),
    			@ObjectID,
    			@IndexID,
    			NULL, -- PartitionID
    			N'DETAILED'
    		) alloc
    	WHERE alloc.[page_type] = 1; -- DATA_PAGE
    
    OPEN page_ids
    
    FETCH	NEXT
    FROM		page_ids
    INTO		@PageID;
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	SET @Query = CONCAT(N'DBCC PAGE([',
    					@DatabaseName,
    					N'], ',
    					@FileID,
    					N', ',
    					@PageID,
    					N', 0) WITH TABLERESULTS, NO_INFOMSGS;');
    
    	INSERT INTO @DbccPage ([ParentObject], [Object], [Field], [VALUE])
    		EXEC(@Query);
    
    	INSERT INTO @RowsPerDataPage ([PageID], [NumRows])
    		SELECT	@PageID AS [PageID], dp.[VALUE] AS [NumRows]
    		FROM		@DbccPage dp
    		WHERE	dp.[ParentObject] = N'PAGE HEADER:'
    		AND		dp.[Field] = N'm_slotCnt';
    
    	DELETE FROM @DbccPage;
    
    	FETCH	NEXT
    	FROM		page_ids
    	INTO		@PageID;
    END;
    
    CLOSE page_ids;
    DEALLOCATE page_ids;
    
    SELECT * FROM @RowsPerDataPage;

    For SQL Server versions 2005, 2008, and 2008 R2 (which do not have the sys.dm_db_database_page_allocations DMV or the CONCAT built-in function), use the following code.

    PLEASE NOTE that DBCC IND and DBCC PAGE are both undocumented.

    SET NOCOUNT ON;
    
    DECLARE	@DatabaseName sysname,
    		@SchemaAndTableName NVARCHAR(260),
    		@FileID INT;
    
    SELECT	@DatabaseName = N'db_name',
    		@SchemaAndTableName = N'schema_name.table_name',
    		@FileID = 1;
    
    DECLARE	@PageID INT,
    		@Query NVARCHAR(1000),
    		@ObjectID INT,
    		@IndexID INT;
    
    DECLARE @RowsPerDataPage TABLE (PageID INT NOT NULL PRIMARY KEY,
    						NumRows INT NOT NULL);
    DECLARE @DbccPage TABLE
    (
    	[ParentObject]	NVARCHAR(255),
    	[Object]		NVARCHAR(255),
    	[Field]		NVARCHAR(255),
    	[VALUE]		NVARCHAR(255)
    );
    DECLARE @DbccInd TABLE
    (
    	[PageFID]			SMALLINT,
    	[PagePID]			INT,
    	[IAMFID]			SMALLINT,
    	[IAMPID]			INT,
    	[ObjectID]		INT,
    	[IndexID]			INT,
    	[PartitionNumber]	INT,
    	[PartitionID]		BIGINT,
    	[iam_chain_type]	NVARCHAR(20),
    	[PageType]		SMALLINT,
    	[IndexLevel]		SMALLINT,
    	[NextPageFID]		SMALLINT,
    	[NextPagePID]		INT,
    	[PrevPageFID]		SMALLINT,
    	[PrevPagePID]		INT
    );
    
    SET @ObjectID = OBJECT_ID(N'[' + @DatabaseName + N'].' + @SchemaAndTableName);
    
    SET @Query =	N'SELECT @IndexID_tmp = si.index_id FROM [' +
    			@DatabaseName +
    			N'].sys.indexes si WHERE si.[object_id] = ' +
    			CONVERT(NVARCHAR(20), @ObjectID) +
    			N' AND si.index_id < 2;';
    -- PRINT @Query; -- DEBUG
    
    EXEC sp_executesql
    	@Query,
    	N'@IndexID_tmp INT OUTPUT',
    	@IndexID_tmp = @IndexID OUTPUT;
    
    -- SELECT @ObjectID AS [ObjectID], @IndexID AS [IndexID]; -- DEBUG
    
    SET @Query =	N'DBCC IND([' +
    			@DatabaseName + 
    			N'], ''' +
    			@SchemaAndTableName +
    			N''', ' +
    			CONVERT(NVARCHAR(10), @IndexID) +
    			N') WITH NO_INFOMSGS;';
    -- PRINT @Query; -- DEBUG
    
    INSERT INTO @DbccInd ([PageFID], [PagePID], [IAMFID], [IAMPID],
    		[ObjectID], [IndexID], [PartitionNumber], [PartitionID],
    		[iam_chain_type], [PageType], [IndexLevel],
    		[NextPageFID], [NextPagePID], [PrevPageFID], [PrevPagePID])
    	EXEC(@Query);
    
    -- SELECT * FROM @DbccInd; -- DEBUG
    
    DECLARE page_ids CURSOR FAST_FORWARD LOCAL
    FOR
    	SELECT	ind.[PagePID] AS [PageID]
    	FROM		@DbccInd ind
    	WHERE	ind.[PageType] = 1; -- DATA_PAGE
    
    OPEN page_ids
    
    FETCH	NEXT
    FROM		page_ids
    INTO		@PageID;
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	SET @Query =	N'DBCC PAGE([' +
    				@DatabaseName +
    				N'], ' +
    				CONVERT(NVARCHAR(20), @FileID) +
    				N', ' +
    				CONVERT(NVARCHAR(20), @PageID) +
    				N', 0) WITH TABLERESULTS, NO_INFOMSGS;';
    
    	INSERT INTO @DbccPage ([ParentObject], [Object], [Field], [VALUE])
    		EXEC(@Query);
    
    	INSERT INTO @RowsPerDataPage ([PageID], [NumRows])
    		SELECT	@PageID AS [PageID], dp.[VALUE] AS [NumRows]
    		FROM		@DbccPage dp
    		WHERE	dp.[ParentObject] = N'PAGE HEADER:'
    		AND		dp.[Field] = N'm_slotCnt';
    
    	DELETE FROM @DbccPage;
    
    	FETCH	NEXT
    	FROM		page_ids
    	INTO		@PageID;
    END;
    
    CLOSE page_ids;
    DEALLOCATE page_ids;
    
    
    SELECT * FROM @RowsPerDataPage;




    • Proposed as answer by André Renato Furtado Thursday, February 11, 2016 9:49 AM
    • Edited by Solomon Rutzky Thursday, February 11, 2016 6:05 PM
    • Marked as answer by chcw Friday, February 12, 2016 1:46 AM
    Thursday, February 11, 2016 7:19 AM

All replies

  • The sys.dm_os_buffer_descriptors DMV has "row_count" at the data page level, but that only works for data pages that have been read into the Buffer Pool (i.e. memory). However, if you are on SQL Server 2012 or newer, then you can use the following query to automate the calling of DBCC PAGE. It was not stated how you planned on using this data so just adapt the query as appropriate (meaning: turn it into a stored procedure -- the top 3 variables would be the input params -- or insert it into an existing query so that you can JOIN to the info).

    PLEASE NOTE that the sys.dm_db_database_page_allocations DMV and DBCC PAGE are both undocumented.

    SET NOCOUNT ON;
    
    DECLARE	@DatabaseName sysname = N'{db_name}',
    		@SchemaAndTableName NVARCHAR(260) = N'{schema_name}.{table_name}',
    		@FileID INT = 1;
    
    DECLARE	@PageID INT,
    		@Query NVARCHAR(1000),
    		@ObjectID INT,
    		@IndexID INT;
    
    DECLARE @RowsPerDataPage TABLE (PageID INT NOT NULL PRIMARY KEY, NumRows INT NOT NULL);
    DECLARE @DbccPage TABLE
    (
    	[ParentObject]	NVARCHAR(255),
    	[Object]		NVARCHAR(255),
    	[Field]		NVARCHAR(255),
    	[VALUE]		NVARCHAR(255)
    );
    
    SET @ObjectID = OBJECT_ID(N'[' + @DatabaseName + N'].' + @SchemaAndTableName);
    
    SET @Query = CONCAT(N'SELECT @IndexID_tmp = si.index_id FROM [',
    				@DatabaseName,
    				N'].sys.indexes si WHERE si.[object_id] = ',
    				@ObjectID,
    				N' AND si.index_id < 2;');
    -- PRINT @Query; -- DEBUG
    EXEC sp_executesql
    	@Query,
    	N'@IndexID_tmp INT OUTPUT',
    	@IndexID_tmp = @IndexID OUTPUT;
    
    -- SELECT @ObjectID AS [ObjectID], @IndexID AS [IndexID]; -- DEBUG
    
    -- If using SQL Server 2005, 2008, or 2008 R2: replace 
    -- sys.dm_db_database_page_allocations with EXEC of:
    -- DBCC IND(db_name, table_name, 1);
    
    DECLARE page_ids CURSOR STATIC FORWARD_ONLY READ_ONLY LOCAL
    FOR 
    	SELECT alloc.allocated_page_page_id
    	FROM   sys.dm_db_database_page_allocations(
    			DB_ID(@DatabaseName),
    			@ObjectID,
    			@IndexID,
    			NULL, -- PartitionID
    			N'DETAILED'
    		) alloc
    	WHERE alloc.[page_type] = 1; -- DATA_PAGE
    
    OPEN page_ids
    
    FETCH	NEXT
    FROM		page_ids
    INTO		@PageID;
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	SET @Query = CONCAT(N'DBCC PAGE([',
    					@DatabaseName,
    					N'], ',
    					@FileID,
    					N', ',
    					@PageID,
    					N', 0) WITH TABLERESULTS, NO_INFOMSGS;');
    
    	INSERT INTO @DbccPage ([ParentObject], [Object], [Field], [VALUE])
    		EXEC(@Query);
    
    	INSERT INTO @RowsPerDataPage ([PageID], [NumRows])
    		SELECT	@PageID AS [PageID], dp.[VALUE] AS [NumRows]
    		FROM		@DbccPage dp
    		WHERE	dp.[ParentObject] = N'PAGE HEADER:'
    		AND		dp.[Field] = N'm_slotCnt';
    
    	DELETE FROM @DbccPage;
    
    	FETCH	NEXT
    	FROM		page_ids
    	INTO		@PageID;
    END;
    
    CLOSE page_ids;
    DEALLOCATE page_ids;
    
    SELECT * FROM @RowsPerDataPage;

    For SQL Server versions 2005, 2008, and 2008 R2 (which do not have the sys.dm_db_database_page_allocations DMV or the CONCAT built-in function), use the following code.

    PLEASE NOTE that DBCC IND and DBCC PAGE are both undocumented.

    SET NOCOUNT ON;
    
    DECLARE	@DatabaseName sysname,
    		@SchemaAndTableName NVARCHAR(260),
    		@FileID INT;
    
    SELECT	@DatabaseName = N'db_name',
    		@SchemaAndTableName = N'schema_name.table_name',
    		@FileID = 1;
    
    DECLARE	@PageID INT,
    		@Query NVARCHAR(1000),
    		@ObjectID INT,
    		@IndexID INT;
    
    DECLARE @RowsPerDataPage TABLE (PageID INT NOT NULL PRIMARY KEY,
    						NumRows INT NOT NULL);
    DECLARE @DbccPage TABLE
    (
    	[ParentObject]	NVARCHAR(255),
    	[Object]		NVARCHAR(255),
    	[Field]		NVARCHAR(255),
    	[VALUE]		NVARCHAR(255)
    );
    DECLARE @DbccInd TABLE
    (
    	[PageFID]			SMALLINT,
    	[PagePID]			INT,
    	[IAMFID]			SMALLINT,
    	[IAMPID]			INT,
    	[ObjectID]		INT,
    	[IndexID]			INT,
    	[PartitionNumber]	INT,
    	[PartitionID]		BIGINT,
    	[iam_chain_type]	NVARCHAR(20),
    	[PageType]		SMALLINT,
    	[IndexLevel]		SMALLINT,
    	[NextPageFID]		SMALLINT,
    	[NextPagePID]		INT,
    	[PrevPageFID]		SMALLINT,
    	[PrevPagePID]		INT
    );
    
    SET @ObjectID = OBJECT_ID(N'[' + @DatabaseName + N'].' + @SchemaAndTableName);
    
    SET @Query =	N'SELECT @IndexID_tmp = si.index_id FROM [' +
    			@DatabaseName +
    			N'].sys.indexes si WHERE si.[object_id] = ' +
    			CONVERT(NVARCHAR(20), @ObjectID) +
    			N' AND si.index_id < 2;';
    -- PRINT @Query; -- DEBUG
    
    EXEC sp_executesql
    	@Query,
    	N'@IndexID_tmp INT OUTPUT',
    	@IndexID_tmp = @IndexID OUTPUT;
    
    -- SELECT @ObjectID AS [ObjectID], @IndexID AS [IndexID]; -- DEBUG
    
    SET @Query =	N'DBCC IND([' +
    			@DatabaseName + 
    			N'], ''' +
    			@SchemaAndTableName +
    			N''', ' +
    			CONVERT(NVARCHAR(10), @IndexID) +
    			N') WITH NO_INFOMSGS;';
    -- PRINT @Query; -- DEBUG
    
    INSERT INTO @DbccInd ([PageFID], [PagePID], [IAMFID], [IAMPID],
    		[ObjectID], [IndexID], [PartitionNumber], [PartitionID],
    		[iam_chain_type], [PageType], [IndexLevel],
    		[NextPageFID], [NextPagePID], [PrevPageFID], [PrevPagePID])
    	EXEC(@Query);
    
    -- SELECT * FROM @DbccInd; -- DEBUG
    
    DECLARE page_ids CURSOR FAST_FORWARD LOCAL
    FOR
    	SELECT	ind.[PagePID] AS [PageID]
    	FROM		@DbccInd ind
    	WHERE	ind.[PageType] = 1; -- DATA_PAGE
    
    OPEN page_ids
    
    FETCH	NEXT
    FROM		page_ids
    INTO		@PageID;
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	SET @Query =	N'DBCC PAGE([' +
    				@DatabaseName +
    				N'], ' +
    				CONVERT(NVARCHAR(20), @FileID) +
    				N', ' +
    				CONVERT(NVARCHAR(20), @PageID) +
    				N', 0) WITH TABLERESULTS, NO_INFOMSGS;';
    
    	INSERT INTO @DbccPage ([ParentObject], [Object], [Field], [VALUE])
    		EXEC(@Query);
    
    	INSERT INTO @RowsPerDataPage ([PageID], [NumRows])
    		SELECT	@PageID AS [PageID], dp.[VALUE] AS [NumRows]
    		FROM		@DbccPage dp
    		WHERE	dp.[ParentObject] = N'PAGE HEADER:'
    		AND		dp.[Field] = N'm_slotCnt';
    
    	DELETE FROM @DbccPage;
    
    	FETCH	NEXT
    	FROM		page_ids
    	INTO		@PageID;
    END;
    
    CLOSE page_ids;
    DEALLOCATE page_ids;
    
    
    SELECT * FROM @RowsPerDataPage;




    • Proposed as answer by André Renato Furtado Thursday, February 11, 2016 9:49 AM
    • Edited by Solomon Rutzky Thursday, February 11, 2016 6:05 PM
    • Marked as answer by chcw Friday, February 12, 2016 1:46 AM
    Thursday, February 11, 2016 7:19 AM
  • Hi,

    if you don't need 100% accurate information but only an avg this query might help:

    SELECT	P.rows,
    		AU.data_pages,
    		P.rows / AU.data_pages AS AVG_records_per_page
    FROM	sys.partitions AS P INNER JOIN sys.allocation_units AS AU
    		ON
    		(
    			AU.container_id = 
    			CASE WHEN AU.type IN (1, 3)
    				 THEN P.hobt_id
    				 ELSE P.partition_id
    			END
    		)
    WHERE	P.object_id = OBJECT_ID(N'YourTable', N'U');
    


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Thursday, February 11, 2016 7:51 AM
  • Hi,

    Thank you very much.

    I am using SQL Server 2005 and it seems that the script does not compatible with it. I try to modify some of there are still errors in the following line:

    DB_ID(@DatabaseName),

    I cannot figure out how to fix the problem.

    Thank you.

    Thursday, February 11, 2016 9:32 AM
  • Solomon,

    And you must add that DBCC PAGE is undocumented. That would complete your answer


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Thursday, February 11, 2016 9:44 AM
    Moderator
  • Do you mean row count or record count. If I am correct you should borther about row count because that is how data will be stored in pages. And if that is true and you have 2005 version This link can help


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Thursday, February 11, 2016 9:51 AM
    Moderator
  • Sorry about not mentioning that. I have updated the query, added another one, and indicated all 3 items that are undocumented. Take care, Solomon..
    Thursday, February 11, 2016 5:55 PM
  • Hi,

    That works amazingly. Thank you very much. I just wonder if there are other ways that can express my thanks other than saying "Thank you"?

    Friday, February 12, 2016 1:46 AM
  • Hi,

    That works amazingly. Thank you very much. I just wonder if there are other ways that can express my thanks other than saying "Thank you"?


    Just help others when they need them like we do here :).

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Friday, February 12, 2016 5:51 AM
    Moderator