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

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!


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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
Scott PletcherSenior DBACommented:
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.
David ToddSenior DBACommented:

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.

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Vendor's application it's running in the same machine where's the SQL Server database?
trzesniakjAuthor Commented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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, ....?
trzesniakjAuthor Commented:
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.

trzesniakjAuthor Commented:
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)."

See http://support.microsoft.com/kb/276220



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trzesniakjAuthor Commented:
We happened to find a solution on our own.  I'm reporting back what it is so others may benefit from the knowledge.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.