April 8, 2009 at 4:55 pm
Hi
One my sql job runs for over 9 hours, it selects 200K records, based on some calculations, the record getting inserted into new table (new table contains 8.5 million record). I need to reduce the time it takes to complete. Can someone provide the suggestions to analyze and improve its performance ?
Thanks in Advance..
Shuaib
April 8, 2009 at 9:22 pm
Provide the SQL being executed, the Actual Query Plan in sqlplan form (XML), attached as a txt file, and the CREATE TABLE statements scripted from the tables including keys & indexes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2009 at 9:57 pm
You should provide the plan, as Barry suggested. The idea is that you look in the plan for what takes a lot of time. Then try to reduce the time. Perhaps you need to tune the SQL, write it more efficiently. Or perhaps you can add or remove indexes.
Without more details about what is slow, it's hard to give specific advice.
Lots of load type operations might disable to remove indexes before they start, and then rebuild them.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 9, 2009 at 7:25 pm
We are using SQL 2000 and there is no option to capture XML excution plan.
THanks
Shuaib
April 9, 2009 at 8:59 pm
sigh* Please note which forum you are in.
Just post the text version then.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 8:12 am
RBarryYoung (4/9/2009)
sigh* Please note which forum you are in.Just post the text version then.
He IS in the 2000 forum, isn't he? 🙂
OP: My guess is that any process that runs for 9 hours is probably WAY to complex to be 'solved' via some forum posts. Hire a tuning consultant for a few hours and he/she should be able to whoop your stuff into shape AND mentor you about how to do it yourself the next time. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 10, 2009 at 8:26 am
Sorry should have noted. I moved this to 2000.
I'd agree with the SQL Guru, hire a consultant if you have no idea where to start. Get them to teach you a few things as they tune.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 10, 2009 at 8:33 am
High-level advice: Break the job down. Run it step by step in a dev environment and see which step(s) are the slow ones. Focus your optimisation work on just the slow pieces.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 8:42 am
Steve Jones - Editor (4/10/2009)
Sorry should have noted. I moved this to 2000.
Heh. You and Kevin really had me going there for a minute. I thought I was completely losing it!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 8:57 am
RBarryYoung (4/10/2009)
Steve Jones - Editor (4/10/2009)
Sorry should have noted. I moved this to 2000.Heh. You and Kevin really had me going there for a minute. I thought I was completely losing it!
I actually figured it had been moved - I just wanted to give you a ribbing. Don't get to do that very often! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2009 at 4:10 pm
Thanks all for the suggestions.
Shuaib
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply