Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you’ll get. This blog isn’t about the magic of the buffer pool or the plan cache, but you should understand how important they are to your server’s performance. Since memory is such an important resource, you want to know how much of it you’re using at any time.
How can you track your server’s memory usage? One way is to use the Performance Monitor (Perfmon) counters exposed through the sys.dm_os_performance_counters DMV. One indicator of memory performance is Page Life Expectancy (PLE). You can capture basic memory usage over time by setting up a SQL Server Agent job to query this DMV, inserting the results into a table, and reporting on the table results.
I’ll show you how to collect this data and report on it!
Collecting the Data
I have a “DBAInfo” database on my instance that I use to track metrics and other information. I create a new table, MemoryHistory.
USE DBAInfo; CREATE TABLE MemoryHistory (ID INT IDENTITY NOT NULL, CollectionDateTime DATETIME, PerfmonObjectName NCHAR(128), CounterName NCHAR(128), CounterValue BIGINT)
Then, I create a new SQL Server Agent job that runs every 5 minutes.
The only step in this job is the below query, which queries the DMV and inserts the results into the table I created.
INSERT INTO MemoryHistory SELECT CURRENT_TIMESTAMP, object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Buffer Manager';
I schedule the job to run every five minutes.
Viewing The Data
Now, this data isn’t going to do me any good unless I view it, and make a decision or perform an action based on what I learn.
To view the data I’ve collected, I run the following query:
SELECT ID, CollectionDateTime, CounterName, CounterValue FROM MemoryHistory;
That’s a lot of junk to sort through when all I want to see is PLE, so I narrow down the query a bit.
SELECT ID, CollectionDateTime, CounterName, CounterValue FROM MemoryHistory WHERE CounterName = 'Page life expectancy';
But who wants to read through results like that each time there’s a problem to see when PLE rose or fell? Not me. I’d rather see it in a graphical format. How can I do that?
SQL Server Reporting Services
I have SSRS at my disposal. I’m going to create a very simple report that will allow me to enter start and end dates, and will display a line chart for PLE during that time.
Reporting on the Data
I set up my report to have DBAInfo as my data source. In order to choose dates, I use the following query as my dataset.
SELECT ID, CollectionDateTime, CounterName, CounterValue FROM MemoryHistory WHERE CounterName = 'Page life expectancy' AND CONVERT(DATE, CollectionDateTime) >= @Start AND CONVERT(DATE, CollectionDateTime) <= @End;
I change my @Start and @End parameters to “Date/Time” so I get a date picker.
I drag a Line Chart onto the design surface and add the CounterValue as my Value and CollectionDateTime as my Category Group.
I can preview the report to view it:
Last but not least, I’ll deploy this report to Report Manager so that I and others can run it, or even schedule a regular subscription.
There are several ways to improve this report. How can you modify the query to capture date and time data individually? How do you add parameters to the report so the user running it can choose their own date range? How would you collect and display data for different instances?
Want to know more about how to use Reporting Services to create reports for your environment? Check out my 90-minute SQL Server Reporting Services Basics training video!