Buffer Pool Space in SQL Server 2005

By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

Problem
I need to determine what objects/structures are consuming the largest amount of space at a given time (or over time) within the SQL Server buffer pool. How can I achieve this in SQL Server 2005?

Solution
In SQL Server 2000, this was a bit complicated to determine to say the least, however with SQL Server 2005's new dynamic management functions/views, it's become exponentially easier to gain this type of insight; additionally, it's also become easy to aggregate this information for use/display/reporting purposes.

Procedure 1
In this scenario, the use of the sys.dm_os_buffer_descriptors DMV optionally correlated against other catalog views within a given database will provide you all the information you need to get this type of information.

Below are a couple of possible solutions to retrieving this type of data. The first and simpler of two procedures provide you with aggregated information from the DMV rolled-up on a per database, file, and page type combination (page type being things like data pages vs. index pages vs. PFS pages vs. etc., etc.). It's a simple procedure with no parameters and a single select statement with some grouping, rollup, and sorting.

Click here to get the code

Here is sample output from the first 10 rows of the result set.

Procedure 2
The second and more complex of the procedures provides more detailed information for each given database on the system - instead of providing only server-level information (like what database, file, etc. is consuming the buffer pool), it will dig into specific database(s) to provide more targeted information within the given database(s) in regards to specific indexes/tables/views/etc. that are utilizing the most space. Optional parameters are included to target to a specific database, all databases on the system, system level only information, and return only a certain number of results.

Click here to get the code

Here is sample output from the first 10 rows of the result set.

Next Steps

• Add these procedures to your toolbox to help get further insight into how your SQL Server 2005 Buffer Pool is being used.
• Take a look at these other tips that relate to DMVs
  • Dynamic Management Views and Functions - SQL Server 2005 Exposed
  • SQL Server 2005 Resource Database Values in Dynamic Management Views\Functions
  • System Monitor (Perfmon) Counters for SQL Server 2005
• As always, be sure to understand the performance impact associated with some possible incarnations of executing these procedures - if you have a large 64-bit system with a large buffer pool, these could get somewhat intensive.
• Special thanks to Chad Boyd of the MSSQLTips.com community for this tip.
  
  
   

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code