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 (https://blog.sqlauthority.com)
28 Comments. Leave new
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 need to run it against the master database, other databases doesn’t contain the DB_ID() function select DB_ID(N’MyDataBaseName’) will return the database id.
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.
Once you rebuild a script ti will clear the statistics on the related table – hence the above query will then not return any results. You will need to wait for the table statistics to build up again before getting a meaningful result set.
Hmm… it sure creates a perspective to things…
Warning: On a large table, this command can take a very very long time to run… I have some indexes that are over 1GB in size (2.6M records) and looking at fragmentation on some individual indexes with properties can take more than 30 minutes and require over 3M physical I/O according to activity monitor.
Hi,
I am using the following query to get my index details to reorgnaize or rebuild them
SELECT
OBJECT_NAME(B.OBJECT_ID) as TableName, b.name as Index_Name,
CASE
WHEN ps.avg_fragmentation_in_percent > 30 THEN ‘REBUILD WITH (,PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=OFF,ALLOW_PAGE_LOCKS=OFF,ONLINE=ON,SORT_IN_TEMPDB = ON,MAXDOP=2)’ –FOR ONLINE ADD USE THIS ‘REBUILD WITH(ONLINE = ON)’
ELSE ‘REORGANIZE’
END AS INDEX_OPTION,avg_fragmentation_in_percent as Fragmentation_Size,
ps.Page_count as TotalPage
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() AND b.name IS NOT NULL AND ps.avg_fragmentation_in_percent > 10
ORDER BY TotalPage desc
But its making my machine hang, I am unable to do anything after executing this. Could we do some improvement in this query.
Is there a way to quantify how much query performance can/will suffer once an index becomes heavily fragmented? I have a client with a 365x24x7 system that suffers from locking/blocking during index optimizations, so they are considering reducing the frequency or doing away with index optimizations altogether for specific indexes. I don’t think this is a good idea, so I’d like to be able to quantify the performance impct that would result.
this will loop thru all tables that have a frag% > 40 and also puts the schema in front of the table name.
create table #frag(
tabName varchar(200)
)
insert into #frag
select S.name + ‘.’ + tbl.[name] TableName
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]
inner join sys.schemas S on tbl.schema_id = S.schema_id
where [database_id] = db_id() and mn.avg_fragmentation_in_percent > 40
order by mn.avg_fragmentation_in_percent desc
while exists(select top 1 tabName from #frag)
begin
declare @name as varchar(200), @sql varchar(1000)
select top 1 @name = tabName from #frag
set @sql = ‘ALTER INDEX ALL ON ‘ + @name + ‘ REBUILD’
select @sql
delete from #frag where tabName = @name
exec(@sql)
end
drop table #frag
Hello prumery. I really like the script you have but can you tell me how i’d be able to return the output for all dbs on a given db server? Say if i join against the sys.databases catalog. I tried to tweak the script you had and it somewhat works, but only if i exec against a specific db. I have to pass in the the “use ” syntax and it works fine. But i was hoping to return the same frag info for all dbs on a given server.
Here’s my updated script:
–passing in for a specific db works fine
use
go
–when i pass in to use master, i get a 0 result set
–use master
–go
insert into #fragindex
SELECT b.name,
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
inner join master.sys.databases b ON ps.database_id = b.database_id
WHERE ps.database_id = DB_ID() AND si.name is not null
AND ps.avg_fragmentation_in_percent >= 70
and si.name not like ‘PK%’ –disregards primary key based indexes
and b.database_id > 4
–and b.name in (‘IBC’,’UBS’,’Petro’) –tried to use this to bring back frag info for specific set of dbs; doesnt work for me
ORDER BY ps.avg_fragmentation_in_percent desc
GO
Bhagesh, Apple
There a way to quantify how much query performance will suffer once an index becomes heavily fragmented? I have a client with a 365x24x7 system that suffers from locking/blocking during index optimizations, so they are considering reducing the frequency or doing away with index optimizations altogether for specific indexes. I knew that this is a good idea, so I’d like to be able to quantify the performance impct that would result.
Very good article!
Thanks
Fabio
tnx
Hello all. How can i return the frag output for all dbs on a given db server? Say if i join against the sys.databases catalog.
Here’s my current script:
–passing in for a specific db works fine
use [dbname]
go
–when i pass in to use master, i get a 0 result set
–use master
–go
insert into #fragindex
SELECT b.name,
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
inner join master.sys.databases b ON ps.database_id = b.database_id
WHERE ps.database_id = DB_ID() AND si.name is not null
AND ps.avg_fragmentation_in_percent >= 70
and si.name not like ‘PK%’ –disregards primary key based indexes
and b.database_id > 4
–and b.name in (‘IBC’,’UBS’,’Petro’) –tried to use this to bring back frag info for specific set of dbs; doesnt work for me
ORDER BY ps.avg_fragmentation_in_percent desc
GO