February 26, 2014 at 7:59 am
how to get obj id, index name, clustered\heaponclustered, filegroup, Percent fragmentation
in a single query
February 26, 2014 at 8:05 am
Here you go, you can modify this to get more data.
SELECT DISTINCT
OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema]
,T.[name] AS [table_name]
,I.[name] AS [index_name]
--,AC.[name] AS [column_name]
,I.[type_desc]
--,I.[is_unique]
--,I.[data_space_id]
--,I.[ignore_dup_key]
--,I.[is_primary_key]
--,I.[is_unique_constraint]
--,I.[fill_factor]
--,I.[is_padded]
--,I.[is_disabled]
--,I.[is_hypothetical]
--,I.[allow_row_locks]
--,I.[allow_page_locks]
--,IC.[is_descending_key]
--,IC.[is_included_column]
,PS.AVG_FRAGMENTATION_IN_PERCENT
FROM
sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
INNER JOIN SYS.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(''), NULL, NULL, 'LIMITED') PS ON PS.[OBJECT_ID]=T.[OBJECT_ID] AND I.[INDEX_ID]=PS.[INDEX_ID]
WHERE
T.[is_ms_shipped] = 0
--AND I.[type_desc] <> 'HEAP'
ORDER BY
T.[name]--, I.[index_id], IC.[key_ordinal]
I wrote this so long ago, there's probably a better way to do it but this works fine. You might want to create the temp_index table somewhere else though.
February 26, 2014 at 8:25 am
josh
percent fragmentation is got from dmv
other columns are got from sysindexes, filegroups
Only To get percent detail i need to go to physical stats dmv. I need result which has objject name, index name, index id, filegroup percent fragmentation.
Your query gives db, table, percent fragmentation - If i am not wrong, index physical stats dmv gives the same information.
February 26, 2014 at 8:54 am
My bad, I think I understand what you're looking for now. Does the above query work? I edited it. I doesn't have the filegroup info but you can add that in if you'd like. You also have to make sure to use the correct database first, if you run this in master, it won't work.
February 26, 2014 at 10:05 am
Check this one by Richard Doering ..
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
--
SQLBuddy
February 28, 2014 at 8:10 am
Thanks for the query. It was helpful.
February 28, 2014 at 11:09 am
You are welcome .. 🙂
--
SQLBuddy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply