.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

I read the BOL on this, but not sure what it is.

Here is what I am trying to do. I am trying to implement an index maintenance procedure that uses the output from sys.dm_db_index_physical_stats to determine if an index on a table should be defragmented.

So, I run a statement like this to see what the fragmentation stats look like on an index.

select avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (@db_id,@table_id,@index_id,null,'LIMITED')


When I do this, on certain indexes, I get back more than 1 row differentiated by something called alloc_unit_type_desc. Now, reading BOL, it sounds like this has something to do with the data types of the keys in your index. The issue is, I want 1 row of avg_fragmentation_in_percent to determine if I should defragment. But when I have an index that returns multiple avg_fragmentation_in_percent due to this alloc_unit_type_desc, I'm not sure what the approach should be. May be I should just look at the max avg_fragmentation_in_percent among the various alloc_unit_type_desc?

View Complete Post

More Related Resource Links

Having issues with sys.dm_db_index_physical_stats



I am trying to write a query that grabs data from the sys.dm_db_index_physical_stats table and join it to the sys.databases table. My queries keep blowing up because databases are offline. I have a CTE that queries only databases that are online, but sys.dm_db_index_physical_stats table just ignores it.

Any help would be appreciated


with CTE (database_id, name)
    (select database_id, name from sys.databases where state_desc = 'ONLINE')
    select * from cte
    JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) ps
       on cte.database_id = ps.database_id

Dave SQL Developer
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend