Memory Utilization In SQL Server (Database Level)

Its common question for DBAs to find memory consumption by each database. With SQL Server 2000 its was quite difficult to find but with SQL Server 2005 + we have DMV (Dynamic Management Views)  “sys.dm_os_buffer_descriptors” which provides memory utilization by each database or table in database.

So let’s use the DMV to find current memory utilization by each database.

Before we start this memory test you need to create a test table and a database with following script.

Create Database DBMemoryTest
Go
Use DBMemoryTest
Go
Create Table TblMemoryTest(Id Int Default 0,NameValue Char(8000) Default 'Mem Test')
Go
Insert Into TblMemoryTest Default Values
Go 20000

Now you have 20000 records in TblMemoryTest Table. Lets check the data space of the table.

exec sp_spaceused 'TblMemoryTest'
Go

Output :

Data : = 160000 KB Means 160000/1024 = 156 MB Around. So it means that when I use select * on this table without any where condition then it must eat up memory upto 156 MB. Lets Test It.

Clear Buffer Cache and Procedure Cache

DBCC FREEPROCCACHE
Go
DBCC DROPCLEANBUFFERS
Go

Now run following query to find memory utilization by each database.

SELECT DB_NAME(database_id) AS DatabaseName, (COUNT(*) * 8)/1024.0 AS [Size(MB)]
FROM sys.dm_os_buffer_descriptors
Where Database_Id<>32767
GROUP BY DB_NAME(database_id)

Output :

Now run select statement in TblMemorycheck table.

Use DBMemoryTest
Go
Select * FRom TblMemory Test
Go

And now again run query to find memory utilization.

SELECT DB_NAME(database_id) AS DatabaseName, (COUNT(*) * 8)/1024.0 AS [Size(MB)]
FROM sys.dm_os_buffer_descriptors
Where Database_Id<>32767
GROUP BY DB_NAME(database_id)

Output :

You can clearly see that database DBMemoryTest is showing 157 MB of memory utilization which was not there in previous run of query.