How to evaluate the health of the indexes in the CRM databases

 

In this article I want to present a way to generate an overview of the health status of the indexes in your CRM database.

The following  script can be used to retrieve the fragmentation ratio of each index in the CRM database. 
Additionally the script evaluates the point of time when the index’s statistics were updated the last time.

IMPORTANT
Before running the script, please replace all occurrences of the placeholder “<Your CRM DB>” with the name of the database you want to check.
The script only works for SQL Server 2005 and 2008.

 

The query retrieves the following information for you:

Database:                       Name of the database where the index belongs to.
Table:                             Name of the indexed object in the database.
Index:                            Name of the index.
Index_Type:                  Type of the index (CLUSTERED, NON-CLUSTERED, etc.).
Fragmentation_Ratio:      Ratio in percent, that describes who much of the index is fragmented.
Last_Statistics_Update: Point of time, when the indexes statistics were updated for the last time.

 

To retrieve this information from the CRM database, please execute the following T-SQL query:

------------------------------------------------------------------------------------
-- !! IMPORTANT !! You need to run this query in the context of the CRM Database you
-- want to analyze. This is needed for SQL system function STATS_DATE to work properly
-- Please insert the name of the CRM database behind the "USE" instead of the placeholder
-------------------------------------------------------------------------------------

USE <Your CRM DB>
GO

------------------------------------------------------------------------------------
-- 1. Variable Declaration
------------------------------------------------------------------------------------

DECLARE @DatabaseName nvarchar(50)
DECLARE @SqlMsg nvarchar(4000)
DECLARE @SqlVariableDec nvarchar(1024)

------------------------------------------------------------------------------------
-- 2. Set the database name
--
-- !! IMPORTANT !! Please insert the name of the CRM database, you want to check
-- Example:  SET @DatabaseName = 'Microsoft_MSCRM'
-- ---------------------------------------------------------------------------------
SET @DatabaseName = '<Your CRM DB>'

-------------------------------------------------------------------------------------
-- 3. Building and executing the queries
-------------------------------------------------------------------------------------
-- 3.1 Index fragemtation in the CRM database
--------------------------------------------------------------------------------------

SET @SqlMsg =

N'
SELECT DB_NAME(s.database_id) AS [Database], t.name AS [Table], i.name AS [Index], i.type_desc AS [Index_Type], s.avg_fragmentation_in_percent As [Fragmentation_Ratio], STATS_DATE(t.object_id, i.index_id) AS [Last_Statistics_Update]
FROM sys.dm_db_index_physical_stats(DB_ID(@DbName), NULL, NULL, NULL, ''DETAILED'') As s
JOIN ' + @DatabaseName + '.sys.indexes AS i ON i.object_id = s.object_id And i.index_id = s.index_id
JOIN ' + @DatabaseName + '.sys.tables As t ON i.object_id = t.Object_id

'

SET @SqlVariableDec =
N'@DbName nvarchar(50)'

--------------------------------------------------------------------------------------
-- 3.2 Execute index fragmentation query
---------------------------------------------------------------------------------------

EXEC sp_executesql @SqlMsg, @SqlVariableDec, @DbName = @DatabaseName;

 

Index Fragmentation
The fragmentation ratio of an index can have a big impact on how fast your database queries can execute. A high value means that  the data pages of the index are out of order so the query execution time will be negatively affected.

 

To get (and  keep) a good system performance you should always keep the fragmentation ratio as low as possible.

Actions when you discover index fragmentation
1. Reorganize the index: 5% < Indexfragmentation_Ratio < 30%
2. Rebuild the index:               Indexfragmentation_Ratio > 30%

 

Please read the following article  for further information about reorganizing or rebuilding an index in SQL Server:
https://msdn.microsoft.com/en-us/library/ms189858.aspx

 

Table Statistics
The table statistics are used by the SQL Servers query optimizer to decide on how the query plan for a query on a certain table or view should be build.
The statistics contain statistical information about the content, order and usage of the data in the table’s indexes. To achieve the best query performance the Statistics
need to be as up-to-date as possible. Therefore you should think about updating the index statistics on a regular basis.

 

Action when you discover that the last update run was a long time ago or even has never been executed (NULL):
1. Update the index statistics
2. Create a maintenance plan in SQL Server to schedule regular updates:

 

Please read the following article on statistics
https://msdn.microsoft.com/en-us/library/ms190397.aspx
https://msdn.microsoft.com/en-us/library/ms187348.aspx

 

Greetings,
Alex Leu