February 12, 2014 at 11:22 am
Benki Chendu (2/12/2014)
GilaMonster (2/12/2014)
Benki Chendu (2/12/2014)
Gila - I would need help in showing my client a document which says 5000 is an agreed value. I can then convince them to tweak my SCOM values.You mean like the book which I referenced in my first reply?
Yeah. Like an MS article or something from a blog
So, like the book that I've already referenced then.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 6:14 pm
GilaMonster (2/12/2014)
Benki Chendu (2/12/2014)
GilaMonster (2/12/2014)
Benki Chendu (2/12/2014)
Gila - I would need help in showing my client a document which says 5000 is an agreed value. I can then convince them to tweak my SCOM values.You mean like the book which I referenced in my first reply?
Yeah. Like an MS article or something from a blog
So, like the book that I've already referenced then.
Which was peer reviewed through a pretty thorough technical editing process. More than any blog.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2014 at 7:58 pm
Grant Fritchey (2/12/2014)
GilaMonster (2/12/2014)
Benki Chendu (2/12/2014)
GilaMonster (2/12/2014)
Benki Chendu (2/12/2014)
Gila - I would need help in showing my client a document which says 5000 is an agreed value. I can then convince them to tweak my SCOM values.You mean like the book which I referenced in my first reply?
Yeah. Like an MS article or something from a blog
So, like the book that I've already referenced then.
Which was peer reviewed through a pretty thorough technical editing process. More than any blog.
Which involved some of the top notch SQL people from around the world (writing and reviewing).
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
February 13, 2014 at 12:22 am
It is not uncommon for the PLE to crash to a very low number for a while if you run a query that requires a lot of data to be read into memory from a large table.
Start by looking at how PLE is behaving over the course of the day.
Does it usually stay at a fairly high level, and then suddenly dip below your alert level for a little while? If that is happening, it is probably not causing much of a problem.
If it's usually very close to your alert level with dips below the alert level, then you should be more concerned. You might need more memory, or need to tune queries to reduce the page turnover, or maybe add indexes. This is not an area with simple answers.
February 13, 2014 at 3:23 am
First as suggested by Gail and others, you need to limit your instances to a max memory or sometimes even you will find difficult to login to the OS if needed or the one instance may cause problems for others.
Observe a trend since you are already recording some data. See if this goes low during backups because the database pages are read into memory while a backup is running. As users not to leave their connection open. At times, my own management studio takes 300-600 MB when I am logged in to the server but I avoid that usually.
During the low PLE intervals, look to find what data is there in the cache. You can use DMVs to find which tables and indexes are there in the cache at a given time. There could be some bad queries running at those time which are scanning too much of data than needed due to poor indexes or queries or bad execution plan.
Easier said than done but all the best:-D
Thanks
Chandan
February 13, 2014 at 3:32 am
chandan_jha18 (2/13/2014)
See if this goes low during backups because the database pages are read into memory while a backup is running.
Backups don't run through the buffer pool so they cannot be the cause of low PLE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2014 at 4:18 am
GilaMonster (2/13/2014)
chandan_jha18 (2/13/2014)
See if this goes low during backups because the database pages are read into memory while a backup is running.Backups don't run through the buffer pool so they cannot be the cause of low PLE.
I had read this in one of the articles from Paul Randal's blog and found this mention in other places too where during the backup times, to read the data for taking the backup, it will flush out some data. Sorry if I interpret it in a wrong way, but can you tell me how will the backup occur if it does not flush other existing pages from memory?
Thanks
Chandan
February 13, 2014 at 5:16 am
chandan_jha18 (2/13/2014)
I had read this in one of the articles from Paul Randal's blog and found this mention in other places too where during the backup times, to read the data for taking the backup, it will flush out some data.
Not on Paul's blog. I'm sure there are blogs that state that data is flushed from cache during backups, but they would be wrong.
Sorry if I interpret it in a wrong way, but can you tell me how will the backup occur if it does not flush other existing pages from memory?
A separate memory area for the backup buffers. Backups do not run via the buffer pool
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2014 at 5:49 am
GilaMonster (2/13/2014)
chandan_jha18 (2/13/2014)
I had read this in one of the articles from Paul Randal's blog and found this mention in other places too where during the backup times, to read the data for taking the backup, it will flush out some data.Not on Paul's blog. I'm sure there are blogs that state that data is flushed from cache during backups, but they would be wrong.
Sorry if I interpret it in a wrong way, but can you tell me how will the backup occur if it does not flush other existing pages from memory?
A separate memory area for the backup buffers. Backups do not run via the buffer pool
So if I am having a dedicated SQL box where lets say out of 32 GB, I am allocating 28 or 30 GB to SQL, am I leaving no room for backups to happen fast enough ?
February 13, 2014 at 5:56 am
30's above what's generally recommended for an instance on a server with that much memory. See the book and cbhapter which I recommended earlier in this thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2014 at 6:53 am
Co-incidentally, the instances that are running on dedicated nodes are not throwing the PLE alerts. I see these alerts only in scenarios where more number of instances run on a same node/server with all of them having dynamic memory set.
I am now looking at setting a memory value at the first level and then see how the servers react.
February 20, 2014 at 7:44 am
Benki Chendu (2/20/2014)
Co-incidentally, the instances that are running on dedicated nodes are not throwing the PLE alerts. I see these alerts only in scenarios where more number of instances run on a same node/server with all of them having dynamic memory set.I am not looking at setting a memory value at the first level and then see how the servers react.
Yes that would be an expected side effect of not having max memory set on a server with multiple instances. This will complicate the calculation for max memory a bit.
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 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply