sql server tempDB files on RAMDisk AND physical disk
We find, for our application, locating TempDB files on RAMDisk improves performance significantly. But we don't want to allocate more RAM to RAMDisk than necessary. Nor do we want SQL Server to fail because it can't grow TempDB. I remember reading that you could put one TempDB file (one data, one log) on physical disk, and permit them to grow, and have the rest (several data, 1 log) on RAMDisk, and do not permit them to grow.
But ... is there any way to stop SQL Server from using those slow TempDB files until the other fast files are full?
Thank you in advance
sql-server ramdisk tempdb asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
add a comment |
We find, for our application, locating TempDB files on RAMDisk improves performance significantly. But we don't want to allocate more RAM to RAMDisk than necessary. Nor do we want SQL Server to fail because it can't grow TempDB. I remember reading that you could put one TempDB file (one data, one log) on physical disk, and permit them to grow, and have the rest (several data, 1 log) on RAMDisk, and do not permit them to grow.
But ... is there any way to stop SQL Server from using those slow TempDB files until the other fast files are full?
Thank you in advance
sql-server ramdisk tempdb asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are reducing it by reducing the amount of RAM available for buffering. SQL Server like all databases will use all available memory to cache as much data as possible to avoid IO.
– Panagiotis Kanavos
Nov 22 '18 at 8:07
1
Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (hint,hint) to store intermediate results. The fact that there's so much intermediate data is also a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps?
– Panagiotis Kanavos
Nov 22 '18 at 8:09
1
Tempdb is used by normal processes too, like snapshot isolation. Identify the real reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files
– Panagiotis Kanavos
Nov 22 '18 at 8:12
add a comment |
We find, for our application, locating TempDB files on RAMDisk improves performance significantly. But we don't want to allocate more RAM to RAMDisk than necessary. Nor do we want SQL Server to fail because it can't grow TempDB. I remember reading that you could put one TempDB file (one data, one log) on physical disk, and permit them to grow, and have the rest (several data, 1 log) on RAMDisk, and do not permit them to grow.
But ... is there any way to stop SQL Server from using those slow TempDB files until the other fast files are full?
Thank you in advance
sql-server ramdisk tempdb asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
We find, for our application, locating TempDB files on RAMDisk improves performance significantly. But we don't want to allocate more RAM to RAMDisk than necessary. Nor do we want SQL Server to fail because it can't grow TempDB. I remember reading that you could put one TempDB file (one data, one log) on physical disk, and permit them to grow, and have the rest (several data, 1 log) on RAMDisk, and do not permit them to grow.
But ... is there any way to stop SQL Server from using those slow TempDB files until the other fast files are full?
Thank you in advance
sql-server ramdisk tempdb sql-server ramdisk tempdb asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
asked Nov 22 '18 at 8:01
Paul DavisPaul Davis
31
31
Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are reducing it by reducing the amount of RAM available for buffering. SQL Server like all databases will use all available memory to cache as much data as possible to avoid IO.
– Panagiotis Kanavos
Nov 22 '18 at 8:07
1
Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (hint,hint) to store intermediate results. The fact that there's so much intermediate data is also a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps?
– Panagiotis Kanavos
Nov 22 '18 at 8:09
1
Tempdb is used by normal processes too, like snapshot isolation. Identify the real reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files
– Panagiotis Kanavos
Nov 22 '18 at 8:12
add a comment |
Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are reducing it by reducing the amount of RAM available for buffering. SQL Server like all databases will use all available memory to cache as much data as possible to avoid IO.
– Panagiotis Kanavos
Nov 22 '18 at 8:07
1
Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (hint,hint) to store intermediate results. The fact that there's so much intermediate data is also a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps?
– Panagiotis Kanavos
Nov 22 '18 at 8:09
1
Tempdb is used by normal processes too, like snapshot isolation. Identify the real reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files
– Panagiotis Kanavos
Nov 22 '18 at 8:12
Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are reducing it by reducing the amount of RAM available for buffering. SQL Server like all databases will use all available memory to cache as much data as possible to avoid IO.
– Panagiotis Kanavos
Nov 22 '18 at 8:07
Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are reducing it by reducing the amount of RAM available for buffering. SQL Server like all databases will use all available memory to cache as much data as possible to avoid IO.
– Panagiotis Kanavos
Nov 22 '18 at 8:07
1
1
Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (hint,hint) to store intermediate results. The fact that there's so much intermediate data is also a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps?
– Panagiotis Kanavos
Nov 22 '18 at 8:09
Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (hint,hint) to store intermediate results. The fact that there's so much intermediate data is also a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps?
– Panagiotis Kanavos
Nov 22 '18 at 8:09
1
1
Tempdb is used by normal processes too, like snapshot isolation. Identify the real reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files
– Panagiotis Kanavos
Nov 22 '18 at 8:12
Tempdb is used by normal processes too, like snapshot isolation. Identify the real reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files
– Panagiotis Kanavos
Nov 22 '18 at 8:12
add a 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
});
}
});
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%2f53426281%2fsql-server-tempdb-files-on-ramdisk-and-physical-disk%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 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.
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%2f53426281%2fsql-server-tempdb-files-on-ramdisk-and-physical-disk%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
Easy. Fix your queries instead of trying to trick SQL Server. Instead of improving performance you are reducing it by reducing the amount of RAM available for buffering. SQL Server like all databases will use all available memory to cache as much data as possible to avoid IO.
– Panagiotis Kanavos
Nov 22 '18 at 8:07
1
Your problem is high tempdb usage, not that tempdb is slow. You get high tempdb usage when there's not enough RAM available (hint,hint) to store intermediate results. The fact that there's so much intermediate data is also a sign of trouble - are there missing indexes? Is SQL Server forced to read and sort a lot of data because there are no indexes on the fields used by a query? Are you using temporary tables and table variables excessively perhaps?
– Panagiotis Kanavos
Nov 22 '18 at 8:09
1
Tempdb is used by normal processes too, like snapshot isolation. Identify the real reason for high tempdb usage. If it's normal operations, use fast storage like an SSD to hold the tempdb files
– Panagiotis Kanavos
Nov 22 '18 at 8:12