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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement