Quirks in the SQL Server Database Engine Tuning Advisor
Monday, July 12, 2010 at 04:27 PMOur enterprise customer corrspondence management product ITP/CCM uses a database to keep track of things. Of course, we try to optimize the performance of our database design: it's very easy to create a slow database, but it takes actual effort to create a fast one.
The Microsoft SQL Server Database Engine Tuning Advisor (DTA) is a very useful tool to help us in our quest for performance. In our development infrastructure, we've implemented automated trace file generation for typical workloads, and we've also managed to run the tuning advisor automatically every night (using the dta.exe command line tool). However, the tool has a lot of quirks, especially when used in conjunction with Microsoft's Entity Framework.
Quirk 1
The sp_executesql construct is not supported by the DTA. And Entity Framework generates pretty much all queries wrapped in sp_executesql calls!
Quirk 2
Statements of the following form are not supported by the tuning advisor:
DECLARE @x type, @y type
SELECT @x=value, @y=value
UPDATE/INSERT/SELECT/DELETE <...>
Quirk 3
Ever since we upgraded from SQL Server 2005 to SQL Server 2008, the tuning advisor kept giving us errors like "59% of consumed workload has syntax errors. Check tuning log for more information.". The tuning log then told us that perfectly fine queries contained an "Invalid object name dbo.Something". And running those queries through the tuning advisor invididually actually went just fine!
We eventually found a workaround, which was to update the queries to reference [Database name].[dbo].[Something] instead of just [dbo].[Something]. This was in a situation that worked in the 2005 version, we passed only a single database for optimization, and we also passed a database to connect to -- and still it couldn't find the objects.
So, the workaround?
Well, we're programmers, so we wrote a tool. The on-disk trace file format is not documented, but the trace table format is, so we loaded the trace file into a trace table. We then wrote a tool to go through that table, and to replace all sp_executesql calls with expanded versions of the same statements. The tool does something similar for the DECLARE...SELECT... statements (or at least for the limited forms of such statements spewed out by Entity Framework). And finally, it replaces "[dbo]." by "[Database name].[dbo].". And voilà: the number of syntax errors went down into the single digits. There still are some minor queries that the tuning advisor doesn't understand, but at least the majority of our queries are analyzed and included in the tuning advice.