Post reply

Slow running job

  • July 5, 2005 at 3:40 am

    #110318

    Hi,

    This sort of follows on from an earlier post. We have a SQL driven product and periodically we rebuild the server with updated procedures, functions, indexes etc....fairly straightforward.

    I recent rebuild on our test environment has left me a little confused. The databases were completely stripped and rebuilt with new objects. Only a few have changed but we always totally rebuild..this is ok. The indexes and calculation procedures have not changed much so I was not expecting much change in performance.

    However, the overnight job which used to run for 3.5 hours is now running for 9, 10 11 hours and seems to be getting longer.

    There seems to be no single procedure/query that is causing the problem and the data has not changed.

    Does anyone have any suggestions on this one.

    I am currently restoring to a different box to eliminate the actual server. The funny thing is, the same build is on our development server and the calculations are more in line with our expectations i.e. they are quicker.

    We initially thought it was an index problem but surely not all the indexes are suddenly wrong when the data has not changed ??? I'm a bit stuck with this one so any help would be appreciated.

    Has anyone else had any experience of this ???

    Thanks for reading

    Regards..Graeme

  • July 5, 2005 at 4:03 am

    #570821

    Can you get back to your previous design? If so, run the overnight stuff against this. Is it fast? If so, get some execution plans and compare to the new design.

  • July 6, 2005 at 12:25 am

    #571104

    ... The indexes and calculation procedures have not changed much  ....

    Did you change clustering indexes ?

       they influence the actual storage order of rows at the datalevel

       and are the reference for all other indexes !

    So if you didn't adjust your load datasequence, or you've chosen bad, this can have a big impact !

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 6, 2005 at 1:14 am

    #571115

    Hi,

    Thanks for that.

    Yes..that's right...and that would be my initial query.

    The confusing thing is that the same build exists on a development box and the calcs take about 4 hours. That would suggest all the indexes are correct...wouldn't it

    I have recently restored the database on a slightly better server and run the calcs on that server...again the time was about 9 hours...so it must be indexes.

    It is rather hard to know where to start as the development environment is running ok.

    Thanks for your comments

    Regards..Graeme

  • July 6, 2005 at 1:19 am

    #571116

    after you load, did you run

    use yourdb

    go

    dbcc updateusage(0) with count_rows

    go

    sp_updatestats

    go

    Even if you have autostats on, this is needed once in a while, espacialy after full loads or heavy delete/insert batches.

    After that, you can start analyse your statistics and access-plans.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 6, 2005 at 1:36 am

    #571120

    Actually..no I didn't

    That is certainly worth a go.

    Thanks..Graeme

  • July 6, 2005 at 1:44 am

    #571121

    so graeme , did the suggestion work?

    please let us know

  • July 6, 2005 at 1:46 am

    #571122

    please give him some time, the updateusage and sp_updatestatst will also take some time, then reruning the queries and some analisys also need more than 15 minutes

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 6, 2005 at 1:53 am

    #571125

    Easy does it.....

    I have indeed run the update.

    I am re-running some queries....and will definitely let you know the results.

    Fingers-crossed.

    Cheers..Graeme

  • July 7, 2005 at 2:42 am

    #571675

    Hi Guys,

    For those of you who are interested........bad news.

    The calc jobs ran about 2 hours quicker but they are still running at 8-9 hrs.

    I think I need to to dig a bit deeper here. We have a DTS package that runs two hefty store procedures in parallel...I'm wondering if something in one is 'holding up' something in the other..??

    Thanks again for all your input.

    ...hope I can return the favour.

    Cheers...Graeme

  • July 7, 2005 at 2:55 am

    #571683

    - did you take a look a the statistics ? (dbcc showcontig, sp_spaceused... )

    - you might use QA to check the queries using the option "display estimated execution plan" and "show execution plan"

    - you could also use profiler to capture the load and analyse it using the index tuning wizard

    - if there are stored procs involved, maybe dbcc freeproccache can help you to bind new accessplans for all procs.

    I hope this gets you on track

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 11, 2005 at 6:40 am

    #572848

    Where can I find more info about

    "dbcc showcontig, sp_spaceused"

    "dbcc freeproccache "

    and a "How To" on performance tuning

    Regards

    Giovanni

  • July 11, 2005 at 6:44 am

    #572852

    Books Online

    qa.sqlservercentral.com 

    http://www.sql-server-performance.com 

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 13 posts - 1 through 12 (of 12 total)

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