Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using filestreams in Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) and Windows server 2008 to store millions of files. Since there are millions of files all of them are grouped into 100 filegroups and inserted sequntially one after another filegroup. Now we have around 15K files in each filegroup and expected to grow to 20K soon. so we want to know is there any maximum limit of rows that a filegroup can have to give optimum performance or is there any maximum on the operating system side for optimum performace as we are storing or going to store around <20K files in single folder?

Any suggestion to the right resources will also be very helpful.

I have a link to msdn blog which states that

4.Check if FILESTREAM directory containers do not have more than 300,000 files individually, as NTFS performance degradation may be an issue especially when generation of 8.3 filenames is enabled.

Msdn Blog Link

Thanks.

share|improve this question

migrated from stackoverflow.com Jul 18 '12 at 21:40

This question came from our site for professional and enthusiast programmers.

4  
Well, one tip, don't ever try to navigate to that folder in Windows Explorer. –  Aaron Bertrand Jul 18 '12 at 21:51
1  
I've been toying around with filling NTFS folders with obscene numbers of files recently (random pet project of mine). Performance tanks in Explorer around 1M, but most file system operations are OK up until you exceed the file system cache. 20k files shouldn't pose a problem. –  ligos Jul 19 '12 at 1:58
    
Aaron, Not to navigate because of security or mistakenly deleting concerns or is there any other reason not to do it? –  cakiran Jul 19 '12 at 14:08
add comment

2 Answers

Actually you can put as many files as you want in a file group.

Basically file group feature included in sql server so that you can archive your organization data properly and can query the data efficiently. Its also depend on the hard disk space on server and configuration that which raid model you use for data storage.

share|improve this answer
    
One of the MSDN Blog suggests the following as best practice. 4.Check if FILESTREAM directory containers do not have more than 300,000 files individually, as NTFS performance degradation may be an issue especially when generation of 8.3 filenames is enabled. LINK - blogs.msdn.com/b/blogdoezequiel/archive/2011/02/11/… –  cakiran Jul 19 '12 at 14:15
add comment

we manage plenty of files in filestream as well. At a number of 1600000 files in one filegroup(directory) the reading and writing operations on filestream-data were very slow. Up to about 1000000 files I didn't experience problems with SQL and C#. But Filesystem was very slow then.

One more suggestion. If you have a lot of files in one filegroup you might get problems during backup. Our system hang up. http://support.microsoft.com/kb/2550552 help us.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.