By default when a database is created it comes with two files
1) Data file ( .mdf)
2) Log file (.ldf )
We can add additional file groups & they go by name (.ndf )
When a new table is created it is created under default primary data file group ( .mdf )
how ever we can set the file group to secondary file groups ( .ndf)
Big Warehouses uses mutiple files & file groups created for a database to address the below scenarios :
1) scale bigger
2) restore faster
3) Querier Quicker
1) DATA :
we can categorise the tables in to different groups based on the importance of them. some of the tables may be small but still they may hold the key for a website to be up & running like user accounts, roles etc..
we can place them in a file group
other set of tables which are not important as above tables, still needed for some of the key functionalities with in a website , we can place them in a different file group.
when disaster hits, we can restore the first file group making the website instantly available even before restoring the second file group which has some other functionality. this is called a piece meal restore.
restore primary file group , then rest of the file groups can be restored one after another & with each restore some portion of database will go online .
2) BINARY :
Binary data ( xml blobs, images, documents, pdfs etc ) can be moved to a different file group as they are mostly historical & doesn't have much of the updates , those kind of tables can be placed on a different file group on a RAID 5 ( Reads are faster but not writes )
3) INDEXES :
Indexes on a seperate filegroup doesn't fetch much of the advantage as read would be faster how ever still the writes (inserts/ updates ) need to write it to two places.
4) LOG :
Log files : Log files are sequential , they start from left & go all the way to right , then they come back to the beginning. If log file is filled up then it increments the size using autogrowth .
Only one log file is needed at any point of time. Initial Size & incremental growth has to be carefully planned so that it doesn't end up in more VLFs ( causing more defragmentation & performance issues ) & at the same time they can't be too big for the log file taking more time to do a back up.
Usually Big Warehouses are suggested to have 50-100 VLFs . Any thing more than that will definately need to be addressed.
Thanks for reading .. more to come :)
1) Data file ( .mdf)
2) Log file (.ldf )
We can add additional file groups & they go by name (.ndf )
When a new table is created it is created under default primary data file group ( .mdf )
how ever we can set the file group to secondary file groups ( .ndf)
Big Warehouses uses mutiple files & file groups created for a database to address the below scenarios :
1) scale bigger
2) restore faster
3) Querier Quicker
1) DATA :
we can categorise the tables in to different groups based on the importance of them. some of the tables may be small but still they may hold the key for a website to be up & running like user accounts, roles etc..
we can place them in a file group
other set of tables which are not important as above tables, still needed for some of the key functionalities with in a website , we can place them in a different file group.
when disaster hits, we can restore the first file group making the website instantly available even before restoring the second file group which has some other functionality. this is called a piece meal restore.
restore primary file group , then rest of the file groups can be restored one after another & with each restore some portion of database will go online .
2) BINARY :
Binary data ( xml blobs, images, documents, pdfs etc ) can be moved to a different file group as they are mostly historical & doesn't have much of the updates , those kind of tables can be placed on a different file group on a RAID 5 ( Reads are faster but not writes )
3) INDEXES :
Indexes on a seperate filegroup doesn't fetch much of the advantage as read would be faster how ever still the writes (inserts/ updates ) need to write it to two places.
4) LOG :
Log files : Log files are sequential , they start from left & go all the way to right , then they come back to the beginning. If log file is filled up then it increments the size using autogrowth .
Only one log file is needed at any point of time. Initial Size & incremental growth has to be carefully planned so that it doesn't end up in more VLFs ( causing more defragmentation & performance issues ) & at the same time they can't be too big for the log file taking more time to do a back up.
Usually Big Warehouses are suggested to have 50-100 VLFs . Any thing more than that will definately need to be addressed.
Thanks for reading .. more to come :)
No comments:
Post a Comment