This article has come from a very experienced colleague in Australia.

Introduction

Microsoft SQL Server 2005 added new system tables to assist in the system reporting. Three of these tables in particular are written to every time a query is run. Microsoft SQL Server 2005 processes each query, determines the time taken for the query, the impact of the query on the system and recommends indexes if required. This is all stored in these new System tables.

We can take advantage of these tables and therefore embed within Sage CRM the functionality to identify required indexes and if necessary create them through the CRM interface.

This article will be broken into 2 parts. This is the first and will run through example functionality and the second will get into the technical details of how to build the functionality.

Please note this is only valid for Microsoft SQL server 2005 and beyond.

The New System tables

There are 3 tables that we can use to assess missing indexes and the impact they have on server performance.

• sys.dm_db_missing_index_group_stats: This contains the details to assess the damage a missing index is causing to performance
• sys.dm_db_missing_index_details : This contains the details required to build the missing index
• sys.dm_db_missing_index_groups: Links the above 2 tables together.

The Development process 1

The objective of the development process is to build an area within Sage CRM Administration to view and search for indexes that are missing from any database on the Microsoft SQL. This can be built using the standard APIs for building Application Extensions either ASP pages or .NET assemblies could be used.

Tour of the completed customization 1

In Administration under Data Management the Missing Index behaviour has been created.

Entering the missing index behaviour allows the user to search for missing indexes in Databases and Tables on the CRM Database server. This is not restricted to the Sage CRM Database.

From this list you can build up the SQL code to build the indexes yourself and figure which index is the most important by seeing the calculated Average user impact column (the higher the number the more impact it has on performance.

The Development process 2

The objective of the first development process illustrates the concepts that can be used but this can be developed a little further. A screen can be created that calculates a definitive number to determine the impact on performance and presents the SQL necessary to build the index for you and then will go off and create the index in the CRM database if you desire. Development can be completed using the same API choices with some additional SQL code to build the index.

Tour of the completed customization 2

In Administration under Data Management the Missing Index Create behaviour has been built.

Entering the missing index create behaviour allows the user to search for missing indexes in the existing database. We can't create outside the existing database without running into security issues. Searching can be conducted on the table or the calculated impact and when you click the green 'go' arrow it creates the index for you — and removes it from the list.

This is very useful functionality but with demo data we can't really see the impact this has — see the below screenshot of the results against a production database.

292 indexes recommended

Sorting by calculated impact can bring up some astounding numbers.

I hope this has been useful and the second part of this article will appear in due course.