Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index physical stats dmv Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 7:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 405, Visits: 984
how to get obj id, index name, clustered\heaponclustered, filegroup, Percent fragmentation
in a single query
Post #1545414
Posted Wednesday, February 26, 2014 8:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 184, Visits: 396
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.
Post #1545420
Posted Wednesday, February 26, 2014 8:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 405, Visits: 984
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.
Post #1545432
Posted Wednesday, February 26, 2014 8:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 184, Visits: 396
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.
Post #1545446
Posted Wednesday, February 26, 2014 10:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1545482
Posted Friday, February 28, 2014 8:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 405, Visits: 984
Thanks for the query. It was helpful.

Post #1546358
Posted Friday, February 28, 2014 11:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
You are welcome ..

--
SQLBuddy
Post #1546491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse