Post reply

Data and log files with UNC path

  • February 7, 2013 at 12:36 pm

    #273929

    Hi,

    I'm analyzing a server with a RAID50 system. The SAN is supposed to support 2000 IO operations per second (at least it's what I think the hardware manager said).

    There's just one "big disk" and it's partitioned to "split" information.

    There's one BIG partition where all the database files are (MDF and LDF) files.

    According to the install recommendations there should be a disk system for MDF, LDF and tempdb, but DELL (they are the hardware managers) state that the 2000 IO operations are enough for everything.

    The instance has 1500 databases, this means 3000 files on the same folder.

    They are monitoring the IO system and there aren't more than 1000 ops/s but the IO statistics from SQL Server give 167ms IO read stall avg for a database. I checked the database properties and noticed it was mapped via UNC (\\sqlsrv01\Data\...) and not E:\MSSQL\Data\....

    Two questions:

    1. does the UNC mapping has some overhead since it access via network adapter or is windows smart enough to know the UNC is a local path and uses the local path instead?

    2. does the 3000 files on the same folder have impact on the file system ? the ideal number of files + folders on a folder is 500, over that the file system is slower accessing the files (read this somewhere)...

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 7, 2013 at 3:14 pm

    #1585679

    PiMané (2/7/2013)

    1. does the UNC mapping has some overhead since it access via network adapter or is windows smart enough to know the UNC is a local path and uses the local path instead?

    Is E:\a mapped network drive or is it a mounted Windows Volume?

    2. does the 3000 files on the same folder have impact on the file system ? the ideal number of files + folders on a folder is 500, over that the file system is slower accessing the files (read this somewhere)...

    As I understand it that is only an issue if Windows has to enumerate the files for something like a DOS DIR command or in Windows Explorer because it runs stat on each file so it can display things like Create Date, Size, etc. When accessing a file by direct path though, as SQL Server would per what sys.master_files shows us, I am not aware of any performance impact related to how many other files might be in that folder.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 7, 2013 at 3:20 pm

    #1585681

    opc.three (2/7/2013)

    PiMané (2/7/2013)

    1. does the UNC mapping has some overhead since it access via network adapter or is windows smart enough to know the UNC is a local path and uses the local path instead?

    Is E:\a mapped network drive or is it a mounted Windows Volume?

    2. does the 3000 files on the same folder have impact on the file system ? the ideal number of files + folders on a folder is 500, over that the file system is slower accessing the files (read this somewhere)...

    As I understand it that is only an issue if Windows has to enumerate the files for something like a DOS DIR command or in Windows Explorer because it runs stat on each file so it can display things like Create Date, Size, etc. When accessing a file by direct path though, as SQL Server would per what sys.master_files shows us, I am not aware of any performance impact related to how many other files might be in that folder.

    1. It's a mounted logical drive over a physical RAID50 system disk, The \\ is a shared folder on the drive and most databases are "mounted" via UNC and not directly to E:2. Ok.

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 7, 2013 at 3:53 pm

    #1585686

    PiMané (2/7/2013)

    1. It's a mounted logical drive over a physical RAID50 system disk, The \\ is a shared folder on the drive and most databases are "mounted" via UNC and not directly to E:\

    Logical Disks can be mapped network drives. Sorry to split hairs but it matters here. Do you see E:\ when you run this from a PowerShell prompt on the server?

    Get-WmiObject win32_volume | select name

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 8, 2013 at 3:16 am

    #1585827

    Disc E: is a logic local drive...

    the command returns

    C:E:Doesn't windows "know" the \\sqlsrv01\Data is a local mapping (E:\MSSQL\Data..)?

    Will it use the DNS and other network related mechanism to reach the files?

    If so, I'll have to create a script to detach all the databases mapped to \\ and attach them as E:\..

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 8, 2013 at 6:51 am

    #1585932

    PiMané (2/8/2013)

    Disc E: is a logic local drive...

    the command returns

    C:E:Doesn't windows "know" the \\sqlsrv01\Data is a local mapping (E:\MSSQL\Data..)?

    I am not sure how it would. I would not take a chance. I would alter my databases to use E:\ instead of the share. By default in SQL 2005 you cannot create a database where the files are hosted on a UNC path even if that path is to a local admin share, I just tried it, so at least that version of SQL Server could not tell. The error was:

    The file "\\computername\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testunc.mdf" is on a network path that is not supported for database files.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 8, 2013 at 7:04 am

    #1585943

    This is a 2008 Standard Edition...

    But I'll create a template to change the path to E:

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 8, 2013 at 7:08 am

    #1585946

    PiMané (2/8/2013)

    This is a 2008 Standard Edition...

    But I'll create a template to change the path to E:

    Thanks,

    Pedro

    I know we're in a 2008 forum. I was just saying that "at least in that version" SQL Server could not tell it was a local drive and threw an error. In 2008 we can host on a UNC path so there is no real way to tell but like I said, I am not sure how it would know. Shares have additional permissions from local paths so I am not sure SQL Server should or would rightfully bypass those.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 8, 2013 at 7:09 am

    #1585948

    PS Don't forget to let us know if you get a speed increase from moving to a local path 😉

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 8, 2013 at 7:52 am

    #1585965

    opc.three (2/8/2013)

    PS Don't forget to let us know if you get a speed increase from moving to a local path 😉

    Sure, but the implementation will only occur in a week, and also along with that change there'll be an index rebuild task... on the 1000 databases there are over 80.000 index with 90% frag and over....

    Just one last thing,if I may 🙂

    The raid 50 has 24 disks, 24 spindles... but it's just one biiiiiggggg system with 8 logical drives (4 with SO + SQL VMs and 4 with the partition for each VM for data storage).

    With 24 disks I could also have 4 RAID 10 systems... 1 for MDF, 1 for LDF, 1 for tempdb, 1 for SO + SQL VMs.

    But never the less it would also be 24 spindles...

    Apart from being RAID10 vs RAID50, is there a gain on creating the 4 RAID10 over the 1 big RAID50? the number of spindles is still the same...

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 8, 2013 at 8:51 am

    #1586006

    PiMané (2/8/2013)

    opc.three (2/8/2013)

    PS Don't forget to let us know if you get a speed increase from moving to a local path 😉

    Sure, but the implementation will only occur in a week, and also along with that change there'll be an index rebuild task... on the 1000 databases there are over 80.000 index with 90% frag and over....

    Just one last thing,if I may 🙂

    The raid 50 has 24 disks, 24 spindles... but it's just one biiiiiggggg system with 8 logical drives (4 with SO + SQL VMs and 4 with the partition for each VM for data storage).

    With 24 disks I could also have 4 RAID 10 systems... 1 for MDF, 1 for LDF, 1 for tempdb, 1 for SO + SQL VMs.

    But never the less it would also be 24 spindles...

    Apart from being RAID10 vs RAID50, is there a gain on creating the 4 RAID10 over the 1 big RAID50? the number of spindles is still the same...

    Thanks,

    Pedro

    My experience says no but it will depend on how good the disk virtualization software is on the SAN as that is what will be responsible for abstracting the 24 spindles. The EqualLogic SAN at one shop performed much better when all spindles were used as one big presented volume and logical drives were carved out from it. In theory the more spindles behind a logical drive the more IOPs you have available. The one I was using, even though there were many logical disks carved out from the one big 24-spindle volume, all logical disks were able to take advantage of all 24 spindles since the disk virtualization software on the SAN took care of that abstraction. Your mileage may vary though depending on SAN and workload. There are other potential bottlenecks along the way too including controllers and sometimes network too.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 8, 2013 at 9:07 am

    #1586015

    It's an EqualLogic SAN 🙂

    So, in theory, it doesn't really matter if its 4 RAID 10 or just the one big RAID 50 with the 24 disks since the spindles will be used...

    Thanks...

    Since there are 4 cores (logical) how many database files should there be? I read paul's article about tempdb and the #core < 8 = #core, #core > 8 = #8 + [#4] files for tempdb...

    adding more files to the database will only increase the already huge number of files SQL has to "manage"..

    But even though there are 1000 databases (or more) only 80 or 100 are "active" at the same time...

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 8, 2013 at 10:22 am

    #1586066

    PiMané (2/8/2013)

    It's an EqualLogic SAN 🙂

    So, in theory, it doesn't really matter if its 4 RAID 10 or just the one big RAID 50 with the 24 disks since the spindles will be used...

    No, I think it does matter. If you go for 4 x 6-disk RAID 10 arrays then each array only has the IOPs equivalent to what 6 disks would offer for each logical drive carved from each array. If you go for 1 x 24-disk RAID 50 array then you have the IOPs that 24 disks would offer for each logical drive carved from the array.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 8, 2013 at 11:42 am

    #1586097

    I got this from Gail Shaw, I had the same question yesterday about the tempdb.

    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

    MCSE SQL Server 2012\2014\2016

  • February 9, 2013 at 3:16 pm

    #1586260

    Thanks but about tempdb already know the "golden" rule... I took paul's course about SQL Myths on pluralsight.

    My doubt is about the user databases and the huge number of files the system will have.

    Pedro


    If you need to work better, try working less...

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy