Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shaileshpatangay
Starting Member
4 Posts |
 Posted - 2009-06-29 : 09:44:06
|
| How to Update Statistics On Tables, which needs to be updated.Following columns are returned from this report TableName : Name for the Table. IndexName : Name for the index.Rows Modified : Number of Rows Modified. ActionRequired : Healing Action required, Generates the sql Command to be executed on the DB. Following is the Sql Statement used to abstract the above required Information.This report would be ran against each user database and would be having a Inclusion and Exclusion List, which would be eliminate and include table names As required. Statement SELECT o.name AS TableName , i.name AS IndexName , i.rowmodctr AS RowsModified ,'update statistics ' || O.NAME As ActionRequiredFROM sysobjects o JOIN sysindexes i ON o.id = i.id WHERE i.rowmodctr > 0 and o.xtype = 'U' and i.createddate in (select max(createddate) from mssysindexes where createddate < o.createddate) and DatabaseOID = ‘$DatabaseOID$’ and i.name in (‘$InclusionList$’) and i.name not In (‘$Exclusion List$’)ORDER BY i.rowmodctr DESC Example Audit Report :********************************************************************************************************************TableName IndexName RowsModified ActionRequired ******************************************************************************************************************** CounterStat PK_CounterStat 99 UPDATE STATISTICS CounterStatContact PK_Contact 57 UPDATE STATISTICS Contactdtproperties pk_dtproperties 46 UPDATE STATISTICS dtpropertiesScripts Scripts 19 UPDATE STATISTICS ScriptsContactGroup PK_ContactGroup 15 UPDATE STATISTICS ContactGroupDiskSpace DiskSpace 12 UPDATE STATISTICS DiskSpaceTargetServerLogins TargetServerLogins 11 UPDATE STATISTICS TargetServerLogins******************************************************************************************************************** cheers shailesh patangay |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
How to Update Statistics On Tables, which needs to