MetaData Navigation March 4, 2012
http://msdn.microsoft.com/en-us/library/ee575372.aspx
Microsoft.Office.DocumentManagement.MetadataNavigation Namespace
Microsoft.Office.DocumentManagement.dll
SharePoint 2010 Large Lists and Filters and Indexes and Folders December 18, 2011
Resource throttles and limits
The following table summarizes information about resource throttles and limits that you need to be aware of. These throttles and limits are set on the Resource Throttling page in Central Administration. Contact your administrator for specific limits and requests for your site.
Note To assist central administration, the computer administrator of the front-end computer and the central administrator site owner accessing a list or library with many items are not subject to the following resource throttles and limits.
| Threshold or Limit |
Default value |
Description |
|---|---|---|
| List View Threshold | 5,000 | Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked.To give you time to make alternative plans, SharePoint 2010 warns you on the List Settings page when your list has exceeded 3,000 items. The warning contains a help link to this topic. |
| Unique permissions limit | 50,000 | Specifies the maximum number of unique permissions allowed for a list or library.Every time you break the inheritance of permissions for an item or folder, it is counted as 1 unique permission toward this limit. If you try to add an item that would lead to exceeding this limit, you are prevented from doing so. |
Ways to manage lists and libraries with many items
The following sections provide recommendations, techniques, and tips for ensuring that you and other users can quickly access many items in a list or library and help to keep your site operating smoothly.
Top of Page
Creating SharePoint indexed columns
To help improve the performance of a large list or library, you can index up to 20 columns. In general, an index on a column enables you to quickly find the rows you want based on the values in that column, even when working with millions of items. When you combine indexes with filtered views, you can quickly retrieve the items you want.
It is important to consider the following when you create and use indexed columns. Each additional column index consumes extra resources in the database and adds some overhead to every operation to maintain the index. Therefore, you should add indexes only to columns that will be used actively for filtering in views on the list or library. It’s a good idea to look at all the views and see which columns are used most commonly across different views for filtering to help choose the right columns to be indexed. Note that any column that you define to have a unique value requires an index.
The following table summarizes what columns can and cannot be indexed.
| Supported Column Types | Unsupported Column Types |
|---|---|
| Single line of text | Multiple lines of text |
| Choice (single value) | Choice (multi-valued) |
| Number | Calculated |
| Currency | Hyperlink or Picture |
| Date and Time | Custom Columns |
| Lookup (single value) Note Indexing the column in the other list or library does not improve performance of the lookup operation. | Lookup (multi-valued) |
| Person or Group (single value) | Person or Group (multi-valued) |
| Yes/No | External data |
| Managed Metadata |
Creating filtered views based on column indexes
For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold. If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data
Organizing items into folders
Although folders (also called containers) are not required to use large lists and libraries, you can still use them to help organize your data and improve the efficiency of your data access. When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top-level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data. Bear in mind that if a folder contains subfolders, each subfolder is counted as an item (but not any items in that subfolder).
Even when the total number of items in a list or library is very large, a view of a single folder is at least as fast as a view that filters the total number of items by using an indexed column. In some scenarios, it may be possible to distribute all of the items in a list or library into multiple folders such that no folder has more than five thousand items.
It is important to consider the following when you use folders to organize a large list or library:
- A folder can contain more items than the List View Threshold, but to avoid being blocked by SharePoint 2010, you may still need to use a filtered view based on column indexes.
- If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on an index to ensure you don’t reach the List View Threshold.
There are two classifications that come into the picture when you have large lists: “List Exceeds The List Value Threshold” and “Container Exceeds The List View Threshold”.
There are operations that can be blocked when the size of the entire list exceeds the list view threshold. This occurs even if the items are placed into folders. These operations include managing and checking versions, operations of all items, and recursive queries. The views that return all items without folders can also be prevented. There are operations that affect a complete list too including adding a column or deleting indexes and they can be blocked.
There can be operations prevented due to the folder for the list containing more items than the list view threshold allows. You won’t be able to rename it or to delete it so you do need to be careful. The list view threshold can prevent you from performing some common actions when you setup your list. This is why you should configure the columns and indexes for a list before the size is greater than the list view threshold.
Cross list query– This includes the various queries by the Content Query Web Part. It follows the list view threshold setting for auditors and administration. The default for it is 20,000. For operations above that threshold it will fail.
Fine grain security permissions– This fails when the list or the folder that is being set has fine grained permissions that contain more items than the list view threshold as too many rows are affected. You can use the fine grain permissions on documents in a large list. However, you can’t set the permissions of the list or of the folders if they contain more than the list view threshold.
http://sqlblog.com/blogs/kalen_delaney/archive/2009/05/03/controlling-lock-granularity.aspx
Lock escalation occurs in the following situations (taken from “SQL Server 2008 Internals”, MS Press 2009):
- The number of locks held by a single statement on one object, or on one partition of one object, exceeds a threshold. Currently that threshold is 5000 locks, but it might change in future service packs. The lock escalation will not occur if the locks are spread over multiple objects in the same statement—for example, 3000 locks in one index and 3000 in another.
- Memory taken by lock resources exceeds 40 percent of the non-AWE (32-bit) or regular (64-bit) enabled memory and the locks configuration option is set to 0. (In this case, the lock memory is allocated dynamically as needed, so the 40 percent value is not a constant.) If the locks option is set to a non-zero value, memory reserved for locks is statically allocated when SQL Server starts. Escalation will occur when SQL Server is using more than 40 percent of the reserved lock memory for lock resources.
SQL Server 2008 provides us with a bit more control, with a new option to ALTER TABLE:
ALTER TABLE <table_name> SET (LOCK_ESCALATION = [TABLE | AUTO |DISABLE]);
The default is escalation is TABLE, and that was the only possibility for escalation prior to SQL 2008. If you set the option to AUTO, locks can escalate to a table or to a partition, if the table is partitioned. The third option is to completely disable escalation for this table. This option is much more manageable that my trick of having another transaction lock a single row, and much more fine-grained that disallowing escalation on the entire instance.
But what if you want the opposite behavior? What if you always want to take table locks in order to conserve resources? Yes, there are lock hints available to request TABLOCK or TABLOCKX, but those must be specified in every query. What if there are only a few tables that you want to always lock at the table level.
SQL Server provides an option to ALTER INDEX to disallow ROW and PAGE locks. (In SQL 7 and 2000 you could use the system procedure sp_indexoption to do the same thing.) If the index is a clustered index, this means ROW and PAGE locks will be disallowed for the table. But, if the table is a heap, you can’t use this option.
ALTER INDEX <index_name> ON <table_name> SET (ALLOW_PAGE_LOCKS = OFF);
Also note that if you set ALLOW_PAGE_LOCKS to OFF, you cannot REORGANIZE (defragment) the index.
Here’s a couple of cool aspects of this feature that aren’t apparent from a single nifty screenshot:
- Metadata navigation lets you slice and dice multiple ways. I might have a bunch of taxonomies on my library that classify content in different ways; for example, I might have a Products field, a Region field and a Competitors field, all bound to domain-specific taxonomies that classify the content along those dimensions. Depending on my current task, it might make more sense to filter by the Region field (for example, if I’m looking for the latest sales figures for the North America region). I get more filters than just my virtual folder; I can combine this filter with any number of key filters or list view column filters to drill down to just the content I want (for example, I want to see all demonstration scripts by the ECM team created after 2007).
- Metadata navigation thinks about indices and large lists so you don’t have to. Hey, remember just a few minutes ago when we were talking about large lists, indices, and being throttled? Well, metadata navigation thinks a lot about indices and how to run queries the “right way” to make them perform well and prevent throttling from happening. For starters, all the fields you configure as navigation hierarchies and key filters get indexed, and the resulting queries are written in a way that ensures the best index is used to make the query succeed.
You aren’t immune from the laws of physics; if you ask for documents tagged with demonstration scripts and there are 10,000 demonstration scripts, we’re not going to be able to show you all of them. In this case, though, you get something better than a simple fallback; you get an indexed fallback, which means that instead of considering the entire list, the query considers only the items that match the indexed portion of your query.
When filtering views with metadata navigation you should consider using per location views. They create unfiltered views for metadata navigation pivots.
With SharePoint Server 2010, as discussed in previous posts, the new list view threshold significantly changes the approach for how to use view with a large list. Users may get errors if they attempt to return more results than the list view threshold allows. The use of totals on the large list will be blocked when that occurs. The number of items have to be scanned and that is what is important versus the number of rows that are returned.
When a list is organized a folder shouldn’t contain more items than the list view threshold. When you show all items outside of folders you should avoid the mechanics being in place where they can filter the results.
MetaData Navigation December 17, 2011
http://msdn.microsoft.com/en-us/library/microsoft.office.server.webcontrols.metadatanavtree.aspx
System.Object
System.Web.UI.Control
System.Web.UI.TemplateControl
System.Web.UI.UserControl
Microsoft.Office.Server.WebControls.MetaDataNavTree
Namespace: Microsoft.Office.Server.WebControls Assembly: Microsoft.Office.DocumentManagement (in Microsoft.Office.DocumentManagement.dll)
MetaDataNavTree.ascx
<!– _lcid=”1033″ _version=”14.0.4758″ _dal=”1″ –> <!– _LocalBinding –> <%@ Control Language=”C#” Inherits=”Microsoft.Office.Server.WebControls.MetaDataNavTree,Microsoft.Office.DocumentManagement,Version=14.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c” compilationMode=”Always” %> <%@ Register Tagprefix=”SharePoint” Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”OfficeServer” Namespace=”Microsoft.Office.Server.WebControls” Assembly=”Microsoft.Office.DocumentManagement, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Import Namespace=”Microsoft.SharePoint.WebControls” %> <%@ Import Namespace=”Microsoft.Office.DocumentManagement.MetadataNavigation” %> <asp:Panel id=”TreeLabelPanel” runat=”server” Visible=”False” style=”padding: 5px 1px;”> <span style=”font-weight: bold; color:#003399;” > <SharePoint:EncodedLiteral runat=”server” text=”<%$Resources:dlcdm, MDN_Tree_Label_Configure_Settings_Here%>” EncodeMethod=’HtmlEncode’/> </span> </asp:Panel> <OfficeServer:NavResizerControl runat=”server” ID=”NavResizer” ResizeTargetControlName=”TreeViewRememberScroll”> <SharePoint:SPHierarchyDataSourceControl runat=”server” ID=”TreeViewDataSource” RootContextObject=”Web” IncludeDiscussionFolders=”true” ShowListChildren=”true” /> <OfficeServer:MetadataHierarchyDataSourceControl runat=”server” ID=”MetadataTreeViewDataSource” SPHierarchyDataSourceId=”TreeViewDataSource” /> <SharePoint:SPRememberScroll runat=”server” id=”TreeViewRememberScroll” onscroll=”javascript:_spRecordScrollPositions(this);” style=”overflow-y: auto; overflow-x: hidden;”> <SharePoint:SPTreeView runat=”server” ID=”WebTreeView” ShowLines=”false” DataSourceID=”MetadataTreeViewDataSource” ExpandDepth=”0″ SelectedNodeStyle-CssClass=”ms-tvselected” NodeStyle-CssClass=”ms-navitem” NodeStyle-HorizontalPadding=”2″ SkipLinkText=”" NodeIndent=”12″ ExpandImageUrl=”/_layouts/images/MDNCollapsed.png” ExpandImageUrlRtl=”/_layouts/images/tvclosedrtl.png” CollapseImageUrl=”/_layouts/images/MDNExpanded.png” CollapseImageUrlRtl=”/_layouts/images/tvopenrtl.png” NoExpandImageUrl=”/_layouts/images/tvblank.gif” AutoExpandSelectedNode=”true” > </SharePoint:SPTreeView> </SharePoint:SPRememberScroll> </OfficeServer:NavResizerControl> <OfficeServer:KeyFiltersControl runat=”server” ID=”ListViewKeyFilters” TemplateName=”KeyFiltersControl” AlternateTemplateName=”KeyFiltersCompositeFieldDefault” />
MetadataWebService.svc
Microsoft.SharePoint.Taxonomy.MetadataWebServiceApplication
Microsoft.Office.DocumentManagement.MetadataNavigation.MetadataNavigationSettings.GetMetadataNavSettingsXml
TaxonomyClientService.AddTerms Method
SharePoint 2010
Namespace: Microsoft.SharePoint.Taxonomy.WebServices Assembly: Microsoft.SharePoint.Taxonomy (in Microsoft.SharePoint.Taxonomy.dll)
http://blog.robgarrett.com/2011/04/28/taxonomyclientservice-addterms-wrong-documentation/
Wrong documentation for the newTerm XML packet.
Problem with Adding a term.
Your code need a bit modification. You need to call termStoreTerm.CommitAll(); after “termSet.IsOpenForTermCreation = true;”. When you change the value of IsOpenForTermCreation, there’s a commit pending. So if you try to add a new term and then commit then the conflict comes up. Your code will look like below after the modification:
SPSite siteTerm = new SPSite(siteURL); TaxonomySession sessionTerm = new TaxonomySession(siteTerm); TermStore termStoreTerm = sessionTerm.DefaultSiteCollectionTermStore; TermSet termSet = termStoreTerm.GetTermSet(TermSetID); termSet.IsOpenForTermCreation = true; termStoreTerm.CommitAll(); termSet.CreateTerm(Term, sessionTerm.TermStores[0].DefaultLanguage); termStoreTerm.CommitAll();
However you can check if IsOpenForTermCreation is true. If so you don’t need to modify the value and commit.
Deploy Metadata Navigation Using CAML February 11, 2011
assigning some XML to the client_MOSS_MetadataNavigationSettings property on the root folder of the list. We can assign this value using code, but you know I prefer to use CAML. We can use the PropertyBag element to make this happen. Before we look at the PropertyBag element itself though, let’s look at the underlying XML. Let’s take my list here with three items selected for Metadata Navigation: a site column named DocumentType, the Content Type of the documents in the library, and the Folders in the library itself. Here is what the XML will look like.
assign this XML to the the client_MOSS_MetadataNavigationSettings property. We need to encode the above XML, because it is being stored inside an attribute of another XML document. Here is what your elements.xml file would look like.
<?xml version=”1.0″ encoding=”utf-8″?>
<Elements xmlns=”http://schemas.microsoft.com/sharepoint/”>
<PropertyBag Url=”Shared Documents” ParentType=”Folder” RootWebOnly=”FALSE” xmlns=”http://schemas.microsoft.com/sharepoint/”>
<Property Name=”client_MOSS_MetadataNavigationSettings”
Speaking of Key Filters, we can add them to the document library using the client_MOSS_MetadataNavigationSettings as well. Let’s look at some more XML. In this case, I am adding key filters for DocumentType, All Tags, and Modified By (editor).
Sharepoint 2010 Metadata Navigation and Fallback Queries April 5, 2010
Every library now has a Metadata Navigation and Filtering settings page where you can configure navigation hierarchies (the filters you see arranged in the tree view) and key filters (the additional filters that show up beneath the tree view)
http://msdn.microsoft.com/en-us/library/ee559293.aspx
Automatic Index Creation
On the Metadata Navigation settings page for a list, you can also specify whether indices are automatically created on the list to match your currently selected navigation hierarchy and key filter fields. If this setting is enabled (as it is by default on a list), when the metadata navigation settings page is saved:
- Single column indices are created on all supported key filter fields, with the exception of the Content Type field and the Choice field.
- Compound indices are created on all supported combinations of navigation hierarchies and key filters.
Leaving the setting that specifies whether indices are automatically created on the list to match your currently selected navigation hierarchy and key filter fields settings is recommended for most scenarios, because if no indices are created on a list, every query on that list is unindexed. Unindexed queries are not supported in Microsoft SharePoint Server 2010 on large lists, or on lists that contain more items than the quantity defined in the site’s List View Query Threshhold. In some advanced scenarios, it may be appropriate to turn off this setting and configure indices manually. Important considerations include:
Indexed Queries
Metadata Navigation and Filtering does its best to put the most beneficial index to work every time a list view is loaded. Every time the user loads a list view, or refreshes it by applying a new filter, clearing a filter, or applying a sort on a field, there is some logic in the background that examines the request and determines a way to query the database that avoids large list throttling. This is not always straightforward; a query might have more than one way in which it can be run, and each way might produce different results. The process works like this:
- When the user navigates to a list view page, or refreshes a list view, the page runs a Collaborative Application Markup Language (CAML) query against the list to get the items that are displayed in the List View Web Part. This query may include one or more filters, which come from selected values in a navigation hierarchy, one or more key filter selections, one or more filters specified in the view definition, or one or more filters specified in column filters of the List View Web Part. If the current list is large, this CAML query may throw a SPThrottledQueryException, which means that the query is not selective. In other words, it examines more items in the database than the List View Query Threshold defined by the administrator. If this exception is encountered, then steps 2 and later are executed.
- Metadata navigation examines each of the filter operations (or clauses) in the CAML query and determines which have corresponding indices created on the list that may be used to satisfy that filter operation. For example, if there is an index on the ModifiedBy column, and the current list view filters on the Modified By column, that index may be used by the query.
- For each of these valid index/filter combinations, Metadata Navigation rewrites the current CAML query to use that index.
- Metadata Navigation and Filtering then attempts to run each of those indexed queries until it finds one that does not generate a SPThrottledQueryException. If all queries result in this exception, it constructs and executes a fallback query.
Fallback Queries
If Metadata Navigation and Filtering determines that the current user request cannot be expressed as an indexed query that is selective, it constructs and executes a fallback query. A fallback query is a modified version of the original user query that queries against only a portion of the list instead of the entire list. Fallback queries are intended to show the user a useful partial set of results, even when the original query could not be run due to large list throttling. Fallback queries can also serve as a warning to list owners that the data distribution in the list is skewed and certain queries that users are running are not able to return a full set of results, which means that users may be blocked from accessing content that they need. Fallback queries occasionally return 0 results if none of the items in the portion of the list scanned by the query contain results that match the original user query.
Indexed fallback queries. This type of fallback query works against one of the indices on the list, examining the N most recently created items that match the filtered value in that index. To use indexed fallback query semantics, the user query must have a filter on an indexed column, and that filter/index combination must meet some additional constraints. Only the types of filter/index combinations listed in Table 2 can participate in an indexed fallback query:
Simple fallback queries. Queries that do not have an indexed filter clause that support an indexed fallback must run a simple fallback query. A simple fallback query operates against the top N most recently created items in the list. If none of these N items matches the query parameters in the user query, no results are returned.
The Developer Dashboard can be used on list view pages with metadata navigation to see the CAML and SQL behind each of the indexed and fallback queries that were run for a user query.
- Some administrators may want to treat a fallback query as a warning that indicates that the data in a list is not well-distributed across indices and field values, and some data redistribution is necessary so that users of the list are able to access the documents that they need. If verbose logging is enabled, ULS log entries are written every time a fallback query is executed. A health rule can be developed to intercept these log entries and show a warning to the administrator that this is happening so action can be taken.
