June 3, 2017 at 2:15 pm
Hi,
I got this alert for one DB server.
SQL Server instance xxx - The operating system is paging at 5795.33 pages/second.
Page life expectancy for xxx is currently 356.
How do I trouble shoot and fix this issue?
The server has 98 GB and max server memory to SQL server is 81 GB.
There are 430 DB's in total on the instance and Total memory consumed by all the DB;s is 68 GB. Instance is on SQL Server 2014 Ent Ed ( Virtual Machine and critical Production Instance )
Thanks in Advance.
June 5, 2017 at 5:34 am
PLE may not be a good indicator of performance problems.
I tend to monitor waits and latency:
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
June 5, 2017 at 6:00 am
As a single point of data, there's no indication that there's an issue that needs fixing.
Look at trends over time, not single data points.
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
June 5, 2017 at 8:02 am
You need to first establish a baseline to compare your data against.
As has already been said, you need to look at trends over time, not as a single point in isolation. The danger here is you see a problem that isn't there.
It's also worthwhile looking at other potential bottlenecks at the sometime, namely cpu, disk, network and concurrency.
Whilst they may not seem relevant at the time, they will give you a more complete picture of what is going on, and steer you away from dead-ends.
Also, performance monitoring on a vm is slightly different to that on physical machine. If using perfmon you want to also look towards the hypervisor to see if there are any problems there. Standard "tin" metrics like 'Physical disk' and 'Memory' may show no problem in perfmon, because your hypervisor is having the problem.
Paul Randall's waits library is a great source of information, as already mentioned.
I was also initiated on Brent Ozar's sp_Blitz* suite. I still like to use these BlitzIndex and BlitzCache as they give me quick insights as to what is going on, then I can focus on specific areas from there.
Finally whatever you do TEST YOUR CHANGES FIRST!!! Don't put them straight in to production, you don't know what other problems you could cause.
June 5, 2017 at 8:19 am
Looking into PLE is a good indicator of performance problems.
Short PLE indicates some significant exchange of data between disk system and buffer.
Which is never good for performance.
Usually it points to dominating table/index scans over index seeks, ineffective indexing, low selectivity of queries, probably bad database design.
And you're right - we have no way to tell what causes the problem.
An good T-SQL developer would be quite useful there.
_____________
Code for TallyGenerator
June 5, 2017 at 8:47 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply