We all know (or should know) that SQL Server wants a lot of memory – in fact, it wants all of it. The more memory you allocate to SQL Server, the better the performance of the server (in general.) So it’s important to know when you have memory pressure, whether it comes from internal or external sources.
Internal memory pressure is what comes from within SQL Server itself. Queries are run that need to pull in a lot of data from disk, and SQL Server has to load that into the buffer cache to process the request. Send too many of these queries to SQL Server, and it has to start removing data from cache to make room for the new data. The Buffer Manager\Page life expectancy performance counter reflects the fact that pages had to be flushed out to make room for the new ones coming in, and this is a key indicator of memory pressure.
First, let’s look at the SentryOne dashboard where we’ve run a query that needs more buffer cache memory than was currently allocated, but there’s still plenty of room.
Internal Memory Pressure with Memory Available
Notice that the system and SQL Server memory used went up, as did the buffer cache memory, but that Page Life Expectancy (PLE) was unaffected.
Next, let’s look at a case where the query run required more memory than was available. We see that system and SQL Server memory expanded as it could, the buffer cache expanded as it could, but that the PLE counter dropped precipitously, and that tells us that SQL Server had to flush out other data to make room for that needed for this query.
Internal Memory Pressure where Buffer Cache pages are flushed to make room
This kind of memory pressure happens all of the time, but it does negatively affect the performance of the other activity on the server, and steps should be taken to minimize these kinds of queries, or at least to move them to off-hours times.
The next kind of memory pressure is technically external, as it’s not SQL Server making a decision. It’s also a contrived scenario, because it’s not something you would do on a production server without a really good reason. After watching a session by Bob Ward (b/t) on memory pressure, where he used sp_configure to change the max server memory to a small number, just to show how SQL Server reacts internally, I thought it’d be a good idea to show how that’s reflected in the SentryOne dashboard.
External Memory Pressure due to sp_configure Change
Notice the cliff-like drop in system and SQL Server memory, how the SQL Server memory contracts, and how the PLE value drops immediately after the command is run. This will obviously affect performance, but seeing how this appears on the dashboard will help you understand how to more effectively manage the memory on your servers.
The last example is another extremely common example of external memory pressure. When processes other than SQL Server are running on the same server as SQL Server, Windows has to manage the needs of those other processes. If they need more memory than is available at the time, Windows will request memory back from SQL Server, and SQL Server must comply. In this case I ran a PowerShell script that spawned about a dozen additional PowerShell jobs, each one having its own memory requirements. The total memory needed by Windows was greater than what was available, so Windows requested memory back, and SQL Server complied, which definitely affected SQL Server’s performance.
External Memory Pressure due to External Processes Run on Server
In my case, I used PowerShell for my example, but it could also be Reporting Services, Analysis Services, a vendor application, or any number of other cases where processes are run on the server besides SQL Server. As you can see, it’d be far better to run these other processes on other servers, so SQL Server can make the best use of memory, to provide the best performance it can.
