Ruuning Update Stats

Question

Ruuning Update Stats

inHouseDBA

Default port

Points: 1403
More actions
June 4, 2012 at 5:37 pm

#257276

Greeting All:
Using SQL Server 2005 (9.00.5000.00), but moving to SQL Server 2008 in 4 months.
I am in the process of updating my Weekend Maint scripts..
Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 100.
Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes.
So at the end of the Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.
Also I have Clustered and Non-Clustered Indexes which were not selected that weekend to be reindexed and heap tables.
After that step I usually run sp_updatestats command for the entirely database, assuming that BOL cannot lie: sp_updatestats updates only the statistics that require updating..., so, for example, Indexes, which were just rebuild in the previous step, will be skipped.
But as my database gets bigger and I see more and more discussions contradicting the above extract from BOL, can someone please outline update statistics steps that needs to be done after Weekend Reindexing.
Thank you,
B

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

inHouseDBA (6/4/2012)
Greeting All:
Using SQL Server 2005 (9.00.5000.00), but moving to SQL Server 2008 in 4 months.
I am in the process of updating my Weekend Maint scripts..
Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 100.
Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes.
So at the end of the Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.
Also I have Clustered and Non-Clustered Indexes which were not selected that weekend to be reindexed and heap tables.
After that step I usually run sp_updatestats command for the entirely database, assuming that BOL cannot lie: sp_updatestats updates only the statistics that require updating..., so, for example, Indexes, which were just rebuild in the previous step, will be skipped.
But as my database gets bigger and I see more and more discussions contradicting the above extract from BOL, can someone please outline update statistics steps that needs to be done after Weekend Reindexing.
Thank you,
B

What are you doing to the heaps?

Re: sp_updatestats, running it after your index maintenance is fine. From Books Online sp_updatestats article:

In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.

If you want to make the best stats possible available to the optimizer and you have time in your maintenance window then run this after running your index maintenance to do a FULLSCAN update of all your column-level stats. Even if you do this there is still no harm in calling sp_updatestats:

USE dbname;

EXEC sys.sp_MSforeachtable

@command1 = N'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

inHouseDBA Default port Points: 1403 More actions · Answer 2

No, I don't update Stats on heap tables.

What I was thinking is to run update statistics on all tables where indexes were NOT rebuld...

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

When an index is rebuilt or reorged only the stats for that index are updated. This is why I suggest also updating column statistics if you have time. If you ran these steps as often as time allows, in this order, you would have very healthy indexes and stats:

1. Your index maintenance routine.

2. The update COLUMN stats code I provided above.

3. sp_updatestats on the whole database.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

inHouseDBA Default port Points: 1403 More actions · Answer 4

Thank you,

I guess my stopping point is the following:

When you rebuild the index - you rebuild it with 100% sample size, but when you run sp_updatestats a default sampling is being used with a following formula:

Rowmodctr 500 + 20% of total rows (rowcnt).

So by running sp_updatestats after Index Rebuild you are already not improving those indexes, but making it even worse?!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

inHouseDBA (6/6/2012)
Thank you,
I guess my stopping point is the following:
When you rebuild the index - you rebuild it with 100% sample size, but when you run sp_updatestats a default sampling is being used with a following formula:
Rowmodctr 500 + 20% of total rows (rowcnt).
So by running sp_updatestats after Index Rebuild you are already not improving those indexes, but making it even worse?!

No, sp_updates stats will only update stats if underlying data has changed beyond a certain point, so if you run all this in order during low utilization it will be skipped.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

inHouseDBA Default port Points: 1403 More actions · Answer 6

Thanks, so by running ...UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;.. it will also update Columns in heap tables as well, right?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

inHouseDBA (6/7/2012)
Thanks, so by running ...UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;.. it will also update Columns in heap tables as well, right?

If the heap has column-stats on it, yes, it will update the stats.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

inHouseDBA Default port Points: 1403 More actions · Answer 8

Including System Stat (or _WA_Sys_) on heap, right?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Yes. Any auto created or manually created column stats.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

inHouseDBA Default port Points: 1403 More actions · Answer 10

Thanks again.

Few more questions about Stats:

1.How about replacing or creating useful statistics from _WA_Sys_ auto created system stats?

Is it recommended to replace an old auto stat (_WA_Sys_) with a new user-created stat? ( And I am NOT talking here about possibility of creating Indexes from those auto stats) ?

Like:

DROP STATISTICS [dbo].[tbl_Account].[_WA_Sys_0000001D]

GO

CREATE STATISTICS [keyname] ON [dbo].[tbl_Account] ([keyname]) WITH FULLSCAN

GO

2.If it is, I guess it should be done BEFORE running ...UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;… ?

3.What is the threshold (minimal number of pages) after which we should start running CREATE STATISTICS command?

If I have a number of tables with row count from 1 to say, 1000. And Auto Created Statistics don’t exist on those tables (Columns).

Should I still Create Statistics on those tables (Columns) or wait when Auto Created Statistics will be created and only after take the action (run CREATE STATISTICS command)?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

inHouseDBA (6/7/2012)
Thanks again.
Few more questions about Stats:
1.How about replacing or creating useful statistics from _WA_Sys_ auto created system stats?
Is it recommended to replace an old auto stat (_WA_Sys_) with a new user-created stat? ( And I am NOT talking here about possibility of creating Indexes from those auto stats) ?
Like:
DROP STATISTICS [dbo].[tbl_Account].[_WA_Sys_0000001D]
GO
CREATE STATISTICS [keyname] ON [dbo].[tbl_Account] ([keyname]) WITH FULLSCAN
GO
2.If it is, I guess it should be done BEFORE running ...UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;… ?

Don't bother with 1 or 2. Such a move would have zero benefit. You cannot reference a stat directly by name in a table or query hint like you can with indexes so the names are irrelevant, and stay hidden in the background.

3.What is the threshold (minimal number of pages) after which we should start running CREATE STATISTICS command?
If I have a number of tables with row count from 1 to say, 1000. And Auto Created Statistics don’t exist on those tables (Columns).
Should I still Create Statistics on those tables (Columns) or wait when Auto Created Statistics will be created and only after take the action (run CREATE STATISTICS command)?

I wouldn't worry too much about creating your own stats. The database engine does a good job of creating stats when it needs them. If you just maintain the ones the database engine creates you'll be in good shape.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

inHouseDBA Default port Points: 1403 More actions · Answer 12

inHouseDBA

Default port

Points: 1403

June 8, 2012 at 5:47 pm

#1498985

Thanks for your help.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

You're welcome.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

inHouseDBA (6/4/2012)
Greeting All:
Using SQL Server 2005 (9.00.5000.00), but moving to SQL Server 2008 in 4 months.

Mostly off subject and I didn't see it mentioned anywhere in this thread (I may have missed it) but I have to ask... why 2008? Why not 2012? 2008 is already almost 4 years old.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)