SQL Server 2012 - Performance Problem With a Single Stored Proc in PROD but NOT UAT

Posted on 2014-08-14
Last Modified: 2014-09-08
Env is SQL Server 2012 (but there was no MS SQL Server 2012 topic to choose from) on Windows Server 2008 R2 Enterprise.

In our PROD env, an upgrade of a vendor application in May made a specific stored proc run slow.  Slow is 2m - 2m30s while calling the stored procedure from SQL Server Management Studio (SSMS).  

The first attempt to resolve included a SQL Server restart and increasing RAM on the SQL Server from 16GB to 32GB. This resolved the issue temporarily.  The next release from the vendor near the end of June saw the issue re-appear.  Again 2m - 2m30sec run time from SSMS.

In an identical UAT env, this whole time the same stored procedure was returning data in the 15 - 23 sec range.  A lot closer to acceptable.

Our most recent attempt at fixing this situation included adding an index, updating statistics, updating usage, and restarting the SQL Server (which recompiles all stored procedures and rebuilds all indexes).  This didn't fix it completely.  But we found that when we recompile the stored procedure, it runs efficiently once and only once (in about 25 seconds). After that, it's slow again.

We've checked for schema differences between PROD and UAT.  None.

We've taken the data from PROD and restored it to the UAT env.  Prob not in the data.

Could there be some setting or setup with SQL Server to check?  

Maybe something with the OS...but what?

Looking for any ideas on where to look/test.

Next, we are going to try to move the UAT DB to the PROD server (renaming the DB, of course) and see if we can recreate it there.  Then the next step would be to create an identical VM with the same server OS and DB versions and restore the PROD DB there to see if we can recreate it.

Yes, we are working with the vendor to solve.  But we are also working on this on our own.  The actual vendor application does not play a part in this.  We are executing these tests at the DB level, cutting the app out of the equation all together.

Any help is greatly appreciated!


Question by:trzesniakj
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    there could be a lot of reasons like, statistics not up to date,  index issues, missing indexes , parameter sniffing,  resource related issues like network, etc.  you need to post the query plan along with the schema. that could be the first step. alternately you can recompile the sp and rerun it on production to see whether it works better
    LVL 68

    Expert Comment

    Given that it's a vendor creation, odds are the indexing is not good (or, more specifically, it's probably God-awful).

    For good overall performance, you need to have the best clustered index on all table.  Otherwise, you're constantly redoing indexes and rewriting virtually every query.  With the best clus index in place, you can concentrate on rewriting the 10-20% of queries that are heavy hitters or monsters and ignore the rest, as they'll already run well anyway.

    Therefore, I suggest reviewing all index usage and missing index info from SQL, at a minimum.
    LVL 35

    Expert Comment

    by:David Todd

    Given that a SQL restart means this procedure runs okay, I suggest that your vendor tests the following:
    1. Disabling parameter sniffing by using local variables in queries and not the parameters
    2. Creating the procedure with the recompile option. If this takes a long time to recompile then obviously not a good solution.

    LVL 44

    Expert Comment

    by:Vitor Montalvão
    Vendor's application it's running in the same machine where's the SQL Server database?

    Author Comment

    Thanks all!  We're starting to attack this issue more today.  

    Vitor - The vendor's application is running on a separated machine.  There are 2 machines involved:  an app server and a separate DB server.
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    That's good (not being in the same box).
    In the other way it let plenty of open reasons for this issue. Your monitoring tools complained on that (warning, errors, ...)?
    And what about auditing the traffic, SQL statements, ....?

    Author Comment

    Hi all - Thanks for the suggestions.  We should have some movement on this issue this week after we try the tests we have scheduled.  I'll report back what we find.


    Accepted Solution

    Hi all,

    Thanks for the suggestions.  Turns out that adding a hint in the stored proc did the trick.

    Inside of the stored procedure we added the hint OPTION (KEEPFIXED PLAN) .

    "The KEEPFIXED PLAN query hint forces the query optimizer to never recompile a query because of changes in statistics or indexed column changes (for example, update, delete, or insert)."




    Author Closing Comment

    We happened to find a solution on our own.  I'm reporting back what it is so others may benefit from the knowledge.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now