Sitecore database maintenance

• Performance
• Sitecore

One of my clients Sitecore installation started to get more and more bogged down and irresponsive. These are some basic steps I took to make sure that the databases of the solution is running as smooth as possible.

The steps I took was:

• Emtpty recycle bin of old items

• Clean up databases

• SQL Fragmentation

• Remove old item versions

Emtpty recycle bin of old items

The recycle bin had over 10 000 items dating back more than 3 years. Cleaning out everything older than a month will make it a bit lighter. This is easy to do by going into the dashboard and running the Clean up deleted items task under DATABASE.

Clean up databases

In the same menu there is an alternative called Clean up databases, this gives you an alternative to select which databases to clean up. I selected all of them since they all probably need it. This can take a long time to run, for this site it took 40 minutes to run all three databases with master and web being 20 GB of size.

What this job does is to run the following functions:

• Cleanup CyclicDependences (items that couldn't be removed by CleanupOrphans because they have a parent but are not in the item tree)

• Cleanup InvalidLanguageData (remove all data from items in languages that are not defined as a valid language in the Sitecore system)

• Cleanup Fields (delete data from fields -SharedFields, UnversionedFields, VersionedFields- that have no related item anymore)

• Cleanup Orphans (delete items that have no parent anymore)

• Cleanup Blobs (delete blob fields that have no related field)

• Cleanup OrphanFields (clean the field data from the orphans removed in 4)

• RebuildDescendants

• Clear All Caches

SQL Fragmentation

Fragmentation in the SQL database can cause serious performance issues. Thanks to this blog post I got two SQL scripts that can make me find and fix the problem somewhat, and by somewhat it is that the fragmentation is only fixed for tables with an Index. I ran the scripts on my core, master and web databases.

SELECT dbschemas.[name] as 'Schema',
  dbtables.[name] as 'Table',
  dbindexes.[name] as 'Index',
  indexstats.alloc_unit_type_desc,
  indexstats.avg_fragmentation_in_percent,
  indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

This script shows the fragmentation of the different tables in the database. And as seen below it was quite extensive on my master db.

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DBCC DBREINDEX(@TableName,' ',80)
        FETCH NEXT FROM TableCursor INTO @TableName
    END
CLOSE TableCursor
DEALLOCATE TableCursor

This script reindexes the database and thus fixes some of the fragmentation. Although not percect, the scores are way better than before.

Remove old item versions

The final thing I did was to get rid of old versions of items. Being a multilanguage site with active content work and workflows some items had over 60 versions. To battle this I made a simple PowerShell script which will remove X old versions so that each item only has a select number of versions saved. This script is not a perfect solution as I need to run it manually, but I will add running it to my monthly maintenance round.

There are also some limitations as it limits the versions depending on the item version number on the webdatabase. Not counting for that there might be versions removed manually before the last version in web database.

<#
  This script will remove old versions of items in all languages so that the items only contains a selected number of versions.
#>
$item = Get-Item -Path "master:\content"
$dialogProps = @{
Parameters = @(
@{ Name = "item"; Title="Branch to analyse"; Root="/sitecore/content/Home"},
@{ Name = "count"; Value=10; Title="Max number of versions";  Editor="number"},
@{ Name = "remove"; Value=$False; Title="Do you wish to remove items?"; Editor="check"}
)
Title = "Limit item version count"
Description = "Sitecore recommends keeping 10 or fewer versions on any item, but policy may dictate this to be a higher number."
Width = 500
Height = 280
OkButtonName = "Proceed"
CancelButtonName = "Abort"
}
$result = Read-Variable @dialogProps
if($result -ne "ok") {
Close-Window
Exit
}
$items = @()
Get-Item -Path master: -ID $item.ID -Language * | ForEach-Object { $items += @($) + @(($.Axes.GetDescendants())) | Where-Object { $.Versions.Count -gt $count } | Initialize-Item }
$ritems = @()
$items | ForEach-Object {
$webVersion = Get-Item -Path web: -ID $.ID -Language $.Language
if ($webVersion) {
$minVersion = $webVersion.Version.Number - $count
$ritems += Get-Item -Path master: -ID $.ID -Language $.Language -Version * | Where-Object { $.Version.Number -le $minVersion }
}
}
if ($remove) {
$toRemove = $ritems.Count
$ritems | ForEach-Object {
$_ | Remove-ItemVersion
}
Show-Alert "Removed $toRemove versions"
} else {
$reportProps = @{
Property = @(
"DisplayName",
@{Name="Version"; Expression={$.Version}},
@{Name="Path"; Expression={$.ItemPath}},
@{Name="Language"; Expression={$_.Language}}
)
Title = "Versions proposed to remove"
InfoTitle = "Sitecore recommendation: Limit the number of versions of any item to the fewest possible."
InfoDescription = "The report shows all items that have more than <b>$count versions</b>."
}
$ritems | Show-ListView @reportProps
}
Close-Window

With these steps done the site runs way smoother than before. There might come more posts on this matter as I'm not fully satisfied yet.