Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes.
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO
You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Can you write the sample script in SQL 2000? Thanks!
Hi there – any chance of amending the above script to show the actual table name where the index resides…..
Thanks
select tbl.[name] TableName,
ind.[name] IndexName,
mn.index_type_desc IndexType,
mn.avg_fragmentation_in_percent [FRAG_%]
from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
where [database_id] = db_id(‘dbSynthetics’)
order by mn.avg_fragmentation_in_percent desc
This modification of the original will return the Parent table name.
USE [your DB]
GO
SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > 30 — min % to return
ORDER BY ps.avg_fragmentation_in_percent desc
GO
I tried teh above script but it give sme this error-
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘(‘.
Do I have to replace the exact Database name etc somewhere in this script.
You can replace DB_ID() with the database ID:
You can do this to find out the ID:
USE yourdatabase
SELECT DB_ID() AS [Database ID];
GO
Can we merge the following into the output
1. Indexed Columns
2. Included Columns
I have tried to defrag the tables in my databases but have discovered that some indexes will not improve better than 85% total fragmentation. How can I overcome this problem? Or is this something that I should not be concerned with?
Try rebuilding the index it will improve it
Pinal Dave, you sir, are, a legend.
Pinal,
Why the script would not return any indexes and their statistics e.g % fragmented? Doe this mean ther are none indexes in this datbase thar are fragmented even a small percent. DO i need to run something else before i run this script.