Hacking SharePoint 2010 DB Performance

Intro

We are using SharePoint Foundation 2010 SP2 as EDM system in our organization. We faced with a performance issue, after 3 years of using SharePoint 2010.

The Issue Context

Every employee in our organization has a pool of tasks to do. The pool of tasks is implemented as a SharePoint list. A task may has a dozen of executors. The executors leaving comments to the task overtime. Therefore, version history of the task (a list item) is growing and growing. On some tasks, we have about 2500 version history items. A SharePoint 2010 wasting about 150000 ms to retrieve these tasks.

Solving The Issue

WARNING! YOU CAN LOOSE SUPPORT ON YOUR COPY OF SHAREPOINT! 

Open SQL Server Management Studio and run following script on a content database of your SharePoint instance:

USE [WSS_Content];
GO
create nonclustered index AllUserData_tpID on dbo.AllUserData ( tp_ID ASC );
create nonclustered index AllUserData_tp_ListId on dbo.AllUserData ([tp_ListId] ASC);
create nonclustered index AllUserData_tp_SiteId on dbo.AllUserData ([tp_SiteId] ASC);
create nonclustered index AllUserData_tp_RowOrdinal on dbo.AllUserData ([tp_RowOrdinal] ASC);
create nonclustered index AllUserData_tp_DeleteTransactionId on dbo.AllUserData ([tp_DeleteTransactionId] ASC);
create nonclustered index AllUserData_tp_Level on dbo.AllUserData ([tp_Level] ASC);
create nonclustered index AllUserData_tp_IsCurrentVersion on dbo.AllUserData ([tp_IsCurrentVersion] ASC );
GO

After that, I have achieved increasing of the DB performance up to 100 times.