December 9, 2015 at 5:01 am
We just upgraded to SQL Server 2014 SP1 CU3 from SQL 2012 SP1 CU6.
For no apparent reason, when monitoring using Idera SQL Diagnostic Manager we are seeing
PLE go from 4000 - 5000 down to 100 at various points throughout the day and night.
We have monitored SQL Server Agent jobs and SQL running at the time this happens and nothing
seems to be taking any more resource than before.
Is there an issue with SQL Server 2014 and Cache that we are missing?
Thanks,
bill goetschius
William.goetschius@blackbaud.com
603-770-1147
December 9, 2015 at 5:05 am
Whats your Server Configuration and memory values
December 9, 2015 at 5:59 am
1) Is this a virtual machine?
2) Do you have anti-virus running on it?
3) You could set up a profile trace to local disk to capture queries with large amounts of reads - say >50000 or 100K or whatever. Done right this is a very efficient thing to do.
4) Have you checked on any non-SQL-Agent batch processes that could be running? Off-server backups, batch jobs, loads, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 9, 2015 at 6:40 am
65 GB Memory
32 CPU's
30,000 min memory
40,000 max memory
Both Cache Hit ratios stay close to 100%
even though the page life expectancy falls
bill
December 9, 2015 at 6:43 am
Cache hit ratios are useless counters. A server has to be under sever, long-term memory pressure before buffer cache hit ratio will dip.
Look for queries that run around those times and do lots of reads. See if you can tune them to read less. Use Extended Events to do this (profiler/SQLTrace is deprecated).
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
December 9, 2015 at 7:03 am
1) Is this a virtual machine? - Yes
2) Do you have anti-virus running on it? - Yes
3) You could set up a profile trace to local disk to capture queries with large amounts of reads - say >50000 or 100K or whatever. Done right this is a very efficient thing to do. - OK
4) Have you checked on any non-SQL-Agent batch processes that could be running? Off-server backups, batch jobs, loads, etc.
There should not be any -
However when I run dbcc memorystatus
Foreign Committed =21165752
How do I found out what is holding that memory?
bill
December 9, 2015 at 7:05 am
1) Is this a virtual machine? - Yes
2) Do you have anti-virus running on it? - Yes
3) You could set up a profile trace to local disk to capture queries with large amounts of reads - say >50000 or 100K or whatever. Done right this is a very efficient thing to do. - OK
4) Have you checked on any non-SQL-Agent batch processes that could be running? Off-server backups, batch jobs, loads, etc.
There should not be any -
However when I run dbcc memorystatus
Foreign Committed = 21165752
How do I found out what is holding that memory?
bill
December 9, 2015 at 12:02 pm
If you can be on the server when PLE drops significantly a script like sp_whoisactive would show the likely suspects...if its within SQL. Had a similar thing and it turned out to be maintenance stored procedures that were embedded in an OLTP application.
'Only he who wanders finds new paths'
December 9, 2015 at 3:27 pm
VM and AV both hold potential to flush your RAM, which flushes your SQL Server performance. 🙂 I would look at those first.
I note I have seen various driver bugs flush RAM too, although I haven't personally come across any in the last oh, 8 years or so.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply