Memory Usage per Database

When we run a query the data is read from disk and placed into a memory area in SQL Server called buffer cache (also known as buffer pool). The buffer cache allow us to read this data subsequently without having to go to disk again.

Since SQL Server 2005 on wards with the introduction of DMVs (Dynamic Management Views) it is possible to get how much memory each database is taking in the buffer cache.

With SSRS, Crystal Reports or your favorite coding language you could create a Chart that will help you identify which memory is taking more space in the buffer cache.

I developed my own dashboard which allows me to check several performance checks at once! Comment here if you use a similar approach and what performance checks you use in your charts.

---Memory used per database in buffer pool
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

ORDER BY DatabaseName

Database I/O

Database I/O is the transfer rate, in megabytes per second, of data from memory to disk, disk to memory, or disk to disk.

The easiest way to check the I/O (MB/sec) on your SQL Instance is by checking SSMS Activity Monitor 

b) You can correlate this information and look at the Server Wait types . You will probably get wait types that are related to I/O (PAGEIOLATCH_IO, ASYNC_DISKPOOL_LOCK, IO_COMPLETION, ASYNC_IO_COMPLETION) 

High I/O causes are normally due outdated statistics, bad query design, lack of proper indexing and/or implicit transactions.

Identify your server's waits

Identifying the cause of your server’s waits is a great starting point for analyzing, at a
high level, why your queries are waiting and thus giving suboptimal performance.

Running the SQL script shown here will identify the top 10 causes of waiting on your server instance.

Use SSRS/Crystal Reports or .NET to create a nice dashboard showing this data in a friendly manner this will help you visualize the top offender(s) wait type(s).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH Waits AS

    (SELECT TOP 10

        wait_type,

        wait_time_ms / 1000.0 AS WaitS,

        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

        signal_wait_time_ms / 1000.0 AS SignalS,

        waiting_tasks_count AS WaitCount,

        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN (

        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',

        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',

        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')

    )

SELECT TOP 10

    W1.wait_type AS WaitType,  

    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage

FROM Waits AS W1

    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

HAVING SUM (W2.Percentage) - W1.Percentage < 95;