This is an interesting one... I've inherited a SQL Server for our ERP system. It currently runs SQL Server 2014 CU 10. I've done some best practices and performance tuning on it and server performance has gone from the toilet to acceptable levels.
However, I have a query/stored procedure that when run on one specific database takes up to 7 minutes to run while on similar databases (there are 6 others, 1 has MUCH more data) it takes maybe 30 seconds tops. I've checked file permissions, I've diff'ed the SP's, I've checked database settings and I can't find anything that points to the slowness.
Does anyone have any recommendations on what I should be looking at?
I was able to get an execution plan excerpt from the databases (2MB limit on Brent's site).
- This is the execution plan on the "bad" database.
- This is the execution plan for the "good" database.
The only difference between the two is the "good" DB doesn't have the current data (i.e. older copy).
I've double checked my server settings in both environments & they appear to be identical. The only thing I can confirm so far is if I drop the "bad" database's compatibility level down to 2008 it performs perfectly. Yet the same database inside the dev environment at 2014 runs flawlessly.
There is a possible coding error in the stored procedure I'm checking. I can't get anything out using CTRL+L but I do if I attempt to run it.
- The databases are not replicated. The server is a VM and the drives are all shared.
- I've done a table comparison & they are identical. Compatibility level is the same as well as are indexes and stats.
I'll update again when I know more.