Slow running job
-
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
-
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.
-
... 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
-
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
-
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
-
Actually..no I didn't
That is certainly worth a go.
Thanks..Graeme
-
so graeme , did the suggestion work?
please let us know
-
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
-
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
-
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
-
- 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
-
Where can I find more info about
"dbcc showcontig, sp_spaceused"
"dbcc freeproccache "
and a "How To" on performance tuning
Regards
Giovanni
-
Books Online
http://www.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