Dynamics CRM Database Optimization with DMVs

On this article I will be covering mainly optimization around indexes and how DMV's can help to monitor and optimize indexes.

Data management views (DMV's)
Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. DMV's are extremely helpful in particular around performance-tuning. The information is stored until you restart SQL server, after a restart all is reset to 0.

There are a number of DMV's to retrieve a different number of metrics for different purposes; we will be looking in particular at the Index related and Execution DMV's:

• Index
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_missing_index_columns
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• Execution
• sys.dm_exec_query_stats
• sys.dm_exec_requests

1. http://msdn.microsoft.com/en-us/library/ms187974(v=sql.105).aspx
2. http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).asp

Indexes Overview
Imagine walking to a library with 1 million books stored across 5 floors, and ask for a book. If the library does not keep a record where each book is stored, which floor, shelf, categories etc... how long to you think staff would take to find your book? a very long time. The same concept applies to databases, indexes are needed to improve response time when users request specific data.

We will be mainly looking at optimization with indexes so before we do that I want to explain very briefly what they are and how they work. There are two types of indexes, clustered index and non-clustered index.

• Non-Clustered IndexesThis type of index, indexes a particular column and hold a reference to where on the table this data can be found. The example below, shows if you want to find an order with an amount of 210 you would need to scan the entire table to find all orders with an amount of 210. If we create an index for the column 'Amount', the index it's ordered descendent and the query would stop at the last 210 and there would be no need to continue the execution.

• Clustered IndexesClustered indexes have the same concept but, they sort the column 'Amount' descendent on the table itself, when orders with the amount 210 are found SQL does not need to lookup the reference to find the details of that order, the data is accessible immediately. 

Both type of indexes have positive and negative areas, the clustered index has a higher cost when you need to re-index the column 'Amount' because all the rows need to shift into the correct place, however the data it's immediately available when data is found. The non-clustered indexes are quicker to re-index but require an extra lookup to gather the data on the table. Also note that the clustered indexes only one index can exist per table. The non-clustered indexes can be created for multiple columns on the same table.

Missing Indexes
DMV's store missing indexes when queries are optimized by the SQL query optimizer; if you have a customized CRM environment you should monitor these type of DMV's on a regular basis. Below a nice script you could use with references to missing indexes DMV's.


SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,   'CREATE INDEX [myIndex_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)   + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'   + ' ON ' + mid.statement   + ' (' + ISNULL (mid.equality_columns,'')     + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END     + ISNULL (mid.inequality_columns, '')   + ')'   + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,   migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Index Usage
It's important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.

To check for index usage run the following query:

 select OBJECT_NAME(a.object_id),b.name,a.user_seeks,a.user_scans,a.* from sys.dm_db_index_usage_stats a join ORG_MSCRM.sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id where a.database_id = DB_ID('ORG_MSCRM') and b.name like 'myIndex%' order by b.name asc The output of this query would be something like:

From the above screenshot is clear that _DMV_09 has not been used since it was created and therefore should be deleted.

Fragmentation
CRM ships with built-in maintenance jobs which are configured during installation and are designed to optimize the database and reduce fragmentation. It's good practice to re-configure these jobs to run out-of-hours.

To configure the built-in sql maintenance jobs download the following tool:
http://crmjobeditor.codeplex.com/

Place the tool under C:\Program Files\Microsoft Dynamics CRM\Tools\

The tool provides a flexible and easy way to configure the maintenance jobs; note that the job will run once every day( every 1440 minutes), you can configure it to run more times during the day.

Normally the CRM built-in maintenance jobs are designed to keep the system healthy and do a very good job, to understand if you need these jobs to run more often you can monitor fragmentation with the following queries:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM sys.dm_db_index_physical_stats (DB_ID(N'ORG_MSCRM'), NULL, NULL, NULL , 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC
Or you can be more specific at the table level:

SELECT avg_fragmentation_in_percent,* FROM sys.dm_db_index_physical_stats ( db_id('ORG_MSCRM'), OBJECT_ID('PrincipalObjectAccess'), NULL, NULL, 'DETAILED')

You can check if the maintenance tasks have run successful by looking at the last time the indexes have been updated:
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('PrincipalObjectAccess') Check for expensive queries
With a better understanding around indexes and DMV's we could start looking at top 20 most expensive queries in your CRM environment using sys.dm_exec_query_stats to give you a better picture what is going on under the hood and analyse whether tuning is needed for those queries.

SELECT DISTINCT TOP 20 t.TEXT QueryName,
s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / NULLIF(s.execution_count,0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()),0), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC

Database Maintenance
However CRM maintenance jobs do a really nice job in keeping the system healthy, I would still recommend  creating SQL jobs to do regular out-of-hours database maintenance:

• Shrink the database
• DBCC SHRINKFILE (1, TRUNCATEONLY);
• DBCC SHRINKFILE (2, TRUNCATEONLY);
• Rebuild indexes
• EXEC sp_msforeachtable 'DBCC DBREINDEX("?"," ",80)'
• Update statistics
• EXEC sp_updatestats;

Summary
There are a number of scripts and documentation on the internet around DMV's, is good practice monitor these metrics and optimize the system accordingly. Note that with each SQL version DMV's may change and new features added etc...

We have covered basic SQL optimization around indexes and an overview on how indexes work and how they can help. I hope you enjoyed the article, please leave your feedback.

References

1. http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
2. http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).aspx