This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to check the available allocation units on a Table in SQL Server. When you create a table and stored data, it stored into different types of allocation units like IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA.
Already shared a couple of articles on the importance of allocation units like ROW_OVERFLOW_DATA. When your data are exceeding 8KB pages, it stores into ROW_OVERFLOW_DATA which is also called internal fragmentation.
Using this script, you will get to know different allocations related information including page start number which helps you to do further require analysis on your table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
DECLARE @object SYSNAME = 'table_name' SELECT OBJECT_NAME ([sp].[object_id]) AS [Object Name], [sp].[index_id] AS [Index ID], [sp].[partition_id] AS [Partition ID], [sa].[allocation_unit_id] AS [Alloc Unit ID], [sa].[type_desc] AS [Alloc Unit Type], '(' + CONVERT (VARCHAR (6), CONVERT (INT, SUBSTRING ([sa].[first_page], 6, 1) + SUBSTRING ([sa].[first_page], 5, 1))) + ':' + CONVERT (VARCHAR (20), CONVERT (INT, SUBSTRING ([sa].[first_page], 4, 1) + SUBSTRING ([sa].[first_page], 3, 1) + SUBSTRING ([sa].[first_page], 2, 1) + SUBSTRING ([sa].[first_page], 1, 1))) + ')' AS [First Page], '(' + CONVERT (VARCHAR (6), CONVERT (INT, SUBSTRING ([sa].[root_page], 6, 1) + SUBSTRING ([sa].[root_page], 5, 1))) + ':' + CONVERT (VARCHAR (20), CONVERT (INT, SUBSTRING ([sa].[root_page], 4, 1) + SUBSTRING ([sa].[root_page], 3, 1) + SUBSTRING ([sa].[root_page], 2, 1) + SUBSTRING ([sa].[root_page], 1, 1))) + ')' AS [Root Page], '(' + CONVERT (VARCHAR (6), CONVERT (INT, SUBSTRING ([sa].[first_iam_page], 6, 1) + SUBSTRING ([sa].[first_iam_page], 5, 1))) + ':' + CONVERT (VARCHAR (20), CONVERT (INT, SUBSTRING ([sa].[first_iam_page], 4, 1) + SUBSTRING ([sa].[first_iam_page], 3, 1) + SUBSTRING ([sa].[first_iam_page], 2, 1) + SUBSTRING ([sa].[first_iam_page], 1, 1))) + ')' AS [First IAM Page] FROM sys.system_internals_allocation_units AS [sa], sys.partitions AS [sp] WHERE [sa].[container_id] = [sp].[partition_id] AND [sp].[object_id] = (CASE WHEN (@object IS NULL) THEN [sp].[object_id] ELSE OBJECT_ID (@object) END) |
Reference is taken from sqlskills.com:
