Update Stats job

Question

Update Stats job

SkyBVI

SSCoach

Points: 15128
More actions
November 9, 2010 at 6:14 am

#240925

Hi,
I run a update statistics job in hte weekend on one of our databases, the size of the database is 100GB. It starts at 1 am in night and when I come in the morning, it stills shows executing. I tried to run sp_who2 to see whats going on and results are:-
status shows its suspended and CPU time is huge with disk I/O time too.
So, I have to stop that job in the morning.
What should i do?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

SKYBVI (11/9/2010)
Hi,
I run a update statistics job in hte weekend on one of our databases, the size of the database is 100GB. It starts at 1 am in night and when I come in the morning, it stills shows executing. I tried to run sp_who2 to see whats going on and results are:-
status shows its suspended and CPU time is huge with disk I/O time too.
So, I have to stop that job in the morning.
What should i do?
Regards,
Sushant

First check is there any lock or block.

If nothing is there then find the spid and kill it and check the status.

What command did you run for update the statistics.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

SkyBVI SSCoach Points: 15128 More actions · Answer 2

@ mutthu

There is no blocking

In morning i stop the job, so whats use of killing spid ?

I run this through a maintenance plan-->

update statistics task--> tables and views--> update(all exisitng stats)

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

SKYBVI (11/9/2010)
@ mutthu
There is no blocking
In morning i stop the job, so whats use of killing spid ?
I run this through a maintenance plan-->
update statistics task--> tables and views--> update(all exisitng stats)
Regards,
Sushant

First of all, why update all statistics? For most applications, it's generally 80% read, 20% write - which means that only 20% of your data should need updating the statistics often.

Also, are you running the update statistics alongwith rebuild/reorganize of the indexes?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

SKYBVI (11/9/2010)
@ mutthu
There is no blocking
In morning i stop the job, so whats use of killing spid ?
I run this through a maintenance plan-->
update statistics task--> tables and views--> update(all exisitng stats)
Regards,
Sushant

How you stop the job ?

Killing a spid is like terminates a connection/user process.

As i told kill the spid with statusonly.

How often you are update the statistics for that Db ?

Do you have any rebuild jobs ?

if yes Whats the schedule ?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ nakul

I am not running update stats with rebuild/reorganize.

Iam just running update stats every weekend or once in two weeks.

@ mutthu

I stop the job by going into jobs, right clicking it, stop job..

I update stats once in a week or once in 2 weeks.

I run rebuild jobs once in a month.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

rajanishvibha 3763 SSC Enthusiast Points: 111 More actions · Answer 6

Hi

my Name Is VIBHAVESH

I wants to know Backup Policy

I Have 2 TB database(SQL SERVER DB) could you help me what will be best backup policy (how to take backup of huge database)

and when on what time

Geoff A SSChampion Points: 11511 More actions · Answer 7

hows the disk space where you have tempdb?

I have seen what you are describing once before and it was because my drive where tempdb was not big enough to handle the next update.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

rajanishvibha 3763 (11/9/2010)
Hi
my Name Is VIBHAVESH
I wants to know Backup Policy
I Have 2 TB database(SQL SERVER DB) could you help me what will be best backup policy (how to take backup of huge database)
and when on what time

VIBHAVESH,

Please start the new thread.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 9

I believe the delay could be because of a number of reasons (I have seen delays due to I/O latch issues, wherein the hard-drive was not able to respond in time to the data requirements). I guess that the best way to start is to run the following query (may need some fine-tuning) to identify the wait type when the waiting next occurs.

select es.session_id,es.status,wt.exec_context_id,wt.wait_type,wt.blocking_session_id,wt.blocking_exec_context_id, wt.resource_description, wt.wait_duration_ms

from sys.dm_exec_sessions es

inner join sys.dm_os_waiting_tasks wt on es.session_id = wt.session_id

where es.is_user_process = 1

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

SkyBVI SSCoach Points: 15128 More actions · Answer 10

@ vibhavesh

Dude, plz ask your question in a new post.

@geoff

My temdb mdf file is 1.9 GB and its stored on c drive having 48 GB free space..

Also,.

i noticed the full backup of my database takes 5 hrs to complete. is it too much time(100gb db)

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 11

@ nakul

Do I have to run the script while the job status is exectuing?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 12

I stop the job by going into jobs, right clicking it, stop job..

Stoping the job is not run next time.

I update stats once in a week or once in 2 weeks.

I run rebuild jobs once in a month.

Did you enable the auto update stats.

If your maintenance plan takes much more time for the 100gb DB.you can use sp_updatestats it only updates the statistics if needed.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

SkyBVI SSCoach Points: 15128 More actions · Answer 13

@ mutthu

The auto update stats is enabled.

So, I will run the t sql sp_updatestats from hte maintennace tasks and see how it goes..

Also, I noticed the full daily backups of hte database takes 5 hrs t ocomplete (100gb db)

Is it normal?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

SKYBVI (11/9/2010)
@ mutthu
The auto update stats is enabled.
So, I will run the t sql sp_updatestats from hte maintennace tasks and see how it goes..
Also, I noticed the full daily backups of hte database takes 5 hrs t ocomplete (100gb db)
Is it normal?
Regards,
Sushant

It depends.

What type of disk level are you use for the backups ?

Did you run the backup local or N/W?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/