SQL Server best practice....

Question

SQL Server best practice....

bobba

SSCommitted

Points: 1605
More actions
January 30, 2018 at 9:36 am

#343169

We recently created some new database servers (VMs), moving content to them from old servers approaching end of life.
Our standard was to create a drive for the application (SQL) another for the operating system. We have a few production databases on them and everything appears to be working properly.
Later realized one mistake right away - not creating a separate drive for TempDB. I'm going to address that shortly, that appears to be pretty straight forward.
Further review suggests setting at least a separate drive for ldfs as well.
Any other suggestions / URLs would be appreciated. Thanks.

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

It’s usually good to break up the files in this manner. However, be sure to check out how this maps back to actual storage. It may be that you’re creating files on what appear to be different drives to the OS, but it’s really the same set of storage for the VM.

K. Brian Kelley
@kbriankelley

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

bobba - Tuesday, January 30, 2018 9:36 AM
We recently created some new database servers (VMs), moving content to them from old servers approaching end of life.
Our standard was to create a drive for the application (SQL) another for the operating system. We have a few production databases on them and everything appears to be working properly.
Later realized one mistake right away - not creating a separate drive for TempDB. I'm going to address that shortly, that appears to be pretty straight forward.
Further review suggests setting at least a separate drive for ldfs as well.
Any other suggestions / URLs would be appreciated. Thanks.

Tempdb data files and log files should ideally be separated and stored away from user database data and log files.
If you cannot separate the tempdb data and log files put them on the same drive together

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

"Ya can't make an omelette without breaking just a few eggs" 😉

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 3

bobba - Tuesday, January 30, 2018 9:36 AM
We recently created some new database servers (VMs), moving content to them from old servers approaching end of life.
Our standard was to create a drive for the application (SQL) another for the operating system. We have a few production databases on them and everything appears to be working properly.
Later realized one mistake right away - not creating a separate drive for TempDB. I'm going to address that shortly, that appears to be pretty straight forward.
Further review suggests setting at least a separate drive for ldfs as well.
Any other suggestions / URLs would be appreciated. Thanks.

We have splitted our TempDB data, log in different drive, UserDB data & Log in different drives, Other system database in seperate drive and seperate drive for backup as well.

bwelch42 Old Hand Points: 335 More actions · Answer 4

While our VMs were using the same disk, we found significant benefits from placing data, log, tempdb data, and tempdb log files on separate drives. It appears that the Windows OS views them differently when on separate drives and this in turn greatly reduces disk latency.
For some instances, we found it very helpful to create multiple data drives and spread data across them, spreading the I/O work, essentially.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

bwelch42 - Tuesday, February 6, 2018 6:44 PM
While our VMs were using the same disk, we found significant benefits from placing data, log, tempdb data, and tempdb log files on separate drives. It appears that the Windows OS views them differently when on separate drives and this in turn greatly reduces disk latency.
For some instances, we found it very helpful to create multiple data drives and spread data across them, spreading the I/O work, essentially.

When you say "separate drives", are you talking about actual physically different drives or not?

--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)

bwelch42 Old Hand Points: 335 More actions · Answer 6

Jeff Moden - Tuesday, February 6, 2018 7:11 PM
bwelch42 - Tuesday, February 6, 2018 6:44 PM
While our VMs were using the same disk, we found significant benefits from placing data, log, tempdb data, and tempdb log files on separate drives. It appears that the Windows OS views them differently when on separate drives and this in turn greatly reduces disk latency.
For some instances, we found it very helpful to create multiple data drives and spread data across them, spreading the I/O work, essentially.
When you say "separate drives", are you talking about actual physically different drives or not?

Separate Windows drives on the same VM and using the same underlying disk. For example, when we measured disk latency on some servers on their data (mdf) drive D:, we created two more data drives E: and F: and then in SQL Server created extra data files for the databases formerly only on D:.