Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Server 2008 CTP, FILESTREAM & NTFS limitations

Back to Forum: Tech Off
  • User profile image
    Jimi2Cool

    I'm just wondering what's up with this.

    I just set up a DB in 2008 CTP to store a massive amount of images. I'm using the FILESTREAM feature to store the binary data in the file system. I was wondering why my migration process came to a slow crawl after about 900,000 images and i was baffled as all the system and SQL server metrics seems to be just fine. after doing a little research i noticed people complaining somewhere about NTFS not playing that well with directories of that size and it seems to be the thing that is slowing my inserts down.

    I also see that you cannot span the files into multiple folders since SQL server manages these files.

    Why would SQL limit you in this way if NTFS cannot support such huge folders well?

    If i'm missing something or there is a workaround for this somewhere i'd love to know cause i'm just shocked that this would be the case in software designed to support gigantic table.

    Any thoughts, Ideas?

  • User profile image
    figuerres

    Well I have known for a long time that folders that have a large number of files can lead to perf issues on all file and folder operations.

    I first saw this on Sun / Solaris around 1997
    An ISP I worked for had users email on a disk array and saw massive slowdowns as the number of users went up.

    also there were / perhaps still are IIS plugins for servers that want to host lots of free web sites.

    the general answer is to create a tree in place of a flat directory.

    example: 

    Mailboxes/
    a/
    b/
    c/
    ...
    z/

    then in each of the folders a..z create folders a..z and in each of them repeate.

    so for example if my pop3 user name was "Denny"
    the file would be found in the folder/path
    Mailboxes/d/e/n/denny/*

    with the result that each folder has a limited number of files and no one folder has a lot of files.
    assuming of course that names are "spread out"
    sometimes a hash is used to create folder names and map real names to the hash.

    I would take the 900000 plus files and make a tree.

    I suspect you could see the same access problem doing a dir-search or a set of file open functions in a program.

  • User profile image
    AndyC

    It's not NTFS, per se, it's the mechanism used to generate legacy 8.3 filenames that slows things down. If you know you're going to have folders containing more than about 300,000 files (and you aren't relying on 8.3 filenames) then it's recommended to turn off 8.3 filename generation(see: http://technet.microsoft.com/en-us/library/bb457112.aspx - scroll down to Optimizing NTFS Performance).

    You can turn off short filename generation by using the command: fsutil behavior set disable8dot3

  • User profile image
    Jimi2Cool

    AndyC said:
    It's not NTFS, per se, it's the mechanism used to generate legacy 8.3 filenames that slows things down. If you know you're going to have folders containing more than about 300,000 files (and you aren't relying on 8.3 filenames) then it's recommended to turn off 8.3 filename generation(see: http://technet.microsoft.com/en-us/library/bb457112.aspx - scroll down to Optimizing NTFS Performance).

    You can turn off short filename generation by using the command: fsutil behavior set disable8dot3
    FANTASTIC! thanks so much Andy! that was exactly what the issue was. i was able to execute that command and my apps performance returned to exactly where it had been when it started (about 100X faster than where it had dropped to by the way).

  • User profile image
    turrican

    AndyC said:
    It's not NTFS, per se, it's the mechanism used to generate legacy 8.3 filenames that slows things down. If you know you're going to have folders containing more than about 300,000 files (and you aren't relying on 8.3 filenames) then it's recommended to turn off 8.3 filename generation(see: http://technet.microsoft.com/en-us/library/bb457112.aspx - scroll down to Optimizing NTFS Performance).

    You can turn off short filename generation by using the command: fsutil behavior set disable8dot3
    You just saved me a lot of headaches! Thank you.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.