none
Performance

    Question

  • Recently we have migrated our production database from MSSQL2005 to MSSQL2008r2.

    we have done all the post migration steps

    Changing Compatibility 

    DBCC UPDATEUSAGE(DBNAME)

    DBCC CHECKDB(DBNAE)

    EXEC sp_Updatestats

    Our application team are complaining that they have slow performance.

    we have check all the memory , hits, diksi/o all are fine.

    when we investigate, we notice that the columns the tables use a lot are spatial data type, geometry. is that problem?

    Please help me, what could be the cause.

    THanks

    Rajesh


    Thursday, April 10, 2014 6:18 PM

Answers

  • Hi Rajesh,

    Rebuilding of indexes will help to resolve this problem. You need to rebuild all indexes for migrated databases.

    We encountered similar issue last year which was resolved by rebuilding of indexes for pending tables indexes.


    SM

    Friday, April 11, 2014 10:47 AM

All replies

  • Please post the results of SELECT @@VERSION.

    Have you installed Service Pack 2?

    Please see:

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

    Thursday, April 10, 2014 7:23 PM
  • Just check the indexes and run the index maintenance job once to correct the index fragmentation, once again cross check all the compatibility levels, 

    Raju Rasagounder MSSQL DBA

    Friday, April 11, 2014 1:20 AM
  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 
    Friday, April 11, 2014 8:49 AM
  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Please install SP2 as already guessed by Tom.

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

    Friday, April 11, 2014 9:27 AM
  • when i check  avg_fragmentation_in_percent one of the table index it shows 98. I think i need to rebuild the index ? is that might be the cause?

    Friday, April 11, 2014 10:28 AM
  • Hi Rajesh,

    Rebuilding of indexes will help to resolve this problem. You need to rebuild all indexes for migrated databases.

    We encountered similar issue last year which was resolved by rebuilding of indexes for pending tables indexes.


    SM

    Friday, April 11, 2014 10:47 AM
  • when i check  avg_fragmentation_in_percent one of the table index it shows 98. I think i need to rebuild the index ? is that might be the cause?


    Please rebuild all indexes and Update stats as well.This should be need to follow process when you see performance degrade

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

    Saturday, April 12, 2014 9:48 AM