/* ** File: dbo.GetPartitionIndexDepth.sql ** ** Summary: Create this proc in a database and use to ** get index depth information for partitioned ** tables. ** ** SQL Server Versions: 2005 onwards ** ---------------------------------------------------------------------------- ** Written by Rainer Unwin (c) 2014 ** ** For more scripts check out http://www.rmuniwn.com ** ** You may alter this code for your own non-commercial purposes. ** You may republish altered code as long as you include this copyright and ** give due credit, however you must obtain my prior permission before ** blogging this code. ** ** 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. You use the script at your own risk and should ** always test in a pre-production or test environment before running ** in production. */ CREATE PROCEDURE dbo.GetPartitionIndexDepth ( @database SYSNAME , @schema SYSNAME , @table SYSNAME , @index SYSNAME ) AS BEGIN SET NOCOUNT ON; -- Deal with errors before they happen and bail out IF NOT EXISTS (SELECT 'Database exists' FROM sys.databases WHERE name = @database) BEGIN -- Declare params for error messages DECLARE @user SYSNAME , @date CHAR(20); SELECT @user = SUSER_SNAME() , @date = CAST(GETDATE() AS CHAR(20)); IF (SELECT CHARINDEX(N';', @database)) > 0 BEGIN -- Most likely a SQL injection attempt, raise severe error RAISERROR( 'There was most likely a SQL injection attemp using stored procedure dbo.GetPartitionIndexDepth by user %s at %s, the string passed in was %s' , 16 , 1 , @user , @date , @database ) WITH LOG, NOWAIT; END ELSE BEGIN -- Most likely not a SQL injection attempt, just go informational RAISERROR( 'Error in procedure dbo.GetPartitionIndexDepth database %s not found' , 10 , 1 , @database ) WITH NOWAIT; END; -- Bail out at this point RETURN 99; END; -- Need some temp tables and variables IF OBJECT_ID(N'tempdb..#roots', N'U') IS NOT NULL DROP TABLE #roots; IF OBJECT_ID(N'tempdb..#page', N'U') IS NOT NULL DROP TABLE #page; CREATE TABLE #roots ( seqno SMALLINT IDENTITY(0, 1) NOT NULL PRIMARY KEY CLUSTERED , partno SMALLINT NOT NULL , fileid SMALLINT NOT NULL , pageid INT NOT NULL , depth TINYINT NULL , [rows] BIGINT NULL ); CREATE TABLE #page ( ParentObject VARCHAR(200) , [Object] VARCHAR(200) , Field VARCHAR(200) , VALUE VARCHAR(200) ); DECLARE @loop_sql NVARCHAR(2000) , @loop_params NVARCHAR(200) , @file_id SMALLINT , @page_id INT , @roots_sql NVARCHAR(2000) , @roots_params NVARCHAR(2000) , @max_root SMALLINT , @record SMALLINT , @level TINYINT; -- Set the initial SQL, this is dynamic to pull it from any DB passed in SELECT @roots_sql = N'/* Get root page dynamic sql */ SELECT p.partition_number, pc.[file_id], pc.page_id, p.[rows] FROM ' + @database + N'.sys.schemas s INNER JOIN ' + @database + N'.sys.tables t on s.schema_id = t.schema_id INNER JOIN ' + @database + N'.sys.indexes i on t.object_id = i.object_id INNER JOIN ' + @database + N'.sys.partitions p ON p.[object_id] = i.[object_id] and p.index_id = i.index_id INNER JOIN ' + @database + N'.sys.system_internals_allocation_units au ON au.container_id = p.HoBT_id CROSS APPLY sys.fn_PhysLocCracker(au.root_page) pc WHERE au.[type] = 1 AND s.name = @SchemaName AND t.name = @TableName AND i.name = @IndexName;' , @roots_params = N'@SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME' , @loop_sql = N'/* DBCC PAGE dynamic SQL */ DBCC PAGE(@Database, @FileID, @PageID, 0) WITH TABLERESULTS, NO_INFOMSGS;' , @loop_params = N'@Database SYSNAME, @FileID SMALLINT, @PageID INT'; -- Grab the root pages for all partitions INSERT INTO #roots (partno, fileid, pageid, [rows]) EXEC sp_executesql @roots_sql, @roots_params, @schema, @table, @index; SELECT @max_root = MAX(seqno) , @record = 0 FROM #roots; -- Now we loop round to get the details we want WHILE @record <= @max_root BEGIN SELECT @file_id = fileid , @page_id = pageid FROM #roots WHERE seqno = @record; -- We use DBCC PAGE because it's very efficient, and don't look at any partitions that have no records IF (@file_id > 0 AND @page_id > 0) BEGIN INSERT INTO #page EXEC sp_executesql @loop_sql, @loop_params, @database, @file_id, @page_id; END; SELECT @level = VALUE + 1 FROM #page WHERE Field = 'm_level' UPDATE #roots SET depth = @level WHERE seqno = @record; -- Need to re-use this table and set some values for the next loop iteration TRUNCATE TABLE #page; SELECT @record += 1 , @file_id = 0 -- Better safe than sorry on these , @page_id = 0; END; -- End of loop -- Return the results and clean up SELECT partno , depth , [rows] FROM #roots order by seqno; IF OBJECT_ID(N'tempdb..#roots', N'U') IS NOT NULL DROP TABLE #roots; IF OBJECT_ID(N'tempdb..#page', N'U') IS NOT NULL DROP TABLE #page; END; GO