SQL Maint optimization

[Glen Whitling]

I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?

Thanks,
Glen

[Glen Whitling]

It is only doing the Update statistics with a 10% sampling. Is the samiling causing the difference in run time?
Glen


------------
Ray Miao at 4/19/00 12:22:28 PM

It'll do dbcc, maybe recreate index also depends on how you created this plan.


------------
Glen Whitling at 4/19/00 11:48:15 AM

I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?

Thanks,
Glen

[AB]

Have a look, please, at the job called "Optimizations Job for DB Maintenance Plan '...'", using EM.

What is the full T-SQL command used by "Step 1" of this job?

------------
Glen Whitling at 4/19/00 12:31:13 PM

It is only doing the Update statistics with a 10% sampling. Is the samiling causing the difference in run time?
Glen


------------
Ray Miao at 4/19/00 12:22:28 PM

It'll do dbcc, maybe recreate index also depends on how you created this plan.


------------
Glen Whitling at 4/19/00 11:48:15 AM

I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?

Thanks,
Glen'

[Glen Whitling]

EXECUTE master.dbo.xp_sqlmaint N'-PlanID DE09580D-3E20-11D3-8CFC-009027286FE2 -Rpt "C:MSSQL7LOGAmnetDW - DB Maintenance 0.txt" -DelTxtRpt 1WEEKS -WriteHistory -UpdOptiStats 10 '

------------
AB at 4/19/00 12:57:44 PM

Have a look, please, at the job called "Optimizations Job for DB Maintenance Plan '...'", using EM.

What is the full T-SQL command used by "Step 1" of this job?

------------
Glen Whitling at 4/19/00 12:31:13 PM

It is only doing the Update statistics with a 10% sampling. Is the samiling causing the difference in run time?
Glen


------------
Ray Miao at 4/19/00 12:22:28 PM

It'll do dbcc, maybe recreate index also depends on how you created this plan.


------------
Glen Whitling at 4/19/00 11:48:15 AM

I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?

Thanks,
Glen'

[AB]

Hi Glen,

There is nothing wrong with your command, as expected.

If nothing else is wrong (like locking, etc.), then the only explanation remains you own explanation: that "10 percent".

1) When you run sp_updatestats, you actually run:
UPDATE STATISTICS table
on all the table from your database. The number of rows sampled is determined automatically, by the system.

2) When you run "xp_sqlmaint ... -UpdOptiStats 10" (i.e. "sqlmaint ... -UpdOptiStats 10&#34, you actually run:
UPDATE STATISTICS table WITH SAMPLE 10 PERCENT
on all the table from your database. The number of rows sampled is 10 % from the total number of rows in each table.

I tested the two commands on two tables from a our test database, with more than 1 million records each. I got:

Table1 Table2
1) 23 sec 39 sec
2) 88 sec 168 sec

It is still very far from you results. But I do not know how many tables you have, with how many records and how many "statistics" are created on them.

I will now try to give you an answer (our answer) to your real question: Do you have to refresh the statistics in this way, when you set the database options to create and update automatically the statistics? And how to do it?

I do not see any benefits from using the database maintenance plan in our conditions: database servers with a few databases. The situation would be different if you have a lot of databases and you have to do the same maintenance operations on all of them.

Therefore we do not use the database maintenance plan wizard. We have maintenance jobs which run directly DBCC commands and stored procedures.

BOL are saying that, after setting the database options specified above, you do not have to care about the statistics, because there are automatically updated after a certain number of insert/delete/update operations. You have to update them only after massive bulk insert operations.

What we actually do is a combination. I set the two options for each database, but we still update the statistics manually once a month, over night.

We do this implicitly, through our job which rebuilds the indexes on all the tables. running
DBCC DBREINDEX(table)
on all the tables from the production database.

Surprisingly enough, this command updates all the statistics too. I know this for sure, from our detailed logs.

We found this a simple and effective solution.

Regards, AB
------------
Glen Whitling at 4/19/00 3:01:56 PM


EXECUTE master.dbo.xp_sqlmaint N'-PlanID DE09580D-3E20-11D3-8CFC-009027286FE2 -Rpt "C:MSSQL7LOGAmnetDW - DB Maintenance 0.txt" -DelTxtRpt 1WEEKS -WriteHistory -UpdOptiStats 10 '

------------
AB at 4/19/00 12:57:44 PM

Have a look, please, at the job called "Optimizations Job for DB Maintenance Plan '...'", using EM.

What is the full T-SQL command used by "Step 1" of this job?

------------
Glen Whitling at 4/19/00 12:31:13 PM

It is only doing the Update statistics with a 10% sampling. Is the samiling causing the difference in run time?
Glen


------------
Ray Miao at 4/19/00 12:22:28 PM

It'll do dbcc, maybe recreate index also depends on how you created this plan.


------------
Glen Whitling at 4/19/00 11:48:15 AM

I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?

Thanks,
Glen'