locked
Performance of server degraded after upgrading to SQL Server 2008 RRS feed

  • Question

  • Hi,

     

    We have recently upgraded one of our servers to SQL 2008 from SQL 2000.

    But since then we are experiencing slow performance on servers. Users take longer time to run their queries compared to SQL 2000.

    We have already done below things after migration:

     

    Changed cmpt level

    Rebuild all the indexes

    DBCC Updateusage

    Exec sp_updatestats

    DBCC CHECKDB

     

    Then updated SP1 also.

     

    Your inputs will really help us.

    Let me know if you need any more details .

     

    Regards,

    Sameer Golam.

    Friday, July 2, 2010 7:52 AM

All replies

  • Hello,

    Please run the following on your SQL Server instance, and post here the results:

    SELECT TOP 15 *

    FROM sys.dm_os_wait_stats
    ORDER BY wait_time_ms DESC

    Thank you for your cooperation.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Saturday, July 3, 2010 4:19 AM
  • Hi Alberto,

    Here you go..

    wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    REQUEST_FOR_DEADLOCK_SEARCH 82943 414741666 5183 414741666
    XE_TIMER_EVENT 13826 414731893 30218 414729202
    LOGMGR_QUEUE 95583 414717489 12089667 16647
    LAZYWRITER_SLEEP 473034 414714197 1647 142583
    ONDEMAND_TASK_QUEUE 348 414657461 233172581 152
    SQLTRACE_BUFFER_FLUSH 103627 414635372 4416 26396
    CHECKPOINT_QUEUE 10118 413765354 37190335 5327
    XE_DISPATCHER_WAIT 84 413381810 43230090 0
    FT_IFTS_SCHEDULER_IDLE_WAIT 6856 411308873 60105 1966
    SLEEP_TASK 13501771 227147255 2102 1405239
    BROKER_TO_FLUSH 202245 207370238 2603 50607
    CXPACKET 26636905 118803336 1075615 6387933
    BACKUPBUFFER 387563 115198497 2453 25427
    ASYNC_IO_COMPLETION 106 114296162 36279954 28
    BACKUPIO 203463 33227067 8158 3701

     

    Regards,

    Sameer Golam.

     

    Tuesday, July 6, 2010 5:23 AM
  • Hello,

    Please verify there are no missing indexes.

    Try setting “max degree of parallelism”:

    http://support.microsoft.com/kb/2023536

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, July 6, 2010 5:59 AM
  • Hi Alberto,

    We have rebuild all the indices migrated from SQL 2000.

    So there is no possibility of missing any index as performance was good on 2K box.

    Also we have Max DOP set as 0 that is default value.

    We have 2 processors on this server.

    Do you recommend to set Max DOP to 2 ?

    Regards,

    Sameer Golam. 

     

    Tuesday, July 6, 2010 7:21 AM
  • Hello,

    Please configure MAXDOP depending on the number of processors involved. Use the following guidelines:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;329204

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, July 6, 2010 7:27 PM
  • What is software configuration, aka operating system?

    What is hardware configuration?

    Is it the same box?

    Is it desinated box only for SQL Server?

    Is it the same RAM as before?


    Sergei
    Wednesday, July 7, 2010 1:22 AM
  • Hi,

    My first question is SQl server 2008 patched to SP4, if not, there is no point in posting because any thing less than SQL Server 2008 SP4 is not supported at all.

    Since you migrated from SQL Server 2000 to 2008 the optimizer changed, yes there was quite lot of improvements so queries are going to perform out of leauge.

    You need to find problematic queries and tune them.

    Have you set proper max server memory ?

    Have you set proper degree of parallelism ?

    Have you set tempdb files according to physical cores you have ?

    The wait stats hows nothing aprt from CXPACKET waits all other are mostly benign wait types


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Friday, April 3, 2015 7:46 AM