Along with cbaker's recommendation about tempdb size tuning, there are two areas that should result in better performance times, with near immediate results:
1) Indexes: You will also need to perform some analysis to determine whether or not your existing indexes work most efficiently with the SQL Server 2005 Engine. Based on your time of up to 50 seconds on SQL 2000, and now 18 minutes in SQL 2005, that there is work to be done.
The first step is to collect a set of information with SQL Server Profiler:
* START >> All Programs >> Microsoft SQL Server 2005 >> Performance Tools >> SQL Server Profiler
* File >> New Trace >> Connect to the Server\Instance that will have the data collected.
* General Tab >> Trace Name >> Give it a name you can reference later.
* Use the Template: Choose "Tuning"
* Check "Save to File" option >> browse to a windows folder and give a descriptive file name, again for future reference.
* Set maximum file size (MB): Leave the default as 5
* Enable file rollover >> Leave the default as checked.
* Optional: Enable trace stop time: Choose a time when you want the trace to stop. (Note: you must start the trace manually.)
* Click the "Run" button.
* Click the "Stop" button to complete the data collection or let the timed job finish.
Once your Tuning data trace is finished, you can begin your analysis with another tool, the Database Engine Tuning Advisor or DTA (this is the new version of the SQL 2000 Index Tuning Wizard).
* START >> All Programs >> Microsoft SQL Server 2005 >> Performance Tools >> Database Engine Tuning Advisor
* Login to the Server/Instance that you will be analyzing.
* Workload >> Use the Binocular Icon to browse to your .TRC file save when using the SQL Server Profiler.
* Database for workload analysis: choose your database.
* Select databases and tables to tune: Check the database(s) and table(s) you want to be analyzed.
Tuning Options Tab
* Limit Tuning Time: Decide as to whether or not your want your analysis to end after some time, the default is 60 minutes (1 hour) after the session was opened, uncheck the box for an unlimited amount of time of analysis.
* Physical Design Structures (PDS) to use in database: Choose the best option here for your database.
* Partitioning strategy to employ: Change the default ONLY if you are using partitioning.
* Physical Design Structures (PDS) to keep in database: For your situation, I recommend you check Do not keep any existing PDS. (This will ignore all existing indexes and indexed views in your system!)
* Advanced Options: NOTE: you may only build the recommended changes online if running against SQL Server Enterprise Edition, and no Developer Edition won't let you, I tried.
* Click the green arrow labeled "Start Analysis" near the top of the screen to begin the analysis.
* When the analysis is complete you will be presented with a report and you will have to click the blue link to access the DML that will generate the code for the new Index or new Statistical object that is recommended for your performance increase. Yes, you can copy/paste, but you must open each one individually and run in a Query window. Also note that you can change the names of the index/statistics objects, SQL Server will give an obscure alphanumeric string for the names.
Technically, at this point, you can make the judgement call to drop your existing Indexes and Statistics, before implementing the new objects.
Once your indexes are recreated, you can now rebuild the indexes online, without having to recreate them, as an option in the new Maintenance Plan Wizard. This can be created and scheduled as a SQL Server Agent Job.
2) Statistics: You will need to recreate your statistics, which will help the optimizer do it's job better.
Statistics can be rebuilt, and automated on a schedule as a SQL Server Agent job as a Maintenance Plan.
There is a lot of work involved here, but you have stated that you have already been working at this issue for over a month, and have not been able to make things execute faster. After rebuilding your indexes and statistics, you should see times for your queries in SQL Server
2005 as executing faster than run times with the old SQL Server 2000 system. Keep us posted on your progress and timing results.
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP