I use two script. The firs one is for general purpose and run faster, the second one is good for partitioned table and get many details about partitioned index and table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,ips.partition_number ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_count ,ips.record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) -- where OBJECT_NAME(ips.OBJECT_ID) ='table_name' ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC |
Detailed query for partitioned 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 52 53 54 55 56 57 58 59 60 61 |
/* Script : Index Fragmentation Status (includes Partitioned Tables/Indexes) Version : 1.0 (April 2010) Author : Richard Doering Web : http://sqlsolace.blogspot.com */ - Hide quoted text - SELECT SCHEMA_NAME(o.schema_id) AS SchemaName ,OBJECT_NAME(o.object_id) AS TableName ,i.name AS IndexName ,i.type_desc AS IndexType ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned ,COALESCE(fg.name ,fgp.name) AS FileGroupName ,p.partition_number AS PartitionNumber ,p.rows AS PartitionRows ,dmv.Avg_Fragmentation_In_Percent ,dmv.Fragment_Count ,dmv.Avg_Fragment_Size_In_Pages ,dmv.Page_Count ,prv_left.value AS PartitionLowerBoundaryValue ,prv_right.value AS PartitionUpperBoundaryValue ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange ,pf.name AS PartitionFunction ,ds.name AS PartitionScheme FROM sys.partitions AS p WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.objects AS o WITH (NOLOCK) ON o.object_id = i.object_id INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv ON dmv.OBJECT_ID = i.object_id AND dmv.index_id = i.index_id AND dmv.partition_number = p.partition_number LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = i.data_space_id LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK) ON ps.data_space_id = ds.data_space_id LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK) ON pf.function_id = ps.function_id LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK) ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number LEFT JOIN sys.filegroups AS fg WITH (NOLOCK) ON fg.data_space_id = i.data_space_id LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK) ON fgp.data_space_id = dds.data_space_id LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK) ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK) ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0 ORDER BY SchemaName ,TableName ,IndexName ,PartitionNumber |