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


Post New Web Links

sys.dm_db_index_physical_stats.alloc_unit_type_desc

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

  

Hello,

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

CODE SNIPPET:

with CTE (database_id, name)
    as
    (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
Categories: 
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