Partitions, SANS and Logical Drives Setup

Question

Partitions, SANS and Logical Drives Setup

SQLH

SSC Journeyman

Points: 76
More actions
January 15, 2009 at 8:07 am

#205110

Database:
SQL Server 2008 or 2005
The database will only be used for reporting
Data will be imported once a day
Usage:
Daily reporting will be 70% usage
Monthly reporting will be 15% usage
Quarterly reporting will be 15% usage
Partitions:
Ranges will be by Month & Year Ex: Jan2008, Feb2008, etc
They will be setup in a SANS
Priority:
1) Performance
2) Performance
3) Performance
I want to create 12 logical drives on the server. One for each month of the partition.
Would this setup gain performance from having does logical drive setup? Why or why not?
This is my first time setting up an partition database.
Any suggestion, thoughts, or source I can read about would be appreciated.
Any question please feel free to ask.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

The thing to really thinik about is not logical disk, but physical disk. If you have one physical hard drive and create two logical drives you will not really see any performance gain because it still has to pass throough one central point/device for writting and reading. In a SAN this becomes more difficult, but to truely see a benefit you need to make sure that you are connecting your drives to seperate physical devices on the san to get the additional throughput so that each section can be reading and writting simultaneously.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQLH (1/15/2009)
Would this setup gain performance from having does logical drive setup? Why or why not?

No. It's still the same physical drive. It may even decrease performance by requiring the drive heads to move more than would otherwise be necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLH SSC Journeyman Points: 76 More actions · Answer 3

Thanks for you quick responses.

I would of thought that SQL Server, would take advantage of the logical drive seperation.

So if I used a SANS, I should only have one drive and stored all the data in that one drive with multiple filegroups? And I should also include the log file in that one drive?

Thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SQLH (1/15/2009)
So if I used a SANS, I should only have one drive and stored all the data in that one drive with multiple filegroups? And I should also include the log file in that one drive?

No. You should have different LUNs of the SAN exposed as different physical devices to windows. Make sure those LUNs are comprised of dedicated disks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

noreik Valued Member Points: 58 More actions · Answer 5

Always wonder by the spliting of drives and SANs.

Thank you.

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Do bear in mind that this thread is 3 years old and the advice may no longer be correct.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass