question about SQL-MDF-Files

wirtnix · 2008-06-23

Hi all,

i have a SQL 2005 64bit machine running. All Navision-data are in one single MDF-File on a 14-Disk SAN. the size is qurrently about 160GB

would it improve performance if the data would be stored in smaller data files (but on the same SAN) or is the file size no reason for low performance?

if yes:
how can i sparse this big file into many small ones?

kriki · 2008-06-23

That size is not the problem for slow performance.
I would seek the slow performance in other reasons:
-Do some index/SIFT tuning for SQL
-the drives must be in RAID10 for DB and RAID10 or RAID1 for TL
-The drives must be dedicated to the DB or the TL. The SAN may NOT use it to store other things (in a SAN, this is a big problem). See also : http://dynamicsuser.net/blogs/singleton/archive/2008/03/30/why-i-don-t-want-my-clients-to-use-sans-for-dynamics-nav-navision.aspx#117092
-the drives should be formatted with a cluster-size of 64KB AND the RAID10 should be striped in 64KB-stripes.
-...

[Topic moved from Navision forum to SQL Performance forum]

Slawek_Guzek · 2008-06-23

wirtnix wrote:

would it improve performance if the data would be stored in smaller data files (but on the same SAN) or is the file size no reason for low performance?

Yes having database spread across several files will make it faster but only if you create separate volume for each new file, and use different drive letter or mount point for each of volume, and then put the extra files one to the new separate disk or directory each. And only if you don't have any bottleneck between SQL and SAN, like saturated network connection during read or write, or small number of spindles.

how can i sparse this big file into many small ones?

You need first add a few new files (at most as many as number of CPUs seen by OS and SQL), presize them to some reasonable size (make them all of equal size), and then shrink your primary file.

Search SQL Books Online for

ALTER DATABASE ... ADD FILE(..)

for adding new files and

DBCC SHRINKFILE(..)

for shrinking your primary file

However I think that Kriki has pointed you to better solutions - separate database file from log file (put it on separate disk set, separate volume on the same disk set is not enough), do some index/SIFT tuning. This will give you much better improvement than only spreading the data across the files.

And - DON'T believe if somebody tells you that SAN will handle everything and the same disk set can be shared between log and data volumes.

Regards,
Slawek

kriki · 2008-06-23

Slawek Guzek wrote:

And - DON'T believe if somebody tells you that SAN will handle everything and the same disk set can be shared between log and data volumes.

2 advices on this:
1) if your SAN-hardware vendor says this, search another one. (in case of a consultant => search another one) (if you get to over 24 disks, it might work well for performance I read somewhere [maybe in the blog of David]).
2) if your SAN-hardware vendor cannot give you a whitepage (or best practices,...) on how to configure a SAN for SQL, search another one.

bbrown · 2008-06-23

kriki wrote:

...
2) if your SAN-hardware vendor cannot give you a whitepage (or best practices,...) on how to configure a SAN for SQL, search another one.

The problem with whitepapers on configuring SQL is that they are too generic. 90% of the ones I see advice RAID 5 as a valid solution. IF you are looking at whitepapers be sure they are dealing with configuring SQL in a transaction processing enviroment.

question about SQL-MDF-Files

Comments

Categories