SQL Server CPU Issues












0















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.










share|improve this question























  • 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


















0















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.










share|improve this question























  • 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
















0












0








0








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.










share|improve this question














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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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





















  • 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












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
});


}
});














draft saved

draft discarded


















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
















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

Liste der Baudenkmale in Friedland (Mecklenburg)

Single-Malt-Whisky

Czorneboh