September 8, 2011 at 12:49 pm
If you had a DB that was several hundred GB’s in size that consisted of a single mdf and ldf file and your IT/Hardware guys said they were adding a new drive to your DB Server for you to do with as you pleased how would you o about determining what would be best to move to that new drive from your DB thru the addition of one or more file/filegroups on this new drive?
My DB is just under 250GB and while we’ve managed to use it with acceptable performance I’d like to see if we can improve upon that now that I’m getting an additional drive form my DB server.
I’m debating between placing the indexes of my very large and top heavy transaction tables (tables that store the details of accounting or financial data) on this drive or the security & access tables that the application that uses this databases uses to determine who gets access as well as to what and whether they are already logged in (from somewhere else) and so on. The security of the data thru the application is controlled by the application and there’s nothing I can do to change it so I have to leave these tables as is.
Thoughts
Kindest Regards,
Just say No to Facebook!September 8, 2011 at 12:52 pm
The log file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2011 at 4:05 pm
GilaMonster (9/8/2011)
The log file.
Thanks Gail and I woudl certainly do that first but the llg file already is on its own drive. I negelcted to say that in my post when detailing the fiels that make up the DB now.
Currently the server has 3 dedicated drives (1 for OS, 1 for mdf and 1 for ldf) and this new one will be the fourth.
Knowning that what would you move next or what process woudl you use to determine what to move to the new drive?
Thanks Gail
Kindest Regards,
Just say No to Facebook!September 8, 2011 at 4:31 pm
Find the largest and hardest hit tables, and try to split them from join to join across two filegroups on the different drives. This way you can access two different spindlesets simuntaneously when doing large queries.
Or get your LOB data (if any) out of the way.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2011 at 4:52 pm
Evil Kraig F (9/8/2011)
Find the largest and hardest hit tables, and try to split them from join to join across two filegroups on the different drives. This way you can access two different spindlesets simuntaneously when doing large queries.Or get your LOB data (if any) out of the way.
Thanks Evil but I'm lucky in that we don' t have any LOB in the database. As for splitting the tables I don't think I can do it and not risk losing our support with the vendor. We have a very tight agreement with the vendor whose app uses the database and unfortunately they are weak in the area of RDBMS and SQL in general and so something like a split table would be beyond their support capabilities and I can guarantee that if anything was wrong that was unexplainable the first thing they'd blame is our table split saying that’s not their recommended best practices and so they cannot provide further support till that is corrected. Good recommendations though.
I can however put the table itself and or its indexes on a separate File/Filegroup that is on the new drive since that would not alter/change the structure of the table.
Thanks for the suggestions.
Kindest Regards,
Just say No to Facebook!September 8, 2011 at 5:04 pm
YSLGuru (9/8/2011)
I can however put the table itself and or its indexes on a separate File/Filegroup that is on the new drive since that would not alter/change the structure of the table.
Sorry, I could see how my comment was misconstrued. That is what I meant. 🙂
So if I had something like:
select * from tbla join tblB join tblC join tblD
I'd split tblB and tblD off to the second filegroup, so it could go back and forth. This of course depends more on the execution plans then what you wrote as to how appropriate it will be, but I've found it to be a reasonable starting point.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 9, 2011 at 1:10 am
YSLGuru (9/8/2011)
GilaMonster (9/8/2011)
The log file.Thanks Gail and I woudl certainly do that first but the llg file already is on its own drive. I negelcted to say that in my post when detailing the fiels that make up the DB now.
Currently the server has 3 dedicated drives (1 for OS, 1 for mdf and 1 for ldf) and this new one will be the fourth.
Knowning that what would you move next or what process woudl you use to determine what to move to the new drive?
Thanks Gail
Gail is the expert in this area but I believe the next thing I'd move is TempDB. If that's already on separate physical spindles, then maybe I'd consider moving non clustered indexes for the big table.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2011 at 2:26 am
Jeff Moden (9/9/2011)
Gail is the expert in this area but I believe the next thing I'd move is TempDB. If that's already on separate physical spindles, then maybe I'd consider moving non clustered indexes for the big table.
Agreed. Log first, TempDB second.
After that it's a lot harder. Splitting the DB apart for performance gains requires first that the bottlenecks are IO and requires a fair bit of analysis of the database to work out what the heaviest IO tables are (hint, they may not be the heaviest used) and to figure out whether splitting them out will help or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 8:19 am
GilaMonster (9/9/2011)
Jeff Moden (9/9/2011)
Gail is the expert in this area but I believe the next thing I'd move is TempDB. If that's already on separate physical spindles, then maybe I'd consider moving non clustered indexes for the big table.Agreed. Log first, TempDB second.
After that it's a lot harder. Splitting the DB apart for performance gains requires first that the bottlenecks are IO and requires a fair bit of analysis of the database to work out what the heaviest IO tables are (hint, they may not be the heaviest used) and to figure out whether splitting them out will help or not.
That makes perfect sense. I can't believe I didn;t even consider moving tempdb. Thanks to all.
Kindest Regards,
Just say No to Facebook!September 9, 2011 at 9:40 am
Just remember that in a SAN envirionment, separate drive letters don't necessarily equate to separate spindles. Check with the folks managing your hard disk storage space to make sure the drive letters do, in fact, relate to separate spindles.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2011 at 10:26 am
Jeff Moden (9/9/2011)
Just remember that in a SAN envirionment, separate drive letters don't necessarily equate to separate spindles. Check with the folks managing your hard disk storage space to make sure the drive letters do, in fact, relate to separate spindles.
Yep. That was the first thing I got clarification on when going over the new drive with my hardware guys. I have not had to deal directly with hardware stuff in years and so the newer SANS setup is very much different from the odler days of internal drives on a RAID.
Thanks Jeff
Kindest Regards,
Just say No to Facebook!September 9, 2011 at 10:44 am
No sooner then I replied back about relocating tempdb to its own drive I remembered that I had already done this last month. I know that sounds bad at first that I did not remember that right away but we have a number of SQL boxes here and the one I've been asking about in this thread is but one of these. My instant memory ain't what it used to be but my notes and change log are very verbose and so I make copious notes for late reference when needed.
When I first posted the question the IT guys had not yet added this new drive but now its in place and ready to use. So as of right now the DB server has the following drives:
C: OS
D: Data
L: Log files (for the databases on the server)
Y: drive just for tempdb
I: the new drive that just came on line
All are separate physical drives in the sans (per my IT guys) so all have their own heads/spindles/ect.
Based on prior posts I’m going to look at putting the big Indexes (non-clustered) on this new drive. With it being the weekend and less users on line it will be a good time to move them. The unique (maybe it’s not really that unique) scenario for us is that depending on the time of the month some tables have heavier I/O then others. We are in an industry where a large number of payments come in at the start of each month and so the tables containing the transactional info (payments & billing details) get hit hard and again around middle of the month when late charges are applied to outstanding items. The rest of the month the transaction tables are not the busiest. Our DB’s I/O is more of a pattern with various groups of tables getting heavy Read/Write access at certain periods within a month. It’s one of the reasons I’ve pondered using dynamic Indexes where the index is enabled/disabled based on the time of the month. Towards the end of the month when we have a large amount of reporting on the financial data it helps to have a lot of index coverage on them. Those same indexes however slow it down when it’s the start of the month and there’s a lot of insert/update activity on these same tables.
Thanks again
Kindest Regards,
Just say No to Facebook!Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply