none
Upgrade SQL Server 2005 to 2012

    Question

  • After upgrading from SQL 2005 to 2012, skipping 2005, users are experiencing random slowdowns. Where should I look for the problem?
    Thursday, April 24, 2014 7:20 PM

Answers

  • After upgrading from SQL 2005 to 2012, skipping 2005, users are experiencing random slowdowns. Where should I look for the problem?

    Believe me that is not a simple question to answer.My first question would be

    1. Did you updated stats for all databases after migration

    2. Did you made sure indexes were all defragmented.

    3. Did you tested your application on 2012.This you should have done.Look for slow performing queries

    select 
        highest_cpu_queries.plan_handle, 
        highest_cpu_queries.total_worker_time,
        q.dbid,
        q.objectid,
        q.number,
        q.encrypted,
        q.[text]
    from 
        (select top 50 
            qs.plan_handle, 
            qs.total_worker_time
        from 
            sys.dm_exec_query_stats qs
        order by qs.total_worker_time desc) as highest_cpu_queries
        cross apply sys.dm_exec_sql_text(plan_handle) as q
    order by highest_cpu_queries.total_worker_time desc
    

    When you find out queries which are taking long time .Look for there estimated query plan .That will tell you missing indexes or scans going if any.

    Finally you can refer to this link  checklist for analyzing slow query




    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, April 24, 2014 7:26 PM