Post reply

Identifying cause of poor perfomance

  • June 16, 2014 at 10:08 am

    #292354

    Hi

    I have an application that is performing slowly in production but is fine in the staging environment. I am fairly sure that SQL Server is not to blame but I need to prove it.

    The data is the same in production as staging.

    I ran some profiler traces on exactly the same clicks on the application in staging and production. I repeated this test three times and found no significant difference between staging and production. This click takes less than a second in staging and about 33 seconds in production. The data below is the sum of all the events from that click excluding the audit log out event.

    Staging

    cpureadswritesDuration

    1095252 125300

    1255200 126437

    1265200 118187

    Production

    cpureadswritesDuration

    9412180 127351

    9312180 108495

    6212360 136811

    Is this enough to prove SQL server is not the cause or are there other tests I can perform?

    I really do appreciate any help you can provide.

    Thanks

    Sarah

  • June 16, 2014 at 10:28 am

    #1722023

    This click takes less than a second in staging and about 33 seconds in production.

    A common cause of this kind of problem is when critical indexes are not moved into production.

    By "this click" you are speaking of an event that executes a specific query / stored procedure? Can you identify a specific query that is fast in staging and slow in prod, capture the query plan in each environment and post it here. See How to Post Performance Problems[/url] for more details.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • June 16, 2014 at 1:01 pm

    #1722080

    Based on the amount of information you have shown, it could be SQL Server. The reads are quite a bit higher I production. I'd suggest getting the execution plan from both databases.

    I know it's your staging environment, but is it an exact copy of production? Same CPUs, memory? How is the database there maintained? Is it the same data? Are the statistics up to date in both environments?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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