Job performance

Question

Job performance

SQLArch

Ten Centuries

Points: 1322
More actions
March 16, 2005 at 11:41 pm

#152367

I have nightly job which insert/ updates daily transactions. Some nights it runs very fast but some nights becomes absolutely slow. I checked the work load of insert and updates when it is slow, the work load is almost identical but throughput is reduced. I checked system memory/ CPU, both are available and not completely utilized. What can be most common reasons for this behaviour? How should I go to find, what is hampering?
Please help.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 1

Hi,

The first thing what comes in mind is your serverload (what is running on the same time (jobs, backups, SUS, Virus scanning, etc.). Try to find out what is happening.

Second thing I should wonder what the server has been doing that particular day. A lot of insert / delete will cause index fragmentation and un-necessary page allocation (and parhaps page splitting) . An Update statictics will speed up ...

Third if you are using database links ( or DTS with VB components pointing to your filesystem or ousite your server ) monitor your network availability and network traffic.

First aid on your problem.....

GKramer

The Netherlands

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 2

Hi,

In addition to what Kramer has said I would also suggest to look for any blocking or locking issues. You may set a trace flag to see if there are any locking issues.

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

SQLArch Ten Centuries Points: 1322 More actions · Answer 3

I am using linked server to get data and it seems to me the issue is there.

There aren't any connections in db and no other maintenence jobs running.

I see one unusual thing is that in linked server, it doesn't have connection for host process. this used to be there when process runs fast. This means host server connects to linked server, gets data and close connection, so process is slow. In the situation of faster run nights, the connection is always open with state sleeping when not doing else show running when getting data.

Any body know why and how some time linked server has connection open with SPID assigned and some time no SPID assigned? How can I check for network availability?

AJ

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 4

This is probably not your main issue, but I recommend it in any case. If you're not already doing so, ensure that all your jobs specify "SET NOCOUNT ON" as the first command. We've seen dramatic improvements in performance by doing this (2.5 hours reduced to 12 minutes, for example)

SQLArch Ten Centuries Points: 1322 More actions · Answer 5

The procs and even triggers are already using nocount ON.

Does anybody know how can I increase DiskIO for process. Like I use DBCC WAKEUP to get the process running if it in sleep mode.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

Add more spindles to increase disk i/o

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 7

If you've got a fill factor set then this could increase the amount of io that SQL needs to do to retreive the data.

I tend to have ours set around the 90% mark to prevent page splits whilst still offering decent io performance.

Forgot to add that you need to know your data before twiddling with things like fill factors, if you don't know what you're doing then it's probably best to leave it at the default setting.