Creating a data and log drive per CPU

Question

Creating a data and log drive per CPU

Edward Shaw

Ten Centuries

Points: 1020
More actions
January 3, 2020 at 5:19 pm

#3712187

Happy New Years everyone.
Have just had a conversation with a client where they are creating a separate drive per CPU inside their SQL Server. They then partition out the data/log files across these (for this case eight) drives. I have never seen that before. Currently at where I work we do have a separate drive (on different SAN's) for 1 data file and 1 log file per database (ignoring the TempDB for which we have number of data files per CPU's).
They claim to have been able to get queries down from hours to seconds by doing this. I understand setting up different partitions and all that, but have never seen anyone set it so the data file(s) would match the number of processors (again, excluding TempDb from this discussion).
Anyone else doing this? Have you seen performance improvements?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

oogibah SSC Eights! Points: 827 More actions · Answer 1

I personally haven't seen it, and I don't know that the per cpu matters, but splitting the data files across multiple physical drives could act as a sort of makeshift RAID maybe?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

When your "drives" are sourced from a SAN, it doesn't really make sense to split out the drives to that extreme, because all of those little drives are likely carved out of the same array of disks from the SAN anyway based on the whims of the SAN admin. I also don't think I've ever seen a case where having multiple transaction log files for a single SQL Server database is a good thing.

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 3

Edward Shaw wrote:

They claim to have been able to get queries down from hours to seconds by doing this.

I've seen a lot of strange claims and they normally turn out to be false. This is no exception for the qualification of "strange" but I can need offer testimony one way or the other because I've not seen it before. All I can do is provide a healthy and somewhat robust "doubt it", especially since the claim of "from hours to seconds" is so fantastic but am open minded enough to ask them for proof.

More likely, I would suspect something like they had somehow made a mistake in the formatting of the original drives (especially if they were several years old) and finally got drives that were formatted with the correct offset. I might also suspect that they had some bad settings on the headend of the old drive system or even through the "wire" they were using.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)