Index

• deepzzzz

  SSCrazy

  Points: 2890

  More actions

  December 16, 2009 at 12:40 am

  #1092838

  using sp_helpindex 'TableName' will give you the details of the index on particular table

  Thanks

• Raghavender Chavva

  SSCrazy Eights

  Points: 8669

  More actions

  December 21, 2009 at 2:51 pm

  #1095125

  Hi All,

  Can we defrag the clustered indexes ?

  when I am running query:

  select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

  I am getting the same index two or more times on same table with different fragmentations and different page numbers. in that which one do we need to choose ?

  Thanks

  Thank You.

  Regards,
  Raghavender Chavva

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  December 21, 2009 at 3:13 pm

  #1095130

  Raghavender (12/21/2009)

  ---

  Hi All,

  Can we defrag the clustered indexes ?

  when I am running query:

  select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

  I am getting the same index two or more times on same table with different fragmentations and different page numbers. in that which one do we need to choose ?

  Thanks

  Yes you can defrag clustered indexes.

  Second part, Is your table partitioned or do you have multiple files in the filegroup for which this table belongs?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 22, 2009 at 1:35 am

  #1095277

  Raghavender (12/21/2009)

  ---

  select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

  I am getting the same index two or more times on same table with different fragmentations and different page numbers. in that which one do we need to choose ?

  In detailed you'll get one row for each level of the index. You can look just at index_level = 0 (the leaf level) for the main fragmentation info.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass