· 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.
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.
ReplyDeleteAlso, 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.
Creating indexes in a different file group provides multiple paths (including threads) to access the records. That includes clustered indexes.
ReplyDeleteGood info on the article. It would be interesting to create test cases to prove your points!
Thanks.
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).
ReplyDeleteFriend 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.
ReplyDeletenorth 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
ReplyDeleteugg,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
ReplyDeleteninest123 16.03