asked
Two databases on two drives - performance increase?
Hello In my production configuration most of SQL Server set ups have only one hard drive configured for all user databases. Whole production storage is based on SAN solutions. Recently I decided to test if moving i.e. two different databases to two different drives would decrease execution time of some heavy queries which tables are located in these two databases. Unfortunately my test did not prove that this solution increases performance, execution times where mostly the same when databases where deployed on one drive or spread to two drives. I am aware that it would work in traditionally attached storage but I was wondering if it works on SAN, anybody have similar experience?
Hello guys, First of all thanks for such rapid answer. So, at least my tests proved what you wrote ;-) Actually my SANs are using NetApp based RAID level so called RAID 6DP which by manufacturer recommendation should not be change. Regarding my load, generally I have very intensive reads/writes during night hours reaching 2000 IOPS and medium reads with rare writes during business hours. Kind regards, Bartek
SAN usually give you an overhead to the latency, that you don't get on a traditional direct accessed storage. High latenvy affects the Random IO negative. What you should do is to use a different LUN for the log files. You can keep the database files in the same LUNs but TempDB and Log files should be placed on different LUNS. If you are using disk groups in the SAN it could make sence to place different databases on different LUNS as long as they are using different diskgroups. I recommend you to read the documents at the following link, and make sure your SAN manager (and your manager) read and understand how a SAN should be configured for best SQL server performance. [
http://www.brentozar.com/sql/sql-server-san-best-practices/][1] [1]:
http://www.brentozar.com/sql/sql-server-san-best-practices/
@Håkan - I haven't seen latency issues myself due to the SANs I have seen being on fast connectors to the servers. The route for a fiber optic cable is insignificantly longer than that of direct storage - nanoseconds IIRC. If there is latency, it would have to do with something else IMO.
Ususally a SAN is configured to solve the storage situation for all servers and all systems without considering SQL server performance.
It has little to do with SAN/no-SAN. You can have a SAN and only 2 disks supplied for your DB-Box and that could be slower than directly attached storage with 14 Disks. It also depends upon data access patterns on your system. Are you reading more than writing, or the other way round? You are normally better off splitting the location of the data files and the log files. The access patterns of these two are significantly different. You need to look at how the LUNs in your SAN are setup - you may have volumes in windows that are in actual fact on the same physical disks in the SAN. This is standard practice for a SAN admin - do not expect them to know that a DB needs exclusive usage of disks in a SAN. Speak with your SAN admin - find out what is going on there before trying things out. Also make sure that he/she isn't sharing your SQL Server LUNs with anyone else.
+2 for "do not expect them to know that a DB needs exclusive usage of disks in a SAN". Sorry, I couldn't click twice on "vote up". :)
And their Pinocchio like noses! :)
http://www.brentozar.com/archive/2010/10/my-pass-lightning-talk-preview-video/
The reason you won't have seen a difference is based on loading. Unless you hit the saturation point of the I/O that a single drive can handle while multiple processes are vying for that I/O, then your performance numbers won't change. However, two fully loaded disks = twice the I/O of one fully loaded disk. Meaning you could perform a disk-intensive query on DB A and DB B at the same time. It's not really a question of 'will it make a single query run faster' it's a question of 'will this increase the overall throughput of the server'. However, if you start having multiple disks in a RAID array, then you will start to see differences in single query execution times - but again, this would not be the correct measure to use. You really want to be measuring overall throughput. Secondly, be sure to choose RAID levels wisely, and match the access pattern of the data that you put on to a disk array to the RAID level that you choose for that array. I won't explain this all here, because that would be pages and pages, but that will give you something to research...
If you are in a multi processor environment you can improve the performance of queries having join operations by creating Multiple files and file groups and putting those tables in seperate file groups, then place those file groups on sepeatate disk will defenetly boost performance.
It depends on your queries... Eg. If your query is accessing tables from two database DB1 and DB2
SELECT T1.C1, T2.C2
FROM DB1Table T1
INNER JOIN DB2Table T2
ON T1.ID = T2.ID
Depends on the SAN solution. Some will allow you to specify "X spindles will be carved and dedicated to SQL Server". If you manage to dedicate 2 of X spindles, then yes, you will see a performance improvement. If you are carving 2 volumes on the same set of spindles, then no. In fact, your performance can be worse.
