Do Not Keep Your SQL Server Databases on a System Drive

By:   |   Comments (3)   |   Related: > SQL Server Configurations


If you are a SQL Server developer who thinks about the time a query is going to take to perform its work, then you know the fewer IOPS, the better the execution time. As for me, I learned this hard way. Once I developed a query that ran really fast when I was developing the query. I went home and was very happy with my work and slept well. The next morning I decided to show my work to my manager and it appeared the query worked slower than earlier. I did not change anything and all seemed to be working as normal, but something screwed up my presentation and it didn't run as fast as before.


I found the reason very quickly by pressing Ctrl+Shift+Esc and reviewing the Performance tab of Task Manager as shown below:

Disk Activity for the Performance tab of Task Manager

Figure 1 - Overall disk C workload

Figure 1 shows that my C drive was very busy, but which program is causing the IOs? To understand, I had to click on the link showing “Open Resource Monitor”. I then looked at the values in columns “Read (B/sec)” and “Write (B/sec)”. I checked the processes which had the biggest values in these columns. Below you can see what I saw.

disk usage

Figure 2 - Windows Update is doing its work

There were a bunch of processes with USOPrivate.  I Googled the keyword “USOPrivate” and the first link I found indicated the process is related to Windows Update.  So this was causing all of the disk activity.  I wanted to check to see if my development database was on the same busy disk, so I executed the following script to find the location of the database files on disk.

SELECT df.file_id
FROM  sys.database_files AS df

Listing 1 - Database files location

This script returned two rows as expected, which are shown below.

database file locations

Figure 3 - Actual database files location

So, my database was on the C disk and this is my system drive. I realized that this was why sometimes my time measurements could be different, because my drive is a shared resource. After the Windows Update completed my second presentation went well.

To confirm this problem, this tip needs some real evidence and some metrics to prove the point related to not putting your databases on the system drive.  To be honest it is quite difficult to duplicate the Windows Update process, so I decided to reproduce a workload that a potential program can perform.


I want to show what happens when a disk where your database lives is busy. I will do that in two experiments. The first one is obvious and I intend to demonstrate the effect that may occur at any point in time. The second one will be more close to reality and I will show how it could happen.

Experiment 1

Create Database

I created a database with the following script:

        NAME = DiskIO_DAT
       ,FILENAME = 'E:\DATA\DiskIO_data.mdf'
    LOG ON
        NAME = DiskIO_LOG
       ,FILENAME = 'E:\DATA\DiskIO_log.ldf'

Listing 2 - Database creation script

We can see that my database lives on disk E, we can confirm this with the following script:

SELECT df.file_id
FROM   sys.database_files AS df

Listing 3 - Database location

The result is presented in the picture below.

database file locations

Figure 4 Database location

Now we can be sure that the experimental database lives on drive E.


At work I have been working on a task named “Security & Search Index” (I am going to write about this cool asynchronous solution in the future) and I have to say that the difference I noticed, when my disk was busy, was very little i.e. 10 milliseconds slower, but this can add up over time.

As I cannot repeat the exact workload of Windows Update, I will show you a situation when my disk is really busy. Also, my T-SQL will be as simple as possible for this example.


First, let’s create a table with 10,000,000 rows.

CREATE TABLE dbo.OrderAmount
        OrderAmountID INT IDENTITY(1, 1) NOT NULL
       ,Amount MONEY NOT NULL


        Amount INT

DECLARE @counter INT;
SET @counter = 1;
WHILE @counter < 1001
    INSERT INTO @tmp
    VALUES (FLOOR(RAND() * (1000 - 50) + 50))
    SET @counter = @counter + 1

INSERT INTO dbo.OrderAmount
SELECT tmp.Amount
FROM   @tmp AS tmp
               SELECT TOP 10000
               FROM sys.objects AS o
               CROSS JOIN sys.objects AS o2
               CROSS JOIN sys.objects AS o3
           ) AS o


Listing 4 - OrderAmount table creation

Let’s have a look at what we did.

SELECT COUNT(*) AS [RowCount] FROM dbo.OrderAmount AS oa

Listing 5 - Count of rows in the experimental table

And the results is:

row count

Figure 5 - Count of experimental rows

Second, let’s write a simple query which returns a SUM from the OrderAmount table.

SELECT SUM(oa.Amount) AS OrderAmountSum
FROM   dbo.OrderAmount AS oa

Figure 6 - Sum of all orders

There is nothing difficult and interesting in that query except that it is going to read many pages from disk. Let’s take a look how many pages it is going to read.



SELECT SUM(oa.Amount) AS OrderAmountSum
FROM   dbo.OrderAmount AS oa

Listing 6 - Count of pages to be read from disk

The result of IO statistics are:

Table 'OrderAmount'. Scan count 9, logical reads 26275, physical reads 1, read-ahead reads 26083, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That means the query is going to read more than 26,000 pages which is around 20 MB.

Next, let’s get how long this takes to run.



SELECT SUM(oa.Amount) AS OrderAmountSum
FROM   dbo.OrderAmount AS oa


Listing 7 - Time statistics

As you can see, I did not use SET STATISTICS TIME. I used some SQL code to show the duration, since this is easier to read.

Below you can see the query results:

query results

Figure 7 - General time spent to return sum by all orders

As you can see SQL Server took around 1.2 seconds to return the result.

But what happens when another program is using the same disk? To get the answer, I developed a very simple program which constantly copies a 1GB file. I called it MakeDiskBusy.exe. When I run this program from my E disk I can see the following metrics.

performance counters

Figure 8 - Disks statistics

Figure 8 depicts that disk E is busy and the total number of bytes sent to the disk (write) and retrieved from the disk (read) over a period of one second and is equal to 114,819,168 / 1024 / 1024 = 109.5 MB/sec.

Now I want to execute my query again while my disk is busy. Here are the results from running the code in Listing 7 again.

query results

Figure 9 - Execution time while the disk is busy

The execution time is slower this time. This shows that if there are multiple IO requests for a single disk both requests will be slower. That means if the disk is serving one request, the other is waiting.

Experiment 2

Now I want to modify the MakeDiskBusy program and make it more similar (from my point of view) to Windows Update. Let’s say it will do something periodically. For example, 3 threads will copy a 100 MB file once every 3 seconds. It will do that for 5 minutes. While this is running, I will execute my SQL query from Listing 7 and log the execution time to a table. Once my experiment is done I will create two charts showing execution duration and disk counters from Perfmon.

Okay, my experiment is done and here are the charts.


Figure 10 - Execution Duration Chart

The chart above (Figure 10) shows the more work the disk is doing, the longer the execution duration. The spike you can see within time period 06:00 till 11:22 is made by the MakeDiskBusy program.

Below I want to show the disk chart.


Figure 11 - Disk chart

The chart above (Figure 11) shows the more work for the disk, the less disk throughput. And therefore the less disk throughput, the longer the execution time.


So based on my experiments, please do not keep your database on a drive that could be used by some program other than Microsoft SQL Server. Moreover, keep an eye on other system resources like CPU, Network and Memory. If you work on optimization issues be sure that your environment is not affected by some unexpected software.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Viacheslav Maliutin Viacheslav Maliutin is a SQL Server Professional with 16+ years of experience in reporting, integration and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, August 22, 2017 - 12:07:05 PM - Viacheslav Maliutin Back To Top (65153)


On floppy? Why not? SQL Server has nothing to do with the actual IO system, rather has deal with Windows IO API. So, only you need to do is to buy some coffee, take a good position on your couch and run a query against DB in the morning. Be sure, you will have an answer in the evening. But note, all works well. :)

Monday, August 21, 2017 - 9:27:39 PM - Randy in Marin Back To Top (65135)

As NVMe becomes more common, performance might become less important a consideration to developers.  I would actually perfer that developers have slower drives or something running that simulates sharing resources with a 100 other users.  Queries can appear to run really well until they hit production.  A workstation with NVMe can be a real speed demon compared to VM fighting for access to a SAN.  So, developers, please use a 5000 rpm laptop drive and put everything on it.  Can SQL run on floppy?  :) 

Monday, August 21, 2017 - 10:03:45 AM - Jeremy Kadlec Back To Top (65120)


Congrats on your first tip and welcome to the team!

Thank you,
Jeremy Kadlec
Community Co-Leader

get free sql tips
agree to terms