locked
Queries Performing Very Poorly after the Migration RRS feed

  • General discussion

  • Dear Team,

    Recently we have migrated our database server from SQL Server 2000 to SQL Server 2012.

    After the database migration the query is used by the cognos reporting services are running very slow, Where as the same reports are running as expected in the SQL Server 2000.

    After the migration we are followed the belosteps to ensure the database consitency on the new SQL Server 2012 server.

    1. Updateusage for all the databases.

    2. Rebuilded all the Indexes exists on all the databases.

    3. Planned to run the update statistics for the all the tables.

    Could you please let us know the cause why the queries are prforming poorly compared to the existing SQL Server 2000 server.

    • Changed type Sofiya Li Tuesday, December 24, 2013 7:50 AM discussion will be better
    Monday, December 23, 2013 10:05 AM

All replies

  • Hello,

    There is a massive change from SQl serevr 2000 to SQl serevr 2012.Did you made any changes to your queries or made changes to you application code before pointing it to 2012. I assume you did not .This was most important thing you should have done

    Is this problem due to single query couple or whole database is slow?


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

    Monday, December 23, 2013 10:11 AM
  • We took database backup from SQL Server 2000 and restored the same into SQL Server 2012.

    Completetion of the restoration we have pointed our Cognos Server to the new database server(SQL Server 2012).

    Performance issue is for more number of queries, we have not made any changes to the querires.

    New Server :

    DB : SQL Server 2012

    App Server : Cognos 10 CS

    OS : Windows 2008 R2

    New Server :

    DB : SQL Server 2000

    App Server : Cognos 8 CS

    OS : Windows 2003

    Monday, December 23, 2013 12:34 PM
  • We took database backup from SQL Server 2000 and restored the same into SQL Server 2012.

    Completetion of the restoration we have pointed our Cognos Server to the new database server(SQL Server 2012).

    Performance issue is for more number of queries, we have not made any changes to the querires.


    Hello,

    If you try to restore SQL 2000 backup to SQl 2012 it wont allow you .It will flash error.Did you restored it on SQL 2005/2008/2008 r2 and then restored on 2012.Because restore will give error if you move directly.

    Also did you made sure you changed compatibility level of database from 80 to 110.

    You should consider involving Cognos team to tune queries.Give them all queries that are performing bad.


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

    Monday, December 23, 2013 12:40 PM
  • >Planned to run the update statistics for the all the tables.

    What does that mean? You really have to run it.

    Turn on Auto Update Statistics and Auto Update Statistics Asynchronously database options.

    UPDATE STATISTICS every night and check for missing indexes.

    Optimization article: http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Edited by Kalman Toth Monday, December 23, 2013 12:59 PM
    Monday, December 23, 2013 12:59 PM
  • >Planned to run the update statistics for the all the tables.

    What does that mean? You really have to run it.

    Turn on Auto Update Statistics and Auto Update Statistics Asynchronously database options.

    UPDATE STATISTICS every night and check for missing indexes.

    Optimization article: http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Hello Kalman,

    I would not recommend to turn Auto update stats on Update stats Async Database option.There is known issue with MS for above options

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

    I would always suggests to keep it disabled unless you have very good reason for that


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

    Monday, December 23, 2013 1:06 PM
  • Hi Shanky,

    I would appreciate if you post your comments at the thread on the auto update statistics topic:

    http://social.msdn.microsoft.com/Forums/en-US/2645d883-0228-49f9-a305-76e4b783ef20/autoupdatestatisticsasync-on-or-off-for-oltp-database?forum=sqldatabaseengine

    Thanks.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, December 23, 2013 1:59 PM
  • Hi Palaniappan,

    According to your description, in my opinion, the type of this question is changed to discussion. It will be better and more experts will focus on this issue and assist you. As other post, there may be many causes which make the performance poorly after migrating database from SQL Server 2000 to SQL Server 2012. I recommend you check the execution plans for the queries that are slow. And use SQL Server Profiler and Dynamic Management Views(DMV) to monitor the performance of server and database. For example, we can  query the dm_os_wait_stats dynamic to get all the waits encountered by threads that executed and so on.

    Hope it can help.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Tuesday, December 24, 2013 7:49 AM