-- http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
-- Appendix B: Data Volume in Each Allocation Unit Type
-- A table or a partition can have three allocation units - IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA. Usually, most of the data in the table is stored in the IN_ROW_DATA allocation unit. Depending upon the row size and the table options, some data can be stored outside the row in ROW_OVERFLOW_DATA or LOB_DATA allocation units. Use the following script to determine how much data is stored in each of the three allocation units.
SELECT s.name AS SchemaName,
o.name AS TableName,
i.index_id,
i.name AS Index_Name,
ps.in_row_used_page_count AS IN_ROW_DATA,
ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA,
ps.lob_used_page_count AS LOB_DATA
FROM sys.tables o
INNER JOIN sys.schemas s (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats ps ON ps.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.index_id = i.index_id
AND o.OBJECT_ID = i.OBJECT_ID
WHERE o.is_ms_shipped = 0
GO
-- Appendix C: Free Space in Database Files
SELECT a.FILE_ID,
LOGICAL_NAME = a.name,
PHYSICAL_FILENAME = a.physical_name,
FILEGROUP_NAME = b.name,
FILE_SIZE_MB = CONVERT(DECIMAL(12, 2), ROUND(a.SIZE / 128.000, 2)),
SPACE_USED_MB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name, 'SpaceUsed') / 128.000, 2)),
FREE_SPACE_MB = CONVERT(DECIMAL(12, 2), ROUND((a.SIZE - FILEPROPERTY(a.name, 'SpaceUsed')) / 128.000, 2))
FROM sys.database_files a
LEFT OUTER JOIN sys.data_spaces b ON a.data_space_id = b.data_space_id
GO
-- Appendix D: On a Page-Compressed Index, Verifying That Nonleaf Pages are Row-Compressed
-- Use the following query on a page-compressed index (clustered or nonclustered) to verify that the leaf-level pages are page-compressed; but the nonleaf pages are row-compressed, not page-compressed.
DECLARE @ObjectName SYSNAME = 'tAuditTax.PropertyLot'
SELECT s.name AS SchemaName,
o.name AS TableName,
ips.index_id,
ips.index_type_desc,
--p.partition_number,
p.data_compression_desc,
ips.index_level,
SUM(ips.page_count) AS page_count,
SUM(ips.compressed_page_count) AS compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@ObjectName), NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o ON o.OBJECT_ID = ips.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
AND ips.index_id = i.index_id
INNER JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID
GROUP BY s.name,
o.name,
ips.index_id,
ips.index_type_desc,
--p.partition_number,
p.data_compression_desc,
ips.index_level
ORDER BY s.name,
o.name,
ips.index_id,
ips.index_type_desc,
ips.index_level
GO
--Appendix E: BULK INSERT into a Heap
--The following query can be used to determine how many pages in a table, index, or partition are page-compressed.
DECLARE @ObjectName SYSNAME = 'tAuditTax.PropertyLot'
SELECT s.name AS SchemaName,
o.name AS TableName,
ips.index_id,
ips.index_type_desc,
--p.partition_number,
p.data_compression_desc,
SUM(ips.page_count) AS page_count,
SUM(ips.compressed_page_count) AS compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@ObjectName), NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o (NOLOCK) ON ips.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
AND ips.index_id = i.index_id
INNER JOIN sys.partitions p (NOLOCK) ON p.OBJECT_ID = o.OBJECT_ID
GROUP BY s.name,
o.name,
ips.index_id,
ips.index_type_desc,
--p.partition_number,
p.data_compression_desc