Geeks With Blogs
My Place For SQL Lets Talk SQL

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
================================

 

 

 

 

 

 

 

Posted on Monday, August 15, 2005 9:05 AM | Back to top


Comments on this post: Understanding UpdateStats

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Veer Ji Wangoo | Powered by: GeeksWithBlogs.net