Fragmentation in SQL 2005

• balbirsinghsodhi

  SSCrazy Eights

  Points: 9210

  More actions

  July 22, 2009 at 11:55 am

  #132115

  I am trying to use sql 2005 dynamic views to caputre fragmentation but it is taking forever but when I use DBCC SHOWCONTIG(TableName), it's very fast.

  DECLARE @DB_ID INT

  SELECT @DB_ID = DB_ID()

  SELECT TableName = object_name(object_id), database_id, index_id, index_type_desc,

  avg_fragmentation_in_percent, fragment_count, page_count

  FROM sys.dm_db_index_physical_stats (@DB_ID, null, NULL, NULL , 'LIMITED')

  WHERE object_name(object_id) is not null

  and avg_fragmentation_in_percent > 30 AND PAGE_COUNT > 1000

  and object_name(object_id) ='TableName'

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  July 22, 2009 at 12:32 pm

  #1028598

  Hi Balbir,

  Check this link:

  http://blogs.techrepublic.com.com/datacenter/?p=251

  Thank You,

  Best Regards,

  SQL Buddy

• balbirsinghsodhi

  SSCrazy Eights

  Points: 9210

  More actions

  July 22, 2009 at 1:02 pm

  #1028620

  Wow. That is pretty faster. I will use the one in the article.

  Thanks man

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  July 22, 2009 at 2:36 pm

  #1028676

  Hi Balbir,

  You're more than welcome.

  Good Luck!

  Yours

  SQLBuddy.