Sfrgttk

When to use the term transposed instead of modulation?

Naming Characters after Friends/Family

If nine coins are tossed, what is the probability that the number of heads is even?

Short story about an infectious indestructible metal bar?

What is better: yes / no radio, or simple checkbox?

Why won't the strings command stop?

Practical reasons to have both a large police force and bounty hunting network?

Are small insurances worth it

Is there a math equivalent to the conditional ternary operator?

Can a space-faring robot still function over a billion years?

“I had a flat in the centre of town, but I didn’t like living there, so …”

Remove object from array based on array of some property of that object

Replacing tantalum capacitor with ceramic capacitor for Op Amps

What is the purpose of a disclaimer like "this is not legal advice"?

3.5% Interest Student Loan or use all of my savings on Tuition?

Affine transformation of circular arc in 3D

I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?

Problems with rounding giving too many digits

Why are special aircraft used for the carriers in the United States Navy?

Sundering Titan and basic normal lands and snow lands

Can inspiration allow the Rogue to make a Sneak Attack?

What is the oldest European royal house?

What is the meaning of option 'by' in TikZ Intersections

What is "desert glass" and what does it do to the PCs?

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

Query Plans Change Performance Worse in SQL Server 2014Query is slow in SQL Server 2014, fast in SQL Server 2012CPU Time Slower in SQL Server 2014 than in SQL Server 2008 R2Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit?CPU Time Slower in SQL Server 2014 than in SQL Server 2008SQL Server changes execution plan - Part 2Performance issues after moving a database to SQL Server 2014Update stats w/full scan on SQL Server 2014 uses 100% cpu, on 2008 R2, 15%Same query way faster with distinct than without distinctProblem with database migrated from SQL Server 2005 to SQL Server 2014

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

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

sql-server sql-server-2014 query-performance

share|improve this question

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

bumped to the homepage by Community♦ 16 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

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

add a comment | 

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

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

sql-server sql-server-2014 query-performance

share|improve this question

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

bumped to the homepage by Community♦ 16 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

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

add a comment | 

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.

sql-server sql-server-2014 query-performance

share|improve this question

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

share|improve this question

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

share|improve this question

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

edited Mar 26 '18 at 16:42

Paul White♦

52.9k14281457

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

asked Mar 22 '18 at 14:20

thatstevecenathatstevecena

83

2 Answers
2

active

oldest

votes

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.

share|improve this answer

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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/

share|improve this answer

answered Apr 7 '18 at 15:05

ASPASP

1363

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f202059%2fperformance-in-a-single-sql-server-2014-database-is-slower-than-the-rest%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

share|improve this answer

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

answered Mar 23 '18 at 19:44

Pablo JavierPablo Javier

594

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/

share|improve this answer

answered Apr 7 '18 at 15:05

ASPASP

1363

add a comment | 

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/

share|improve this answer

answered Apr 7 '18 at 15:05

ASPASP

1363

add a comment | 

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/

share|improve this answer

answered Apr 7 '18 at 15:05

ASPASP

1363

share|improve this answer

answered Apr 7 '18 at 15:05

ASPASP

1363

answered Apr 7 '18 at 15:05

ASPASP

1363

answered Apr 7 '18 at 15:05

ASPASP

1363