Update Stats or Rebuild Index Strategies

Question

Update Stats or Rebuild Index Strategies

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 1

see this post on the statistics, after a rebuild you still need to update statistics on col.

http://sqlblog.com/blogs/ben_nevarez/archive/2009/10/06/rebuilding-indexes-vs-updating-statistics.aspx

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

Tara Kizer SSCommitted Points: 1789 More actions · Answer 2

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 3

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

Tara Kizer SSCommitted Points: 1789 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tara Kizer SSCommitted Points: 1789 More actions · Answer 6

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tara Kizer SSCommitted Points: 1789 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tara Kizer SSCommitted Points: 1789 More actions · Answer 11

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

Tara Kizer SSCommitted Points: 1789 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 13

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

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

Tara Kizer SSCommitted Points: 1789 More actions · Answer 15

GilaMonster (8/4/2010)
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).

Yeah I made sure to question if what he gave me back was auto update or sp_updatestats, and he said it applies to both. I've got another DBA running some tests since the code of sp_updatestats doesn't reflect the info we received.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog