Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Seaparating Data files and Log Files


Seaparating Data files and Log Files

Author
Message
chandan_jha18
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8845 Visits: 16579
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" ;-)
chandan_jha18
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
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
scogeb
scogeb
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 420
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.
scogeb
scogeb
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 420
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.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8845 Visits: 16579
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" ;-)
scogeb
scogeb
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 420
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search