Database Partitioning

Question

Database Partitioning

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

if you have multiple drives you would see an io improvement as data would be written to different files in parallel. Its like doing disk striping via the database physical layout.

If you are using raid devices you could also see IO improvements as the different files are laid down on different parts of the SAN.

as with everything you need to test to see what benefits you gain.

If you plan your partitions carefully you should be able to end up with filegroups containing data for one partition only of just one table, then perhaps those filegroups could be set read only and you could look to do filegroup level backups.

Personally unless this database is very large and you are having problems with your backup window or length of time to recover I would avoid this level of complexity.

---------------------------------------------------------------------

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

George, what is meant when you are speaking about data being written to files in round robin fashion (I get the part about proportionaly growing files, but need clarification on which 'files').

Per filegroup, shouldn't there be just one .mdf, .ndf, or .ldf file?

janyalee Valued Member Points: 74 More actions · Answer 3

TK432.ndf is the file. Each filegroup may contain one or more files.

To illustrate this, see the tabs for files and filegroups in SS Management Studio.

It's explained in Books Online Files and Filegroups Architecture

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

janyalee (7/12/2010)
TK432.ndf is the file. Each filegroup may contain one or more files.
To illustrate this, see the tabs for files and filegroups in SS Management Studio.
It's explained in Books Online Files and Filegroups Architecture

Thanks for that. I missed that. OK! There it shows more than one file specified for the same filegroup. The paths are identical except the filenames are different.

Why would one want to create two seperate files going to the same drive on the same filegroup? (Certainly it wouldn't be running out of space as both filegroups are on the same drive).

Does anyone have a 'rule of thumb' for determining how many data and log files to create for a new database for optimal performance?

Thank you very much. I appreciate your patience.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

round robin - one after the other then back to the beginning, so if 4 files, 1,2,3,4,1,2,3,4 etc.

One definite rule, only one log file because log files are sequential write files, not round robin, so a file is filled before the next one is written to, therefore no point in a second .ldf file.

for filegroups and data files - it depends totally on the database. Only rule of thumb i have is for 95% of databases just an mdf is good enough.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 6

see this thread as well, there are some links to msdn description of how SQL uses database files.

http://www.sqlservercentral.com/Forums/Topic950819-361-1.aspx#bm951048

---------------------------------------------------------------------

KoldCoffee SSCoach Points: 18971 More actions · Answer 7

KoldCoffee

SSCoach

Points: 18971

July 13, 2010 at 9:14 am

#1192127

Thank you!

KoldCoffee SSCoach Points: 18971 More actions · Answer 8

One more question on partitioning. Is it common to add a DateCreated column to every table and assign every table to the same partition scheme so that related data ends up in mostly the same filegroups?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 9

hxkresl (7/21/2010)
One more question on partitioning. Is it common to add a DateCreated column to every table and assign every table to the same partition scheme so that related data ends up in mostly the same filegroups?

One thing is unrelated to the other. You can distribute data as it pleases you no matter if partitioning schemas are alike or not.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.