1

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).

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.

migrated from stackoverflow.com Mar 22 '18 at 21:09

This question came from our site for professional and enthusiast programmers.

0

Also try to look the estimated execution plan. Besides, while executing your SP open a new session and look for wait_stats. That will give you more hints on what is your query waiting on.

  • Thanks to all. I'm still working on the execution plan. This is an of-the-shelf ERP system with custom stored procedures added for functionality. I need to talk to the application manager as this is one of the queries she wrote. If I try to get an execution plan using CTRL+L I get a coding error. However, if I just execute the query outright it runs ok. I should have some kind of update today. – thatstevecena Mar 26 '18 at 11:01
  • Ok; 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][1]. This is the [execution plan for the "good" database][2]. The only difference between the two is the "good" DB doesn't have the current data. 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. [1]: brentozar.com/pastetheplan/?id=rJeXYYU9M [2]: brentozar.com/pastetheplan/?id=r1Kh9KI5G – thatstevecena Mar 27 '18 at 12:05
  • I'm still not having any luck. If I compare the query execution plans between those that work and the one that doesn't they're almost identical except for warnings on various commands. As I have a working copy of the DB at the latest revision, it's looking like something must have gotten corrupted when I upgraded the database from 2005 to 2014. I'm going to keep at it as performance is just killing this one department. – thatstevecena Apr 2 '18 at 12:28
  • 1
    It has turned out that the performance issues were not in fact related to just this one database, but a much wider spread issue. I apologize for the lack of updates. – thatstevecena May 17 '18 at 19:12
0

SQL Server 2014 cardinality estimator can cause performance issue sometime. Run your query by adding OPTION(QUERYTRACEON 9481) at end of your query and check if this is issue with Cardinatliy Estimator.

Refer https://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.