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