Issue: There is a table with an index on several columns. The table is Schema bound and makes up part of an indexed view. User getting the below error
Drop failed for Index ‘UNIQUE_RECORD’.
Workaround: ALTER INDEX is the best option for this case. Modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index. We can disable it .DISABLE Marks the index as disabled and unavailable for use by the SQL Server 2005 Database Engine. Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data. Disabling a clustered index prevents user access to the underlying table data. To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING. For more information, see Disabling Indexes.
Index Fragmentation Report in SQL Server 2005 and 2008 by Arshad ali/William
select object_name(IPS.[object_id]) as [TableName]
,SI.index_id as [SI.index_id]
,SI.name AS [IndexName]
from sys.dm_db_index_physical_stats(db_id(), null, null, null, ‘detailed’) as IPS
inner join sys.indexes as SI with (nolock) on IPS.[object_id] = SI.[object_id] and IPS.index_id = SI.index_id
inner join sys.tables as ST with (nolock) on IPS.[object_id] = ST.[object_id]
where ST.is_ms_shipped = 0
and IPS.avg_fragmentation_in_percent>=10 — allow limited fragmentation
and IPS.page_count>25 — ignore small tables
and IPS.index_type_desc<>’heap’ — ignore heaps
order by IPS.avg_fragmentation_in_percent desc
|avg_fragmentation_in_percent||It indicates the amount of external fragmentation you have for the given objects.
The lower the number the better – as this number approaches 100% the more pages you have in the given index that are not properly ordered.
For heaps, this value is actually the percentage of extent fragmentation and not external fragmentation.
|avg_page_space_used_in_percent||It indicates how dense the pages in your index are, i.e. on average how full each page in the index is (internal fragmentation).
The higher the number the better speaking in terms of fragmentation and read-performance. To achieve optimal disk space use, this value should be close to 100% for an index that will not have many random inserts. However, an index that has many random inserts and has very full pages will have an increased number of page splits. This causes more fragmentation. Therefore, in order to reduce page splits, the value should be less than 100 percent.
|fragment_count||A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. So the less fragments the more data is stored consecutively.|
|avg_fragment_size_in_pages||Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance.|