Can you really have more than 2000 items per folder in SharePoint now?

With Windows SharePoint Services 2.0 and SharePoint Portal Server 2003, The SharePoint capacity planning guidelines had this to state about Document Libraries and capacity

From the “Capacity Planning for Windows SharePoint Services” (For WSS 2.0) administration guide.

http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsb07.mspx?mfr=true

In the section “About Capacity and Scale Guidelines

“Documents – Folder – 2,000 – The interfaces for enumerating documents in a folder do not perform well beyond a thousand entries. “

This was not a hard limit, and many customers were not affected by having many thousand more entries per folder.  Until…one day 🙂  When the Database pages lined up just right, the SQL clustered indices statistics were not refreshed, and it was just a very bad day for the SharePoint admin.  Then you were in trouble.  The only real solution is to reduce the number of items per folder to within capacity planning guidelines.  And it solved just about every issue I was ever involved in.  That’s why SPSiteManager’s analysis routine checked all capacity planning guidelines.  We could easily identify “Where” to go look.

For Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 (MOSS) the capacity planning guidelines where changed “Slightly”

From the “Plan for software boundaries (Windows SharePoint Services)” Technet article

http://technet2.microsoft.com/windowsserver/WSS/en/library/2aa12954-2ea7-475c-9dce-663f543820811033.mspx?mfr=true

“Throughput differences between flat document library vs. document library with folders

Throughput for certain operations decreases as the number of items in a folder increases.

The following figure shows the difference in throughput between viewing all items in a document library with and without the effective use of folders, which is critical for scaling. As shown in the graph below, throughput performance degrades as the number of documents increases when flat library storage is used. The quickest drop in throughput occurs when the total number of documents is less than 2,000, from 151 RPS (at 200 documents) to 63 RPS (at 2,000 documents). At 4,000 documents, throughput decreases to about 13 RPS, or an overall throughput decrease of over 90% from an empty library.

Note: Remember MOSS is built on top of WSSv3 just like SPS 2003 utilized WSSv2 as the platform, so this applies to MOSS libraries as well.

That statement is followed by a table much like the old V2 capacity planning guidelines with the following for Document Libraries

“Document – 5 million per library – You can create very large document libraries by nesting folders, using standard views and site hierarchy. This value may vary depending on how documents and folders are organized, and by the type and size of documents stored.”

I believe it’s worded this way specifically because the “2000” item statement for folders before was a not a hard limit, and the purpose of the updated guidelines is to stress that this is not a hard limit, and testing showed the performance degradation (And a cool chart!) with the total number of items, not that the minute you add that 2001th item to your folder that you were going to be in some serious pain :).  The 2000 item limit was more on the “View” side of things.  (Go look at the old docs, you’ll see).

The new guidelines have this to state about views:

“Item – 2,000 per view – Testing indicates a reduction in performance beyond two thousand items. Using indexing on a flat folder view can improve performance. – List view”

So there is a bit more separation on the limits for “Views” versus “True Item counts”, but remember, a File in a a Document Library is really just an Item in a list.   Its just a reversal when your comparing Document Libraries to Lists (List items have metadata and file attachments, Document Library items are really just list items with metadata, and a blob of data).  The point is, that “View” performance cannot be truly compared to overall “Document Library” performance, but your items within a folder are going to directly affect your view performance.  Clear as mud? 🙂

Again, when SPSiteManager reported these exceptions to capacity planning guidelines, it was done with a “Warning” element, not a “Oh my goodness!! Do something quick!” element 🙂  But the importance of that call out was there.  It’s point was to show you where you may want to start investigating restructuring of your content.

So, back to the question “Can you really have more than 2000 items per folder in SharePoint now?”

The answer is, “You could always have more than 2000 items per folder in SharePoint, even back in WSSv2/SPS2003.  It boils down to acceptable performance, even in WSSv3/MOSS2007.

Well, that’s “My” answer 🙂

Remember, the new capacity planning documents state:

“This value may vary depending on how documents and folders are organized, and by the type and size of documents stored.”

The debate will probably never end, as it depends on a wide variety of factors before you “May” be impacted by this, but if you want my opinion on what you should limit your document library folder contents too? Keep them no larger than 2000 items per folder.  The same goes for List Items within a list folder too. This is “My” opinion, and not a direct statement from Microsoft. :).

I would highly recommend that you subscribe to and read Joel Olesons’ blog at http://blogs.msdn.com/joelo.  He’s got TONS of data on this subject linked from his blog.

Ah, I remember the days of sharing emails with “Dances with Goats” back and forth on capacity planning, and the joys there of.  Joel, I miss ya buddy, but I’m still watchin’ ya :).

Till next time!

HTH

 – Keith Richie

7 thoughts on “Can you really have more than 2000 items per folder in SharePoint now?

  1. Keith,

    what happens during a full crawl when the sitedata.asmx returns those lists with 40,000 items and is not so smart to apply a view that I have custom coded for the list?

    Piotr

  2. Are those 40,000 items in a single folder? If so, it’s going to hurt 🙂 Because most of the load will be on the backend to enumerate and return those items. If they are in a single folder, that’s where you perf hit is going to be.

  3. Keith,

    One thing I’m curious about is what if you never plan to enumerate the entire list? In other words, what if you were only to query the list for sets of items that would never even exceed 100 let alone 2000? Could we then put the documents in a flat list without issues? Have you ever tried this?

    thx.

    Matt

  4. If you access the items via the SPList.Items[] collection, I believe it’s going to try to fetch a large number of items to cache locally to prevent multiple round trips to the database. Been ages since I did a SQL profile trace on it to confirm, but I worked with a lot of customers (And even experienced it myself) where subscripting the collection would cause a major delay, and you can see a noticible proc increase on your SQL server if your environment is just right :). I think if you use a SPQuery object, it’s going to be a better solution, as the query that would be executed to gather the list items on the SQL server would be structured completely different. It’s one of those things I too would like to spend some time on, run SQL Profiler on, and document my findings. In fact, I may actually be getting to that in a couple of weeks as I plan to begin performance testing the new crawling engine I’ve written for DeliverPoint.

    Still, you can never gaurantee that a third party web part may be tuned to work “Best” with this scenario, and these things can be very difficult to track down.

    Since posting this, people are still telling me that it’s views, but it’s more than just views. It all depends on your backend hardware, your SQL server, etc, etc. but when it hits, it hits hard. I’d like to prove to myself that this problem is completely gone in MOSS, but as I note the official Microsoft guidance, I’d still recommend keeping them to ~2000 items per level just to be 100% sure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s