May 20, 2013 at 9:23 am
Hi All.
I have a database with a single file which size is 288GB.
I'm dropping the indexes and the data is only 90GB. I added three more files and re-created the dropped indexes. Data is equally distributed on the three files and reaches about 66GB on each. In future all four files will have same size.
There is also possibility to remove the data, and then insert it again, but that is the harder option, and much time consuming. So in that case I can have all four files with equal size -288/4 = 72GB. My situation is 90GB,66GB,66GB,66GB.
I know it is important for the tempdb data files' initial sizes to be equal and with same autho-grow configuration.
Does it matter with the user-database data files? I think no. Anyone against, with suggestion, or to confirm ..you're welcome.
Thanks in advance,
IgorMi
Igor Micev,My blog: www.igormicev.com
May 20, 2013 at 9:48 am
IgorMi (5/20/2013)
I have a database with a single file which size is 288GB.I'm dropping the indexes and the data is only 90GB. I added three more files and re-created the dropped indexes. Data is equally distributed on the three files and reaches about 66GB on each. In future all four files will have same size.
Why? What's the purpose of all this work? What do you expect to gain?
I know it is important for the tempdb data files' initial sizes to be equal and with same autho-grow configuration.
Does it matter with the user-database data files? I think no. Anyone against, with suggestion, or to confirm ..you're welcome.
The file recommendations for TempDB mostly do not apply to user databases.
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
May 20, 2013 at 9:58 am
GilaMonster (5/20/2013)
IgorMi (5/20/2013)
I have a database with a single file which size is 288GB.I'm dropping the indexes and the data is only 90GB. I added three more files and re-created the dropped indexes. Data is equally distributed on the three files and reaches about 66GB on each. In future all four files will have same size.
Why? What's the purpose of all this work? What do you expect to gain?
I know it is important for the tempdb data files' initial sizes to be equal and with same autho-grow configuration.
Does it matter with the user-database data files? I think no. Anyone against, with suggestion, or to confirm ..you're welcome.
The file recommendations for TempDB mostly do not apply to user databases.
Yes, the data files number is not related to the tempdb data files. I also think to go with two data files.
More files is good when they are on different drives.
And will it bring performance gain when on one drive?
Igor Micev,My blog: www.igormicev.com
May 20, 2013 at 10:17 am
IgorMi (5/20/2013)
More files is good when they are on different drives.
Maybe
And will it bring performance gain when on one drive?
No.
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
May 20, 2013 at 2:01 pm
IgorMi (5/20/2013)
More files is good when they are on different drives.
Maybe
And will it bring performance gain when on one drive?
No.
Having one physical drive D:\ on your notebook and having four physical drives configured in RAID5 as D:\ is different, and having multiple four-disks configured in RAID5 (D:\, E:\, F:\ and G:\) is different than the previous two.
For what is more concretely your 'Maybe' and 'No'?
Best,
Igor
Igor Micev,My blog: www.igormicev.com
May 20, 2013 at 2:12 pm
Doesn't matter whether it's one drive or multiple in a RAID array, my answers stand. You may or may not get a performance improvement from splitting files onto multiple independent drives (or arrays). You will not see a performance improvement by splitting files onto the same drive (or array).
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply