3

I am attempting to use the SQL profiler and Tuning wizard to investigate index usage in my SQL 2008 R2 database. The application connects to the db using ODBC. The db has not Stored Procedures and all the data is accessed using SQL statements. The profiler shows the statement as 

declare @p1 int
set @p1=10234
exec sp_prepexec @p1 output,N'@P1 varchar(max),@P2 varchar(max),@P3 int,@P4 int',
N'SELECT  p.PaymentID from Payment where DivisionCode = @P1 and [...],','DM','A',1,1
select @p1

The tuning wizard will analyse the data and give a warning

49% of consumed workload has syntax errors. Check tuning log

The log shows the reason

Event does not reference any tables.

or

[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'Payment'.

What settings do I need to use to get the tuning wizard to understand the sp_prepexec queries? What changes must I make to the trace file to make it useable by the tuning wizard?

Thanks.

Yossi
  • 136
  • 2
  • 7

1 Answers1

3

I was able to get mine to work! Simply comment out the set statement like this:

declare @p1 int;
--set @p1=9
exec sp_prepexec @p1 output,N'@p0 bit,@p1 varchar(8000),@p2 bit',N'select blah from blah where @p0=1 and blah.LastName=@p1',@p0=0,@p1='Kumar'
select @p1
Jess
  • 23,901
  • 21
  • 124
  • 145