Post reply

Help on performance issues using SQL Server 2012

  • May 31, 2013 at 5:03 am

    #394885

    Hi,

    I need help on performance issues with our new database server using SQL Server 2012. A migration was done over the weekend. And we moved several databases to a new data center. Now the ERP system is slow and users cannot get their work done.

    I created a reindex and reorganize scripts, we were using a job but that was taking too long and failing. I ran a fragmentation analysis and using a threshold of 20% for reindexing and everything else reorganize. This enable the job to complete. However the performance is still awful. The database server shares multiple databases, one of the recommendations I've made was to have a dedicated server for the ERP system.

    There is an audit trail file that hasn't been purged in about 10 years. Could this be the reason for this bad performance. It's affecting all systems pointing to this database server.

    I didn't have a say in this decision - Now the business is ticked.

    Any help would be greatly appreciated. And if none if this works we need to migrate back to SQL Server 2005.

  • May 31, 2013 at 5:39 am

    #1620020

    Have you checked the memory settings in the new server? Is Maximum Server Memory limited?

    Is the audit trail file an external file or is it in the database?

  • May 31, 2013 at 6:12 am

    #1620031

    Not sure what you mean. Can you be more specific?

    Regards,

  • May 31, 2013 at 6:42 am

    #1620042

    We have 24GB of RAM where in the old servers were running 32GBs where the sys admin is stating the additional reads from no indexing caused massive delays.

    Not sure what that statement means.

  • May 31, 2013 at 7:20 am

    #1620060

    Also these servers are VMs not physical servers.

  • May 31, 2013 at 9:17 pm

    #1620255

    If you want the system diagnosed and fixed (if possible) ASAP, the ONLY way to do that with any reasonable chance for success is to hire a qualified performance tuning consultant and get them access to the system. Failing that:

    1) have you run a file IO stall analysis?

    2) have you run a wait stats analysis?

    3) did you do the MANDATORY UPDATE of EVERY statistics on every updated database WITH FULL SCAN??

    4) What build are you on? Some builds have some VERY significant issues/bugs.

    5) MANY other things could be at play here ...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply