SQL 2016 - High page writes, but normal/low everything elseSSAS queries time out when Windows File System...
What is the wife of a henpecked husband called?
How can I get my players to come to the game session after agreeing to a date?
Does SQL Server 2017, including older versions, support 8k disk sector sizes?
How can animals be objects of ethics without being subjects as well?
CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHY
How to limit sight distance to 1 km
One Half of Ten; A Riddle
Advice for a new journal editor
Why is the copy constructor called twice in this code snippet?
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
Writing a character who is going through a civilizing process without overdoing it?
Find some digits of factorial 17
Why do stocks necessarily drop during a recession?
Better VM Ubuntu on Windows 10 or VM Windows 10 on Ubuntu?
Explain the objections to these measures against human trafficking
How do Chazal know that the descendants of a Mamzer may never marry into the general populace?
How long is the D&D Starter Set campaign?
Why Prushim were the ones who "separated"?
If I delete my router's history can my ISP still provide it to my parents?
The weather forecast
Normalization for two bulk RNA-Seq samples to enable reliable fold-change estimation between genes
Why isn't there a non-conducting core wire for high-frequency coil applications
Why did the villain in the first Men in Black movie care about Earth's Cockroaches?
Consequences of lack of rigour
SQL 2016 - High page writes, but normal/low everything else
SSAS queries time out when Windows File System Cache dropsHow can a connection for one DB block a connection to another DB in SQL Server?High Page Faults/sec low Page Reads/secLow CPU Utilization but High Signal WaitsHow can I Debug a Buffer Issue?SQL SERVER 2016 high CPUHigh number of page reads and PLE counter, but only when tracing with ProfilerSQL 2016 low TPM with HammerDB-Testhigh writelog wait on OLTP system and fast SSD storage, log flush is slowSQL Server 2008 R2 Performance Degradation - High Lazy writes/sec and free list stalls/sec
I am a sysadmin and I am not very well versed in SQL metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.
This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.
The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.
Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?
So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?
sql-server performance sql-server-2016
asked 8 mins ago
blizzblizz
1013
add a comment |
I am a sysadmin and I am not very well versed in SQL metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.
This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.
The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.
Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?
So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?
sql-server performance sql-server-2016
asked 8 mins ago
blizzblizz
1013
add a comment |
I am a sysadmin and I am not very well versed in SQL metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.
This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.
The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.
Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?
So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?
sql-server performance sql-server-2016
asked 8 mins ago
blizzblizz
1013
I am a sysadmin and I am not very well versed in SQL metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.
This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.
The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.
Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?
So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?
sql-server performance sql-server-2016
sql-server performance sql-server-2016
asked 8 mins ago
blizzblizz
1013
asked 8 mins ago
blizzblizz
1013
asked 8 mins ago
blizzblizz
1013
asked 8 mins ago
blizzblizz
1013
asked 8 mins ago
blizzblizz
1013
1013
add a comment |
add a comment |
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f231008%2fsql-2016-high-page-writes-but-normal-low-everything-else%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 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.
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%2fdba.stackexchange.com%2fquestions%2f231008%2fsql-2016-high-page-writes-but-normal-low-everything-else%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
