SQL Server 2012 - Performance Problem With a Single Stored Proc in PROD but NOT UAT
Posted on 2014-08-14
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!