Post reply

Job performance

  • 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.

  • March 17, 2005 at 2:02 am

    #547469

    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

     

  • March 17, 2005 at 3:00 am

    #547485

    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

  • March 17, 2005 at 12:10 pm

    #547640

    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

  • March 22, 2005 at 10:20 am

    #548244

    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)

  • March 22, 2005 at 8:17 pm

    #548359

    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.

     

  • March 23, 2005 at 6:20 am

    #548441

    Add more spindles to increase disk i/o  

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

  • March 23, 2005 at 6:39 am

    #548451

    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.

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

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