3

In our PROD environment, there are two servers on which we have system critical database. The management requirement is to monitor these databases for high memory usage or access or long running queries, etc. My requirement is to create a SQL Job for this purpose and if any spikes are observed for this database an event gets generated. So that we can take corrective actions, before the sites hosted on these Dbs slows down. Please don't recommend using any third party tools for this purpose.

Also, I have come up with one idea, using the following query:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters 
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT 
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767 
       THEN 'Resource DB' 
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3), 
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

The output of the above code is BUFFER USAGE (MB) and % BUFFER for each database, except system Dbs. Planning on creating a job which will keep on monitoring the output and check if the Buffer usage goes beyond a threshold.

Kindly suggest something or give comments about my idea. Thanks in advance

  • What is version and edition of SQL server ? – Shanky Jan 20 '15 at 19:12
  • SQL SERVER 2008 R2 10.50.4321 – Abhinav Singh Jan 20 '15 at 19:16
  • Please don't recommend using any third party tools for this purpose. Why? Please read about re-inventing the wheel, and also consider that there are plenty of free 3rd party scripts out there, like sp_WhoIsActive, sp_AskBrent, sp_Blitz, etc. – Aaron Bertrand Jan 20 '15 at 20:17
  • Hey aron, our management won't allow solution related to third party softwares. That's why I said that. – Abhinav Singh Jan 20 '15 at 21:41
  • And they didn't give you a reason? – Aaron Bertrand Jan 20 '15 at 21:44
2

You can use the below query to determine the Memory usage at database level:

SELECT

(CASE WHEN ([database_id] = 32767)
    THEN N'Resource Database'
    ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
 FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id];
   GO

If you want to analyse further at object level that which object in that database from above query is using a lot memory use below query:

 EXEC sp_MSforeachdb
N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
 USE [?]
SELECT
''?'' AS [Database],
OBJECT_NAME (p.[object_id]) AS [Object],
p.[index_id],
i.[name] AS [Index],
i.[type_desc] AS [Type],
--au.[type_desc] AS [AUType],
--DPCount AS [DirtyPageCount],
--CPCount AS [CleanPageCount],
--DPCount * 8 / 1024 AS [DirtyPageMB],
--CPCount * 8 / 1024 AS [CleanPageMB],
(DPCount + CPCount) * 8 / 1024 AS [TotalMB],
--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
    FROM
   (SELECT
    allocation_unit_id,
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 1 ELSE 0 END) AS [DPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE 1 END) AS [CPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
FROM sys.dm_os_buffer_descriptors
WHERE [database_id] = DB_ID (''?'')
GROUP BY [allocation_unit_id]) AS buffers
 INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
 INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
 INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than   100MB
  ORDER BY [FreeSpacePC] DESC;
  END';

Use the querys above in SP's to run at a time period to collect the data you require and this would help you analyse better!

  • why MSforeachdb..Its really not documented. What analysis you would do if you fin out certain object is using memory it just says that pages belonging to that object is in memory – Shanky Jan 20 '15 at 19:36
  • @AbhinavSingh You are going in wrong direction why do you want to set threshold if certain database or object utilities memory. Its totally normal for database to use memory as set by max server memory limit. Again no body can tell you threshold value. You should focus on monitoring costly queries again its highly unlikely memory spike will occur because when SQL Server utilities memory it only release when SQLOS asks it to do so. So even if you set a threshold your alerts wont stop because SQL server is not going to release memory – Shanky Jan 20 '15 at 19:52
  • 2
    Please stop using sp_MSforeachdb - it's undocumented, unsupported, and buggy - see this and this. – Aaron Bertrand Jan 20 '15 at 19:54
1

If you want to monitor memory utilized by SQL Server 2008 and above below is simple and accurate query which would give you memory utilized by SQL Server. Dont refer to task manager for SQL Server memory utilization it wont show you correct value if SQL Server Service account has locked pages in memory privilege.

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

The column Memory_usedby_Sqlserver_MB is memory utilized by SQL Server database engine in MB.

You can put this query in stored procedure which would run at defined interval and give the output to you

Edit: The script you wrote in question is already giving you information about memory utilized by various databases. What else do you need it seems correct query to me.

Again IMO there is no point in taking action based on how much memory each database is utilizing the value is might change continuously and you cannot draw any inference regarding performance after looking at database utilizing more memory. This happens because you run a query which requested a large page read and that pages happened to be from particular database so its not any issue its what memory is designed for to cache as much possible to avoid any physical reads.

Again its better monitoring memory at instance level than database level. I would also say if you have set max server memory correctly considering every aspect in picture you should not worry much about memory.

You would be going in wrong direction if you are going for threshold. Why do you want to set threshold if certain database or object utilities memory. Its totally normal for database to use memory as set by max server memory limit. Again no body can tell you threshold value. You should focus on monitoring costly queries. Its highly unlikely memory spike will occur because when SQL Server utilities memory it only releases it when SQLOS asks it to do so. So even if you set a threshold your alerts wont stop because SQL server is not going to release memory under normal condition

Please let me know if you require further information

  • Hey shanky, Thanks for your reply. Actually, we wanted to monitor memory usage at the database level as you can see from my query above. And accordigly configure alerts in that way. The quey u gave is Server level. Kindly suggest something on Db level. Thanks again – Abhinav Singh Jan 20 '15 at 19:23
  • edited my answer please check and let me know if you require any further help – Shanky Jan 20 '15 at 19:34
  • Thanks shanky for info. Regarding my query, can you please help me decide the threshold value that I should set, surppasing which my SQL Agent Job will generate an event. Please tell me on what basis I can decide this parameter. – Abhinav Singh Jan 20 '15 at 19:49
  • Also shanky, what do you think about SQL data collector for monitoring db usage.? Please comment – Abhinav Singh Jan 20 '15 at 19:51
  • 1
    If you want to initiate chat please do it I would explain you how SQL Server memory works and then you would understand why threshold is not required. – Shanky Jan 20 '15 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.