Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Seaparating Data files and Log Files Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 1:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 466, Visits: 1,930
Hi Folks,

I am facing an issue which I thought I understood but looks like reading and implementing a strategy are very different.

I have a SQL 2008 running on a VM with Windows 2003 and having 8 logical processors and 64 GB of RAM.

When I login to the server I see that there are 4 logical drives : C,D,E and F

We are keeping data and log files spread across D,E and F.

Now, there are a number of times when perfmon or DMV information shows high contention on I\O.
So I owned up a task to plan to move data and log files separately.

now as per best practices we know that due to different nature of the writes on both the two files, they should be placed on separate drives and now this is the biggest hurdle I am facing.

Even though these drives look different to me when I login to the server but if they are logical drives and physically on the same storage, I do not think it will make much of a difference. So here are my questions:

1) A log of places it is written that both the files should be on separate logical drives so is it true that if both are logical but actually point to same location physically it will help in dealing with contention

2) If the scenario I described above is true and you agree that putting files on logical drive does not make much sense, what should be my next step then?

3) What should be my approach while communicating with Admin guys? Shall I ask them to provide me different physical disks or is there a way I can write them more effectively

Finally the lat question comes about RAIDS, how do I mention this while trying to justify need for a better storage architecture.

Thanks in advance. Your valuable suggestions are welcome.

Regards
Chandan Jha

Post #1384443
Posted Wednesday, November 14, 2012 2:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 6,745, Visits: 14,383
chandan_jha18 (11/14/2012)
Hi Folks,

I am facing an issue which I thought I understood but looks like reading and implementing a strategy are very different.

I have a SQL 2008 running on a VM with Windows 2003 and having 8 logical processors and 64 GB of RAM.

When I login to the server I see that there are 4 logical drives : C,D,E and F

We are keeping data and log files spread across D,E and F.

Now, there are a number of times when perfmon or DMV information shows high contention on I\O.
So I owned up a task to plan to move data and log files separately.

now as per best practices we know that due to different nature of the writes on both the two files, they should be placed on separate drives and now this is the biggest hurdle I am facing.

Even though these drives look different to me when I login to the server but if they are logical drives and physically on the same storage, I do not think it will make much of a difference. So here are my questions:

1) A log of places it is written that both the files should be on separate logical drives so is it true that if both are logical but actually point to same location physically it will help in dealing with contention

2) If the scenario I described above is true and you agree that putting files on logical drive does not make much sense, what should be my next step then?

3) What should be my approach while communicating with Admin guys? Shall I ask them to provide me different physical disks or is there a way I can write them more effectively

Finally the lat question comes about RAIDS, how do I mention this while trying to justify need for a better storage architecture.

Thanks in advance. Your valuable suggestions are welcome.

Regards
Chandan Jha


Which hypervisor are you using to host the VM?
Are the virtual machines disks RDMs or virtual disks?
Do they all reside on the same data store?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1384491
Posted Wednesday, November 14, 2012 3:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 466, Visits: 1,930
Perry,

Thanks for replying.

The server uses VMWare and the sysadmin wrote the following to me:

'All of these drives are situated on the same set volume on the NetApp'.

He also wrote that though the files are separated on the logical level but may give more benefits on physical level. If the files are on different logical drives only then it just gives better manageability and nothing else.

Please suggest.

Thanks
Chandan
Post #1384501
Posted Wednesday, November 14, 2012 7:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
chandan_jha18 (11/14/2012)
Perry,

Thanks for replying.

The server uses VMWare and the sysadmin wrote the following to me:

'All of these drives are situated on the same set volume on the NetApp'.

He also wrote that though the files are separated on the logical level but may give more benefits on physical level. If the files are on different logical drives only then it just gives better manageability and nothing else.

Please suggest.

Thanks
Chandan


Can your NetApp storage admin give you an additional LUN or two on a seperate volume on the NetApp? Is he/she seeing latency on the NetApp? Where is your tempdb? It might be worthwhile to move that to a seperate LUN as well.

As for the RAID, depending on the NetApp, it might all be a certain type, maybe RAID 6 or some kind of proprietary RAID. You could also ask your NetApp admin about that as well. Se if he/she has volumes with different RAID types or if they're all the same.

On a side note, when are you seeing latency spikes? What are the numbers? Spikes are normal at times. Depending on what other VMs you have running on that same physical server, it could be an HBA bottleneck. That is why you need to ask your NetApp person if they're seeing latency. They should have monitoring tools to check the volume or LUN.
Post #1384631
Posted Wednesday, November 14, 2012 7:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
Oh, one more thing. What blocksize do you have those drives set to? I have seen that make a difference in speed. Find out what your mean read/write size is and set it accordingly.
Post #1384633
Posted Wednesday, November 14, 2012 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 6,745, Visits: 14,383
chandan_jha18 (11/14/2012)
Perry,

Thanks for replying.

The server uses VMWare and the sysadmin wrote the following to me:

'All of these drives are situated on the same set volume on the NetApp'.

He also wrote that though the files are separated on the logical level but may give more benefits on physical level. If the files are on different logical drives only then it just gives better manageability and nothing else.

Please suggest.

Thanks
Chandan

Are they VMWare virtual disks or Raw Device Mappings?
If virtual disks, are they all on the same VMFS data store?

VMWare virtual disks really suck for random I\O at the best of times. If you don't believe me, setup a VM with a virtual disk and an RDM attached and run SQLIO against each, then check the results. Sequential I\O I have found virtually no difference.

scogeb (11/14/2012)
As for the RAID, depending on the NetApp, it might all be a certain type, maybe RAID 6 or some kind of proprietary RAID. You could also ask your NetApp admin about that as well. Se if he/she has volumes with different RAID types or if they're all the same.

Netapp storage systems use RAID6 for the disk aggregates with Netapps own proprietary Write Anywhere Filesystem Layout (WAFL).


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1384674
Posted Wednesday, November 14, 2012 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
Perry Whittle (11/14/2012)
Netapp storage systems use RAID6 for the disk aggregates with Netapps own proprietary Write Anywhere Filesystem Layout (WAFL).


Ah, interesting. I knew that is how our's was, didn't know they were all set that way. Good to know, thanks.

Post #1384692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse