Understanding Rows In SharePoint 2010

Understanding columns and rows in SharePoint 2010 is important when considering the holistic product. As you add columns to a list in SharePoint 2010 they get mapped to columns in the SQL Server database table. The rows in the database table supports a fixed number of column types. With a single database table row you can support eight date and time columns and then twelve number columns. When you have more than eight date and time columns then you need to use two database table rows.

With small lists the performance of row wrapping can be defective. When you use large lists though this will have a major effect for you. The limit for any number of columns before you use row wrapping does occur. However, that will only be the case if one column type goes over the limit. The number of columns for different data types without row wrapping happening is:

  • Single line of text 64 columns
  • Choice & multiple lines of text – 32
  • Date & time 8 columns
  • Yes/No 16 columns
  • Number and Currency 12 columns
  • Calculated 8 columns
  • Single value look up 16 columns
  • Unique identifier 1 column

With row wrapping you get to decrease the throughput by ~35% (give or take) for each additional row with most of the operations. Find out how many rows for a list you need by analyzing the line schema. You can also examine the column types for the different fields on the list.

Columns can be added to a list into columns with the Microsoft SQL Server database table. In order to support a large number of columns the SharePoint allows multiple rows to store the data. For a small list the performance of row wrapping may not work as well but it is excellent for larger sized lists. The performance is, as mentioned, about 35% per additional row. However, it is a good idea with the large lists to avoid wrapping more than one or two additional rows.

The size limits of the rows will be specified depending on the maximum number to table rows for the internal database. This applies to each of the items in a list so that they can be accommodated. This requires several columns with can being wrapped in as many as six internal table rows. When you have a list with many small columns then you can add more of them if you need to. This means that additional rows for larger lists can be done in order to minimize the number of table rows for the database used for each item in a list.


Implementing List Governance With Performance Consideration In SharePoint 2010 – Part 2

Managed Metadata – With Managed metadata you will have a new set of features that add information to the architecture of the SharePoint Server. The managed metadata will offer a shared service known as the managed metadata service. This can be used for storing term sets and it can also be used to deploy the SharePoint environment. Elements of this feature include terms that can support both deep and flat hierarchies, managing metadata column type, and term sets that are open or restricted.

The use of managed metadata columns and terms that are set for organizing content allow you to use the features of content querying. Both metadata and the Content Query Web Part navigation help a user to find content. With managed metadata there is also the regular search queries available. This allows for keywords to be added to classified documents. It also allows for managed metadata to be used in the search panel.

Limits and Throttles – Through SharePoint Server 2010 there are various limits that can be configured for farm performance to be maintained. The Web application level is configured with limits and throttles. They were added to offer more operations for individual users or processes. However, they don’t affect the overall farm performance. The list view threshold is a limit that stops queries from affecting a given number of list items.

Compound Index – One of the important elements of large lists is the use of indexes. With SharePoint Server you will be able to create a compound index. These are useful for queries that will be performed on two columns versus just one. When you only have one, it may not be selective enough. A compound index allows for utilization of various views but they can still be accessed with metadata navigation. A throttle condition will occur when they metadata attempts to retry and then indexing is based on the various filters that apply.

Developer Dashboard – With the developer dashboard display there is a diagnostic detail for each page that loads. The default can be left on if you like. The developer will allow you to get information about load times, errors, and database queries. The metadata navigation is visible in the developer dashboard.

This means that large lists and throttle conditions are for the list of indexes. They are used to retry and for partial results that can appear in the operation tree. This is on the left side. The SQL Server queries are found on the right side. The developer dashboard allows for debugging the custom Web Parts and queries.

Content Iterator – The content Iterator is developed for API and it simplifies the writing code for large lists. This is important when there is a new list view limit. The content iterator offers a way for retrieving content and performing operations on small sets. This is different than performing operations on the entire set as before. The result is that you don’t have to worry about exceeding the threshold for a give list view.

Remote BLOB Storage – The SharePoint default results in files stored as Binary Large Objects (BLOB). This is in the SQL Server database. There is a large amount of content found in the database. BLOB storage also allows for more to be stored outside of the SQL Server. This allows for less cost to be involved with storing the options. At the same time this reduces the content database size. Remote BLOB storage is a library API set for the add on pack with the SQL Server 2008. There can be a 3rd party remote BLOB storage provider required for the remote BLOB storage API.