THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Find table and index name for fragmented indexes

Got this question from a newsgroup today. The answer is pretty simple, just use the dynamic management view sys.dm_db_index_physical_stats. I'm posting this here mostly so I have somewhere to refer to when asked this question...

I prefer to have a helper function to get the index name: 

CREATE FUNCTION dbo.index_name (@object_id int@index_id int)
RETURNS sysname
AS
BEGIN
  RETURN
(SELECT name FROM sys.indexes WHERE object_id @object_id and index_id @index_id)
END;
GO

And then a simple query:

SELECT 
 
OBJECT_NAME(object_idAS tblName
,dbo.index_name(object_idindex_idAS ixName
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent 20
AND index_type_desc IN('CLUSTERED INDEX''NONCLUSTERED INDEX')

Then you just adjust the search clause to your liking. One hint is to exclude nindexes with few pages (the page_count column).

 

 

Published Sunday, November 11, 2007 9:53 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

dia said:

excellent!

March 24, 2009 12:43 PM
 

mike h. said:

Perfect!!

May 13, 2009 11:12 AM
 

Brian Bunin said:

But how do you do this if you're querying the entire server

(NULL instead of DB_ID()

November 17, 2009 7:18 PM
 

TiborKaraszi said:

<<But how do you do this if you're querying the entire server >>

You don't. Sys-indexes is local to the database and a function doesn't accept dynamic SQL. So one would use a stored procedure instead, utilizing dynamic SQL.

November 18, 2009 4:46 AM
 

Alex said:

Doesn't this work on SQL Server 2008 (R2) ?

January 26, 2011 7:04 AM
 

TiborKaraszi said:

Work for me on 2008 R2...

January 26, 2011 7:21 AM
 

Robert Nimström said:

I do it this way

SELECT p.database_id

,d.name

,OBJECT_NAME(p.object_id) AS tblName

,p.index_id

,p.object_id

,i.name AS 'indexName'

,i.type_desc

,p.avg_fragmentation_in_percent AS 'frag%'

,p.page_count AS 'pageCount'

,p.avg_page_space_used_in_percent

,p.record_count

,p.avg_record_size_in_bytes

,d.crdate

,d.cmptlevel

,d.filename

--,p.*

--FROM sys.dm_db_index_physical_stats(DB_ID('Credit'), NULL, NULL, NULL, 'DETAILED')

FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED') AS p

JOIN sys.sysdatabases AS d ON p.database_id = d.dbid

LEFT OUTER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id

WHERE p.avg_fragmentation_in_percent >= 0

AND p.page_count >= 0

ORDER BY p.avg_fragmentation_in_percent DESC, p.page_count DESC, d.filename, p.index_id, p.object_id

February 9, 2011 6:26 AM
 

Robert Nimström said:

Sorry forgot the database name first

USE msdb

GO

SELECT p.database_id

,p.index_id

,p.object_id

,d.name AS 'dbName'

,object_schema_name(p.object_id) AS 'schema'

,OBJECT_NAME(p.object_id) AS tblName

,i.name AS 'indexName'

,i.type_desc

,p.avg_fragmentation_in_percent AS 'frag%'

,p.page_count AS 'pageCount'

,u.user_seeks

,u.user_scans

,u.user_lookups

,p.avg_page_space_used_in_percent

,p.record_count

,p.avg_record_size_in_bytes

,d.crdate

,d.cmptlevel

,d.filename

--,p.*

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS p

JOIN sys.sysdatabases AS d ON p.database_id = d.dbid

LEFT OUTER JOIN sys.indexes AS i ON p.object_id = i.object_id --AND p.index_id = i.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats AS u ON p.object_id = u.object_id

WHERE p.avg_fragmentation_in_percent >= 30

AND p.page_count >= 0

--AND i.name IS NOT NULL

--i.name = 'AK_Document_rowguid'

ORDER BY p.avg_fragmentation_in_percent DESC--, p.page_count DESC, d.filename, p.index_id, p.object_id

February 9, 2011 7:28 AM
 

Adam Mikolaj said:

Robert, I tend to avoid 'detailed' unless I put in a specific index that I am trying to troubleshoot.

the detailed parameter can take forever if your DB is big enough.

Check out http://sqlskills.com/BLOGS/PAUL/post/Inside-sysdm_db_index_physical_stats.aspx

October 10, 2011 6:33 PM
 

Chris Beardsley said:

Robert N., nicely done. Very handy script.

October 23, 2011 10:35 PM
 

Tahir Gul said:

Nice post.

Thanks

December 20, 2011 2:52 AM
 

Jake said:

How do I run Roberts Query specifically against one database?

May 23, 2012 4:48 PM
 

Brent Wright said:

Robert.  Great script.

Jake,

In the from statement, instead of (), include the DB name in single quotes.  Example:

FROM sys.dm_db_index_physical_stats (DB_ID('MyDatabase'), NULL, NULL, NULL, 'SAMPLED')

July 16, 2012 4:11 PM
 

SQLLKR said:

USE DBname

GO

SELECT  

OBJECT_NAME(p.object_id) AS tblName

,si.name as IdxName

,partition_number, index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'DBname'), OBJECT_ID(N'DBname.dbo.tblName'), NULL, NULL, 'LIMITED') as p

inner join sys.indexes si

on si.object_id = p.object_id and si.index_id = p.index_id

WHERE avg_fragmentation_in_percent > 20

AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

January 15, 2013 5:21 AM
 

Ray said:

Great, Thanks

February 19, 2015 3:15 PM
 

Stalin said:

One hint is to exclude nindexes with few pages.. Few pages means..Can you please tell the approx no?

November 5, 2015 8:49 AM
 

TiborKaraszi said:

A number often used is less than 1000 pages. With todays hardware, we could probably bump that up to some 10000 pages, but the important aspect is to not bother with indexes having just a few hundred pages, or even less.

November 5, 2015 1:40 PM
 

Brendan S said:

Thank you very much, I couldn't return the table name with my query.

April 11, 2016 11:08 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement