Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database data files Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 9:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 3,014, Visits: 3,101
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,
SQL Server developer at Seavus
www.seavus.com
Post #1454587
Posted Monday, May 20, 2013 9:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1454601
Posted Monday, May 20, 2013 9:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 3,014, Visits: 3,101
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,
SQL Server developer at Seavus
www.seavus.com
Post #1454607
Posted Monday, May 20, 2013 10:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1454615
Posted Monday, May 20, 2013 2:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 3,014, Visits: 3,101

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,
SQL Server developer at Seavus
www.seavus.com
Post #1454721
Posted Monday, May 20, 2013 2:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1454726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse