Job running slow

[TomR100]

I had a job on a SQL 7 database that ran in about seven minutes. The database was moved from SQL 7 to SQL 2000 because the user's processes and jobs were dogging on SQL 7. Now the user's processes and jobs are faster but my job went from a seven minute run to one hour and fifteen minutes.

There have been no changes made to the code recently. I was just wondering if anyone could point us to some kind of answer to this dilemma. For right now we just moved the job to an earlier run time so the file will be done when the user needs it.

 

[WoodLark]

Have you tried re-creating your indexes after moving the database to SQL2000? I don't know if that is the answer, but it might help.

 

[tlbroadbent]

There could be lots of reasons. Statistics need to be updated after upgrading or indexes rebuilt as suggested by Woodlark. Make sure the queries aren't using optimizer hints. Hints that worked in SQL 7 may cause poor performance in SQL 2000. You may need different indexes on the table(s) involved.

SQL Server has great tools for anlayzing queries. See SQL BOL for info about the Index Tuning Wizard, Display Estimated Execution Plan, Statistics IO and Statistics Time. I have gathered several performance resources into one web page. You may find some of them useful.

http://tlbroadbent.home.attbi.com/sql_performance.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.