Sitecore – Maintaining Sql Server indexes

In my previous post I talked about the impact of how Sitecore runs and the tradeoffs that has when using Sql Server. These aren’t bad points but they do mean you have to be able to peek under the hood to keep everything running as best as you can.

So now I’m going to share some of the procedures I use to help me maintain the databases I’m responsible for. Its worth noting that I’m no DBA. I’ve been using Sql Server for years and a combination of curiosity and my employers needing someone to step in and figure things out has motivated me to pick this topic up. I’m definitely standing on the shoulders of giants in this area but we all have to start somewhere.

There are quite a few books on Sql Server performance and I it would be a fools errand to try and distill all of that wisdom into a single blog post. So instead I’ll begin with pointing out the areas I think you need to be familiar with:

  1. Sql Server Profiler
  2. Included columns in indexes (I will probably blog about this one 🙂 ).
  3. General familiarity with Sql Server Manager – creating scripts, inspecting indexes, generating diagrams etc.
  4. Reading execution plans

With that out of the way, in building up our tool set of items to look at, we want to use the Sql Server management stored procedures (or DMVs) to create utility stored procedures. Then we can call these manually, integrate them into Reporting Services reports or even create our own managed pages inside the Sitecore desktop (or whatever else your fevered imagination comes up with).

Our initial areas of interest are what tables we have, what indexes are in those tables, are they fragmented, do we need new ones and do we have too many? Sql Server has these as dynamic, system views and system tables:

  • sys.dm_db_index_physical_stats
  • sys.dm_db_index_usage_stats
  • sys.indexes
  • sys.objects

There are many, many more that let us look at query statistics, operating system related items and the like. But in the interests of keeping it simple we’ll focus solely on indexes.

Get index sizes in a db (basic)

This makes an estimate on the index sizes. A true value would take into account any large objects etc as well as inspecting the data in greater detail. However that would also take an age to run. Treat this as an indicative set of values.

Get unused indexes in a db

Indexes have to be maintained. If a database is no longer of use or being used then it should be removed. Its worth remembering, though, that requirements change. So again this is a place to start.

Get index recommendations – gist

These recommendations come from Sql Server and its interpretation of the statistics it has recorded. So bear in mind that if the statistics are out of date then these will need updating first. Its also worth considering using the Sql Profiler toolset on your common queries as well to get a complete view. You should apply some pragmatism here as these are only recommendations.

If your recommendations include using ‘included columns’ then Sql Server has been looking at the common Sql usage, specifically your where clauses. I’ll write a small blog about this topic in the future.

The sample SQL scripts are available in this gist on GitHub.

It can be fun to poke around and see how everything is running, and how your data is being stored and managed. NoSql is here (and here to stay) but not every project you work on will use it and there are plenty of Sitecore installations that will be on Sql Server for a long time to come.

Simon

Resources

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s