update statistics logging - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

update statistics logging

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-21 : 16:19:59

im running Sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'
Seems to be taking hours.

Is there a way to see what table it is on - add parameter to print out or write to a table for reference.

Looked in profiler couldn't see anything.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 16:22:17

Do you really have to update all tables or only tables with certain schema? Full scan will take time.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-21 : 17:20:13

No only really wanted to update the ones that needed to be updated.
Is there a better way to do this.

I can't turn on the auto on db as i noticed it made processes take way too long.

So now im at the stage to run this but not really sure what % i should use

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 17:27:04

If only certain schema tables are affected then you can use sp_Msforeachtable like this:

Exec sp_MSforeachtable @command1 = " Update Statistics ? ", @whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'yourschema')

But question is do you need to update stats on your all huge tables?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-21 : 18:20:24

i would say no ...... what be suggestion....a PERCENTAGE.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-21 : 18:21:01

I use 25%.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-21 : 18:42:49

Tkizer - Your script CREATE PROC isp_UPDATE_STATISTICS
Can i set up a job and put in my SYSTEMADMIN Database the stored procedure

Then just call exec isp_UPDATE_STATISTICS 'MYDATABASENAME','25'

Or best run as a sqlcmd script cheers.

I think fullscan is way too much i did run first on upgrade to sql2005 so i think i look at 25% and try that on our DEV box.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-21 : 18:45:26

Yes, and here's how my production environments are configured for SQL jobs:
http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-21 : 20:11:20

Thanks for advice.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-22 : 14:13:21

Do you run the update statistics before or after index rebuild - i do index rebuild everynight but only on the ones greater than 30%.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-22 : 14:14:54

Remember Rebuilding index will automatically update statistics but Reorganize doesn't.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-22 : 14:17:25

I put the isp_UPDATE_STATISTICS in my SYSADMIN Database and used

exec isp_UPDATE_STATISTICS 'FINANCEDB' ,'25'
Returns Invalid object name 'FINANCEDB.dbo.sysobjects'

Is there a way to run without having to add stored procedure to every database first.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-22 : 14:19:50

Was on the wrong server....ignore.
But it did return error Table 'POOL' does not exist.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-22 : 14:38:47

Got it to work...hmmm yes that right reindex does do the statistics update....

So no need to do update statistics or just run once a week for good measure.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-09-18 : 18:21:46

When the index rebuild happens ALTER INDEX ...what % is it using if it does call the update statistics.

Because if updates statistics is run with 25 percent will this not update all that happened in alter index.

I just cannot find what % the update statistics uses in alter index.

Cheers

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-18 : 20:46:38

Rebuild index will update statistics with Full scan . That's why it is huge operation.

Check this:
http://sqlug.be/blogs/wesleyb/archive/2007/11/13/update-statistics-before-or-after-my-index-rebuild.aspx

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-09-19 : 10:26:09

Curious, have you compared your proc (tkizer) to just calling sp_updatestats on the database? I use this in a loop utilizing sp_MSforeachDB using the 'resample' option. Does not appear to cause any issues and I've been using this on all my databases for about a year now. The main reason I chose this is this was optimized in SQL 2005 to update only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-09-19 : 16:09:50

Now my reindex rebuild is
EXEC ('ALTER INDEX ' + @indname + ' ON ' + @tabname + ' REBUILD WITH(PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)')

The STATISTICS_NORECOMPUTE IS OFF -- When i read this it talking about Distribution statistics.
Is this STATISTICS or another set of statistics - shouldn't i have this ON for the reindex rebuild to go update all the statistics.

----From bol.
STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether distribution statistics are recomputed. The default is OFF.

ON
Out-of-date statistics are not automatically recomputed.

OFF
Automatic statistics updating are enabled.

To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

---
It is probrably best to go use the sp_updatestats with a resample option i am assuming it will not go back and override the indexes.statistics that have been done in the rebuild.

Now im curious if my reindex is doing the statistics due to the option above.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-19 : 17:01:20

Here is a simple script that will update the statistics on all user databases in SQL 2005. The sp_updatestats procedure automatically decides if the statistics on an index need to be updated.

Edit: Updated script with more checks to make sure database is online, updateable, and not in single-user mode.


-- Update Statistics on all Databases

declare @cmd nvarchar(max)
set @cmd = ''

select @cmd = @cmd+
'
use '+quotename(a.name)+'

if db_name() <> N'''+a.name+''' goto End_DB_'+convert(varchar(20),a.dbid)+'

print	''''
print 	''Start Update Statistics for database ''+db_name()+
	'' at ''+convert(varchar(30),getdate(),121)
print 	''''

exec sp_updatestats

print 	''Update Space used for database ''+db_name()+
	'' at ''+convert(varchar(30),getdate(),121)

exec sp_spaceused @updateusage = ''true''

print	''''
print 	''End Update Statistics for database ''+db_name()+
	'' at ''+convert(varchar(30),getdate(),121)
print	''''

End_DB_'+convert(varchar(20),a.dbid)+':

'
from
	master.dbo.sysdatabases a
where
	a.name not in ('master','model','msdb','tempdb')	and
	databasepropertyex(a.name,'Status') = 'ONLINE'	and
	databasepropertyex(a.name,'Updateability') = 'READ_WRITE'	and
	databasepropertyex(a.name,'UserAccess') in ('RESTRICTED_USER','MULTI_USER')

print 'Start Update Stats at '+convert(varchar(30),getdate(),121)

exec ( @cmd )

print 'End Update Stats at '+convert(varchar(30),getdate(),121)

CODO ERGO SUM

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-09-19 : 20:11:22

Thanks for script...I think i got the handle on this now. One table had 8096 rows in it.

When run the exec sp_updatestats

Results
-- See the row sampled.
DBCC SHOW_STATISTICS ('dbo.TESTSTATS', PK_TESTSTATS) WITH STAT_HEADER;

Example rows rows sampled
PK_TestStats Sep 19 2008 7:59PM 8086 3107

Now run the rebuild index the rows sampled goes to the no of counts in row.
PK_TestStats Sep 19 2008 8:03PM 8086 8086

So running the exec sp_updatestats is ok to run as it will only
update the statistics that havent been updated with a percent rows.

But UPDATE STATISTICS will override what just been done in the reindex which shouldn't be done lightly.

Very intersting .....
Thanks.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-09-19 : 20:14:31

Michael Valentine Jones
Script failed if database is RM-AM01XP and if database read-only access mode.
I have to add that in .

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources