x

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?
more ▼

asked Oct 28, 2010 at 01:44 AM in Default

Bartolo gravatar image

Bartolo
101 4 4 5

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
Oct 28, 2010 at 02:39 AM Bartolo
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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...
more ▼

answered Oct 28, 2010 at 02:04 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered Oct 28, 2010 at 01:52 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

@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.
Oct 28, 2010 at 01:55 AM WilliamD
Ususally a SAN is configured to solve the storage situation for all servers and all systems without considering SQL server performance.
Oct 28, 2010 at 02:12 AM Håkan Winther
Håkan Winther, congrats for crossing 6K...!
Oct 28, 2010 at 02:28 AM Cyborg
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 28, 2010 at 01:52 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+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". :)
Oct 28, 2010 at 02:28 AM Håkan Winther
I see the multiple LUNs on the same spindles trick all the time. The unsuspecting DBA just sees three volumes in the OS, and happily splits up the data and log files.
Oct 28, 2010 at 08:31 AM KenJ
@KenJ - Damn those pesky SAN Admins and their money saving tactics! ;)
Oct 28, 2010 at 09:19 AM WilliamD
Oct 29, 2010 at 09:50 AM KenJ
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 28, 2010 at 02:14 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

Hi Cyborg, but isn't it the same solution if I have these tables in two diffrent databases which obvisoulsy are on two seperate files?
Oct 28, 2010 at 02:44 AM Bartolo

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

and imagine they are on separate physical disk then SQL Server can make use of parallel scan across those disk. and if they are on same disk you won't get much performance gain.
Oct 28, 2010 at 03:09 AM Cyborg
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 28, 2010 at 06:40 AM

ozamora gravatar image

ozamora
1.4k 2 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x250
x32
x13
x12

asked: Oct 28, 2010 at 01:44 AM

Seen: 1914 times

Last Updated: Oct 28, 2010 at 01:44 AM