SQL Server CPU Issues
We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, and run the following test:
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @Loops <= 100000
BEGIN
IF COALESCE('123', '456') = '456'
PRINT 1
SET @Loops = @Loops + 1
END
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:
Total CPU time: 1150
Total milliseconds: 13473
On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:
Total CPU time: 2
Total milliseconds: 1183
I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.
Is there some SQL Server setting that might be configured wrongly? Any idea how to troubleshoot this issue?
Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.
sql-server
asked 14 mins ago
PatrickPatrick
1563
add a comment |
We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, and run the following test:
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @Loops <= 100000
BEGIN
IF COALESCE('123', '456') = '456'
PRINT 1
SET @Loops = @Loops + 1
END
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:
Total CPU time: 1150
Total milliseconds: 13473
On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:
Total CPU time: 2
Total milliseconds: 1183
I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.
Is there some SQL Server setting that might be configured wrongly? Any idea how to troubleshoot this issue?
Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.
sql-server
asked 14 mins ago
PatrickPatrick
1563
Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
– Learning_DBAdmin
13 mins ago
No I haven't, I don't really see how they are relevant. Will SQL Server be parallelizing that testcase? Anyway values (on both databases) are "max degree of parallelism"=1 and "cost threshold for parallelism"=5
– Patrick
8 mins ago
add a comment |
We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, and run the following test:
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @Loops <= 100000
BEGIN
IF COALESCE('123', '456') = '456'
PRINT 1
SET @Loops = @Loops + 1
END
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:
Total CPU time: 1150
Total milliseconds: 13473
On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:
Total CPU time: 2
Total milliseconds: 1183
I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.
Is there some SQL Server setting that might be configured wrongly? Any idea how to troubleshoot this issue?
Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.
sql-server
asked 14 mins ago
PatrickPatrick
1563
We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, and run the following test:
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @Loops <= 100000
BEGIN
IF COALESCE('123', '456') = '456'
PRINT 1
SET @Loops = @Loops + 1
END
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:
Total CPU time: 1150
Total milliseconds: 13473
On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:
Total CPU time: 2
Total milliseconds: 1183
I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.
Is there some SQL Server setting that might be configured wrongly? Any idea how to troubleshoot this issue?
Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.
sql-server
sql-server
asked 14 mins ago
PatrickPatrick
1563
asked 14 mins ago
PatrickPatrick
1563
asked 14 mins ago
PatrickPatrick
1563
asked 14 mins ago
PatrickPatrick
1563
asked 14 mins ago
PatrickPatrick
1563
1563
Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
– Learning_DBAdmin
13 mins ago
No I haven't, I don't really see how they are relevant. Will SQL Server be parallelizing that testcase? Anyway values (on both databases) are "max degree of parallelism"=1 and "cost threshold for parallelism"=5
– Patrick
8 mins ago
add a comment |
Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
– Learning_DBAdmin
13 mins ago
No I haven't, I don't really see how they are relevant. Will SQL Server be parallelizing that testcase? Anyway values (on both databases) are "max degree of parallelism"=1 and "cost threshold for parallelism"=5
– Patrick
8 mins ago
Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
– Learning_DBAdmin
13 mins ago
Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
– Learning_DBAdmin
13 mins ago
No I haven't, I don't really see how they are relevant. Will SQL Server be parallelizing that testcase? Anyway values (on both databases) are "max degree of parallelism"=1 and "cost threshold for parallelism"=5
– Patrick
8 mins ago
No I haven't, I don't really see how they are relevant. Will SQL Server be parallelizing that testcase? Anyway values (on both databases) are "max degree of parallelism"=1 and "cost threshold for parallelism"=5
– Patrick
8 mins ago
add a comment |
0
active
oldest
votes
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230636%2fsql-server-cpu-issues%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230636%2fsql-server-cpu-issues%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
– Learning_DBAdmin
13 mins ago
No I haven't, I don't really see how they are relevant. Will SQL Server be parallelizing that testcase? Anyway values (on both databases) are "max degree of parallelism"=1 and "cost threshold for parallelism"=5
– Patrick
8 mins ago