July 31, 2014 at 7:45 am
We have a couple of 200GB databases that are recreated each night on a SQL2012 server connected to a disk array. The SQL disk is an auto-tiered combo of 10k and 7k drives in a RAID1 lun, and both data and log files are there.
Recently, some room has opened up on an older array that contains smaller, but many, 15k drives that I could use in a RAID1 config. Being that I'd like to split up the mdf and ldf files, which would you put onto the new (faster) disk? I don't have the room to put both over there.
Thanks!
Jason
EDIT: Add'l info: the only current performance issues I see in the SQL Log are FlushCache messages occuring throughout the night, when all activity happens for this DB. Things like this: "FlushCache: cleaned up 388690 bufs with 23474 writes in 409743 ms (avoided 179747 new dirty bufs) for db 47:0"
July 31, 2014 at 7:51 am
It depends on what you want to be faster: the nightly recreate process or the daily activities?
If you want the recreate process be faster I would place the LDF on the fastest disks. The MDF can take advantage of the "Instant File Initialisation" setting, but the LDF can't.
If you want the daily activities to benefit from the faster disks, you need to look at what these activities are. Most likely (but without knowing enough of your situation) it will benefit if the MDF's are on the fast disks...
July 31, 2014 at 7:51 am
It depends on the read/write ratio of your database. If you are mainly a read-only database (80% reads or more) then I would suggest your mdf file be moved over there if on the other had you have a more typical 50/50 reads and writes I would suggest you move your ldf file over there. The reason for this is due to write-ahead logging inside SQL Server. For every data modification it's transaction must first be written to the transaction log (read: write to disk), but once your data pages have been read into memory then you are using the buffer pool to read from and write to (dirty page).
July 31, 2014 at 8:05 am
I should have elaborated, sorry. These databases and tables are cleared out most nights, and recreated from table dumps from our ERP system and are used lightly throughout the day after that. So almost all writes during heaviest times. Moving the LDFs over make sense to me.
Thanks for all the wisdom!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply