Question: We are setting up a SQL Server 2012 database instance which will be hosted on a remote network share. However, we are worried about performance over the network share. What should we look for when setting up this database?
Answer: The biggest thing that you’ll want to do is make sure that the local server and the server running the remote network share are both running the newest version of the SMB protocol. At the very minimum you’ll want to ensure that both servers are running Windows Server 2008 R2 or if possible wait until both servers are running Windows Server 2012. The reason for this is that the newer versions of SMB (SMB 2.2 on Windows 2008 R2 and SMB 3.0 on Windows 2012) are greatly improved, with the SMB version 3.0 being even better than SMB 2.2.
The ability for Windows Server 2012 being able to run at near network wire speed is easily testable. In order to perform this test I used a laptop which had an SSD drive in it and installed two VMs within that laptop. The laptop had a SQL Server 2012 instance installed on it and one of the virtual machines had an instance installed while the other VM was used to host the database. When creating a database of the host laptop to get a baseline of the performance of the SSD drive we see the following performance in task manager.
Creating a database locally
As you can see we are writing to the disk at the rate of 208 MB/second. When we create a database using the exact same specifications on the virtual machine on a remote network share we can see that data is being written at 201 MB/second. This is a performance loss of only 3.4% when writing the database over the wire. (A large amount of data was written when creating the database by simply turning off instant file initialization.)
Creating a database remotely
When reading on this SSD drive we can see reads of 278 MB/sec on the local disk when running a database backup of a database which has data within it.
Reading a local database
When running a database backup against a database of similar size on the network drives we can see that the disk speed that’s available has been reduced somewhat to 179 MB/second. While this is a pretty heavy performance loss, 179 MB/second is still a very fast network throughput to be receiving when hosting databases over a remote network share.
Reading from a network database
If we look at perfmon on the virtual machine which is hosting the network share we can see that the disk response time and queue length is very low so the performance loss that we are seeing here isn’t from the disk but instead from the network.
PerfMon on DC1 network reads
Where these same tests to be run on virtual machines which are running Windows Server 2008 R2 the performance would be slightly lower as Windows Server 2008 R2 is running SMB 2.2. Were these same tests being run on Windows Server 2008 or lower the performance would be much worse as Windows Server 2008 is running SMB 2.0 (or lower if the servers are old enough).
If you intend of running databases which are hosted on remote network shares, especially databases which are high IO databases you’ll need to be using Windows Server 2012 for maximum network performance.
Extra Notes:
Some extra notes about this hardware configuration:
The host computer is an Asus Laptop running Windows 8 RTM, 32 Gigs of RAM with a SanDisk Extreme SSD drive. The SQL Server instance installed is SQL Server 2012 RTM.
Virtual Machines are running within VMware Workstation. Both guest machines are running Windows Server 2012 RP. One virtual machine is a domain controller while the second is a member server. The second server is running SQL Server 2012 RTM. Both virtual machines have 2 Gigs of RAM.
Both test databases were created at 1 Gig in size with 1 Gig Transaction Logs. The database was setup for simple recovery mode. Approximately 1 Gig of data was written into each database using the following T-SQL Code.
select *
into prosess
from sys.sysprocesses
while 1=1
insert into prosess
select * from sys.sysprocesses
Write testing was done while the database was being created. Read testing was done by backing up the database to NULL so that only read IOs were being generated.
