Microsoft Dynamics CRM 2011 Performance Tuning – Part 2

In part 1 of my performance tuning series, I talked about the purpose of the series and covered a couple of simple tasks you can perform right now to see a performance improvement. As promised in part 1, this post will talk about monitoring your Microsoft Dynamics CRM SQL Server for index potential.

You may be asking yourself what is an index and why do I care? Well, simply put an index is a collection of data designed to be organized in such a way that queries can access that data in the most efficient way possible. Think of an index in the back of a cookbook. If you want a recipe for chicken soup you’ll probably open to the back of the book and look under “Ingredients” and then find “Chicken”, then you’ll scan under “Chicken” looking for “Soup”. An index in SQL works in fundamentally the same way.

If they exist out of the box, why do I need to worry about indexes? Many indexes do exist out of the box in Dynamics CRM but because of the tremendous flexibility the application provides you (this is where the term XRM comes from); you can create custom entities or even add columns to existing out of the box entities. New columns are added to the root entity in the <entity>ExtensionBase table as new_<columnname> while new entities are usually called New_<entityname>. As you create new entities and add columns to your database to make the application fit your business there are no indexes created. How could they? Microsoft has no idea what you’ll call the entities or column names and so it leaves the task of indexing those objects up to you.

But how do I know when to create indexes? SQL and even CRM provide several handy tools for identifying when an index might be helpful:

1. If you go to the CRM application server and look at the event viewer under “Application” you may find some warnings from the MSCRMPlatform regarding “Query execution time of ## seconds exceeded the threshold of 10 seconds.
2. You can also check the activity monitor of your SQL server and find those “Recent Long Running Queries”:
   • Pay special attention to the number of executions, if it runs a lot, there might be a real benefit to building an index if one doesn’t already exist.
3. Another option for you is to use the SQL provided DMV’s that give you all sorts of useful information! SQL Server Index DMV’s
4. In all cases, if you copy the suspect query and paste it to a new query window in SQL Server and select “Display Estimated Execution Plan”. You just might get lucky and see some green text like the sample below:

    

Great! I’m going to create indexes on everything! Not so fast! Just because SQL tells you the index is going to provide an expected benefit of 99% doesn’t mean you should create it. There are all sorts of reasons to either create or not create an index and it’s outside the scope of this post to cover those. But, you can always drop an index if you find it isn’t providing the value you expected or it has a significant negative impact on your other operations such as inserts and updates. It’s also important to note that as data is inserted or updated to a table, all of its indexes with those columns are similarly updated as well. Indexes also take up disk space so make sure you’ve done some capacity planning for those indexes!

Finally, don’t forget to maintain those indexes! Here are a couple of tips:

1. Microsoft officially recommends that indexes be reorganized when fragmentation reaches 5-30% and they should be rebuilt when fragmentation is > 30%. These are strictly guidelines and in some cases, if there aren’t in excess of 1000 pages in your indexes a rebuild may not have an appreciable impact. It is important to note however that a REBUILD will automatically Update Statistics, a reorganize does not. For more information on those concepts, check out SQL Books Online. How do you know what your index stats are? There are distributed management views or DMV for that! Check out sys.dm_db_index_usage_stats in books online.
2. DBCC REINDEX is an “Offline” routine. Meaning, the job will block other processes while it is being run. If you can have your db unavailable, this works. It is important to note that the DBCC commands may be deprecated in future versions of SQL Server.
3. ALTER INDEX … REBUILD WITH (ONLINE = ON) (SQL 2005 and later) Allows you to rebuild the indexes “online” which means the process WILL NOT block existing or new processes that attempt to access the table. It’s still a good idea to do this at off-peak times however!

As you extend your Dynamics CRM system make sure you are also taking steps to insure the data can be accessed in the most efficient way possible. Keep an eye on those queries that are running as a function of the advanced finds, saved queries, reports and custom applications. Indexes are no substitute for query tuning but they are a very effective piece of the performance puzzle.

If you’d like additional information on optimizing and maintaining the performance of your Dynamics CRM installation, please give us a call at 855.437.7202 and see how RSM can help you.

By: Josh Thompson – Minnesota Microsoft Dynamics CRM Partner

Josh Thompson