Performance in a single SQL Server 2014 database is slower than the rest [closed]

Posted on

Question :

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.

Answer :

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.

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/

Leave a Reply

Your email address will not be published. Required fields are marked *