How to find out index fragmentation level for large db

Question

hi folks: 

 I tried the following query on small db and all got results within a few seconds. however, this DB is giving me trouble by showing me the PAGEIOLATCH_SH wait type. DB itself has over 300G on SQL 2012 Ent.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, indexstats.page_count,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 10 and indexstats.page_count >1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Is there any other way to find out index fragmentation info for relatively large db. 

Thanks

 HS

---

--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Answer 1

SELECT db_name(database_id) AS DatabaseName,
OBJECT_NAME(i.object_id) AS TableName 
,
i.name AS TableIndexName 
,
phystat.avg_fragmentation_in_percent

FROM
sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i 
ON i.object_id = phystat.object_id 
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40 
and page_count>=1000

---for specific table

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
( 
  DB_ID('testdb'),
  OBJECT_ID('dbo.T1'),
  1,
  NULL,
  NULL
);

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

PAGEIOLATCH_SH generally indicates a slower drive subsystem, but try alternative scripts given in the following links. They appear to use query the same DMV that you provided in your script, but try anyway and see if you get results quicker. It could take some time for a VLDB.

https://logicalread.com/fix-sql-server-index-fragmentation-mc11/#.XGJqSvZFyUk

https://www.mssqltips.com/sqlservertip/4331/sql-server-index-fragmentation-overview/

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

Answer 3

That makes things worse, not better. Cat_ca were using the default for te fifth parameters translating to LIMITED meaning traversing the level above the leaf level. You specified DETAILED meaning traversing the leaf level resulting in far more reads than the original query.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 4

You could try using SAMPLED for the fifth parameter (mode). That means sample pages. You are already using LIMITED (it is the default), so whether you see improvements or not you'll see...

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 5

I did not mention that it takes time, that right bit as you see I added script for single table to minimize reads on the db 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 6

Yes, but if the whole point of the original post is that the command takes too long time, then I suggest that you at least add some text if you post a reply with a command that will take a great deal *longer* time.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 7

thanks all for the inputs. I did use the SAMPLED and results came back  after 30 seconds vs never came back in the default mode. However, the BOL says SAMPLED used only 1% to give approximate result. 

cheers

---

--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Answer 8

thanks all for the inputs. I did use the SAMPLED and results came back  after 30 seconds vs never came back in the default mode. However, the BOL says SAMPLED used only 1% to give approximate result. 

"SAMPLED" is good enough, if you have a huge table then a small piece is good enough for statistical purposes, you can trust it 99%. :)

Josh