SQL Server 2005 comes with a tool called Database Tuning Advisor (DTA) that can help you tune your database given a particular workload. The tool is an evolution of Index Tuning Wizard from previous versions of SQL Server. DTA can be a very useful tool, but there are a few things to keep in mind when using it with Microsoft CRM.
Workload Input
DTA needs some kind of workload in order to make tuning recommendations. This can be as simple as a single query, or as complex as a full production workload captured during peak system use. When feeding a raw profiler trace from CRM into DTA, you might see a log like this:
It indicates that DTA was only able to consume 32% of the workload, with the reason for many statements being “Event does not reference any tables”. This will be the case for many statements that begin with “exec sp_executesql”, which is how parameterized SQL statements are executed. CRM uses parameterized SQL for performance and security reasons, so the vast majority of statements cannot be processed directly by DTA. If DTA is to process these queries, they must first be converted from this form:
exec sp_executesql N’select top 51 equipment0.Name as ”name”, equipment0.EquipmentId as ”equipmentid” from Equipment as equipment0 where ((equipment0.DeletionStateCode in (@DeletionStateCode0)) and (equipment0.BusinessUnitId is not null)) order by equipment0.Name asc, equipment0.EquipmentId asc’,N’@DeletionStateCode0 int’,@DeletionStateCode0=0
To this form:
select top 51 equipment0.Name as ‘name’, equipment0.EquipmentId as ‘equipmentid’ from Equipment as equipment0 where ((equipment0.DeletionStateCode in (0)) and (equipment0.BusinessUnitId is not null)) order by equipment0.Name asc, equipment0.EquipmentId asc
Doing this manually for one or two statements is no big deal, but doing this for a large workload is impractical. I wrote a tool that will convert all the “sp_executesql” type statements in a trace to an un-parameterize version and it should be up on the GotDotNet User Samples site soon.
Tuning Options
There are a variety of tuning options provided by DTA. In the UI version of the tool, you can choose which kinds of Physical Database Structures (PDS) can be used (Indexes, Indexed Views, etc.), which existing PDS are available for deletion, the duration of tuning, etc. There is also a command line version of the tool where there are even more options, including a minimum percentage improvement to be met for the recommendations.
The details of all the options are too rich to go into here, so I would recommend reviewing the documentation for the available tuning options, the dta command line utility and its XML input file reference.
One thing I will mention is that if you have the Enterprise edition of SQL Server, Indexed Views can be a very powerful tool since SQL’s query optimizer can use indexed views to get a better plan even if the indexed views are not referenced directly in the query. This is only available with the Enterprise edition of SQL Server.
Evaluating Recommendations
After DTA has performed its analysis (and depending on your tuning options), it will have a series of recommendations with some evaluation of the percentage improvement you can expect. You’ll have the option of implementing this directly, or saving the recommendations into an XML file or a SQL script.
It is important to remember that DTA is only making good guesses at these recommendations and the improvement they will have, so it’s important to have a plan for evaluating the recommendations in practice. This also means having a plan to revert any recommendations you choose to apply. For an individual SQL query, it may be as simple as just running the query directly in SQL, but if you are tuning a whole workload, you may want to consider using something like the performance toolkit to evaluate the recommendations. Be sure to understand the cost of maintaining added indexes as well – it might not be worth the cost if a query improvement degrades create/update performance significantly.
Don’t be too surprised if performance doesn’t improve or even degrades with the recommendations, especially for complex workloads. Database tuning is a complex undertaking, especially for an application like CRM where users are allowed to craft custom advanced find queries and create their own user queries. DTA can often help (especially with individual statements), but there is no substitute for understanding your customizations and how your users are using the system.
Customizations
If you implement any recommendations that involve adding indexes on customer-added attributes or indexed views that involve these attributes, you’ll need to be aware that if you ever decide to delete these attributes, you should first delete all your custom indexes and indexed views on these attributes first, or else it might interfere with CRM deleting the attributes.
Wrapping Up
DTA can be a very helpful tool to tune your database, but it requires some special care when using it with a Microsoft CRM implementation. Hopefully this entry has given you a little more background on how to use it properly. You can also learn more about how DTA (or really its predecessor, Index Tuning Wizard) works based on this paper: ftp://ftp.research.microsoft.com/users/autoadmin/vldb97.pdf.