On SQL Server 2008, I'm running the following query on a 220GB database to list out the indexes and their levels of fragmentation:

ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count
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 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

It's taken 8 minutes and then I cancelled the query, as it's a production DB (no DEV to test on), on this page someone stated it affects the server and should be run at slow times:


I'm just curious how it would impact the server, short of using memory/cpu for the query, is it causing locks on tables I'm not aware of?

  • It looks like you are just doing this for a single table, with all the indexes on that table. I would start out by dumping the joins, just throw the sys.dm_db_index_physical_stats information into a temp table. Much better to join on that. Otherwise use cross apply with the dmf.
    – Nic
    Commented Oct 14, 2016 at 21:01
  • I'm an idiot... pasted the wrong query... I was running it without a table specified...with this table specified the query took 1 minute to complete... given that there's 100 tables... I'm guessing it will run for awhile, wanted to make sure I don't do any damage.
    – Neostim
    Commented Oct 14, 2016 at 21:03
  • 1
    You'll see an increase in IO, potentially some in CPU, and provided you aren't taking exclusive table locks you shouldn't see a problem otherwise.
    – Nic
    Commented Oct 14, 2016 at 22:36

1 Answer 1


The link you posted refers to rebuilding the indexes, which can have a performance impact as they may take indexes offline and they also cause a high amount of I.o when rebuilding.

As far as your query there will be a slight performance impact from the amount of I.o you are doing but I doubt it will be noticeable. In your query --

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)

Specifies LIMITED mode which is the fastest mode and scans the smallest number of pages, as this is the default run level.


Paul Randal has a good post that shows the performance impact and how you can monitor if anything is happening on your system when you kick off this job. http://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/

One other thing, it does look like you are going to be filtering out heaps in your database based on your join to index_id. I always like to confirm what is happening with heap tables and how many I have when looking at a database from a fragmentation level.

This query will return the schema name and table name for each heap table that you have...

SELECT SCH.name AS [Schema Name] ,
OBJ.name AS [Table_Name]
FROM sys.indexes INX
JOIN sys.objects OBJ 
ON INX.object_id = OBJ.object_id
JOIN sys.schemas SCH 
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0 --filters SQL objects
AND INX.index_id = 0 --meaning it has none
AND OBJ.type = 'U'; --user table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.