performance

If you’re looking at performance and trying to understand I/O on a per database level in SQL Server, sadly there is not a report in Management Studio that you can run from those available.

It will need some T-SQL to pull out this information from an existing DMV – sys.dm_io_virtual_file_stats.

The information returned by the DMV is data which has been recorded since the SQL Server was last restarted and so it is therefore cumulative data.

It helps the DBA to understand where the I/O distribution is across databases at both the data and log file level.

Let’s take a look at I/O at the database level using this T-SQL:

WITH IO_Per_DB
AS
(SELECT 
  DB_NAME(database_id) AS Db
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS TotalMb
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs
 GROUP BY database_id)

 SELECT 
    Db
    ,TotalMb
    ,CAST(TotalMb / SUM(TotalMb) OVER() * 100 AS DECIMAL(5,2)) AS [I/O%]
FROM IO_Per_DB
ORDER BY [I/O%] DESC;

On my test instance this produces the following output:

I can see that the top I/O consumer is the sample MS database AdventureWorks and this is because I’ve been running some simple SELECT’s against the tables. If I start running queries against the others, the distribution will quickly change so it’s important not to assume anything about the results without regular sampling.

Take some scenario where workload patterns are constant across the day and the results do not change much. Then in the evening some large job runs which reads a lot of data from a database which is otherwise not very active during the day. At that time of the day, that database may show up at the top of the report having generated a lot of I/O. However after this time, normal workloads would resume and the results will change again.

Looking at I/O usage at the database file level in SQL Server

The results above are very high level. If you want to look in more detail, for example at the data file level, you can run something like this:

WITH IO_Per_DB_Per_File
AS
(SELECT 
    DB_NAME(dmivfs.database_id) AS Db
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS TotalMb
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read) / 1024 / 1024) AS TotalMbRead
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_written) / 1024 / 1024) AS TotalMbWritten
  , CASE WHEN dmmf.type_desc = 'ROWS' THEN 'Data File' WHEN dmmf.type_desc = 'LOG' THEN 'Log File' END AS DataFileOrLogFile
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs
 JOIN sys.master_files dmmf ON dmivfs.file_id = dmmf.file_id AND dmivfs.database_id = dmmf.database_id
 GROUP BY dmivfs.database_id, dmmf.type_desc)

 SELECT 
    Db
  , TotalMb
  , TotalMbRead
  , TotalMbWritten
  , DataFileOrLogFile
  , CAST(TotalMb / SUM(TotalMb) OVER() * 100 AS DECIMAL(5,2)) AS [I/O%]
FROM IO_Per_DB
ORDER BY [I/O%] DESC;

I’ve included some extra columns here to help display the megabytes read and written as well as the data file the I/O was generated against.

In the following results, I ran an update against one of the larger tables to help demonstrate. You can see the log file for the AdventureWorks db saw some activity and the distribution of I/O is now more transparent.

As a DBA there may be a time when you want to try and ascertain how much CPU time each of your databases is consuming. This is useful to complement high CPU investigations or to just try and understand which of your databases overall is the highest CPU consumer over time.

The out of the box reporting that Management Studio provides is sadly missing this report so we have to run some T-SQL to extract it.

As with other DMV’s, the statistics produced are only available from the time when the SQL Server was started and can change over the course of the day. It’s important to remember that they are cumulative figures and may not correlate to any current spike in CPU performance caused by some heavy query.

If you want to track this data over time, you will need to capture data at regular intervals and persist it to some kind of logging table or file.

So here is some code to do it:

WITH CPU_Per_Db
AS
(SELECT 
 dmpa.DatabaseID
 , DB_Name(dmpa.DatabaseID) AS [Database]
 , SUM(dmqs.total_worker_time) AS CPUTimeAsMS
 FROM sys.dm_exec_query_stats dmqs 
 CROSS APPLY 
 (SELECT 
 CONVERT(INT, value) AS [DatabaseID] 
 FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
 WHERE attribute = N'dbid') dmpa
 GROUP BY dmpa.DatabaseID)
 
 SELECT 
 [Database] 
 ,[CPUTimeAsMS] 
 ,CAST([CPUTimeAsMS] * 1.0 / SUM([CPUTimeAsMS]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUTimeAs%]
 FROM CPU_Per_Db
 ORDER BY [CPUTimeAs%] DESC;

This is what the results looks like on my test instance.

I hope you found this useful 🙂

As a DBA it’s important to understand what the buffer pool is doing and which databases are using it the most.

Data in SQL Server is stored on disk in 8k pages. The buffer pool (Aka “buffer cache”) is a chunk of memory allocated to SQL Server. It’s size is determined by the minimum and maximum memory settings in SQL Server memory options:

sp_configure 'min server memory (MB)'
go
sp_configure 'max server memory (MB)';

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
min server memory (MB)              0           2147483647  0            16

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
max server memory (MB)              128         2147483647  2147483647   2147483647

Data is processed via the buffer pool. When an application requests some data for reading and those pages are not in the buffer pool, the server has to read them from disk. These are known as physical reads. If those pages have been read already into the buffer pool, these are known as logical reads and will typically return to the application more quickly than the physical reads – memory is faster than disk.

The buffer pool is also where modifications are made to pages (dirty pages) before those changes are persisted to storage.

If sufficient memory is not available, pages are aged out of the buffer and space is made available for the pages being read from disk when they are requested. Excessive “churn” of pages in the buffer pool could indicate that it is sized too small. The Page Life Expectancy counter can be used to help diagnose if this is a problem on your server.

The buffer pool is a critical area of the system that must be adequately sized for optimal performance.

SQL Server can tell you how many of those pages reside in the buffer pool. It can also tell you which databases those pages belong to. We can use sys.dm_os_buffer_descriptors to provide this information as it returns a row for each page found in the buffer pool at a database level.

SELECT 
  CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END AS DatabaseName,
  COUNT(*) AS cached_pages,
  (COUNT(*) * 8.0) / 1024 AS MBsInBufferPool
FROM
  sys.dm_os_buffer_descriptors
GROUP BY
  database_id
ORDER BY
  MBsInBufferPool DESC
GO

DatabaseName                   cached_pages MBsInBufferPool
------------------------------ ------------ ---------------------------------------
ResourceDB                     2968         23.187500
AdventureWorks2012             2614         20.421875
msdb                           438          3.421875
master                         390          3.046875
ReportServer                   332          2.593750
AdventureWorksDW2012           245          1.914062
tempdb                         225          1.757812
PerformanceDW                  205          1.601562
ReportServerTempDB             172          1.343750
Test                           155          1.210937
model                          66           0.515625

(17 row(s) affected)

The data tells us what is in the buffer pool at that moment in time. These results can change quickly depending on the activity of the server. Tracking this data over the course of a day for example would require some kind of job to periodically run this query and capture the output to file or logging table.

The data can be reset if the buffer pool is emptied either upon a SQL Server restart or by executing DBCC DROPCLEANBUFFERS which will clear out the unmodified (clean) pages from the buffer pool.

Emptying the buffer pool of clean pages by running DBCC DROPCLEANBUFFERS will increase load on the disks and decrease application performance until the cache fills again.