Physical Design

Likely everyone who makes it to the position of a database administrator has been through the ropes of serving as an elementary developer. By now you already know how to design tables, views, relationships between tables, stored procs, on and on. Early in your administrator career though you will run into the underworkings of the database engine, and recognize that it is time to face straight up and address the physical design of your databases. As much as poorly designed normalization can hose your system's queries, the underlying physical design can wreak just as much havoc.

Physical design covers several related aspects of adjustments to the databases, but deep down it's all about disk IO across the various files. Adding server memory nowadays is a fast and inexpensive affair, so the limiting operational performance in the host of most database systems is the IO throughput. Fast IO can be achieved numerous ways, but once you've established a baseline for the physical hardware and make sure you don't have any bottlenecks in disk controllers or bandwidth to your NAS, then what remains is spreading out the active database files across various drives and controllers to maximize the IO parallelism.

I use a couple different management queries to identify places where files should be split. These two queries below tell you where your usage is heaviest on your SQL instance:

WITH IOFORDATABASE AS
(
SELECT
 DB_NAME(VFS.database_id) AS DatabaseName
,CASE WHEN smf.type = 1 THEN 'LOG_FILE' ELSE 'DATA_FILE' END AS DatabaseFile_Type
,SUM(VFS.num_of_bytes_written) AS IO_Write, SUM(VFS.num_of_bytes_read) AS IO_Read
,SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) AS Total_IO
,SUM(VFS.io_stall) AS IO_STALL
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
JOIN sys.master_files AS smf   ON VFS.database_id = smf.database_id
 AND VFS.file_id = smf.file_id
GROUP BY  DB_NAME(VFS.database_id), smf.type
)
SELECT
 ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS RowNumber
,DatabaseName, DatabaseFile_Type
,CAST(1.0 * IO_Read/ (1024 * 1024) AS DECIMAL(12, 2)) AS IO_Read_MB
,CAST(1.0 * IO_Write/ (1024 * 1024) AS DECIMAL(12, 2)) AS IO_Write_MB
,CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12, 2)) AS IO_TOTAL_MB
,CAST(IO_STALL / 1000. AS DECIMAL(12, 2)) AS IO_STALL_Seconds
,CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10, 2)) AS IO_STALL_Pct
FROM IOFORDATABASE
ORDER BY IO_STALL_Seconds DESC;

select db_name(mf.database_id) as database_name, mf.physical_name,
left(mf.physical_name, 1) as drive_letter,
vfs.num_of_writes, vfs.num_of_bytes_written, vfs.io_stall_write_ms,
mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read, vfs.io_stall_read_ms,
vfs.io_stall, vfs.size_on_disk_bytes
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
order by vfs.num_of_bytes_written desc

The files that pop out from this with the highest stall percentages should likely be split and spread. Of course you need to pay special attention to tempDB as it gets shared across all of the applications running on each particular instance (I'll have more to say about allocating tempDB in a later post).

Once you've split up and created multiple files for your heavily used logs and databases, it's time to dig into some deeper level detail. This query below shows you table IO activity on a particular database; you should select your database connection (or add a USE statement) to see the details of one database at a time.

SELECT TableName = object_name(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
GROUP BY object_name(s.object_id)
ORDER BY writes DESC

This will show you which specific tables are being heavily read and written to. Not infrequently I like to take the heavyweights and move them to their own filegroup.

You can do more IO tuning down at the query level, but that's a topic for another post.