The Database Engine Tuning Advisor provides features that enable both new Sage CRM Administrators and/or experienced database administrators to tune databases for better query performance. Its primary recommendations are related to clustered/non-clustered indexes and statistics improvements on a database, in order to improve database performance.

My experience with the Database Engine Tuning Advisor is on the whole positive - I have never seen a situation where it has affected database performance negatively. For those of us in Support/PSG teams it has proven beneficial where Customer sites may not necessarily have on-site database administration skills and the CRM database is underperforming. As the number of users' increases and the volume of data grows over years of use, so does the necessity to fine-tune and optimise your database.

A 'quick-win' and performance boost might be to run that CRM database and associated SQL Query Profiler Workload, through SQL Server's Database Engine Tuning Advisor tool.

So the Database Engine Tuning Advisor in my book, represents "non-destructive tuning", you are not dropping current indexes or statistics, it won't make things worse!!

Characteristics of an Under-Performing Database

An underperforming CRM database may exhibit some of the following characteristics:

-SQL Timeouts are apparent in CRM logs e.g. your SQL log might display an error like this

Jul 14 2009 10:08:50.509 INSERT INTO Cases....(case_primarycompanyid,case_primarypersonid)...Timeout expired

[if you gather logs over various days, you can isolate which table seems to be the source of the timeouts, in my example inserts into the Cases table are a problem]

-The database generally has become a bottleneck - If you export your SQL Log file into Excel and Order it by the longest running queries [column 'f' below corresponds to 'time' taken to run the SQL Query] So below we have queries ranging from 133 seconds up to 530 seconds:

These are significant query times.

-An increase in database Locks. Locks are not necessarily a bad thing, if a table record is already being edited by one User then no other User will be able to make any changes to that record [hence the lock occurs, standard functionality]. In addition, CRM locks might indicate poorly structured asp customisations or SQL locks might indicate recursive actions (customised table level scripts, triggers, stored procedures issues etc).

But overall an increase in Locks on a table may indicate that inserts/updates are not tuned or running efficiently.

-Where Performance monitoring tools like Microsoft Perfmon counters e.g. Page Life Expectancy; % Processor Time;Buffer Cache Ratio; % Disk Time ...all point to the DB Server being under memory pressure. In such situations, I would look at the CRM database, is there a bottleneck somewhere, is it under performing?

Consequently, one effective action I would take on a database displaying any of these characteristics, is to gather an 'effective' SQL Profiler workload and run the resultant SQL Profiler workload through the Database Tuning Advisor.

Let's look at the workload first of all.

Gathering an 'effective' Workload/SQL Profiler

SQL Server Profiler is a graphical user interface for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event [TSQL] to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.

TIP!! Run SQL Profiler on a different machine!This will take the load off the 'Live' Production Server.

TIP! Gather a Workload that is "representative" of the User workload.ie. run for longer than a day. If run for only an hour, it will lead to only those specific queries being indexed. That means that indexes added are not representative of normal day to day usage and may "mis-read" what indexes should be carried out. So don't tune for a short period of time. Why not start the workload file on a Monday morning, pause it Monday evening, restart it Tuesday morning, pause in the evening and so on for one week.

TIP! Ensure you capture all "effective" traffic e.g. is it a busy day with lots of users, is the whole Sales Team in the office or are they at a conference, are large, end of month reports being run?etc.

TIP! Why not get end users, and the Customer to gather this effective workload?I have been involved in issues where the gathered workload was useless where those creating the Workload were testing areas of the product the end Customer wasn't using! ie you NEED to know the Customers system to some degree and be aware of why/how they use CRM, know the most 'hit', frequently used areas of functionality in order to 'optimise' their customised database.

Once you have the Workload, run it through the Database Engine Tuning Advisor.

Database Engine Tuning Advisor Configuration

This is run from SQL Server Management Studio under Tools | Database Engine Tuning Advisor

N.B. From a SQL permissions perspective, SQL's sysadmin role allows you tune databases. Users who are members of the db_owner fixed database role can also tune databases that they 'own'.

-Choose your SQL Profiler [Workload] file, and your database ['Database for workload analysis']

-I tend to go with the defaults in the 'Tuning Options' tab.

'Physical Design Structures' mean indexes, and the option below means clustered, non-clustered indexes and Statistics will be recommended.

I would recommend going with 'no partitioning', unless you are using partitioning of database underlying tables or views[Partitioning is a conversation for another day!]

'Keep all existing PDS' means that all existing structures are left intact. We prefer that as again it represents "non-destructive" database tuning.

Once you select the 'Advanced Options' button, you can type the 'maximum amount of space to be used by physical design structures' as recommended by the Database Engine Tuning Advisor. You can set a limit there.

I recommend choosing 'All recommendations are offline' - best not to create these recommendations online unless you view the proposed recommendations first. Consequently, maybe run the recommendations outside of office hours.e.g this is useful in our own Hosted environment, where we don't want recommendations generated while Users are online.

Select 'Start Analysis'.

Database Tuning Advisor Recommendations

The Database Tuning Advisor provides recommendations, and an 'estimated improvement' based on the affect of running those suggestions .e.g. a 12% estimated improvement based on my test workload file and test database:

This is largely based on new indexes and statistics around my huge and under-performing imported Company table:

There is a nice Tuning Summary Reports section, where you can see how many indexes and statistics are recommended, the space these will use etc.

Again there are options to review/evaluate these recommendations, to save them for another day and ultimately an option 'To Apply' these proposed Recommendations, go to the Actions menu and Apply Recommendations.

Database Maintenance Plan

I would suggests going forward, that EVERY customer has an effective Maintenance Plan in place.

This should incorporate some of the following

[a] Database Integrity Check

[b] Monthly rebuilding of database indexes

[c] Update Statistics after your indexes have been rebuilt, again once a month.

And going forward, your database has been optimised via the Database Engine Tuning Advisor and you now have an effective Maintenance Plan to ensure your database stays optimised!