itagent007
asked on
SQL jobs and code running slow
I have a 64-bit SQL Server 2005 install with 4 processors and 16 GBs of RAM. When running jobs executing stored procedures on it, it takes over 14 hours to complete. When it was originally on SQL Server 2000 32-bit, it ran just under 2 hours. With the upgrade, I would assume it would run faster. The DBs were detached and attached to 2005 when the move was made. 15 GBs of RAM was allocated to the SQL Server, leaving a 1 GB for the OS. Is there some configuration I'm missing that has severly slowed down things? Please help!
ptjcb
Have you run Profiler against the stored procedures? Have you rebuilt the indexes?
ASKER
No, the indexes were not rebuilt. Memory and CPU are 100% utilized. Bring over the databases and just attaching them, do I need to rebuild the indexes if they were already rebuilt?
Yes. Index statistics are probably out of date. SQL Query Optimizer uses the statistics to determine the cost of each query. If the statistics are out of date the optimizer may be choosing bad indexes or none at all.
Yes, you *must* update the statistics when going from one version of SQL to another. You do not have to rebuild the indexes, just UPDATE STATISTICS on *all* user tables.
ASKER
Updating statistics will make the query go faster? Is there anything else I should look at or perform as the 4 CPU's are 100% utilized right now.
ASKER
I ran the suggested updates and DBCC commands (updateusage, reindex and update statistics) and that did not help! Is there something that I'm missing?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.