Monitoring SQL Server Buffer Pool Usage by Database with PowerShell
By: Alejandro Cobar | Updated: 2020-09-28 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | More > Monitoring
If every now and then you have a memory constrained SQL Server, then this PowerShell script can give you a bit of information to start your investigation process. It will not tell you the root cause of why your system's memory is constrained, but it won't leave you empty handed, that's for sure. By knowing the Buffer Pool usage of all the databases within your SQL Server instances, you will have a starting point for your course of action.
This module will gather information about the Buffer Pool from all the specified instances. It will connect to each server in inventory.MasterServerList and capture the data for each of these instances. The connection to each SQL Server instance will be based on the settings in the inventory.MasterServerList table.
In order to use this module, you need to create the core objects found here.
For this particular module, only one table will be created (in case it doesn't exist), and it is the table to store the information of the Buffer Pool usage of all the databases, from each instance.
This is the structure of the table, so you can get an idea of what data will be stored.
- serverId - serverid ties back to inventory.MasterServerList
- database_name - the name of the database
- db_buffer_MB - the amount in megabytes that the database is using in the Buffer Pool
- db_buffer_percent - the percent of 8KB pages in the Buffer Pool that belong to the database
- clean_pages - the amount of 8KB pages in the Buffer Pool that are exactly the same as their counterpart in the Storage Subsystem. In other words, the amount of 8KB pages that haven't changed.
- dirty_pages- the amount of 8KB pages in the Buffer Pool that have changed and haven't been written to disk.
- data_collection_timestamp - when data was collected
* If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle that additional field.
There will be times when you simply don't want to collect this information from all the registered instances you have in your Master Server List. To address this, simply modify the query text inside the variable $instanceLookupQuery within the PowerShell script and you should be good to go.
The PowerShell script that creates the above object and inserts data into the monitoring.BufferPool table is called:
The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.
If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.
How to Use
Navigate to the folder where you created the files and you can run the PowerShell script as follows:
- Right click on Get-MSSQL-Instance-BufferPool.ps1 and select Run with PowerShell.
- Open a command window and navigate to the folder where you saved the above files and run:
- Schedule this as a SQL Server Agent Job to run on a regular basis.
- Schedule this as a Windows Task Scheduler job to run on a regular basis.
Check Creation of Database and Objects
After running the PowerShell script, we can see the objects that are created.
If we query the monitoring.BufferPool table, we can see the data that has been collected.
Note: The PowerShell script will store only the information from the last execution, no historical data is retained. If you'd like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.
Checking for Errors
To check for errors query the monitoring.ErrorLog table using the following query:
SELECT * FROM monitoring.ErrorLog WHERE script = 'Get-MSSQL-Instance-BufferPool'
If you'd like to know the SQL Server instance that got the errors, you would have to issue the query like this:
SELECT CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE CONCAT(msl.server_name,'\',msl.instance) END AS instance, e.script, e.message, e.error_timestamp FROM monitoring.ErrorLog e JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId WHERE e.script = 'Get-MSSQL-Instance-BufferPool
Which databases have the highest percentage allocation in the Buffer Pool?
SELECT * FROM monitoring.BufferPool ORDER BY db_buffer_percent DESC
Which databases have the highest number of 8KB pages in the Buffer Pool?
SELECT * FROM monitoring.BufferPool ORDER BY db_buffer_MB DESC
Which databases have a high count of clean pages in the Buffer Pool?
SELECT * FROM monitoring.BufferPool ORDER BY clean_pages DESC
Which databases have a high count of dirty pages in the Buffer Pool?
SELECT * FROM monitoring.BufferPool ORDER BY dirty_pages DESC
Are there databases with more clean pages than dirty pages in the Buffer Pool?
SELECT * FROM monitoring.BufferPool WHERE clean_pages > dirty pages
- Download the PowerShell script to collect data for SQL Server databases Buffer Pool usage.
- Check out the other parts of this series
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
- Monitor all Indexes in SQL Server with PowerShell
- SQL Server High CPU Query Use Monitoring with PowerShell
About the author
View all my tips
Article Last Updated: 2020-09-28