Index Fragmenaton linked wth Table
-
Hi All,
I normally use sys.dm_db_index_physical_stats to figure out Fragmentation.
Now i want help from you guys here, i want the following information...
I want to know which index exist in which table ??
how can I join Index with its table !!!
i know this can be achieved with sys.tables and sys.indexes but i am confuse with objectids...
any script with some comments will be highly appreciated...
-
For a start have a look at the below query
select o.name TableName,i.name IndexName
from sys.sysobjects o inner join sys.sysindexes i
on i.id=o.id where o.xtype='U' and i.FirstIAM is not null
and i.name is not null
order by o.name
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server -
I wrote this some time back to return the fragmentation levels across the entire database where fragmentation exists for tables that contain more than 100 pages.
You can amend it to return the values for a specific table if you wish, you may also wish to change the type of check that it performs as the current setup is very intensive and was designed to be run out of hours.
SELECT
st.name
,si.name
,ips.partition_number
,ips.index_type_desc
,ips.alloc_unit_type_desc
,ips.index_depth
,ips.index_level
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,ips.avg_fragment_size_in_pages
,ips.page_count
,ips.avg_page_space_used_in_percent
,ips.record_count
,ips.ghost_record_count
,ips.version_ghost_record_count
,ips.min_record_size_in_bytes
,ips.max_record_size_in_bytes
,ips.avg_record_size_in_bytes
,ips.forwarded_record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL , 'DETAILED') IPS
LEFT OUTER JOIN Sys.Tables stonst.object_ID = ips.object_ID
LEFT OUTER JOIN Sys.Indexes sionsi.index_ID = ips.Index_ID
AND si.Object_ID = ips.Object_ID
WHERE ips.Fragment_count <> 0
AND Page_Count > 100
ORDER BY st.name, ips.Index_ID;
Hope this helps,
Rich[p]
[/p]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply