A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

Lock Pages In Memory

It's necessary to keep SQL from paging to disk to reduce physical I/O. One way is to keep the SQL working set in Memory. The steps below will set you up for this. Before performing this, ensure that your SQL Server has enough ram to handle your current production load. You wouldn't want your primary indexes dropping out of memory to replace 1 off Jobs.
I generally deploy new servers with Lock Pages in Memory set for the SQL Server Service start-up account .

To assign the Lock pages in memory user right, follow these steps:

  1. Click Start, click Run, type gpedit.msc, and then click OK.

    Note The Group Policy dialog box appears.
  2. Expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Click User Rights Assignment, and then double-click Lock pages in memory.
  5. In the Local Security Policy Setting dialog box, click Add User or Group.
  6. In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
  7. Close the Group Policy dialog box.
  8. Restart the SQL Server service.

What the? How much memory is SQL Server using?

The question is fairly straight forward. There are many many many different articles online which will allow any person to dive into the following queries in detail. I'm only giving a high level view of memory usage based on DMV's (Dynamic Management Views).
How much memory is SQL Server Using?


declare @obj_cache_size float, @obj_data_size float 

            --OBJECT and PLAN DATA IN MEMORY

select @obj_cache_size = sum(pages_kb) / 1024.0 from sys.dm_os_memory_cache_counters; 


with buffer (Buffer_Pool_MB) 

as ( 

 SELECT count(*) * 8192.0 / (1024.0 * 1024.0) 

 FROM sys.dm_os_buffer_descriptors  

 GROUP BY db_name(database_id) ,database_id 


select @obj_data_size = sum(buffer_pool_mb) from buffer 



select @obj_cache_size [Object_Memory_Usage_MB]  

 , @obj_data_size [Data_Memory_Usage_MB] 

 , [Misc_Memory_Usage_MB] 

 , [Misc_Memory_Usage_MB]  

 + @obj_data_size  

 + @obj_cache_size [Total_Memory_Usage_MB] 



 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]  

 from sys.dm_os_performance_counters  

 where object_name like '%memory%' 

 and ( 

   counter_name like '%Connection Memory (KB)%' 


   counter_name like '%Lock Memory (KB)%' 


   counter_name like '%Optimizer Memory (KB)%'


   counter_name like '%SQL Cache Memory (KB)%'


) x



Let’s take a peek into the Server Memory.

 Syscacheobjects actually reads from sys.dm_exec_cached_plans.
But we’ll use the view to get the sql.



select top 25


, ObjType

, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]

, db_name(dbid) [database_name]

, object_name(objid, dbid) [object_name]

, [sql]

from syscacheobjects (nolock)

order by pagesUsed desc


Distinct Cached Plans and Objects returned
Compiled Plan         Proc
Compiled Plan         Trigger
Compiled Plan         Adhoc
Compiled Plan         Prepared
Extended Proc         Proc
Parse Tree               UsrTab
Parse Tree               Check
Parse Tree               View






Buffer Pool Memory Per DB


SELECT LEFT(CASE database_id

                  WHEN 32767 THEN 'ResourceDb'

                  ELSE db_name(database_id)

        END, 20) AS Database_Name,

      count(*)AS Buffered_Page_Count,

      count(*) * 8192.0 / (1024.0 * 1024.0) as Buffer_Pool_MB

FROM sys.dm_os_buffer_descriptors

GROUP BY db_name(database_id) ,database_id

ORDER BY Buffered_Page_Count DESC






 How much space are my indexes taking up in memory?

Buffer Index Memory By Object



      i.[name] [index_name],


      count(page_id) AS Buffered_Page_Count,

      cast(count(page_id) as bigint) * 8192 / (1024 * 1024) as Buffer_MB


FROM sys.dm_os_buffer_descriptors AS bd


    ( SELECT object_name(par.object_id) AS [obj_name] , par.index_id , par.object_id, au.allocation_unit_id

        FROM sys.allocation_units AS au

        INNER JOIN sys.partitions AS par ON au.container_id = par.hobt_id

    ) AS au_p ON bd.allocation_unit_id = au_p.allocation_unit_id

LEFT JOIN sys.indexes i on i.object_id = au_p.object_id AND i.index_id = au_p.index_id

WHERE database_id = db_id('<DATABASE NAME>')

GROUP BY obj_name, au_p.index_id , i.[name],i.[type_desc]

ORDER BY count(page_id) DESC




 If you have any comments or suggestions regarding these queries, feel free to post suggestions.

Disk Space Monitoring Procedure without OLE Automation Procedures

The procedure "Monitor_Disk_Space" was created to return more information than the widely used "capacity_DiskSpaceTracking" procedure which is used for monitoring.
 The capacity_diskspacetracking procedure can be found all over the web a simple google search will return many results.
Compare the output of two procedures: "capacity_DiskSpaceTracking"  and  "Disk_Space_Monitoring"

"monitor_disk_space"  - the procedure code for this is below.
 as you can see , we're gathering much more information in the new procedure without  enabling OLE Automation Procedures.
Procedure: Monitor_Disk_Space





create proc monitor_disk_space 



 -- CREATED BY Don Rickman 
-- 9/1/2007


create table #disk_usage ( 

   drive char(1) 

 , free_space_mb float 

 , total_space_mb float 

 , isShared bit) 


 insert into #disk_usage (drive, free_space_mb) 

 exec master..xp_fixeddrives 


 create table #output (drive char(1), [output] varchar(256)) 


 declare @drive char(1), @cmd varchar(256), @t bigint, @u bigint 


 declare drive_cursor cursor for select drive from #disk_usage 

 open drive_cursor 

 fetch next from drive_cursor into @drive  


   while @@fetch_status =


    set @cmd = 'fsutil volume diskfree ' + @drive + ':' 

    insert into #output ([output]) EXEC XP_CMDSHELL @cmd 

    update #output set drive = @drive where drive is null 


    fetch next from drive_cursor into @drive 



 close drive_cursor 

 deallocate drive_cursor 



 SELECT drive, 

   cast(SUM(CASE WHEN [output] LIKE 'Total # of bytes             : %' THEN CAST(REPLACE(SUBSTRING([output], 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) / 1024.00 / 1024 / 1024 as decimal(10,2)) AS total_gigabytes, 

   cast(SUM(CASE WHEN [output] LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING([output], 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) / 1024.00 / 1024 / 1024 as decimal(10,2))AS avail_free_gigabytes 

   , case when s.drivename is not null then 1 else 0 end is_shared_drive 

   , sa.name 

   , sa.[filename] [file_name] 

   , CASE   

    WHEN sa.groupid = 1 THEN 'data' 

    WHEN sa.groupid = 0 THEN 'log' 

   END as 'file_type' 

 , cast( cast(sa.size as bigint) * 8192 / 1024.00 / 1024.00 / 1024.00 as decimal(10,3)) file_size_gigabytes 

 into #temp 

 FROM ( 

    SELECT Drive, 


    FROM #output 

    WHERE [output] LIKE 'Total # of %' 

   ) AS

 left join master.dbo.sysaltfiles sa on left(sa.filename,1) = d.drive 

 left join ::fn_servershareddrives() s on s.drivename COLLATE SQL_Latin1_General_CP1_CI_AS = d.drive 


 GROUP BY Drive, s.drivename, sa.size, sa.name, sa.filename, sa.groupid 


 select @@SERVERNAME [server] 

 , drive 

 , total_gigabytes 

 , avail_free_gigabytes 

 , cast( ((avail_free_gigabytes/ total_gigabytes)*100) as decimal(10,2) ) [percent_avail_disk_space] 

 , is_shared_drive 

 , name 

 , [file_name] 

 , file_type 

 , file_size_gigabytes  

 , getdate() [date]

 from #temp 

 ORDER BY Drive, name