A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


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?

--HOW MUCH MEMORY IS MY 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; 

-- DATA IN CACHE (PAGES, INCLUDING INDEXES) 

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 

 

-- CACHE TOTALS 

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] 

from  

( 

 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)%' 

   or 

   counter_name like '%Lock Memory (KB)%' 

   or  

   counter_name like '%Optimizer Memory (KB)%'

   or  

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

 ) 

) x

 
 
 
Results:
 

 

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.

 

SyscacheObjects

select top 25

cacheobjType

, 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

 

Results:


 

-----------------------

 

Buffer Pool Memory Per DB

 
dm_os_buffer_descriptors

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

 

Results:


 

-----------------------

 

 How much space are my indexes taking up in memory?

Buffer Index Memory By Object

SELECT

      au_p.[obj_name],

      i.[name] [index_name],

      i.[type_desc],

      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

    INNER JOIN

    ( 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

 
 

 

Results: 

 
 
 
 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"

"capacity_DiskSpaceTracking"
 
 
 
"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 

as 

begin 

 -- 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 =

   begin 

    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 

   end 

 

 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, 

     [output] 

    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 

 

end  

 

 

GO

 

 

 

 

 

Shrink Transaction Logs

 
Running the following code truncates the transaction log. A full database backup should be run after this script to insure point of time recovery in any production environment. 

USE <DB>
GO
DBCC SHRINKFILE(<TLOGNAME>, 1)
BACKUP LOG <DB> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TLOGNAME>, 1)
GO

 
 
-- New way
use <DB>
ALTER DATABASE <DB> SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(<DB_LOG>, 1)
ALTER DATABASE <DB> SET RECOVERY FULL WITH NO_WAIT