March 17, 2011 at 12:12 pm
Hi,
I have a 100GB+ database that is on 1 data file and 1 log file. The only drive (e:) accessible is a 1TB disk array RAID10 (14 hard drive I think).
I was told that there will be no performance gain if I add more data files for data and indexes since they will be on the same disk array (e:).
Is it right?
Thanks,
March 17, 2011 at 12:15 pm
Correct. All it does is get you ready for a separation down the road.
The only time you get performance gains between the multiple files is when they are on separate physical spindles, that includes the ldf/mdf combo, as well. If you haven't split physically, you haven't gained anything.
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
March 17, 2011 at 12:47 pm
Thanks.
If I add a data file on my primary group, is there a was to move objects form the current one to the new one? If I want to balance the files for example.
I know we can move from one group the the others/
Thanks,
March 17, 2011 at 12:53 pm
Rem70Rem (3/17/2011)
Thanks.If I add a data file on my primary group, is there a was to move objects form the current one to the new one? If I want to balance the files for example.
I know we can move from one group the the others/
Thanks,
SQL Server will balance them out over time for you. The only direct way to control what's in each file is by filegroup assignment.
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
March 17, 2011 at 3:21 pm
Is this on a SAN? Are you looking for performance improvements in reads or writes? speak to your Storage people. Depending on the exact configuration you can see improvements because of the striping in a RAID configuration.
Only way to be sure is to test it out.
We got a 20% improvement in an ETL process by creating the filegroup with 4 files all on the same drive. didn't believe it myself till I saw it.
your mileage may vary.
---------------------------------------------------------------------
March 17, 2011 at 3:43 pm
george sibbald (3/17/2011)
Is this on a SAN? Are you looking for performance improvements in reads or writes? speak to your Storage people. Depending on the exact configuration you can see improvements because of the striping in a RAID configuration.Only way to be sure is to test it out.
We got a 20% improvement in an ETL process by creating the filegroup with 4 files all on the same drive. didn't believe it myself till I saw it.
your mileage may vary.
Was that on TempDB or a User DB? I'm still not sure I'd believe it for a User DB, doesn't make sense offhand, unless something was happening in the caching between four files vs. one in the SAN between the physical drives and the database level.
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
March 17, 2011 at 4:03 pm
Userdb.
don't quite understand why myself, but as disk technology moves forward old truths can become outdated.
I still would not recommend it as a default approach to file layout, and have not done it anywhere else but this particular application, but if you are desperate for performance and have tried the more standard approaches, might be worth a test, remembering its a bit hard to back out once done.
---------------------------------------------------------------------
March 17, 2011 at 4:12 pm
george sibbald (3/17/2011)
Userdb.don't quite understand why myself, but as disk technology moves forward old truths can become outdated.
I still would not recommend it as a default approach to file layout, and have not done it anywhere else but this particular application, but if you are desperate for performance and have tried the more standard approaches, might be worth a test, remembering its a bit hard to back out once done.
Would you be able to easily find out what your SAN hardware configuration is? I'm very curious about this.
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
March 17, 2011 at 4:13 pm
What are the steps to rollback if needed?
March 17, 2011 at 4:17 pm
Rem70Rem (3/17/2011)
What are the steps to rollback if needed?
Generate a new filegroup, move *everything* into the new filegroup. Rebuild the original Primary as desired, move everything back, and remove the temporary filegroup.
IE: You don't, not really, unless you really, really have to.
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
March 17, 2011 at 5:09 pm
Craig Farrell (3/17/2011)
george sibbald (3/17/2011)
Userdb.don't quite understand why myself, but as disk technology moves forward old truths can become outdated.
I still would not recommend it as a default approach to file layout, and have not done it anywhere else but this particular application, but if you are desperate for performance and have tried the more standard approaches, might be worth a test, remembering its a bit hard to back out once done.
Would you be able to easily find out what your SAN hardware configuration is? I'm very curious about this.
I'll have a go. Easy might not be the word though.
---------------------------------------------------------------------
March 17, 2011 at 5:24 pm
rollback -
dbcc shrinkfile with the emptyfile option, followed by alter database command to remove the file.
- or restore from the backup you would of course take before doing this, but that means possible data loss.
Would require an outage though and take a while and I have seen posts about about people having problems with emptyfile.
So, although I am no longer convinced multiple files per filegroup on the same physical drive is a complete waste of time, unless you were able to test this in the development stage or can recreate your production database and test thoroughly I cannot in all honesty recommend going ahead and splitting your filegroup into multiple files, especially if you do not have a pressing IO issue.
---------------------------------------------------------------------
March 17, 2011 at 6:39 pm
george sibbald (3/17/2011)
rollback -dbcc shrinkfile with the emptyfile option, followed by alter database command to remove the file.
That would work (and was my first thought too), but I think I prefer Craig's much more intensive (time and I/O) method of emptying the primary filegroup and rebuilding it. Emptyfile can end up with some serious fragmentation issues, depending on how much data was added while multiple files were enabled.
March 18, 2011 at 12:37 am
We got a 20% improvement in an ETL process by creating the filegroup with 4 files all on the same drive. didn't believe it myself till I saw it.
March 18, 2011 at 3:29 am
alexus8888 (3/18/2011)
We got a 20% improvement in an ETL process by creating the filegroup with 4 files all on the same drive. didn't believe it myself till I saw it.
Alexus, I believe I speak for everyone when I say your marketing is highly annoying. Dig yourself a hole, climb into it, and then bury yourself. Please?
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply