Wednesday, March 7, 2012

SAN/Filegroup Question

Does anyone know if there is a significant advantage to break up a database into seperate files & filegroups if the files are located on the same SAN? Should the logical drives that they are located on matter?
Thanks,
TimOn the same SAN probably no performance benefit. However, there are other reasons to create filegroups, etc.|||

It depends somewhat on your SAN. We use a 3PAR SAN, which spreads all files across all available drives in the SAN using 256MB "chunklets". With 3PAR, it does not matter quite so much how you setup your files.

With more conventional SANs (like your typical EMC), I like to have several smaller data files in separate filegroups instead of one large one, just to make administration easier. For example, you can do filegroup backups faster with a setup like that.

I like to create files like this on a SAN:

One log file on its own drive/LUN on the SAN (since log files use sequential access).

One TempDB file per CPU (on a separate drive/LUN on the SAN) to help prevent allocation contention issues.

Several data files, each in its own filegroup (on a separate drive/LUN on the SAN) since data files use random access.

No comments:

Post a Comment