SQL Server Performance

SS2000 Performance - Unconvincing

Discussion in 'Performance Tuning for DBAs' started by john, Mar 3, 2003.

  1. john New Member

    Has anyone experienced performance degradation after converting / uprading from SS7 -> SS2000 ? .... wrt executing a reporting stored proc. that creates dynamic sql and makes extensive use of temporary tables.

    If so, what measures were taken to progress towards a final resolution ?

    Our scenario:

    SS7 code was executed in isolation. ie. it was the only instance running on the box. When the SS7 job had completed, the SS7 instance was shutdown and the SS2000 instance was started .... then the SS2000 code was executed

    - Identical h/ware (The SS7 and SS2000 were installed on the same box)
    - Identical schema, indexes, triggers, views & procs
    - Db files on identical RAID5 array
    - Stats updated
    - Tables reindexed
    - data & procedure caches purged

    Note that the temporary table(s) were NOT indexed.

    TIA

    John
  2. Argyle New Member

    Before each run reboot the server so that the environment is identical.

    1. Set sql 7 to autostart, sql 2000 to disabled
    2. Reboot server
    3. Run query on sql 7. write down performance, save execution plan etc.
    4. Set sql 2000 to autostart, sql 7 to disabled
    5. Reboot server
    6. Run query on sql 2000. write down performance, save execution plan etc.
    7. compare performance and plans.

    Edit:
    Also make sure that tempDB is the same size (and large enough) on both sql 7 and 2000. Dont want it to autogrow when the query is run.

    /Argyle
  3. bradmcgehee New Member

    When converting from SQL 7.0 to 2000, it is very important to update the statistics for all indexes in all your databases, as statistics are using differently between these two versions, and using SQL Server 7.0 statistics for 2000 will hurt performance.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page