Lock pages in memory for multiple instances on single server

up vote
0
down vote

favorite

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.

edited 2 days ago

Mister Positive

1,98451833

asked 2 days ago

Anyesh Srivastava

New contributor

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
2 days ago

@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
2 days ago

i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
2 days ago

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
2 days ago

straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
yesterday

add a comment |

up vote
0
down vote

favorite

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.

edited 2 days ago

Mister Positive

1,98451833

asked 2 days ago

Anyesh Srivastava

New contributor

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
2 days ago

@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
2 days ago

i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
2 days ago

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
2 days ago

straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
yesterday

add a comment |

up vote
0
down vote

favorite

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.

edited 2 days ago

Mister Positive

1,98451833

asked 2 days ago

Anyesh Srivastava

New contributor

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 2 days ago

Mister Positive

1,98451833

asked 2 days ago

Anyesh Srivastava

New contributor

edited 2 days ago

Mister Positive

1,98451833

asked 2 days ago

Anyesh Srivastava

New contributor

edited 2 days ago

Mister Positive

1,98451833

edited 2 days ago

Mister Positive

1,98451833

edited 2 days ago

Mister Positive

1,98451833

asked 2 days ago

Anyesh Srivastava

New contributor

asked 2 days ago

Anyesh Srivastava

asked 2 days ago

Anyesh Srivastava

New contributor

Anyesh Srivastava is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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
2 days ago

@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
2 days ago

i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
2 days ago

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
2 days ago

straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
yesterday

add a 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
2 days ago

@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
2 days ago

i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
2 days ago

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
2 days ago

straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
yesterday

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
2 days ago

@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
2 days ago

i would assume so. But I can't give you a 100% answer fro the info you have provided.
– Mitch Wheat
2 days ago

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
2 days ago

straight off, i would never recommend "... 8 instances on same server with Windows server 2012 R2"
– Mitch Wheat
yesterday

add a comment |

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

}
});

Anyesh Srivastava is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

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

Name

Required, but never shown

active

oldest

votes

Anyesh Srivastava is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Anyesh Srivastava is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu