Has anyone tried the SQL Server 2005 Tuning Advisor with Profiler output from LLBLGen?

It's reporting that all the SQL contains Syntax Errors for me... I've tried it on several machines and was wondering if anyone can confirm this?

Marcus

Could you elaborate what kind of syntax errors? Because the SQL isnt different from sqlserver 2000's....

Otis wrote:

Could you elaborate what kind of syntax errors? Because the SQL isnt different from sqlserver 2000's....

I was just asking if anyone has successfully managed to run the tuning advisor in 2005 agaisnt an output from LLBLGen... It's probably a problem with SQL Server 2005 rather than LLBLGen.

Here's what happens:

Basically if I run a trace using the profiler (which I've done successfully many times with SQL Server 2000 ) and save it to a file, I should be able to run the Tuning Wizard (now called the tuning advisor) against this file and it will highlight missing indexes etc.

With SQL Server 2005, the Database Tuning Advisor reports during the "Consuming Workload" phase of the Analysis: "85% of the consumed workload has syntax errors"

Here is a selection of the errors from the log which include some security and other issues:

S001    declare @FolderID bigint,@ItemID bigint,@IsActive bit
select @FolderID=107,@ItemID=1455,@IsActive=1
INSERT INTO [ModernArk].[dbo].[Ark_FolderItemLink] ([FolderID], [ItemID], [IsActive]) VALUES (@FolderID, @ItemID, @IsActive)    declare @FolderID bigint,@ItemID bigint,@IsActive bit
select @FolderID=107,@ItemID=1455,@IsActive=1   1   Statement does not reference any tables

S008    exec sp_executesql N'INSERT INTO [ModernArk].[dbo].[Ark_FolderItemLink] ([FolderID], [ItemID], [IsActive]) VALUES (@FolderID, @ItemID, @IsActive)',N'@FolderID bigint,@ItemID bigint,@IsActive bit',@FolderID=107,@ItemID=1460,@IsActive=1      1   Event does not reference any tables

S008    BEGIN TRANSACTION 54    Event does not reference any tables

E000    IF @@rowcount = 0 54    [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '0'. 

Basically it seems ALL the "exec sp_executesql N'...'" is ignored because it thinks they dont reference any tables...

I just wanted to confirm if this is just me or does everyone have this same problem?

Marcus

I'm currently looking to solve the same problem, it seems like the SQL Server 2005 Database Engine Tuning Advisor is not able to correctly analyze the trace generated by the execution of LLBL queries. Has anyone been successfully using DTA on queries executed by LLBL code?

Thanks, Davide

Have you installed SQL Server 2005 SP2? Which version of SQL server are you using?

select @@version

I already applied SP2 on SQL Server 2005. Here's the version.

Microsoft SQL Server 2005 - 9.00.3050.00 (X64)

What does " it seems like the SQL Server 2005 Database Engine Tuning Advisor is not able to correctly analyze the trace generated by the execution of LLBL queries." really mean? What's 'not correctly' exactly? As the queries are simply T-SQL queries, nothing fancy.

Hi,

I had the same "error messages" when the Tuning advisor processed the trace output of the Profiler, but it seems to me that they're just warnings, nothing really serious, because the advisor finished normally....but I could not analyze the impact/problem for 100%.

Try it for yourself....then you'll see the warnings !

Kind regards, Danny

Otis wrote:

What does " it seems like the SQL Server 2005 Database Engine Tuning Advisor is not able to correctly analyze the trace generated by the execution of LLBL queries." really mean? What's 'not correctly' exactly? As the queries are simply T-SQL queries, nothing fancy.

I'm experiencing exactly the same behaviour Marcus described. DTA marks nearly over 80% of the queries traced as "syntax errors".

I'm just asking if anyone else had this problem and how to work around this behaviour of DTA when using LLBL, as I didn't find a response to the problem originally reported by Marcus.

DavideB wrote:

Otis wrote:

What does " it seems like the SQL Server 2005 Database Engine Tuning Advisor is not able to correctly analyze the trace generated by the execution of LLBL queries." really mean? What's 'not correctly' exactly? As the queries are simply T-SQL queries, nothing fancy.

I'm experiencing exactly the same behaviour Marcus described. DTA marks nearly over 80% of the queries traced as "syntax errors".

I'm just asking if anyone else had this problem and how to work around this behaviour of DTA when using LLBL, as I didn't find a response to the problem originally reported by Marcus.

Ok, no problem, I wasn't questioning your question, I was more interested in what kind of errors you got .

What I find a little funny is that the queries are syntax errors though. All our queries are IMHO formulated correctly and use full names for fields/tables etc. But perhaps the analyser of them isn't that smart, I have no idea. The exec sp_executesql calls are from ADO.NET's SqlClient. The SqlClient sends all queries as exec sp_executeSql ... to the database. This is done to re-use execution plans more easier.

It might be the exec sp_executesql is confusing the analyser.