IT Tips

How to list memory utilization by database? You can do that with this script: 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 ( DECI...

Sometimes we need to send an e-mail when our SQL Server identify some blocking process. The script below will help you. Just create a job, change the information of your mail profile, recipients, subject and body. SET NOCOUNT ON ; DECLARE @ blockingProcess INT SELECT   @ blockingProcess = COUNT (*) FROM     sys.dm_tran_locks L         JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id         JOIN sys.objects O ON O.object_id = P.object_id         JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id         JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id         JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT .transaction_id         JOIN ...