2

We have a production server that dumps it's Page Life Expectancy many times a day (climbs to 2500s and falls back to near 0s)... After watching some training videos about virtualization for SQL Server, I was pointed towards several Perfmon counters to look at:

  • Memory Limit in MB
  • Memory Ballooned in MB
  • Memory Reservation in MB
  • Memory Swapped in MB

Ballooned, Reserved, and Swapped are all at 0 but Memory Limit was set to nearly 3tb (perhaps a default setting like SQL's Max Memory setting...)

I viewed these counters during an event when the PLE dropped to 0 and none of the counters changed. According to Spotlight, Buffer Cache and Procedure Cache are remaining at high % utilization and no stats seemed to really budge except the PLE itself.

To me, this points to not enough RAM allocated to the machine to the point that many of the queries are forcing the server to look to disk to get results.

What steps do I need to take to prove or refute this?

Server Config: - Windows 2008 r2 VM - 4 cores - 12GB RAM - main DB is ~150GB

  • 1
    Is there a standard behavior, such as every 5 minutes or is it more random? – RLF Feb 15 '17 at 20:00
  • It's hard to put down a real pattern... It certainly isn't every 5 minutes... it happened at: 12:03AM | 7:11AM | 7:33AM | 7:44AM | 8:54AM | 10:03AM | 10:46AM < all of these are today – Wes Feb 15 '17 at 20:07
  • 1
    @Wes what is output of select @@version. It seems like you might be hitting the bug. – Shanky Feb 20 '17 at 14:57
  • @Shanky Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) – Wes Feb 21 '17 at 15:54
3

Wes. Those Perfmon counters are a good start to make sure that the VM is configured correctly (meaning, the host isn't wildly overcommitted on RAM.)

However, they don't help size the VM.

If you're having performance problems, you'll want to identify two things:

  1. The SQL Server's top wait during that time, and
  2. The queries causing that wait

To get the top wait, try running sp_BlitzFirst @SinceStartup =1. (Disclaimer: that's an open source script, and I'm one of the authors.) You can also use 3rd party monitoring tools like Idera SQL DM, Quest Spotlight, and SQL Sentry Performance Advisor. (Disclaimer: those cost money, and I didn't write any of those, but they're awesome.)

The @SinceStartup = 1 switch gives you waits since startup, which isn't as cool as monitoring software, but it's a free start.

If your top wait is PAGEIOLATCH, that means reading data pages from a data file. If that's the case, you want to find the queries reading the most data. For that, use sp_BlitzCache @SortOrder = 'reads' (again, disclaimer, open source script, and I'm one of the authors.)

That'll give you the top 10 queries ordered by how much data they're reading. They're usually candidates for index tuning or query tuning. I'd always rather tune those rather than add memory, but if PAGEIOLATCH is your top wait, and you're not allowed to tune those top 10 queries, nor tune their indexes, then the next fix is to add memory to the VM. (But only after going down this route of troubleshooting.)

  • Thanks for the response, Brent. I just finished watching your video on 'Configuring SQL Server for Virtualization' and it was REALLY insightful. I'm going to dive in to what you outlined ASAP! I definitely use your (phenominal) sp_Blitz modules and we also have Spotlight. – Wes Feb 15 '17 at 21:09
  • 1
    @Wes I thought those Perfmon counters sounded familiar. ;-) Glad I could help. – Brent Ozar Feb 15 '17 at 21:11
  • Top three results are PREEMTIVE_XE_DISPATCHER (1575hr wait time), CXPACKET (1487hr wait time), and PAGEIOLATCH_SH (192hr wait time). This kind of makes it seem like PageIOLatch isn't a huge issue. It's AVG ms per wait is 8.2ms. – Wes Feb 15 '17 at 22:02
  • 1
    Yeah, don't bother with the storage - work on queries going parallel and reading a lot of data from RAM. You can still use that same set of sp_BlitzCache parameters to catch them. – Brent Ozar Feb 15 '17 at 23:00
  • 1
    @Wes yeah, this is really beyond what I can troubleshoot here - your best bet is to follow the stuff I posted in the answer. – Brent Ozar Mar 3 '17 at 18:40
1

You can use the sys.dm_io_virtual_file_stats DMV to capture details about how much I/O is occurring against database files.

Since all I/O to non-log files relates directly to requests for data, which is read into the buffer pool, and is captured in the DMV, you can see how much I/O is happening over the period of time when page life expectancy dips. I use the following code to spot-check I/O rates over a 1-minute period, but you can adjust the time period to whatever suits your needs:

DECLARE @t TABLE 
(
    RunNum int NOT NULL
    , DBName sysname NOT NULL
    , DBFileName sysname NOT NULL
    , DBPhysicalFile varchar(260) NOT NULL
    , BytesRead bigint NOT NULL
    , BytesWritten bigint NOT NULL
    , Reads bigint NOT NULL
    , Writes bigint NOT NULL
    , SampleMs bigint NOT NULL
);

INSERT INTO @t 
SELECT 1
    , d.name
    , mf.name
    , mf.physical_name
    , vfs.num_of_bytes_read
    , vfs.num_of_bytes_written
    , vfs.num_of_reads
    , vfs.num_of_writes
    , vfs.sample_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
    INNER JOIN sys.databases d ON vfs.database_id = d.database_id
    INNER JOIN sys.master_files mf ON vfs.database_id = mf.database_id
        AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'ROWS'
ORDER BY d.name
    , mf.name;

WAITFOR DELAY '00:01:00';  --switch this to whatever delay you like

INSERT INTO @t 
SELECT 2
    , d.name
    , mf.name
    , mf.physical_name
    , vfs.num_of_bytes_read
    , vfs.num_of_bytes_written
    , vfs.num_of_reads
    , vfs.num_of_writes
    , vfs.sample_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
    INNER JOIN sys.databases d ON vfs.database_id = d.database_id
    INNER JOIN sys.master_files mf ON vfs.database_id = mf.database_id
        AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'ROWS'
ORDER BY d.name
    , mf.name;

--details by database and file
;WITH cte AS 
(
    SELECT t1.RunNum
        , t1.DBName
        , t1.DBFileName
        , t1.DBPhysicalFile
        , BytesRead = LEAD(t1.BytesRead, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.BytesRead
        , BytesWritten = LEAD(t1.BytesWritten, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.BytesWritten
        , Reads = LEAD(t1.Reads, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.Reads
        , Writes = LEAD(t1.Writes, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.Writes
    FROM @t t1
)
SELECT *
FROM cte
WHERE cte.RunNum = 1;

--summary
;WITH cte AS 
(
    SELECT t1.RunNum
        , t1.DBName
        , t1.DBFileName
        , t1.DBPhysicalFile
        , BytesRead = LEAD(t1.BytesRead, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.BytesRead
        , BytesWritten = LEAD(t1.BytesWritten, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.BytesWritten
        , Reads = LEAD(t1.Reads, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.Reads
        , Writes = LEAD(t1.Writes, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.Writes
        , MillisecondsDuration = LEAD(t1.SampleMs, 1, 0) 
            OVER (PARTITION BY t1.DBName, t1.DBFileName ORDER BY t1.RunNum) - t1.SampleMs
    FROM @t t1
)
SELECT TotalBytesRead = SUM(cte.BytesRead)
    , TotalBytesWritten = SUM(cte.BytesWritten)
    , TotalReads = SUM(cte.Reads)
    , TotalWrites = SUM(cte.Writes)
    , SampleSeconds = MAX(cte.MillisecondsDuration) / 1000
FROM cte
WHERE cte.RunNum = 1;

You can use the summary numbers returned in the second result set to see how many bytes are being read into memory. If that number is higher than the max server memory (mb), then you certainly need more memory to avoid low PLE numbers.

  • 1
    Thanks for the well explained response, Max! I will take a look at the script you provided and see what I come up with. – Wes Feb 15 '17 at 21:12
  • 1
    @BrentO's explanation about only adding memory after you try optimization techniques is certainly very valid, and offer a great way to improve performance while potentially reducing the need for more server memory. Having said that, 12GB of RAM for a 150GB database seems a little small to me, just "off the cuff". – Max Vernon Feb 15 '17 at 21:22
  • 1
    I felt like it was way too little as well, but going to the SA saying 'this isn't enough' isn't enough justification for them. – Wes Feb 15 '17 at 21:27
  • 1
    I ran the script on prod (after dev) and I get a very low 'TotalBytesRead' (8192)... my assumption is most of the activity on the server is done for the afternoon. The current PLE is ~3500s which seems to affirm the low current TotalBytesRead. I will run this tomorrow when traffic is heavy. – Wes Feb 15 '17 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.