Query on optimization

• karthikaug18

  Say Hey Kid

  Points: 662

  More actions

  January 30, 2012 at 4:55 am

  #255575

  Is there any query to get the tables that are to be optimizated (defrag, reindex, update stats). Thanks in advance

  Regards

  Karthikeyan.T

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 30, 2012 at 5:06 am

  #1439902

  For fragmentation - sys.dm_db_index_physical_stats

  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

• Devendrakumar

  SSC-Forever

  Points: 42493

  More actions

  January 30, 2012 at 5:35 am

  #1439910

  sp_createstats

  http://msdn.microsoft.com/en-us/library/ms186834.aspx

  DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

  http://msdn.microsoft.com/en-us/library/ms174384.aspx

  UPDATE STATISTICS table_or_indexed_view_name

  http://msdn.microsoft.com/en-us/library/ms187348.aspx

  More: http://msdn.microsoft.com/en-us/library/ms190397.aspx

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 30, 2012 at 6:46 am

  #1439948

  If you want to optimize indexes and stats this is a good place to start looking:

  http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

  -- Gianluca Sartori