Using Database Tuning Advisor with CRM


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.

Jay Grewal

Comments (10)

  1. I think that the problem with sp_executesql not working in DTA was fixed in a service pack – it certainly seems to work on SQL 2005 SP2, though it does give that cryptic warning: ‘Replaced event [sql] for tuning purposes’.  It still doesn’t work with the ‘Show Execution Plan’ tool though, which is pretty inexcusable from the SQL team.

    Turning to the CRM team 🙂 … does this mean that applying DTA optimizations to the CRM database is now supported?  The previous position was that any direct modifications to the SQL database were unsupported.  Has this changed?

  2. ray says:

    Are you sure you want to put this on gotdotnet and not codeplex?

  3. Jay Grewal says:

    "I think that the problem with sp_executesql not working in DTA was fixed in a service pack"

    You’re right — it gives a different message now, at least.  I have not yet verified if the tuning recommendations are identical with and without the parameterized SQL, but I will look into that as well.

  4. Jay Grewal says:

    "Are you sure you want to put this on gotdotnet and not codeplex?"

    It seems there are a few different places these kinds of projects end up.  I will work with Jim Glass to make sure it ends up in the most appropriate location.

  5. Jan Klode says:

    I was not able to find your tool for converting the “sp_executesql” statements on gotdotnet. Is it already available there?

  6. John G. says:

    I agree with the last poster i could not find the sample either….

  7. Valentijn says:

    I see the ‘Replaced event exec xxxx yyy with yyyyy for tuning reasons’. Does this mean the event is used in the analysis?

  8. manojsy says:

    I am a developer in the Database Tuning Advisor team. In Yukon SP2, we addressed the issue of tuning a fairly large class of Transact-SQL statements that are executed  in the context of a sp_executesql command.  

    Some background/clarifications: In order to tune a statement DTA relies on getting an execution plan back from the server. For sp_executesql,  the server does not return a plan corresponding to the statement in the sp_executesql. Therefore, DTA extracts the SQL statement from sp_executesql (including parameters etc.) and then tunes the statement. DTA logs an event in the tuning log which is simply an informational message to the user. In case you encounter any issues tuning sp_executesql statements, please let the SQL Server team know.

  9. Jeffrey Roughgarden says:

    I get a series of S008, S007, E000 messages in the Tuning Log. These are the S008 ‘Event does not reference any tables’ because of sp_executesql, followed by the S007 replaced message (where the sp_executesql is replaced with its contents), and finally an E000 event which says the table does not exist (when it does). Very annoying.

    See http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/95f6a9e9-d4f6-42e6-926d-00b2939e5d41/ for another user’s example.

    There is also reference to an XML cleanup tool in this article but I cannot find it. It would be nice for Microsoft to either provide a tool or fix the DTA.

    Jeff Roughgarden, MCSD, MCDBA

  10. I also couldn’t find the tool mentioned, so I wrote one myself – it’s available at http://code.msdn.microsoft.com/spExecuteSqlparser

Skip to main content