August 3, 2010 at 2:14 pm
We have a large database (close to 900GB) that we used to do update stats on weekly and that usually took 15 hours to complete. That strategy didn't serve us well with users trying to get access to the system 24/7.
We went with rebuilding index job that end up saving us 7 hours until we recently experienced some blocking issue that was a result of out dated stats on a table with 72 million rows. Our code/script in the rebuilding index was based of either reorg or rebuild the index based on fragmentation level. We were also under the assumuption that by rebuilding the indices we need NOT to bother with update stats any more.
Now we are rethinking the whole strategy and would like to know what others with similar large database environments are doing and the frequency of doing what they doing.
Anything would help and if you guys have any further questions please ask.
Thanks for all inputs
August 3, 2010 at 4:16 pm
see this post on the statistics, after a rebuild you still need to update statistics on col.
AS far as the large tables goes, they only work around I have found is to create another table for archiving. every quarter we would move data to the archive table.
This may or may not be possible in your case
August 3, 2010 at 4:42 pm
How are you running update stats? Are you doing everything each time or are you running sp_updatestats instead?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
August 3, 2010 at 7:17 pm
I update everything...in theory you shouldnt have to(rowmodctr should keep track), but I do it just to be safe. I just make sure I watch my cpu seems that is biggest thing.
With that said I do run it first thing Friday morning. I have had problems where after updating my query plans have changed, sometime this is good thing something not. In theory the optimer should make better a plan with the newly added info but its not always the case..
As far as blocking goes this should only be occurring during index rebuild assuming you are on Standard. Update stats as the most would only require a Shared latched.
But just to be safe I did a take a look this seems to confirm it according to folks at microsoft........http://social.msdn.microsoft.com/forums/en-us/sqldatabaseengine/thread/6392C40F-DE6C-4F11-89F8-1852649FC102
i also went over the white paper on stats...did not see anything there on blocking --if you have something please post..we will take a look
http://technet.microsoft.com/en-us/library/cc966419.aspx
hope this helps
August 4, 2010 at 11:57 am
You should not update stats on everything as that puts an unecessary load on the database server. Instead you should use sp_updatestats which makes a decision on what needs to be updated starting with SQL Server 2005. So just run sp_updatestats.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
August 4, 2010 at 12:13 pm
Tara Kizer (8/4/2010)
You should not update stats on everything as that puts an unecessary load on the database server. Instead you should use sp_updatestats which makes a decision on what needs to be updated starting with SQL Server 2005. So just run sp_updatestats.
The threshold for sp_updatestats is one row changed.
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
August 4, 2010 at 3:26 pm
Hmmm that's not what we've been told here by Microsoft. I'll have to wait for our MS guy to get back from vacation, but I'll certainly be asking him about this!
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
August 4, 2010 at 3:34 pm
which part is in ?
blocking,locking or one row changed?
I was not able to confirm or deny Gail's comment...I looked at the white paper in stats and kalen Delaney's book couldnt not find anything there....
But if Gail says it, I side with her as she is very knowledgeable.
August 4, 2010 at 3:41 pm
Easy to prove.
sp_helptext 'sp_updatestats'
Excerpt:
set @index_names = cursor local fast_forward read_only for
select name, indid, rowmodctr from sys.sysindexes
where id = @table_id and indid > 0 and indexproperty(id, name, 'ishypothetical') = 0
order by indid
open @index_names
fetch @index_names into @ind_name, @ind_id, @ind_rowmodctr
-- more omissions
if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0)))
begin
select @exec_stmt = @exec_stmt_head + @ind_name_quoted
-- and still more omitted for brevity
if (len(@options) > 0)
select @exec_stmt = @exec_stmt + ' WITH ' + @options
--print @exec_stmt
exec (@exec_stmt)
Also, when I did my presentation on stats last year, I updated just 10 rows of a 200000 row table, ran sp_updatestats again and the stats on that table were updated.
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
August 4, 2010 at 3:47 pm
That is some bad code then. 😉
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
August 4, 2010 at 3:53 pm
Tara Kizer (8/4/2010)
That is some bad code then. 😉
If you're curious, have a look at the code and read some of the comments that I left out... Also of interest is that the proc's using deprecated features.
Whether this is intended behaviour or not I don't know, but it's like that on all the versions I've checked of 2005 and 2008.
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
August 4, 2010 at 4:32 pm
So I was able to get a hold of Microsoft regarding this and here's what I got back:
That is only true when you pass from no rows to more than 0.
Then after the first 500 and finally when around 20% plus 500 modifications have occurred which is the ongoing behavior for it.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
August 4, 2010 at 4:52 pm
The Microsoft engineer directed me to this article, which discusses the inner workings: http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx
I haven't read it yet.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
August 4, 2010 at 11:39 pm
Tara Kizer (8/4/2010)
That is only true when you pass from no rows to more than 0.Then after the first 500 and finally when around 20% plus 500 modifications have occurred which is the ongoing behavior for it.
That's how the auto_update of stats behaves. It may be how sp_updatestats is supposed to behave, but it's not how it does behave. As I mentioned, in a presentation I did on stats last year I was able to update 10 rows in a 200 000 row table and when I reran sp_updatestats, it updated the stats on that table. The 20%+500 would be 40 500 rows, not 10. Drop me a PM with an email address I can use and we can take this to mail.
As for the stats article, I've read that several times, along with the 2005 and 2000 versions. Mostly correct, but the bit about filtered stats is misleading (or was late last year, I don't know if they've rewritten).
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
August 5, 2010 at 8:15 am
Tara Kizer (8/4/2010)
That is some bad code then. 😉
I don't think it is bad code. Works fine to accomplish the objective. You may argue with the REQUIREMENT they implemented, which is to update stats on any table with at least one modification. But the name of the sproc is sp_updatestats, so it seems to fit the description. 😛
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply