Lock pages in memory for multiple instances on single server
0
We have 8 instances out of which 6 are SQL Server 2014, 1 is SQL Server 2017 and all servers are running on Windows Server 2012 R2. The min and max memory set correctly for SQL Server 2014 & 2017. However, one of our DBAs enabled lock pages in memory for SQL Server 2017, but rest do not have this counter enabled.
There is one instance which sometime run into a problem "insufficient memory to process the thread" on same box.
What would be the recommendation for lock pages in memory for server with multiple instances? Should we enable it or not, even if we set min and max memory correctly.
sql-server-2012 sql-server-2014
sql-server-2017 edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
|
show 1 more comment
0
We have 8 instances out of which 6 are SQL Server 2014, 1 is SQL Server 2017 and all servers are running on Windows Server 2012 R2. The min and max memory set correctly for SQL Server 2014 & 2017. However, one of our DBAs enabled lock pages in memory for SQL Server 2017, but rest do not have this counter enabled.
There is one instance which sometime run into a problem "insufficient memory to process the thread" on same box.
What would be the recommendation for lock pages in memory for server with multiple instances? Should we enable it or not, even if we set min and max memory correctly.
sql-server-2012 sql-server-2014
sql-server-2017 edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
brentozar.com/archive/2011/12/consulting-lines-pilot-dog : "“With LPIM, SQLOS will back down its usage to your ‘min server memory’ value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can’t respond efficiently this way, you may face a crash due to Out Of Memory for the OS."
– Mitch Wheat
Nov 19 '18 at 11:08
@MitchWheat, the sql server instance with LPIM disabled is facing this problem. Is SQL Server 2017 with LPIM enabled is related to it ?
– Anyesh Srivastava
Nov 19 '18 at 11:26
i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
Nov 19 '18 at 11:49
Hi @MitchWheat, First of all thanks for your responses :) . Let me try to explain again.There are 8 instances on same server with Windows server 2012 R2, 7 SQL Server 2014 and 1 SQL Server 2017. After analysis, we set min and max memory setting for all 8 SQL instances. LPIM is only enabled for SQL Server 2017. Now, one of the SQL Server 2014 instance is facing problem: There is insufficient memory in resource pool 'default' to run this query. I understand that query need more memory to process but will disabling LPIM for SQL 2017 will help ? Can SQL 2017 LPIM could be causing this problem ?
– Anyesh Srivastava
Nov 19 '18 at 13:23
1
straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
Nov 20 '18 at 3:13
|
show 1 more comment
0
0
0
We have 8 instances out of which 6 are SQL Server 2014, 1 is SQL Server 2017 and all servers are running on Windows Server 2012 R2. The min and max memory set correctly for SQL Server 2014 & 2017. However, one of our DBAs enabled lock pages in memory for SQL Server 2017, but rest do not have this counter enabled.
There is one instance which sometime run into a problem "insufficient memory to process the thread" on same box.
What would be the recommendation for lock pages in memory for server with multiple instances? Should we enable it or not, even if we set min and max memory correctly.
sql-server-2012 sql-server-2014
sql-server-2017 edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
We have 8 instances out of which 6 are SQL Server 2014, 1 is SQL Server 2017 and all servers are running on Windows Server 2012 R2. The min and max memory set correctly for SQL Server 2014 & 2017. However, one of our DBAs enabled lock pages in memory for SQL Server 2017, but rest do not have this counter enabled.
There is one instance which sometime run into a problem "insufficient memory to process the thread" on same box.
What would be the recommendation for lock pages in memory for server with multiple instances? Should we enable it or not, even if we set min and max memory correctly.
sql-server-2012 sql-server-2014
sql-server-2017 sql-server-2012 sql-server-2014
sql-server-2017 edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
edited Nov 19 '18 at 16:57
Mister Positive
2,03151834
2,03151834
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
asked Nov 19 '18 at 11:05
Anyesh SrivastavaAnyesh Srivastava
12
12
brentozar.com/archive/2011/12/consulting-lines-pilot-dog : "“With LPIM, SQLOS will back down its usage to your ‘min server memory’ value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can’t respond efficiently this way, you may face a crash due to Out Of Memory for the OS."
– Mitch Wheat
Nov 19 '18 at 11:08
@MitchWheat, the sql server instance with LPIM disabled is facing this problem. Is SQL Server 2017 with LPIM enabled is related to it ?
– Anyesh Srivastava
Nov 19 '18 at 11:26
i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
Nov 19 '18 at 11:49
Hi @MitchWheat, First of all thanks for your responses :) . Let me try to explain again.There are 8 instances on same server with Windows server 2012 R2, 7 SQL Server 2014 and 1 SQL Server 2017. After analysis, we set min and max memory setting for all 8 SQL instances. LPIM is only enabled for SQL Server 2017. Now, one of the SQL Server 2014 instance is facing problem: There is insufficient memory in resource pool 'default' to run this query. I understand that query need more memory to process but will disabling LPIM for SQL 2017 will help ? Can SQL 2017 LPIM could be causing this problem ?
– Anyesh Srivastava
Nov 19 '18 at 13:23
1
straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
Nov 20 '18 at 3:13
|
show 1 more comment
brentozar.com/archive/2011/12/consulting-lines-pilot-dog : "“With LPIM, SQLOS will back down its usage to your ‘min server memory’ value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can’t respond efficiently this way, you may face a crash due to Out Of Memory for the OS."
– Mitch Wheat
Nov 19 '18 at 11:08
@MitchWheat, the sql server instance with LPIM disabled is facing this problem. Is SQL Server 2017 with LPIM enabled is related to it ?
– Anyesh Srivastava
Nov 19 '18 at 11:26
i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
Nov 19 '18 at 11:49
Hi @MitchWheat, First of all thanks for your responses :) . Let me try to explain again.There are 8 instances on same server with Windows server 2012 R2, 7 SQL Server 2014 and 1 SQL Server 2017. After analysis, we set min and max memory setting for all 8 SQL instances. LPIM is only enabled for SQL Server 2017. Now, one of the SQL Server 2014 instance is facing problem: There is insufficient memory in resource pool 'default' to run this query. I understand that query need more memory to process but will disabling LPIM for SQL 2017 will help ? Can SQL 2017 LPIM could be causing this problem ?
– Anyesh Srivastava
Nov 19 '18 at 13:23
1
straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
Nov 20 '18 at 3:13
brentozar.com/archive/2011/12/consulting-lines-pilot-dog : "“With LPIM, SQLOS will back down its usage to your ‘min server memory’ value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can’t respond efficiently this way, you may face a crash due to Out Of Memory for the OS."
– Mitch Wheat
Nov 19 '18 at 11:08
brentozar.com/archive/2011/12/consulting-lines-pilot-dog : "“With LPIM, SQLOS will back down its usage to your ‘min server memory’ value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can’t respond efficiently this way, you may face a crash due to Out Of Memory for the OS."
– Mitch Wheat
Nov 19 '18 at 11:08
@MitchWheat, the sql server instance with LPIM disabled is facing this problem. Is SQL Server 2017 with LPIM enabled is related to it ?
– Anyesh Srivastava
Nov 19 '18 at 11:26
@MitchWheat, the sql server instance with LPIM disabled is facing this problem. Is SQL Server 2017 with LPIM enabled is related to it ?
– Anyesh Srivastava
Nov 19 '18 at 11:26
i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
Nov 19 '18 at 11:49
i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
Nov 19 '18 at 11:49
Hi @MitchWheat, First of all thanks for your responses :) . Let me try to explain again.There are 8 instances on same server with Windows server 2012 R2, 7 SQL Server 2014 and 1 SQL Server 2017. After analysis, we set min and max memory setting for all 8 SQL instances. LPIM is only enabled for SQL Server 2017. Now, one of the SQL Server 2014 instance is facing problem: There is insufficient memory in resource pool 'default' to run this query. I understand that query need more memory to process but will disabling LPIM for SQL 2017 will help ? Can SQL 2017 LPIM could be causing this problem ?
– Anyesh Srivastava
Nov 19 '18 at 13:23
Hi @MitchWheat, First of all thanks for your responses :) . Let me try to explain again.There are 8 instances on same server with Windows server 2012 R2, 7 SQL Server 2014 and 1 SQL Server 2017. After analysis, we set min and max memory setting for all 8 SQL instances. LPIM is only enabled for SQL Server 2017. Now, one of the SQL Server 2014 instance is facing problem: There is insufficient memory in resource pool 'default' to run this query. I understand that query need more memory to process but will disabling LPIM for SQL 2017 will help ? Can SQL 2017 LPIM could be causing this problem ?
– Anyesh Srivastava
Nov 19 '18 at 13:23
1
1
straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
Nov 20 '18 at 3:13
straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
Nov 20 '18 at 3:13
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373291%2flock-pages-in-memory-for-multiple-instances-on-single-server%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 Stack Overflow!
- 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
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%2fstackoverflow.com%2fquestions%2f53373291%2flock-pages-in-memory-for-multiple-instances-on-single-server%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
brentozar.com/archive/2011/12/consulting-lines-pilot-dog : "“With LPIM, SQLOS will back down its usage to your ‘min server memory’ value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can’t respond efficiently this way, you may face a crash due to Out Of Memory for the OS."
– Mitch Wheat
Nov 19 '18 at 11:08
@MitchWheat, the sql server instance with LPIM disabled is facing this problem. Is SQL Server 2017 with LPIM enabled is related to it ?
– Anyesh Srivastava
Nov 19 '18 at 11:26
i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
Nov 19 '18 at 11:49
Hi @MitchWheat, First of all thanks for your responses :) . Let me try to explain again.There are 8 instances on same server with Windows server 2012 R2, 7 SQL Server 2014 and 1 SQL Server 2017. After analysis, we set min and max memory setting for all 8 SQL instances. LPIM is only enabled for SQL Server 2017. Now, one of the SQL Server 2014 instance is facing problem: There is insufficient memory in resource pool 'default' to run this query. I understand that query need more memory to process but will disabling LPIM for SQL 2017 will help ? Can SQL 2017 LPIM could be causing this problem ?
– Anyesh Srivastava
Nov 19 '18 at 13:23
1
straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
Nov 20 '18 at 3:13