March 25, 2017 at 12:29 am
Sean Lange - Friday, March 24, 2017 12:25 PMWe are back to you needing to ask a question. What that means is you need to provide enough details of your situation that others have a chance at helping you. At this point all we know is you think there is too much memory pressure because "It is experiencing high Page reads/sec, low PLE , high free list stalls and high value of lazy writes". We don't even know what those values are. Keep in mind we can't see your screen, have no idea what your architecture or hardware is like. The ONLY information we have is what you have posted...and at this point you haven't posted anything resembling details. Look at all the people on this thread trying to pry the details out of you. We all want to help you. But for us to be able to do that requires that you provide us those details. Unless you can do that nobody can do anything to help you.
free list stalls/sec=20
lazy writes/sec=7639
page reads/sec=1060131091
PLE=306
March 25, 2017 at 12:32 am
Jeff Moden - Friday, March 24, 2017 2:53 PMJeff Moden - Friday, March 24, 2017 10:40 AMcoolchaitu - Friday, March 24, 2017 10:07 AMInstance is under memory pressure. How to relieve it from pressureThe two most common causes of this are 1) Not enough memory to begin with and 2) a whole lot of bad code.
Let's start with #1. How much memory has been assigned to the instance, how much has been setup for Max Memory in SQL, and how much memory has been reserved for the Operating System? Also, has the memory been "thin provisioned" through a VM or is it permanently provisioned?
While I agree that the question is a bit vague, we can still work on it. We first need answers to my questions above.
Max server memory=27 GB
memory for OS=3GB
It is a VM
March 25, 2017 at 12:35 am
Eirikur Eiriksson - Friday, March 24, 2017 1:38 PMChrisM@home - Friday, March 24, 2017 12:40 PMSean Lange - Friday, March 24, 2017 12:25 PMWe are back to you needing to ask a question. What that means is you need to provide enough details of your situation that others have a chance at helping you. At this point all we know is you think there is too much memory pressure because "It is experiencing high Page reads/sec, low PLE , high free list stalls and high value of lazy writes". We don't even know what those values are. Keep in mind we can't see your screen, have no idea what your architecture or hardware is like. The ONLY information we have is what you have posted...and at this point you haven't posted anything resembling details. Look at all the people on this thread trying to pry the details out of you. We all want to help you. But for us to be able to do that requires that you provide us those details. Unless you can do that nobody can do anything to help you.Anadin.
The English food is questionable, the English clothing challenging but the English humor is superb, Gold on this one mate!
😎
free list stalls/sec=20
lazy writes/sec=7639
page reads/sec=1060131091
PLE=306
March 25, 2017 at 10:11 am
coolchaitu - Saturday, March 25, 2017 12:32 AMJeff Moden - Friday, March 24, 2017 2:53 PMJeff Moden - Friday, March 24, 2017 10:40 AMcoolchaitu - Friday, March 24, 2017 10:07 AMInstance is under memory pressure. How to relieve it from pressureThe two most common causes of this are 1) Not enough memory to begin with and 2) a whole lot of bad code.
Let's start with #1. How much memory has been assigned to the instance, how much has been setup for Max Memory in SQL, and how much memory has been reserved for the Operating System? Also, has the memory been "thin provisioned" through a VM or is it permanently provisioned?
While I agree that the question is a bit vague, we can still work on it. We first need answers to my questions above.
Max server memory=27 GB
memory for OS=3GB
It is a VM
Is ballooning enabled?
In addition, memory reserved for OS is too little for a 27GB machine. You should be sitting at about 6GB for the OS.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2017 at 1:30 am
SQLRNNR - Saturday, March 25, 2017 10:11 AMcoolchaitu - Saturday, March 25, 2017 12:32 AMJeff Moden - Friday, March 24, 2017 2:53 PMJeff Moden - Friday, March 24, 2017 10:40 AMcoolchaitu - Friday, March 24, 2017 10:07 AMInstance is under memory pressure. How to relieve it from pressureThe two most common causes of this are 1) Not enough memory to begin with and 2) a whole lot of bad code.
Let's start with #1. How much memory has been assigned to the instance, how much has been setup for Max Memory in SQL, and how much memory has been reserved for the Operating System? Also, has the memory been "thin provisioned" through a VM or is it permanently provisioned?
While I agree that the question is a bit vague, we can still work on it. We first need answers to my questions above.
Max server memory=27 GB
memory for OS=3GB
It is a VMIs ballooning enabled?
In addition, memory reserved for OS is too little for a 27GB machine. You should be sitting at about 6GB for the OS.
RAM=30 GB
March 30, 2017 at 8:14 am
coolchaitu - Thursday, March 30, 2017 1:30 AMRAM=30 GB
I'm pretty sure that you've just discovered the reason for your "memory pressure". 30GB isn't enough room to even change your mind if you're doing anything substantial. The two steps to fix this would be like I said... 1) add more memory and 2) fix the code so that it's not so memory intensive.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2017 at 8:40 am
Jeff Moden - Thursday, March 30, 2017 8:14 AMcoolchaitu - Thursday, March 30, 2017 1:30 AMRAM=30 GBI'm pretty sure that you've just discovered the reason for your "memory pressure". 30GB isn't enough room to even change your mind if you're doing anything substantial. The two steps to fix this would be like I said... 1) add more memory and 2) fix the code so that it's not so memory intensive.
Its just a Dev server.
March 30, 2017 at 9:18 am
coolchaitu - Thursday, March 30, 2017 8:40 AMJeff Moden - Thursday, March 30, 2017 8:14 AMcoolchaitu - Thursday, March 30, 2017 1:30 AMRAM=30 GBI'm pretty sure that you've just discovered the reason for your "memory pressure". 30GB isn't enough room to even change your mind if you're doing anything substantial. The two steps to fix this would be like I said... 1) add more memory and 2) fix the code so that it's not so memory intensive.
Its just a Dev server.
If it's "just" a Dev server why does it matter if it's taking a long time to get things to work? If it's "just" a dev server, it doesn't matter.
On the other had, if it's a true dev environment, then you should be aiming to have it similarly spec'ed (if not the same), as your testing and production environment. That way you can truly develop in an environment that you know will reflect and work in UAT and Prod.
Otherwise, if you won't/can't upgrade, as Jeff as strongly recommended, improve your SQL (which will help UAT and Prod anyway), or otherwise you'll have to bite the bullet.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 30, 2017 at 9:28 am
Thom A - Thursday, March 30, 2017 9:18 AMcoolchaitu - Thursday, March 30, 2017 8:40 AMJeff Moden - Thursday, March 30, 2017 8:14 AMcoolchaitu - Thursday, March 30, 2017 1:30 AMRAM=30 GBI'm pretty sure that you've just discovered the reason for your "memory pressure". 30GB isn't enough room to even change your mind if you're doing anything substantial. The two steps to fix this would be like I said... 1) add more memory and 2) fix the code so that it's not so memory intensive.
Its just a Dev server.
If it's "just" a Dev server why does it matter if it's taking a long time to get things to work? If it's "just" a dev server, it doesn't matter.
On the other had, if it's a true dev environment, then you should be aiming to have it similarly spec'ed (if not the same), as your testing and production environment. That way you can truly develop in an environment that you know will reflect and work in UAT and Prod.
Otherwise, if you won't/can't upgrade, as Jeff as strongly recommended, improve your SQL (which will help UAT and Prod anyway), or otherwise you'll have to bite the bullet.
Personally I kind of prefer to have the dev server be underpowered compared to QA and Prod. If you can make it work fast on an underpowered server then it is more likely to perform better on a real server when there is also lots of traffic.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 30, 2017 at 9:39 am
Sean Lange - Thursday, March 30, 2017 9:28 AMThom A - Thursday, March 30, 2017 9:18 AMcoolchaitu - Thursday, March 30, 2017 8:40 AMJeff Moden - Thursday, March 30, 2017 8:14 AMcoolchaitu - Thursday, March 30, 2017 1:30 AMRAM=30 GBI'm pretty sure that you've just discovered the reason for your "memory pressure". 30GB isn't enough room to even change your mind if you're doing anything substantial. The two steps to fix this would be like I said... 1) add more memory and 2) fix the code so that it's not so memory intensive.
Its just a Dev server.
If it's "just" a Dev server why does it matter if it's taking a long time to get things to work? If it's "just" a dev server, it doesn't matter.
On the other had, if it's a true dev environment, then you should be aiming to have it similarly spec'ed (if not the same), as your testing and production environment. That way you can truly develop in an environment that you know will reflect and work in UAT and Prod.
Otherwise, if you won't/can't upgrade, as Jeff as strongly recommended, improve your SQL (which will help UAT and Prod anyway), or otherwise you'll have to bite the bullet.
Personally I kind of prefer to have the dev server be underpowered compared to QA and Prod. If you can make it work fast on an underpowered server then it is more likely to perform better on a real server when there is also lots of traffic.
I like that approach as well. The server I like to have close to prod is the "staging" or "uat" server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2017 at 9:42 am
coolchaitu - Thursday, March 30, 2017 1:30 AMSQLRNNR - Saturday, March 25, 2017 10:11 AMcoolchaitu - Saturday, March 25, 2017 12:32 AMJeff Moden - Friday, March 24, 2017 2:53 PMJeff Moden - Friday, March 24, 2017 10:40 AMcoolchaitu - Friday, March 24, 2017 10:07 AMInstance is under memory pressure. How to relieve it from pressureThe two most common causes of this are 1) Not enough memory to begin with and 2) a whole lot of bad code.
Let's start with #1. How much memory has been assigned to the instance, how much has been setup for Max Memory in SQL, and how much memory has been reserved for the Operating System? Also, has the memory been "thin provisioned" through a VM or is it permanently provisioned?
While I agree that the question is a bit vague, we can still work on it. We first need answers to my questions above.
Max server memory=27 GB
memory for OS=3GB
It is a VMIs ballooning enabled?
In addition, memory reserved for OS is too little for a 27GB machine. You should be sitting at about 6GB for the OS.
RAM=30 GB
30gb for the server and 27GB allocated to SQL server is improper configuration for memory. That is problem #1.
Do you have the ballooning feature enabled for the VM? If so, that is problem #2.
The real kicker is the massive index and checkdb maintenance you have running. This is probably being blocked or throttled by another process or another vm on the same host. Top it off with not having the memory properly allocated between OS and SQL and you have this big problem. The two threads you have open are related.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply