SQL Server Performance

From SQL 2000 to SQL 2005

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by jimmyljm, Jan 9, 2007.

  1. jimmyljm New Member

    Hi Guys,

    I've encounter the situation like this. I did a SP on SQL 2000, The SP runs pretty well as in performance. But when i upgrade from SQL 2000 to SQL 2005, the SP performs really bad. I detached the DB from 2000 and copy the MDF files to the new machine and attach it to the 2005 to restore the DB, the machine that the SQL 2005 is running on is better than the one 2000 is running. i'm very new in SQL 2005, so i was wondering if there is any setting or configuration that i need to set in order to achieve the same performance previously.

    Thanks in advance..

    Regards
    Jimmy
  2. thomas New Member

    Did you run update stats after the upgrade?
  3. madhuottapalam New Member

    as thomas told run SP_updatestats to update statistics and DBCC DBREINDEX for each table to rebuild indexes.

    Madhu
  4. MohammedU New Member

    After doing all maintenance check the query plan and i/o stats and compare them with 2000 plan/stats..

    Mohammed U.
  5. jimmyljm New Member

    I've update all statistic and rebuilt the indexes. As for the query plan, is it the execution plan?? as for I/O statistic seems like the 2000 has more scan count than the 2005, then what does it mean by this??
  6. jimmyljm New Member

    btw what is parallelism things in the 2005?? cos i've read the forum, some say there is a bug in parallelism bug in 2005, when working on multiple CPU machine whice is exactly what the machine that i'm running on.. can someone advice me??? thanks.
  7. satya Moderator

  8. MohammedU New Member

    When generating an execution plan for a query, the SQL Server optimizer attempts to choose the plan that provides the fastest response time for that query. If the query#%92s cost exceeds the value specified in the cost threshold for parallelism option and parallelism has not been disabled, then the optimizer attempts to generate a plan that can be run in parallel. A parallel query plan uses multiple threads to process the query, with each thread distributed across the available CPUs and concurrently utilizing CPU time from each processor. The maximum degree of parallelism can be limited server wide using the max degree of parallelism option or on a per-query level using the OPTION (MAXDOP) hint.


    Troubleshooting Performance Problems in SQL Server 2005
    http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#E1BAG




    Mohammed U.

Share This Page