Category Archives: Memory

Find the Buffer Pool usage per database in SQL Server

In this short article i will share a simple query that i wrote to find the number of pages of a database in the buffer pool and there usage of the buffer pool

SELECT CASE database_id WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS [Database],COUNT(*) AS PageCount ,
( COUNT(*) * 8.0 ) / 1024 AS [SpaceOccupiedInBuffer-Mb]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id


SQL Server 2012 – How to enable the “Lock Pages in Memory” Option

In this article I will discuss the feature “lock pages in memory” and demonstrate how we can enable the “Lock pages in Memory” option for SQL Server 2012 on the OS. The OS decides which accounts can utilize a process to retain data in the RAM. This would not allow the system to page out data to virtual memory on the physical disk. In SQL Server 2012, the memory architecture has been simplified for the usage of locked pages across all editions including processor. In SQL 2012 we enable this option by allowing the account which runs the SQL Server service to have this right. The below illustration shows the requirements for different editions of SQL server for Locking pages in memory. LP1If you have migrated your SQL server to SQL 2012 from SQL 2005 or SQL 2008 and if you have been using this option then you can remove the trace flag 845 from the start up parameters.

We will now see how to enable this option.

        1. Click Start, click on Run. Type gpedit.msc. Click on OK and you will find the Local group policy editor opening.
          LP2         LP3
        2. On the Local Group Policy Editor, expand Computer Configuration -> expand windows Settings -> Security settings
          LP4 LP5
        3. Open Security Settings, and then Open Local Policies and then open “User Rights Assignment” folder
        4. Once you open the User Rights Assignment folder all the policies will be displayed. Locate “Lock pages in Memory”. Double Click on “Lock pages in memory”
        5. Click on “Add user or Group” and add the account that runs SQL Server service. Restart SQL server.
          We can verify whether SQL Server 2012 is using “locked pages” by the following ways:
          1) Run the following query:

select od.node_id, os.memory_node_id, od.node_state_desc, os.locked_page_allocations_kb
from sys.dm_os_memory_nodes os
inner join sys.dm_os_nodes od on
os.memory_node_id = od.memory_node_id
where od.node_state_desc <> 'ONLINE DAC'

The locked_page_allocations_Kb will display a non-zero value.

LP10              2) DBCC MEMORYSTATUS will show the following:
LP11 I hope this article was helpful in understanding how we can enable this option for SQL Server 2012.

Buffer cache hit ratio – How useful it is?

Buffer cache hit ratio(BCHR) : Well every dba who has ever done any Performance tuning work would know about this particular counter. So what is BCHR: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

And how much guidance does it give us….

Well to know that i had to share this great article from . I read this article on Read the article and challenge your understanding of BCHR once again.

%d bloggers like this: