The File Placement Dilemma
-
We have a 45GB database heavily used for both for OLTP and batch processing. As usual, a small percentage of tables are responsible for most of the impact in terms of size (in some cases tens of millions of records) and frequency of access. Obviously we try to separate batch and OLTP, but some transgressions are unavoidable. The largest tables tend to be accessed in batch mode, but there are exceptions.
The primary data file is on its own RAID-5 array. One transaction log and TEMPDB are isolated on two separate RAID-1 arrays. Now I have another RAID-5 array on its own controller at my disposal (let’s call it DATA2). So, the question is what to put on DATA2? Keeping in mind that I don’t have lots of time to spend in this, which of the following are likely to give the most performance bang for the buck?
1. Create a secondary data file on DATA2 of roughly equal size to the primary and let it fill in naturally as extends are allocated to objects in the database.
2. Move some heavily used tables to DATA2.
3. Move non-clustered indexes of heavily used tables to DATA2.
4. Some combination of the above.
I’m sure the best answer requires intimate knowledge of the data and usage patterns, but general suggestions and personal experiences are welcome!
-
You comment "don't have lots of time to spend in this" is concerning. Is this important enough to even continue? The replacement for time, might be throwing hardware/money at it.
My gut is telling me that you have outgrown discrete RAID arrays and need to move on to something with more IO bandwidth, multi fiber connected NAS or SAN devices.
My experience with growing a system by adding more RAID controllers causes you to spend more time managing the hardware and moving file around to accomodate performance while dealing with disk space constraints.
[font="Arial"]Clifton G. Collins III[/font]
-
Clifton, thanks for your feedback. Unfortunately I'm in an environment where the IT operation is at the severe end of the "every-IT-shop-is-understaffed-and-underfunded" scale. Given the huge project load (much of which is not even DBA related) at some point I have to cut corners and make compromises that might not be necessary if I could devote at least 30% of my time to the DBA role.
We've discussed high-speed NAS and SAN, but having the time/resources to manage the storage is as much an issue as cost. Since I already have an available RAID-5 array I may as well use it until the larger issues can be resolved. More resources and funding are supposedly coming soon or "in the mail" as they say.
In the absence of feedback to the contrary, I'm leaning toward option 1 (some performance improvement; less time consuming). The alternative is probably a combination of 2 and 3 (possibly better performance at the cost of additional time for utilization measurement, analysis, set up and maintainenance.).
-
1. Create a secondary data file on DATA2 of roughly equal size to the primary and let it fill in naturally as extends are allocated to objects in the database.
2. Move some heavily used tables to DATA2.
3. Move non-clustered indexes of heavily used tables to DATA2.
4. Some combination of the above.
Option 1 is far and away the least useful of the options you list. If improving OLTP performance is your primary goal, index placement on the new array is prolly best bang for simple/quick buck. If improving batch job performance is primary, create a filegroup on each array and then create a clustered index on a table spread across those filegroups to move your largest table(s) onto both arrays. Assuming you have good controllers, you can do both and gain even more performance.
I also recommend hiring a performance consultant to give your system a review. This should provide best ROI.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
SQLGuru, thanks for the helpful feedback. Too bad option 1 has little value in your experience since it's the most attractive option time wise. I really like the index and splitting large table combination. I just have to watch my time due to other roles and keep in mind that this is a vendor database. But they rely on me for database admin anyway.
The other thing is, when I create (restore) copies of this production database for test and for sending copies to the vendor for ongoing development projects, I need to put the database back in simple form (one data file; one transaction log; data and indexes together in primary filegroup). This will be easy once I get everything scripted.
Other than disk I/O bottlenecks on the primary data file, this server is in good shape (memory, CPU, network) and I've already identified the worst performing queries which the vendor has improved.
Congratulations to those who can afford a performance consultant!
-
1) You don't need to put the database back into simple form when you restore it elsewhere. Generate a restore script that simply places all existing files on the same drive. That keeps filegroup structures without any other change necessary.
2) A good performance consultant is NOT an expense!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Are you a performance consultant by any chance?
-
Yes, performance tuning is one of my bailiwicks. You will note, however, that I didn't say you should hire me.
I get most of my business via word-of-mouth, with almost all of the rest coming from attendees at the classes I teach or the presentations I give. I just got my first forum-acquired consulting engagement ever about a month ago. Gig was 2000 miles away - all done via chat, phone, email and VPN. Gotta love the information age!!
Seriously though - unless you have a very good DBA available from application design through end-of-life the odds are high that there is a lot of headroom to be opened up on your servers. I have never had a client that was otherwise. And the best consultants will not only improve performance, they will mentor the permanent staff so they can do better in the future.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply