SharePoint 2010 Indexed Columns – Best Practices

The concept of indexing a column in SharePoint is somewhat same as you can index a column in database table. The internal working and methodology to use and maintain the index column is different than how it works in case of Database indexes.

In General index on a column help you locate the row or item quick and in efficient manner and avoid scanning of several other rows. And it help improve the performance of a large list or library. When you are writing a query on your List or you are creating a filtered view, you can use the index columns as filter criteria by which you can quickly retrieve the item you want.

Also as a good practices you should not end up creating several columns of your list as index columns, there has to be logical decision on that, you can take a look at the Type of the column, values of the columns, how the value of the column is used, is it used in filter criteria’s at several other places, keep in mind that each additional column index consumes extra resources in database and add some overheads on SharePoint to perform some additional operations to maintain the indexes. It is very important that you take a look at the usability of the column across all the views, filters criteria where is most commonly used as to filter the information for retrieval. And accordingly you can decided if you want to create the column as indexed column. However SharePoint has imposed some limitation on how many and on what you can create an index column. In SharePoint list or library you can index up to 20 columns.

The following table summarizes what columns can and cannot be indexed.

Column Types that can be indexed

Single line of text

Choice (single value)

Number

Currency

Date and Time

Lookup (single value)

Person or Group (single value)

Yes/No

Managed Metadata

Column Types that can NOT be indexed

Multiple lines of text

Choice (multi-valued)

Calculated

Hyperlink or Picture

Custom Columns

Lookup (multi-valued)

Person or Group (multi-valued)

External data

Improve SharePoint Performance by limiting memory allocation of SQL Server

Many times I have seen developers complaining about their SharePoint development environment performance. Since they have SharePoint standalone installation on their development Virtual Machine they always complain their machine is too slow.

This is not only the case with development environment, even for SharePoint Test, Staging and Production environment, we always blame SharePoint the cause of performance hit. But wait a min the problem is not with SharePoint there could be other factors which might be causing performance issue. And one of the factor is default settings of SQL Server database. In this article I am not going to touch all other factors but yes I am going to touch base very important and quick setting which you can do to boost your SharePoint environment performance.

SQL Server is the one who eats all your memory every time, because by default it is given freedom to SQL Server to consume all memory on the server. By Default, SQL Server is set to use max 2TB of RAM. You might not be having 2 TB RAM in your environment, you might have 8 GB, 16 GB, 32 GB or more depending upon your farm and size of SharePoint installation. So what will happen if SQL Server itself is free to take up to 2 TB RAM, then whenever any operation comes to SQL Server it will try to consume as much as it can consume. What will happen to the OS and other processes you have running on the server, they also need some memory, they are also responsible for doing their task. So here is what you can do to limit the memory size of SQL Server and tell him that you are not the one there are others who also needs some memory. At the end you need to make sure your SQL Server and your Server they are not competing themselves to the same memory resource this causes bad performance on the server.

Read entire post at this link  http://social.technet.microsoft.com/wiki/contents/articles/18899.sharepoint-performance-best-practices-limit-sql-server-memory-allocation.aspx

SharePoint Performance - List.Items.Count Vs List.ItemCount

There are some common coding mistakes which severely hits the SharePoint performance. Let me take an example on List.Items.Count Vs List.ItemCount code.

Int itemCount = SPContext.Current.List.Items.Count;

The above code returns number of items from the list, to get the number of items it retrieves all items of the list from the content database and then returns the count. This code might not be a problem for a small list, normally during development we don’t have large data in the list so it doesn’t reflect the performance hit but when you have a large list, this code will become performance overhead.

There is a different property on the SPList which can be used in such scenario and will be better for performance.Here is the alternative to the above code

Int itemCount = SPContext.Current.List.ItemCount;

In above code, SharePoint just query a single record from the List table from the content database. There is a redundant column for number of items in the list which stores the count, so the above code just fetch the number of items without querying the entire data.

So the best practice is to use SPContext.Current.List.ItemCount if you want to fetch item count.