August 22, 2008 at 2:44 pm
Hi,
I have to run UPDATE STATISTICS query for a database with thousand of tables. (sic!)
For 10-20 tables in that database I need to execute the that query with a parameter ie. WITH SAMPLE 50 PERCENT (or less) becouse they are huge.
What is the best option to sort that ?
At the moment I have a huge query with thousand of lines, one for each of the table eg.
UPDATE STATISTICS [dbo].[table1] WITH FULLSCAN
...
UPDATE STATISTICS [dbo].[table2000] WITH SAMPLE 50 PERCENT
Any thoughts?
- cursor with data in a seperate table
- loop with IF (tablename=table2000 then WITH SAMPLE else FULLSCAN)
August 22, 2008 at 2:48 pm
You could insert the output of sp_spaceused (consider using sp_MSForeachtable to get this data) and then create a string to execute based on some logic that would include looking at the table size to determine what sampling you want to put on the statistics.
Give that a go - or someone may give you a better option - and post with any problems you have.
Have fun!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 22, 2008 at 2:57 pm
I already have defined sample levels based on the sp_spaceused & number of the statistics created for each table - there are many of them in some cases.
This should be a part of maintenance plan, so I would like to do that professional way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply