question about SQL-MDF-Files

wirtnixwirtnix Member Posts: 50
edited 2008-06-23 in SQL Performance
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?

Comments

  • krikikriki Member, Moderator Posts: 9,086
    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]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • krikikriki Member, Moderator Posts: 9,086
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
Sign In or Register to comment.