CUSTOMIZED MAINTENANCE – Based on company searches

Creating and Managing Indexes

A common problem that affects Microsoft Dynamics CRM performance is searching the SQL Server database for information in a specific view without having a corresponding index that adequately satisfies that particular query. For example, if you query for leads ordered by company name, but do not have an index set up that gathers leads by company name, SQL Server must search through every record in the Leads table, looking for the company name that you requested. When SQL Server uses an index, it goes directly to the records that match the query.

Important:    Creating indexes and managing indexes are advanced tasks. Make sure that you have the necessary knowledge and experience before you try the tasks described in this section.

For more information, see the “Indexes” topic in the SQL Server 2000 section of the MSDN Library:


Optimizing Indexes with the Index Tuning Wizard

If you have a large database, determining what indexes to create can be a complex task. The Index Tuning Wizard enables you to select and create an optimal set of indexes and statistics for a Microsoft SQL Server 2000 database without requiring an expert understanding of the structure of the database, the workload, or the internals of SQL Server.

The Index Tuning Wizard can perform the following actions:

  1. Recommend the best mix of indexes for a database given a workload, by using SQL Query Analyzer to analyze the queries in the workload.

  2. Analyze the effects of the proposed changes. These include index usage, distribution of queries among tables, and performance of queries in the workload.

  3. Recommend ways to tune the database for a small set of problem queries.

  4. Enable you to customize the recommendation by specifying advanced options such as disk space constraints.

A recommendation consists of SQL statements that can be executed to create new, more effective indexes and, if it is necessary, remove existing indexes that are ineffective. We recommend indexed views on platforms that support their use. After the Index Tuning Wizard has suggested a recommendation, you can then use this information to determine how you want to handle the recommended change:

  1. Implement the change immediately.

  2. Schedule the change to be implemented later by creating a SQL Server job that executes an SQL script.

  3. Save the change to an SQL script, which you can execute manually later, or on a different server.

For more information about index views, see the white paper “Improving Performance with SQL Server 2000 Indexed Views”, available in the MSDN Library:


The Index Tuning Wizard does not recommend indexes for:

  1. Tables that are referenced by cross-database queries that do not exist in the selected database.

  2. System tables.

  3. PRIMARY KEY constraints and unique indexes.

For more information about index tuning, see the article “Index Tuning Wizard,” available in the MSDN Library:


Warning:       Before you perform any of the following optimization procedures, back up your databases and Active Directory. If you do not back up these items, you risk losing the information that is contained in them.

Determine what indexes to create

The first step in using the Index Tuning Wizard to optimize your indexes is to determine what indexes must be created.

To determine what indexes to create:

1.    Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

2.    Expand SQL Server Group, and then expand the server that contains the Microsoft Dynamics CRM database for which you want to create an index.

3.    On the Tools menu, click Wizards.

4.    Expand Management.

5.    Double-click Index Tuning Wizard.

6.    Complete the steps in the wizard.

Create an index using the Create Index Wizard

If you want to add indexes without using the Index Tuning Wizard, you can use the SQL Server Enterprise Manager to create the indexes that you must have by following the steps described in this section.

For more information, see the “Creating an Index” topic in the SQL Server 2000 section of the MSDN Library:


To create an index using the Create Index Wizard:

1.    In SQL Server Enterprise Manager, expand a server group, and then expand the server in which to create the index.

2.    On the Tools menu, click Wizards.

3.    Expand Database.

4.    Double-click Create Index Wizard.

5.    Complete the steps in the wizard.

Optimizing Indexes with the Database Engine Tuning Advisor

If you are using SQL Server 2005 you can use the Database Engine Tuning Advisor instead of the SQL Server 2000 Index Tuning Wizard. The latest version of this tool includes many improvements, and frequently displays good recommendations for indexes to use in the Microsoft Dynamics CRM database.

Note:  Additional indexes may affect performance both negatively and positively. Therefore, backups should be performed before you add any indexes or change any indexes. Default Microsoft CRM indexes should not be modified or deleted.

For more information about how to use the Database Engine Tuning Advisor to optimize your indexes, see the topic “How to: Tune a Database by Using Database Engine Tuning Advisor” in the SQL Server 2005 section of the MSDN Library:


Finding Missing Indexes with SQL Server 2005 Database Management View

You can use SQL Server 2005 Database Management View (DMV) to find any indexes that may be missing.

This script will only work using SQL Server 2005. The script recommends and generates CREATE INDEX statements based on the last five hundred SELECT statements that were run since the SQL Server 2005 server was last started. It bases these indexes on the worst-performing queries that are missing indexes.


·         The Create Index statements may not have the index columns in the correct order. In addition, to receive more accurate results that take into account UPDATE and DELETE statements, you should run the SQL Server Database Engine Tuning Advisor with a representative profiler trace workload when Microsoft Dynamics CRM is being accessed by users.

·         You may not find a business need for every new index that DMV suggests. Because too many indexes can cause performance problems, it is important to test and validate each set of results before you implement a new index in your production environment.


1.    Before you run the script at the end of this procedure:

·         Change the format of the results to text or a file.

·         Change the database context to the OrganizationName_MSCRM database.

For example, if you were working with the Adventure Works Cycle Microsoft Dynamics CRM database, you would type the following commands:

USE Adventure_Works_Cycle_MSCRM



2.    To create the indexes recommended by DMV, copy the CREATE INDEX statements from the results into a new SQL Management Studio window and run them on the Microsoft Dynamics CRM database.

      set nocount on

      set ansi_warnings on

      set ansi_padding on

      set arithabort on

      set concat_null_yields_null on

      set numeric_roundabort off


      declare @exec_stmt nvarchar(4000),

      @table_name nvarchar(521),

      @column_name sysname,

      @column_usage varchar(20),

      @column_id smallint,

      @index_handle int,

      @database_id int,

      @object_id int


      declare ms_cri_tnames cursor local static for

      Select Top 5



                        mid.statement as table_name,

                        mig.index_handle as index_handle


      (select (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage,

                        migs.*      from sys.dm_db_missing_index_group_stats migs) as migs_adv,

      sys.dm_db_missing_index_groups mig, sys.dm_db_missing_index_details mid

      where migs_adv.group_handle = mig.index_group_handle and

      mig.index_handle = mid.index_handle and migs_adv.index_advantage > 10

      order by migs_adv.index_advantage DESC


      create table #tablenametab

      ( table_name  nvarchar(521) collate database_default )

      create table indexList

      ( execstmt  nvarchar(521) collate database_default )


            insert INTO indexList (execstmt) values ('--THESE INDEXES ARE ONLY BASED ON THE LAST 500 SQL SERVER QUERIES EXECUTED')


      truncate table #tablenametab

      open ms_cri_tnames

      fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle


      while (@@fetch_status <> -1)


            if (@table_name not in (select table_name from #tablenametab ))


                  declare ms_cri_cnames cursor local for

                        select column_id, quotename(column_name,'['), column_usage

                        from sys.dm_db_missing_index_columns(@index_handle)


                  open ms_cri_cnames

                  fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage

                  declare @index_name sysname

                  declare @include_column_list nvarchar(517)

                  declare @key_list nvarchar(517)

                  select @index_name = '_MS_Sys'

                  select @key_list = ''

                  select @include_column_list = ''

                  declare @num_keys smallint

                  declare @num_include_columns smallint

                  select @num_keys = 0

                  select @num_include_columns = 0


                  while @@fetch_status >= 0


                        if (@column_usage = 'INCLUDE')


                              if (@num_include_columns = 0)

                                    SET @include_column_list = @column_name              


                                    SET @include_column_list = @include_column_list + ', ' +@column_name

                              SET @num_include_columns = @num_include_columns + 1




                              if (@num_keys = 0)

                                    SET @key_list = @column_name


                                    SET @key_list = @key_list + ', ' +@column_name


                              SET @num_keys = @num_keys + 1

                              SET @index_name = @index_name + '_'+cast ( @column_id as nvarchar(10))



                        fetch next from ms_cri_cnames into @column_id, @column_name, @column_usage


                  close ms_cri_cnames

                  deallocate ms_cri_cnames


                  if (@num_include_columns > 0)

                        SET @exec_stmt = 'CREATE INDEX ' +  @index_name  + ' ON ' + @table_name + '(' + @key_list + ') INCLUDE ('+ @include_column_list + ')'-- WITH (ONLINE = ON)'


                        SET @exec_stmt = 'CREATE INDEX ' +  @index_name  + ' ON ' + @table_name + '(' + @key_list + ')'-- WITH (ONLINE = ON)'

                  insert INTO indexList (execstmt) values (@exec_stmt)



            fetch next from ms_cri_tnames into @database_id, @object_id, @table_name, @index_handle


      deallocate ms_cri_tnames

      drop table #tablenametab

      select * FROM indexList      

      drop table indexList

set nocount off


Removing Messages Stored in the Microsoft Dynamics CRM Connector for Microsoft Dynamics GP Integration Database

The size of the integration database used by the Microsoft Dynamics CRM Connector for Microsoft Dynamics GP can affect the performance of Microsoft Dynamics CRM 3.0.

For more information about maintaining the size of this integration database, see Chapter 9 in the Microsoft Dynamics CRM 3.0 Connector for Microsoft Dynamics GP Guide. This chapter discusses how to maintain the connector. The chapter includes details on how to use the int_DatabaseCleanup stored procedure to clean up old messages in the integration database to reduce its size. By decreasing the number of old messages stored in this database, the Integration Monitor can perform better, because it will not have to query as much data in the following tables:

·         IntMessageBodyHeader

·         IntMessageBodyData

·         IntMessageComponentLogs

The latest version of the connector includes many improvements for both performance and data integrity. For more information about the Microsoft Dynamics CRM 3.0 Connector for Microsoft Dynamics GP, see:

Find a remedy.