February 12, 2014 at 8:48 am
I get the following alert atleast 20 times a day for a couple of my production instances:
Life Expectancy of SQL instance "production" on computer "xxxxxxx" server is too low.
How do I fix this? My client isn't ok with tweaking the threshold value in SCOM:-P
February 12, 2014 at 8:52 am
Chapter 4: http://www.red-gate.com/community/books/accidental-dba
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 8:55 am
We will need some more information than what you have provided.
Are you currently experiencing issues with the production server?
What is the normal baseline for PLE on that server?
How much memory is installed on that server?
What is the max server memory set to?
Is your system 32 or 64 bit?
I would start with this video[/url] by Brent Ozar and go from there.
February 12, 2014 at 9:11 am
Keith Tate (2/12/2014)
We will need some more information than what you have provided.Are you currently experiencing issues with the production server?
What is the normal baseline for PLE on that server?
How much memory is installed on that server?
What is the max server memory set to?
Is your system 32 or 64 bit?
I would start with this video[/url] by Brent Ozar and go from there.
We are not reported of any issues so far.
Current PLE is set at 300 seconds/5 minutes
Total memory on the instance is 64 GB but we have 4 instances, all with dynamic memory allocated(no max memory)
System is 64 bit.
February 12, 2014 at 9:14 am
Benki Chendu (2/12/2014)
Current PLE is set at 300 seconds/5 minutesTotal memory on the instance is 64 GB
300 is insanely low for that. 300 was a too low recommendation when servers had 4GB of memory. For 64 GB, 5000 would be a worrying number.
but we have 4 instances, all with dynamic memory allocated(no max memory)
There's a problem to start with
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 9:16 am
Total memory on the instance is 64 GB but we have 4 instances, all with dynamic memory allocated(no max memory)
I would recommend reading the book (not just the chapter) that Gail recommended and also setting your Max Server Memory on all instances. You could be experiencing one instance starving the other instances of memory.
February 12, 2014 at 9:18 am
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.
February 12, 2014 at 9:21 am
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?
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 9:25 am
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.
Hiding the problem by changing your SCOM thresholds is a complete waste of time. Low PLE is a real issue that needs to be addressed... following the advice already given is a good place to start
February 12, 2014 at 9:36 am
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
February 12, 2014 at 9:51 am
The book that Gail mentioned is the reference, but changing the SCOM alerting threshold is not the point. The point is that you need to understand memory management and monitoring first. Get more knowledge of your memory settings and performance then create a baseline and then worry about creating thresholds for your environment.
February 12, 2014 at 9:55 am
Keith Tate (2/12/2014)
The book that Gail mentioned is the reference, but changing the SCOM alerting threshold is not the point. The point is that you need to understand memory management and monitoring first. Get more knowledge of your memory settings and performance then create a baseline and then worry about creating thresholds for your environment.
Sure. Thanks.
February 12, 2014 at 9:59 am
Gail, apologies in advance for hijacking the thread but can you confirm something for me? In that chapter (4) the Lock Pages in Memory states:
Initially, in SQL Server 2005 and 2008, Lock Pages in Memory was an Enterprise-only
feature. However, it was added to SQL Server Standard Edition in SQL Server 2008 R2, and can be used in SQL Server 2008 SP1 with Cumulative Update 2 or later, and in SQL Server 2005 SP3 with Cumulative Update 4 or later. Having applied the appropriate update to the server, the startup Trace Flag –T845 must be added to the SQL Server service startup parameters for the instance, in order to begin using the feature. Since the memory allocated using Lock Pages in Memory is locked and cannot be paged, it is recommended that the max server memory sp_configure option be set to limit the amount of memory that SQL Server can use, and so prevent starving the OS of memory
Does this mean that in addition to setting "Lock pages in memory" in the Local Security policy, you must also add the -T845 startup trace flag in order for it to have any effect?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 12, 2014 at 10:06 am
See the following KB article on when you need to add the -T845 trace flag: http://support.microsoft.com/kb/970070
February 12, 2014 at 10:14 am
Thanks Keith - this answers my question: "Note This trace flag is not required for customers who use Enterprise 64-bit editions or Developer 64-bit editions of SQL Server. Enabling this trace flag on these editions has no effect. " So I'd need to add that trace flag to my Standard Edition instances.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply