Sql Server 2008 Ctp File Stream Ntfs Limitations

SQL Server 2008 CTP, FILESTREAM & NTFS limitations | Tech Off | Channel 9

Microsoft Communities

Home

Recent

Media

Shows

Forums

Key Topics

Wiki

Search

Sign In

About

Subscribe

Forums > Tech Off

SQL Server 2008 CTP, FILESTREAM & NTFS limitations Posted By: Jimi2Cool | Jun 5th, 2008 @ 2:09 PM

page 1 of 1

Comments: 4 | Views: 878

● ●

● What you are watching Active Forum Threads

❍

Reply

#Jun 5th, 2008 @ 2:09 PM

●

INDECT

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

Forum: The

RicoCoffeehouse Mariani: Inside Visual

I just set up a DB in 2008 CTP to store a massive amount of images. I'm using the FILESTREAM

St…Posted - Performance and By:

feature to store the binary data in the file system. I was wondering why my migration process Jimi2Cool

GoddersUK Reliability

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?

Thedataset Visual binding Studio WPF Documentary: Forum: Tech Off Jason Zander

If i'm missing something or there is a workaround for this somewhere i'd love to know cause i'm

Full Length Interview Posted By: spivonious Today @ 10:58 AM Comments: 2

just shocked that this would be the case in software designed to support gigantic table. Any thoughts, Ideas?

●

MSDN Online Facelift Reply

#Jun 5th, 2008 @ 3:24 PM

Today @ 11:49 AM Comments: 1

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.

http://channel9.msdn.com/forums/TechOff/407930-SQL-Server-2008-CTP-FILESTREAM--NTFS-limitations/ (1 of 4) [10/20/2009 3:15:38 PM]

Forum: The Coffeehouse Posted By: sysrpl Today @ 10:18 AM Comments: 1

SQL Server 2008 CTP, FILESTREAM & NTFS limitations | Tech Off | Channel 9

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 figuerres ???

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. Reply

#Jun 6th, 2008 @ 2:24 AM

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

AndyC

http://channel9.msdn.com/forums/TechOff/407930-SQL-Server-2008-CTP-FILESTREAM--NTFS-limitations/ (2 of 4) [10/20/2009 3:15:38 PM]

SQL Server 2008 CTP, FILESTREAM & NTFS limitations | Tech Off | Channel 9

Reply

#Jun 6th, 2008 @ 8:53 AM

In reply to AndyC#Jun 6th, 2008 @ 2:24 AM 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 Jimi2Cool

(about 100X faster than where it had dropped to by the way). Reply

#Jun 7th, 2008 @ 10:52 PM

In reply to AndyC#Jun 6th, 2008 @ 2:24 AM You just saved me a lot of headaches! Thank you. turrican Condemnation without investigation is the height of ignorance! - Albert Einstein

Comments: 4 | Views: 878 Forums > Tech Off > SQL Server 2008 CTP, FILESTREAM & NTFS limitations

http://channel9.msdn.com/forums/TechOff/407930-SQL-Server-2008-CTP-FILESTREAM--NTFS-limitations/ (3 of 4) [10/20/2009 3:15:38 PM]

page 1 of 1

SQL Server 2008 CTP, FILESTREAM & NTFS limitations | Tech Off | Channel 9

Contact Us | Code Of Conduct | Terms Of Use | Privacy Statement | Subscribe This site is hosted for Microsoft by ORCSWEB Copyright 2007-2009 Microsoft

http://channel9.msdn.com/forums/TechOff/407930-SQL-Server-2008-CTP-FILESTREAM--NTFS-limitations/ (4 of 4) [10/20/2009 3:15:38 PM]