Database Tuning Advisor doesn't process trace file.

Last Post 06 Jun 2008 06:32 AM by jdunleavvertex. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
jdunleavvertex
New Member
New Member

--
05 Jun 2008 08:05 AM
I'm running 2005 SP2 - Database Tuning Advisor.
I receive the error 99% of consumed workload has syntax errors while processing a trace file.
Database Tuning Advisor doesn't like the format of the tracefile from the Sql profile. Most of my statement are parameterized queries running as a batch from jdbc.
The trace file has about 500000 rows, so changing the format by hand isn't going to happen.

Here's is two example rows from a standard trace file I collected with Sql Profiler.

SQL:BatchStarting create proc #jtds000055 as UPDATE DMActivityLogStrng SET criteriaString = 'Importing Journal Completed data set: journal.lineitemlocation' WHERE activityLogId = 14 AND stringTypeId = 57 jTDS gold 123 53 2008-06-04 22:07:28.920
SQL:BatchCompleted create proc #jtds000055 as UPDATE DMActivityLogStrng SET criteriaString = 'Importing Journal Completed data set: journal.lineitemlocation' WHERE activityLogId = 14 AND stringTypeId = 57 jTDS gold 0 26 0 0 123 53 2008-06-04 22:07:28.920 2008-06-04 22:07:28.920
RPC:Completed exec #jtds000055 jTDS gold 0 14 1 3 123 53 2008-06-04 22:07:28.920 2008-06-04 22:07:28.920 0X0000000001000000160023006A007400640073003000300030003000350035001400000003000600380469006E00740000000000

SQLUSA
New Member
New Member

--
05 Jun 2008 09:50 AM
There is a SPECIAL template for performance tuning in SQL Server Profiler.

Can you use that template?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
jdunleavvertex
New Member
New Member

--
05 Jun 2008 10:17 AM
I can use any template or format that sql profile supports.
There is no template titled "special" in the sql profiler. There is a template titled "tuning" in the sql profiler. Is this the template you are referring to?
Thank you.
jdunleavvertex
New Member
New Member

--
05 Jun 2008 08:26 PM
I only see the following templates listed in sql server profiler:
blank, sp_counts, standard, tsql, tsql_duration, tsql_grouped, tsql_replay, tsql_sp, tuning.

I utilized the standard template in sql profiler to generate the trace file. When I processed the trace file in DTA, DTA couldn't understand
the trace file format.
SQLUSA
New Member
New Member

--
06 Jun 2008 12:56 AM
You should use the TUNING template.

Let us know how it goes.
jdunleavvertex
New Member
New Member

--
06 Jun 2008 06:32 AM
The Tuning template fails as well. I believe all the templates will fail.
The error is: 99% of consumed workload have syntax errors. Check the tuning log for more information.

The jdbc driver creates sql statements as a temporary stored procedure to speed execution. When DTA goes to analyze the stored procedure, it's not there so it fails. I thinking I'll have to change the application jdbc connection settings to get a trace file to analyze.
SQLUSA
New Member
New Member

--
06 Jun 2008 07:17 AM
Sounds like a bug.

Can you report it to Microsoft?

Here is a link how: http://windowsitpro.com/article/art...osoft.html

Let us know what happens.


Acceptable Use Policy
---