3 Reasons Not to Install Databases On the C Drive

We’re probably all familiar with the fact that SQL Server by default will install not only the program binaries but all your databases on the C drive as well.  Accidental DBA’s, i.e. System Administrators and Developers who inherited the maintenance of a database, may not be aware of how bad it can be if someone simply clicked the “Next” button all the way through the SQL Server install.  There are surely many reasons not to accept this default during the install, but I will start with three.

First, from a purely practical standpoint, I think installing to the default path makes backups and restore scripts unduly difficult.  Which is easier to remember, “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup” or something simple like “D:\SQLBackups”? Which one would you rather have to type or copy from Windows Explorer after browsing the path?  With the second one it is so easy you don’t have to spend time browsing and copying it.  You just quickly type it out.  For the restore script would you rather type “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SomeDB.mdf” or “D:\SQLData\SomeDB.mdf”?  The same point applies here – Simplicity.  Why make it hard on yourself?

Second, if you don’t put your databases on the C drive then database growth can’t crash the entire computer by filling up the C drive.  We all know that databases grow and that growth has to be monitored and managed.  If the System Administrator misses those emails from Database Mail or her favorite monitoring tool, then she will be awakened first thing in the morning by a company end user who can’t do their regular work only to discover that the entire machine is experiencing paralysis.  If the database files are not on the C drive with the OS then at least database growth will not be the culprit of server paralysis from a full C drive.

Third, if you install all your databases to the C drive, then you are inviting performance issues.  Databases and the OS will be fighting for drive resources.  Disks only spin so fast.  If you have the OS and 10, 14, 50 or more database files all trying to read and write to the same drive, then your C drive is going to be spinning like a yo yo.  Not only is there contention between the OS and the database files, but the database files will be fighting each other for disk usage too.

Let me provide some real numbers from an environment I have seen.  I had a system with 12 databases all on the C drive.  That number includes 4 system databases and 8 user databases.  Latency was in the thousands for several of the databases.  I moved the user databases and TempDB off to another drive and left the System databases on the c drive.  I know I can move those too, but one step at a time.  See the comparisons below.

In some cases there was an immediate impact for latency.  Clearly, removing the activity of the eight user databases made a huge difference in performance.  As you can imagine, the user databases performed much better from a latency perspective as well, especially since I put them on a Fusion IO card.

To see latency for your drives you can run the following query from Paul Randal:

SELECT
[ReadLatency] = 
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

Well, I hope this has given you something to think about.  Your homework is to find out the latency in your environment, do some research, make changes as needed and then follow up to see if the changes you made helped.  Let me know how the homework turns out.