Tuesday, 5 December 2006

Recommendations for SQL Server File Group Creation

· The primary file group must be totally separate and should be left to have only system objects and no user defined object must be created on this primary file group.
· The primary file group should not be set as default file group.
· Separating the system objects from other user objects will increase performance and enhance ability to access tables in the case of serious data failures
· If there are more than one physical drive available in the system then try to create as many physical files per file group and put one file per disk. This will improve performance because whenever a table is accessed sequentially in SQL Server, a separate thread is created for each file to read the table’s data in parallel.
· A separate file group can be created for indexes and the number of physical files can be created as mentioned in the above point.
· Creating a table in one file group and place the text, next and image columns in a different file group on different physical disks.
· The log file must be placed on different physical disk then the data files, because the logging is more write intensive, it is important to have the log on the disk that have good I/O performance. The log file should be RAID 0 or 1 which supports write intensive operation.

11 comments:

Anonymous said...

Um no, you never put a database on a RAID without fault tolerance. Hard drives always crash. That is a good way to ruin your database.

Also, why would you seperate out the index and tables? Where the clustered index is... so is your data. Your non-clustered indexes are not that large - not a whole lot of I/O gained by doing this.

Anonymous said...

Creating indexes in a different file group provides multiple paths (including threads) to access the records. That includes clustered indexes.

Good info on the article. It would be interesting to create test cases to prove your points!

Thanks.

Anonymous said...

on VLDB you should indeed have a different file group for indexes and data. However, one thing I do is put the cluster index on the same drive as the data and non clustered indexes in the index file group. The reason this is done is that your data is organized and stored according to the cluster index (have you ever noticed that the clustered index takes up little to no space).

Alexis said...

Friend said me about one nice tool-recover mdf,which recover sql files.And after some time I used it and tool recovered all my data very quickly.Besides that it executed this operation for free and showed repair .mdf files of Microsoft SQL Server 2005.

oakleyses said...

louis vuitton outlet online, kate spade handbags, ray ban sunglasses, kate spade outlet online, prada outlet, michael kors outlet, coach outlet store online, nike shoes, nike air max, jordan shoes, nike free, polo ralph lauren, christian louboutin shoes, longchamp outlet, michael kors outlet online, longchamp handbags, gucci handbags, longchamp outlet online, polo ralph lauren outlet, michael kors outlet store, michael kors outlet online, oakley vault, red bottom shoes, prada handbags, tory burch outlet, cheap oakley sunglasses, michael kors outlet online, burberry outlet online, chanel handbags, oakley sunglasses, burberry outlet online, coach purses, louis vuitton outlet, christian louboutin outlet, tiffany jewelry, coach outlet, nike air max, tiffany and co jewelry, ray ban outlet, louboutin shoes, louis vuitton handbags, michael kors outlet online, louis vuitton, coach outlet, louis vuitton outlet

oakleyses said...

north face, sac michael kors, abercrombie and fitch, scarpe hogan, nike air max, hollister, louis vuitton, nike free, true religion outlet, ray ban uk, vans pas cher, oakley pas cher, ray ban pas cher, lululemon, hollister, air max pas cher, michael kors canada, north face pas cher, air jordan, longchamp pas cher, nike trainers, nike free pas cher, hermes pas cher, air max, sac louis vuitton, true religion outlet, guess pas cher, converse pas cher, true religion, mulberry, nike huarache, timberland, ralph lauren pas cher, burberry pas cher, nike air force, sac vanessa bruno, lacoste pas cher, louis vuitton uk, nike roshe run, chaussure louboutin, longchamp, ralph lauren, barbour, nike roshe, nike blazer pas cher, michael kors uk, tn pas cher, new balance pas cher, louis vuitton pas cher, nike air max

oakleyses said...

giuseppe zanotti, insanity workout, canada goose, valentino shoes, bottega veneta, new balance outlet, canada goose pas cher, marc jacobs outlet, moncler, asics shoes, beats headphones, hollister, reebok shoes, ugg boots, canada goose outlet, vans outlet, rolex watches, ugg outlet, moncler outlet, canada goose outlet, mac cosmetics, lululemon outlet, uggs outlet, wedding dresses, ferragamo shoes, soccer shoes, babyliss pro, north face outlet, mont blanc pens, jimmy choo shoes, mcm handbags, longchamp, ugg, canada goose uk, soccer jerseys, ghd, birkin bag, uggs on sale, celine handbags, moncler, nfl jerseys, roshe run, chi flat iron, instyler ionic styler, herve leger, p90x workout, ugg soldes, abercrombie and fitch, north face jackets, canada goose outlet

ninest123 said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

ninest123 said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03