September 16, 2008 at 4:24 pm
I am rebuilding the indexes nightly if greater than 30%.
This performs the udpate statistics.
If the tables are not 30% would it be wise to do update statistics on all tables....
Any recommendations and what best way to see which tables statistics are not udpated.
Update statictis with %...what do you use.
September 17, 2008 at 12:26 pm
in sql 2005 sp_updatestats will only update stats which need updating, unlike sql 2000 so the impact is far less. I update stats twice a day on my prod server, out of date stats can be a killer.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 17, 2008 at 1:21 pm
Just one note on 'sp_updatestats'. By default, this will update statistics using the default sample rate. If you pass in the parameter @resample = 'RESAMPLE' - then the statistics will be updated using the last sampling rate for that object.
In some cases, the default sampling rate will not be adequate and can cause performance issues. In most cases (that I have seen), the default sampling rate works just fine.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2008 at 1:28 pm
What parameters do you use on your twice daily.
I was under impression that the index will do the statistics so it only be on the tables that need to be done.
September 17, 2008 at 1:29 pm
You can also inspect the last time stats have been updated :
SELECT i.name as Index_Name
, STATS_DATE(i.object_id, i.index_id) as Statistics_Date
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id;
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2008 at 1:42 pm
Thats good the index statistics check..
getting names called
clust
NULL
clst
nc1
nc2
is it possible to get table also printed out.
Yes some are out of date .... 🙁
The last time i ran was when did upgrade from sql 2000 TO SQL2005 with
Sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'
That i recall took many hours...
So just run with a percent 20%?
Some of the statistics are being updated i guess from the index rebuilds.
September 17, 2008 at 1:55 pm
SELECT object_name(i.object_id) as Table_Name
, i.name as Index_Name
, i.index_id
, STATS_DATE(i.object_id, i.index_id) as Statistics_Date
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
order by Table_Name, i.index_id ;
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2008 at 1:57 pm
TRACEY (9/17/2008)
Thats good the index statistics check..getting names called
clust
NULL
clst
nc1
nc2
is it possible to get table also printed out.
Sure. Add o.name to the select clause
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
September 17, 2008 at 2:05 pm
Thanks...
Lastly what do you guys use as your percentages with the statement
sp_updatestats
Fulls - i be there until xmas......................
One table i saw sampled 10098562 how do you know which sample it used.
September 18, 2008 at 10:24 am
i ran this
Create PROCEDURE dbo.SP_UpdateStatsALL
AS
Set Nocount on
Declare db Cursor For
Select name from master.dbo.sysdatabases where name = 'APPDBX'
--not in ('master','TempDB', 'msdb', 'model')
Declare @dbname varchar(60)
Declare @execmd nvarchar(150)
Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
begin
if @dbname is null
Begin
Print 'null Value'
end
else
Begin
PRINT '###########################################################################'
PRINT 'Update Statistics in ' + @dbname
SELECT @execmd = 'USE ' + @dbname + ' EXEC sp_updatestats'
EXEC(@execmd)
PRINT ''
End
Fetch Next from db into @dbname
end
Close db
Deallocate db
GO
Then
SELECT object_name(i.object_id) as Table_Name
, i.name as Index_Name
, i.index_id, o.name
, STATS_DATE(i.object_id, i.index_id) as Statistics_Date
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
order by Table_Name, i.index_id ;
GO
Noticed that all statistics dates are not being updated.
Should i just use the update statistics.
September 18, 2008 at 12:19 pm
that's the point - it changed from 2000 to 2005. To update ALL stats regardless you must run update statistics against each table not sp_updatestats. If you start altering sample rates you'll maybe need to turn off auto update stats which only updates at default sample rate.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 12:32 pm
Lost now.
SQL 2005 - use update statistics
sql 2000 - sp_updatestats
I thought it was the other way around and that the sp_updatestats will only update the statistics that are necessary.
(Now back to the %) - Still confused why use FULL, or use % if you use 10% how do you really know if this is sufficient.
September 18, 2008 at 12:42 pm
sorry ! sp_updatestats used to update all stats regardless in sql 2000, in SQL2005 it only updates stats "it thinks" needs updating - doesn't mean it's right.
so if you examine stats in SQL2005 after running sp_updatestats then some stats will show as not being updated.
The ONLT way to force ALL stats to update in SQL2005 ( regardless of them needing updating or not ) is to use Update Statistics.
I generally default sample rate works fine, however if you start altering sample rates in your update statistics commands a system based auto update stats will resample at default rate ( 10% )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 2:40 pm
Update statistics with sample 25 percent.
Optimizer will only use 10% at even given rate is that correct theory.
Anyone got a update statistics script that writes to a table to say which ones it done.
I don't have much luck with the sp_msforeachtable for some reason.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply