question

Vishu-6622 avatar image
0 Votes"
Vishu-6622 asked AmeliaGu-msft commented

Find CPU and memory utilization database wise

Hi Experts,

Can you please help with a query which can provide details around the CPU and memory utilization , database wise i.e if 5 user databases provide the CPU and memory utilization separately for each database.

How to use it to capture data over a period of time

Thanks

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Vishu-6622,
Did the answer help you?
Please feel free to let us know if you have any other question.
If you find the post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi VishalGarg-6622,
Welcome to Microsoft Q&A.
To monitor memory usage per database, we can use sys.dm_os_buffer_descriptors DMV to determine which database(s) are utilizing the majority of your buffer pool memory and use sys.dm_os_performance_counters to calculate the percentage of the buffer pool being used by each database. Please refer the following query from this article which might help:

 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 = 'Database 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;

To monitor CPU usage per database, we can use sys.dm_exec_query_stats and sys.dm_exec_plan_attributes. The query uses the CROSS APPLY operator that works with the Database ID field that exists in both dynamic views with a GROUP BY clause on the Database ID fields and the aggregate SUM function on the total_worker_time column from the dm_exec_query_stats view. Please refer to this article which might help.
We also can use Performance Monitor to monitor CPU and Memory usage in specified time. Please refer to Monitor CPU Usage and Monitor memory usage which might help.
Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.