In case you weren’t already aware, Sitecore is an object based database (the item). The result is that everything is expressed in the database as a ‘thing’ with fields, whether shared, unversioned or versioned. Whether we publish, make a package or replicate our data, we need to uniquely identify each one of these items and so use a UniqueIdentifier as our ID type.
Whilst this has some advantages, when it comes to Sql Server it has a few performance issues when used as a primary key. We can’t rely on a sequential ID (whether numeric or a psuedo guid) due to potential collisions between systems and so we have a clustered index on a random data set.
What this means is that any table relying on a UniqueIdentifier as a PK (so all of the Sitecore ones) will suffer from fragmentation and so require regular maintenance.
Some things to consider at this point though:
- Sitecore ships with a small number of indexes, if any, per table as your database size will be unknown as will your usage.
- Adding more indexes without analysing your database and using recommendations created by Sql Server tools will result in additional wasted resources
- Sql Server ships with a large set of management stored procedures, collectively called the dynamic management values or DMVs. Maintaining your Sitecore installation requires some familiarity with them.
So when looking at a Sitecore database, I first want to see how fragmented the core database is. Though, arguably, this contains the least modified set of tables, it drives the Sitecore administration area and so directly affects the performance of the editing environment.
Next I want to look at the master database in the same way but I also review how many versions of content items the database is maintaining. Its common in some sites I’ve visited to see 30+ revisions of single content items still being maintained.
Finally I’ll look at the web database. Though this is, arguably, as important as the master database it is also the most aggressively cached in application code. A tune up is a very good idea but one table had to come last in the list.
This review and tune up process uses the Sql Server tools and the DMVs (as already mentioned). A good start point is the ‘sys.dm_db_index_physical_stats’ call. Rather than go into detail, I’ve included some resources at the bottom of this post. Not every developer is a budding DBA so here Google really is your friend. Though the links I’ve put below are a good point for picking up some new knowledge.
Its worth noting, however, that any database maintenance will affect the running of your database. Depending on your version of Sql Server, the processing may not be done with the database online. For small sites, out of hours support should achieve this easily. For larger sites, this is really infrastructure dependant and whether downtime is justified.
- sys.dm_db_index_physical_stats – MSDN documentation
- Sql Authority Blog – recommended
- Sql Server DMVs in Action – I’ve not read this (someone send me a review copy!!) but check it out anyway
Continue reading on strategies to maintain indexes in my follow up post here