Well all of us in DBA world know to use UPDATE STATISTICS and its significance in keeping or SQL Space Ship updated with current index pattern and it is further usage by optimizer to process our queries at escape Velocity. As per SQLBOL
Query optimization depends on the accuracy of the distribution steps:
- If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
- If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS
But what and how does it know which tables have changed and where.I knew it few years back and suddenly followed back the track. here.
What was the net benefit :- REDUCE MAINTENANCE TIME WINDOW
My first point of contention was SYSINDEXES and I studeid the table abck as if I knwo the table for the first time and I found a column name RowModCtr .But what and how does this column impact my Update Statistics. I used a querry
SELECT
o.name AS [table name],
o.name AS [Index Name],
i.rowmodctr AS [Rows Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = 'U'
ORDER BY i.rowmodctr DESC
Hre I joined all the tables whose counter > 0 and which are tables and I found a comparative list. I moved to New groups and found lots of queries on this issue have come already I studied some of them . I was on a right path.
Now the next question for me was how to reduce my maintenance time as I used a previous document Dtored proc for UPdateStats which will take a huge time.the Above query was very much there as I would hva e list of all those tables which need to update stats.As such I can reduce my maintence time.I tweaked some some genric Statements and came out with a nice syntax..
======================== THE SCRIPT THAT GAVES ME ALL DBS/TABLES on Server that need update stats ==============
Create table #DB_Name (dbname varchar (100))
Insert into #DB_Name(dbname)
select [name] from master.dbo.sysdatabases where name not in ('tempdb','model')
DECLARE @dbName varchar (100), @sql varchar (1000)
set @dbName = ''
DECLARE db_Update CURSOR
FOR
SELECT dbname from #DB_name
OPEN db_Update
FETCH NEXT FROM db_Update INTO @dbName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @sql = 'use ['+@dbname+ '] '
set @sql = @sql +'SELECT O.name AS [table name], O.name AS [Index Name],
I.rowmodctr AS [Rows Modified] FROM SYSOBJECTS o JOIN SYSINDEXES I ON O.id = I.id WHERE I.rowmodctr > 0
and O.xtype = ''U''
ORDER BY I.rowmodctr DESC'
print @sql
Exec (@sql)
END
FETCH NEXT FROM db_Update INTO @dbName
END
CLOSE db_Update
DEALLOCATE db_Update
drop table #db_name
============================ The Script that I can use regularly on a Server to update stats across all DBS amd Database ==============
Create table #DB_Name (dbname varchar (100))
Insert into #DB_Name(dbname)
select [name] from master.dbo.sysdatabases where name not in ('tempdb','model')
DECLARE
@dbName varchar (100),
@sql varchar (1000)
set @dbName = ''
DECLARE db_Update CURSOR
FOR
SELECT dbname from #DB_name
OPEN db_Update
FETCH NEXT FROM db_Update INTO @dbName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @sql = 'SET NOCOUNT ON '+ char(13)+ 'Use ' + @dbName + ' SELECT Distinct '' UPDATE STATISTICS ''+ SPACE(1) + O.NAME + CHAR(13)+''GO'' FROM SYSOBJECTS O '
+ 'JOIN SYSINDEXES i ON o.id = i.id '
+ 'WHERE i.rowmodctr > 0 and o.xtype = ''U'''
print @sql
Exec (@sql)
END
FETCH NEXT FROM db_Update INTO @dbName
END
CLOSE db_Update
DEALLOCATE db_Update
drop table #db_name
================================