[Tricks and Tips] Using DBCC IND and DBCC PAGE to view a table without affecting DM_DB_INDEX_USAGE_STATS

First off: don't use this in production, or at least understand what you're/I'm doing.  This uses two undocumented DBCC commands.  There IS a replacement for DBCC IND in 2012+, but I needed this for older servers (and besdies, DBCC PAGE would still be unsupported).

Problem: I'm consolidating servers. As part of that, I need to see what tables the database has that are used. I use a script elsewhere on the blog for that, one that uses a DMV called SYS.DM_DB_INDEX_USAGE_STATS. It, unsurprisingly, tells you the last time an index was used - but can also be used to tell when a query last touched a table.  So if it's had inserts for 2 years but no selects, odds are you need to reconsider whether you need it.  (An aside - you might ought to consider rebooting your machines more often than once every 2 years)

However, I don't want to do a SELECT from the table, since that would affect the DMV.  Technically I have a way around that - I use my EPR (again, another post) to save out the stats twice a day.  However, when you go back and look at 3 months of history, there's always the thought "was that me, or some process I don't know about that only runs once a quarter?".  Hence, this.

What we do is use two massively unsupported DBCC commands to get the list of pages allocated to a table, then use DBCC PAGE to show the contents of them.  Once that's done, we hopefully pull the data through a pivot into a viewable table.  By default it assumes there's a clustered index, but that's pretty optional - I did it that way due to an implied speed boost.  (After all, it has to pull back ALL the pages associated with a table.)

Nifty bit: you don't need to use TRACEON (3604) with PAGE if you use WITH TABLERESULTS. Didn't know that!

My "project nickname" for this is Medusa's Mirror, since you're not looking at the table directly.  Technically it should be Perseus' Shield, but I didn't think of it when I created the Gist.  Besides - it's alliterative.

Did some lightweight testing on some tables, though I don't have offrow data.  Use at your own risk.

	DECLARE @page_command VARCHAR(4000), @ind_command VARCHAR(4000), @dbid VARCHAR(5)
	, @pagefid varchar(50), @pagepid varchar(50)
	DECLARE @database_name sysname, @table_name sysname, @has_clustered_index VARCHAR(2)
	SET @database_name = 'mydbname'
	SET @table_name = 'mytablename'
	SET @has_clustered_index = '1'
	--assumes it has one, to make the DBCC IND run faster. If you don't care, set to -1.
	--You could set it to an actual index page, but you have to know the ID
	SELECT @dbid = DB_ID(@database_name)
	IF object_id('tempdb..#temp_ind') is not null
	BEGIN
	DROP TABLE #temp_ind
	END
	CREATE TABLE #temp_ind
	(PageFID BIGINT, PagePID BIGINT, IAMFID BIGINT, IAMPID BIGINT, ObjectID BIGINT, IndexID INT,
	PartitionNumber INT, PartitionID bigint, iam_chain_type VARCHAR(50), PageType SMALLINT,
	IndexLevel SMALLINT, NextPageFID BIGINT, NextPagePID BIGINT, PrevPageFID BIGINT, PrevPagePID BIGINT)
	IF object_id('tempdb..#temp_page') is not null
	BEGIN
	DROP TABLE #temp_page
	END
	CREATE TABLE #temp_page (ParentObject VARCHAR(100), [Object] VARCHAR(100), [field] VARCHAR(255), [value] VARCHAR(100))
	SET @ind_command = 'DBCC IND(' + @dbid + ',' + @table_name + ',' + @has_clustered_index + ')'
	INSERT INTO #temp_ind EXEC(@ind_command)
	SELECT TOP 1 @PageFID = PageFID, @PagePID = PagePID FROM #temp_ind WHERE iamfid IS NOT NULL ORDER BY PagePID DESC
	SET @page_command = 'DBCC PAGE(' + @dbid + ',' + @pagefid + ',' + @pagepid + ',3) WITH TABLERESULTS'
	INSERT INTO #temp_page EXEC (@page_command)
	--Now pivot the data from a page to view some sample records
	DECLARE @SQL as VARCHAR (MAX)
	DECLARE @Columns AS VARCHAR (MAX)
	SELECT @Columns=
	COALESCE(@Columns + ',','') + QUOTENAME(field)
	FROM
	(
	SELECT DISTINCT field,
	SUBSTRING([Object], CHARINDEX('Column ',[Object])+7, CHARINDEX(' Offset ',[Object]) - CHARINDEX('Column ',[Object])-7) AS field_num
	FROM #temp_page WHERE [Object] LIKE '%Column%Offset 0x%'
	) AS B
	ORDER BY B.field_num
	SET @SQL='
	SELECT ParentObject, ' + @Columns + ' FROM
	(
	SELECT ParentObject, field, value FROM
	#temp_page WHERE Object LIKE ''%column%offset%'' ) AS source
	PIVOT
	(max(value) FOR source.field IN (' + @columns + ')
	)AS pvt'
	EXEC (@sql)

view raw look_at_table_indirectly hosted with ❤ by GitHub