DTA & sp_executesql


  • Hi


    I am struggling with Database Tuning Advisor. The application in question generates sp_executesql queries only.


    --1. application query

    exec sp_executesql n'select top 200 surname from person where surname like @p0',n'@p0 nvarchar(50)',@p0=n'%smith%'

    --2. dta converted query from above

    declare @p0 nvarchar(50)

    set @p0 = n'%smith%'

    select top 200 surname from person where surname like @p0

    --3. this works

    select top 200 surname from person where surname like n'%smith%'


    DTA converts the query from 1 to 2, but then says query didnt reference any tables.


    The third form does work.


    So for my exercise I have had to tediously convert queries of form 1 to 3.


    I was wondering whether:

    1. Anyone new of a tool for converting parameterized queries to literal ones for my testing current testing purposes?

    2. If anyone had overcome this with regard to Profiler traces; ultimately I work like to be able to capture a significant workload and put that through DTA on a regular basis?




    Friday, December 5, 2008 1:16 PM

All replies

  • 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.

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

    Jeff Roughgarden, MCSD, MCDBA

    Friday, November 13, 2009 2:54 AM