cancel
Showing results for 
Search instead for 
Did you mean: 

Running Table statistics from within a Background job

ken_halvorsen2
Active Participant
0 Kudos

Hi All

Does anyone know if and how, I can set up a background job to run Table Statistics?

My purpose is to have a step in a several step background job, that will initicate a subset of table statistics at a specific point in the sequence.

I've explored the "external command" part of a background job, but can't seem to get it going.

Ken

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

HI

I prefer using this script and schedule it from SQL Agent in management studio. It will update only those tables whose statistics are outdated.

use SID

sp_updatestats

Thanks

Mushtaq

former_member524429
Active Contributor
0 Kudos

Hi,

My purpose is to have a step in a several step background job, that will initicate a subset of table statistics at a specific point in the sequence.

Why you want to run Update statistic in sequence ? Any reason ?

You can use [Update Statistics Task (Maintenance Plan) |http://msdn.microsoft.com/en-us/library/ms178678.aspx]of SQL Enterprise Studio.

Or Take the benefit of scheduler of SQL Ent. Studio and try with the below mentioned SQL command for multiple tables.

UPDATE STATISTICS <table names> WITH FULLSCAN

Regards,

Bhavik G. Shroff

ken_halvorsen2
Active Participant
0 Kudos

It's not that I have to update the statistics in sequence.

It's that during a billing run batch job with several steps, the correspondence print step runs about 3 times faster if I run the statistics of some billing tables after the invoicing step.

So between these steps of the one billing background job, I would like to create a step that does an external call to the SQL script I have. So far all I have been doing is scheduling it to run at a specific time at the OS level, which may or may not coinside with the background job steps run time (Best Guess type of timing).

Any ideas anyone?

Former Member
0 Kudos

Can you please tell us the parameter value stats_change_threshold from init<SID>.sap. The default value is 50, if you change to 5 then I don't think so you need any additional statistics job.

Read the documentation for stats_change_threshold on help.sap.com

ken_halvorsen2
Active Participant
0 Kudos

Thanks Sunil

I've checked and unfortunately can not find a file called init<sid>.sap (where our <sid> is R3P).

Is that file possibly for Oracle db's only? We're using SQL Server 2005 here.

Ken

ken_halvorsen2
Active Participant
0 Kudos

For anyone interested, I have found a very simple way to run the Statistics from a background job.

1st create a SQL script to run the Statistics and save in the work directory

for example: USE <DBNAME>

GO

exec sp_updatestats

2nd create a DOS .bat file to start that script

for example: sqlcmd -U <user> -P <password> -i <sql_script.sql>

3rd create a background job, using External program for the step, pointing to the DOS.bat file

How easy is that? I hope this saves some other poor slob some time.