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!
Thanks!
John
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!
Thanks!
John
Aneesh
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
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.
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.
Hi,
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.
HTH
David
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.
HTH
David
Vendor's application it's running in the same machine where's the SQL Server database?
ASKER
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 - The vendor's application is running on a separated machine. There are 2 machines involved: an app server and a separate DB server.
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, ....?
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, ....?
ASKER
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.
John
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We happened to find a solution on our own. I'm reporting back what it is so others may benefit from the knowledge.