Why you should not store user data on the same drive as SQL files

I am not aware of any official “Best Practice” statement from Microsoft in relation to storing user files on the same drive as SQL database and log files, however, I would recommend against the practice for three reasons:

1. Drive space.  SQL database and log files can grow quickly without advance notice.  Best practice is to keep 15% drive space free at all times to safeguard against unexpected growth causing the drive to run out of space.  Users are much more unpredictable than the database, and can run the drive out of space without warning, causing SQL to crash.
2. Disk I/O.  Each HBA (Host Bus Adapter) has a finite amount of I/O throughput that it can handle.  Even if the concerns from #1 are handled using disk space quotas or some other limiting measure, I am not aware of a good way to limit certain users I/O capacity.  User file operations are typically larger and slower than SQL data operations.  So even if the users are not filling up the drive, they could easily use enough of the I/O throughput to cause a choke point for SQL and degrade performance.  These types of performance hits would be very difficult to diagnose and track down, so better to avoid them up front.
3. Fragmentation.  When set up properly, SQL server grows data and log files by allocating a large block of contiguous disk space.  Since user files are much smaller, they can create thousands of tiny gaps in disk space.  The result is that SQL Server cannot find a continuous block large enough for the next growth operation.  The SQL data and log files will become fragmented.  There is no safe way to defragment them with windows tools, and the SQL Server Maintenance operations that do so can only handle defragmenting the SQL file if there is sufficient continuous disk space, not moving the user files.  The end result is a performance hit and can become significant over time.  This is also one of the reasons that the SQL user databases and tempdb data and log files should not be stored on the OS drive.

 (I did not list security concerns, as those can be overcome with proper AD setup and administration, but many organizations’ security policy still do not allow this for fear of undiscovered exploits in AD security.)

If this is still not clear, hopefully this analogy will shed some light.
Imagine a swimming pool filled with barrels with a garden hose running to it that can be moved from barrel to barrel.

The swimming pool is the hard drive.
The barrels are the database growth allocation blocks.
The garden hose is the HBA. 
The water is the data. 

SQL pulls and pushes water through the garden hose, usually at a trickle. 
A user storing or reading a file blasts water at high pressure through the hose intermittently.  There is nothing stopping the user from blasting so much water in that they overflow the pool.  If they blast or suck water through the hose for an extended period of time (seconds is extended), then SQL has to wait for them to finish to resume the trickle. 
Users can put water in any barrel, but SQL prefers an empty barrel.  If there are no empty ones, SQL must use part of two or more barrels, but must remember which barrels go together. 

This quickly becomes laborious to keep track of, and translates into degraded performance.

Did this help you?  If so, please leave a comment!