SMO Index.EnumFragmentation method analyzes entire database, not just the individual index - by Kyle Neier

Status : 

 


11
0
Sign in
to vote
ID 713488 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 12/12/2011 10:28:33 AM
Access Restriction Public

Description

When attempting to gather index fragmentation statistics from SQL Server using the SMO method "EnumFragmentation" on an index, the command that is issued to SQL 2005, 2008, and 2008R2 scans every index in the database - creating unnecessary IO and blocking when using this method on larger databases.  The information returned is indeed only relative to the index in question, but on larger databases, obtaining this information can create unavailability and take significantly longer than it should.
	
It seems the problem is that the command sent to SQL Server limits the results sent to the client in the where clause of the query instead of populating as parameters of the dm_db_index_physical_stats function. The only parameters populated to the function are the database_id and mode ('LIMITED'). All other parameters are NULL. This forces the function to analyze all of the indexes in the database first then limit to only the one that was requested by SMO.

Using this same method on SQL 2000 behaves as expected, limiting the results in the DBCC SHOWCONTIG statement, not in the query that is run after the temp table has been populated.
Sign in to post a comment.
Posted by jyao on 11/30/2014 at 5:05 PM
I would request MS to reconsider its position to fix this issue instead of simply closing it, as this issue still persists in sql server 2012. It is indeed very bad for big database in performance.
Posted by Microsoft on 6/20/2013 at 12:53 PM
Hello Kyle. Thanks for the feedback. We triaged this issue and at this time do not plan to address this in a future version of SQL Server. The suggestion will be saved for consideration with future releases.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by s_osborne2 on 2/25/2013 at 1:28 AM
Hi,

Is there any news on this one? I ran into this problem at the end of last week. I really didn't expect the whole database to be scanned on each iteration.

Thanks,

Simon
Posted by Microsoft on 3/26/2012 at 6:21 PM
Thank you for reporting this issue - we are investigating and we will get back to you shortly.

Thanks,

Alex Grach [MSFT]
Posted by Ben Miller - DBAduck on 3/19/2012 at 1:09 PM
This is a bad issue on a database that is 1 TB as in my case. The WHERE clause is executed after the DMF is executed in entirety. This should be fixed because when you use SSMS and click on the Fragmentation option you get a very specific query "from sys.dm_db_index_physical_stats(6, 797961919, 15, NULL, 'SAMPLED')" which is very focused on that index in that database on that table. It also uses SAMPLED as the default where SMO uses LIMITED.

The workaround is TSQL and that is OK, but users should not have to rely on that, there should be some consistency between the tools and SMO.