SQL Server sharing drive location

Question

DBA newbie here. Did some searching online but cannot find a definite answer on this.

Is it acceptable to share the drive where .MDF files reside with other non-SQL Server files such as applications that go under a Program Files folder?

I assume it is not but maybe this depends on the type of storage that is used? We use SAN storage.

I'll rephrase....If the drive that contains .MDF files is shared with other non-SQL Server applications, could this potentially affect SQL Server's read/write performance depending on the storage that is used?

The answer isn't obvious to someone that is new to SQL Server, let alone hardware storage. If everything is located on the same SAN, does it really matter if they are on the same drive? I'm researching and can't find the answer and the only SQL Server hardware book out in the market is from 2011.

Answer 1

"could this potentially affect SQL Server's read/write performance?"

yes. Here are some things to consider:

1 - The type of file(s) matters in context of performance. Files which are often read/written to will of course share the resources with the SQL Server files. Files which aren't being accessed when SQL Server files are being accessed won't noticeably affect performance.

2 - The type of storage matters. Shared IO has more severe performance consequence on spinning disk storage than SSD.

The idea is similar to the "Placing both data AND log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files." as stated in Microsoft docs. Read placing any two files with simultaneous IO demand on the same device can cause contention for that device, resulting in poor performance.

Will it cause poor performance? Far too many variables to take into consideration such as storage specs & config, workload patterns of both SQL Server and w/e is sharing its resources, etc. to have concrete answer.

Answer 2

I don't know if it answers your question, but I'd like to point out a couple of things.

1. You need to get your terminology straight for a meaningful discussion. Speaking of "disk drives" in the context of a SAN is a bit misleading. What you see as "drive D:", for example, on Windows, if that's what you mean by a "disk drive", is in fact a LUN, or a logical volume, defined on top of a SAN physical volume (PV), which in turn is comprised of an arbitrary large number of actual spinning or solid state drives. All this is to say that, even if all you see on your drive D: is a single .mdf file, it does not at all mean that there are not bits and pieces of other files from other LUNs, mounted on other servers, located on the same actual physical drive(s). And it may or may not mean anything, and it's the headache of your storage administrator to ensure your IOPS is up to specs.

2. Your "disk drive", the LUN behind it, the fiber channel link, the SAN switch, PVs, and the actual physical drives in a rack are all shared resources. If there's more than one consumer of a shared resource, there will be contention, which in turn will affect performance of one or more consumers of that resource. To what extent, depends entirely on how all these competing consumers interact at what time of what day of the week. Imagine elevators in a highrise building at 3 AM on a Wednesday; at 8 AM on a Monday, when everyone is going to work (post-pandemic); at 6 PM on a weekday, with a lineup of UberEats drivers on the ground floor; and all day on the 1st of the month, when new tenants are moving in.

TL;DR:

• Could this potentially affect SQL Server's read/write performance? -- It sure could.
• Will it? -- Only you will know, when you conduct obligatory load testing.