SQL Server CPU Utilisation won't exceed 10%
We have a virtual machine running in Azure. When i remote onto the server box desktop and run SQL queries, each query connection will only use 10% of the processing capability of the server. When i simultaneously open two or more connections in SS Management Studio, each query will use 10%. E.g. if i have three connections open running queries, the CPU utilisation will go up to 30%.
Is there a way to change this? I would like each process to be able to utilise more CPU power. There are 16 virtual cores, 112 GB memory.
I'm running CPU work only queries to test, so there aren't other bottlenecks at play for instance io/network/disk limitations.
I've tried stored procs, and stored procs within jobs and the CPU utilisation still remains at 10%.
I've had a good look around the SQL server properties and the virtual machine properties and can't seem to find a setting to change this.
Hope someone can help,
cheers.
Details of SQL platform: Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
CPU work only query used: DECLARE @T DATETIME, @F BIGINT; SET @T = GETDATE(); WHILE DATEADD(SECOND,30,@T)>GETDATE() SET @F=POWER(2,30);
virtual-machine sql-server sql azure
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
add a comment |
We have a virtual machine running in Azure. When i remote onto the server box desktop and run SQL queries, each query connection will only use 10% of the processing capability of the server. When i simultaneously open two or more connections in SS Management Studio, each query will use 10%. E.g. if i have three connections open running queries, the CPU utilisation will go up to 30%.
Is there a way to change this? I would like each process to be able to utilise more CPU power. There are 16 virtual cores, 112 GB memory.
I'm running CPU work only queries to test, so there aren't other bottlenecks at play for instance io/network/disk limitations.
I've tried stored procs, and stored procs within jobs and the CPU utilisation still remains at 10%.
I've had a good look around the SQL server properties and the virtual machine properties and can't seem to find a setting to change this.
Hope someone can help,
cheers.
Details of SQL platform: Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
CPU work only query used: DECLARE @T DATETIME, @F BIGINT; SET @T = GETDATE(); WHILE DATEADD(SECOND,30,@T)>GETDATE() SET @F=POWER(2,30);
virtual-machine sql-server sql azure
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
(I fo postgres, not sql server, so I could be way off base). It sounds like you are either trying to use a single table so sql server can't split the query to parellise it or that it doesn't have the ability to split queries.
– davidgo
Jan 10 at 19:27
add a comment |
We have a virtual machine running in Azure. When i remote onto the server box desktop and run SQL queries, each query connection will only use 10% of the processing capability of the server. When i simultaneously open two or more connections in SS Management Studio, each query will use 10%. E.g. if i have three connections open running queries, the CPU utilisation will go up to 30%.
Is there a way to change this? I would like each process to be able to utilise more CPU power. There are 16 virtual cores, 112 GB memory.
I'm running CPU work only queries to test, so there aren't other bottlenecks at play for instance io/network/disk limitations.
I've tried stored procs, and stored procs within jobs and the CPU utilisation still remains at 10%.
I've had a good look around the SQL server properties and the virtual machine properties and can't seem to find a setting to change this.
Hope someone can help,
cheers.
Details of SQL platform: Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
CPU work only query used: DECLARE @T DATETIME, @F BIGINT; SET @T = GETDATE(); WHILE DATEADD(SECOND,30,@T)>GETDATE() SET @F=POWER(2,30);
virtual-machine sql-server sql azure
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
We have a virtual machine running in Azure. When i remote onto the server box desktop and run SQL queries, each query connection will only use 10% of the processing capability of the server. When i simultaneously open two or more connections in SS Management Studio, each query will use 10%. E.g. if i have three connections open running queries, the CPU utilisation will go up to 30%.
Is there a way to change this? I would like each process to be able to utilise more CPU power. There are 16 virtual cores, 112 GB memory.
I'm running CPU work only queries to test, so there aren't other bottlenecks at play for instance io/network/disk limitations.
I've tried stored procs, and stored procs within jobs and the CPU utilisation still remains at 10%.
I've had a good look around the SQL server properties and the virtual machine properties and can't seem to find a setting to change this.
Hope someone can help,
cheers.
Details of SQL platform: Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
CPU work only query used: DECLARE @T DATETIME, @F BIGINT; SET @T = GETDATE(); WHILE DATEADD(SECOND,30,@T)>GETDATE() SET @F=POWER(2,30);
virtual-machine sql-server sql azure
virtual-machine sql-server sql azure
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
asked Jan 10 at 15:58
Z3ROINZ3ROIN
1
1
(I fo postgres, not sql server, so I could be way off base). It sounds like you are either trying to use a single table so sql server can't split the query to parellise it or that it doesn't have the ability to split queries.
– davidgo
Jan 10 at 19:27
add a comment |
(I fo postgres, not sql server, so I could be way off base). It sounds like you are either trying to use a single table so sql server can't split the query to parellise it or that it doesn't have the ability to split queries.
– davidgo
Jan 10 at 19:27
(I fo postgres, not sql server, so I could be way off base). It sounds like you are either trying to use a single table so sql server can't split the query to parellise it or that it doesn't have the ability to split queries.
– davidgo
Jan 10 at 19:27
(I fo postgres, not sql server, so I could be way off base). It sounds like you are either trying to use a single table so sql server can't split the query to parellise it or that it doesn't have the ability to split queries.
– davidgo
Jan 10 at 19:27
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fsuperuser.com%2fquestions%2f1392808%2fsql-server-cpu-utilisation-wont-exceed-10%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 Super User!
- 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%2fsuperuser.com%2fquestions%2f1392808%2fsql-server-cpu-utilisation-wont-exceed-10%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
(I fo postgres, not sql server, so I could be way off base). It sounds like you are either trying to use a single table so sql server can't split the query to parellise it or that it doesn't have the ability to split queries.
– davidgo
Jan 10 at 19:27