October 13, 2016 at 11:07 am
I know what the standard answer is on this but I am looking for someone to say this would be better.
Scenario: I move 3 billion rows of data from various sources, a lot of it is linked servers but just about as much is SSIS packages and various others.
I typically load all of the data into a staging database and then move that data into a database that has general read access once its all staged. This data is pretty unique as I am typically only moving 90 days worth of data and we truncate and/or drop and reload these tables (2k) every day. Worth noting, previously the dept had allowed some rouge users to have some space on the server by allowing them to create and maintain their own databases, I am trying to bring them to heel but that's another issue.
Current Drive Configuration.
C- has all the system databases except tempdb.
D (SSD) has all of our owned database files and Tempdb database file.
E has all of our owned log files
F has all of the rouge users database and logs and Tempdb log file.
I inherited this mess and I am just trying to tune the hardware before I fix anything else.
All of my database files have been sized appropriately and have auto-growth set to add a large chunk with a limit to the max(just as a fail safe)
This configuration is relatively new as two weeks ago all of the data and logs were on the system drive.
The two main databases that are used staging and feeding have recovery set to bulk_logged since everything is recreated at least once a day.
I think the optimal situation would be to have the staging and feeding databases on their own SSD's but my real question is about tempdb, my bottleneck at this point is the tempdb logfile. Should have add another ssd and put just the tempdb on it, or both the log and the database files? or should I move the tempdb log up to the existing ssd.
Finally, many of these jobs are managed with SQL agent. Should the msdb database be moved off the system drive and if so to where?
Thanks in advance.
October 13, 2016 at 11:22 am
Without being able to profile things, I'd start by putting all logs on SSD since nothing happens in sql server until it gets logged. Move the main data off the SSD because it will be written fairly sequentially when the lazy writer kicks in every 5 minutes or so. Run tempdb and its log on ssd. A big data cache can take a load off the drives depending on your queries.
Not knowing sizes and loads, I could be totally wrong. My experience with SSD shows 10x to 50x improvement over hard disks. The drawback is all the updates and logging you do will wear out the SSD in a couple years.
October 14, 2016 at 6:43 am
I like Bill's recommendations. The thing is, the more drives you can throw at it, the better. The exception to that is if you're going through a single controller, which then becomes the bottleneck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply