Performance anomalies with sp_executesql

Hi all,
I am facing a performance problem with a piece of dynamic
SQL (on SQL Server 2000 SP4), and it really has me stumped.
The execution times for the same query executed using
sp_executesql and directly from Query Analyzer differ by
many orders of magnitude, and I have so far been unable to
find out why.
The statement in question looks as follows:
sp_executesql N'SET ROWCOUNT 9; SELECT [SID] FROM [S] WHERE EXISTS
(SELECT * FROM [A] INNER JOIN [Q] ON [A].[AID] = [Q].[AID] INNER JOIN
[P] ON [Q].[QID] = [P].[QID] WHERE ([A].[X] <= 0 OR @.P1 = ''false'') AND
([A].[SID] = [S].[SID]))', N'@.P1 nvarchar(4000)', N'true'
This is actually a heavily reduced version of a real life
statement, but the necessary parts to display the problem
have been preserved - I'm aware that the query can be
optimized. The use of sp_executesql is mandated by the
application server framework.
Executing the statement above makes the database enter what
looks like a livelock state with the CPU fully loaded. So
far I have killed the process when it has executed for more
than an hour on a reasonably powerful machine (3 GHz, 4
GB ram).
If I execute the query from Query Analyzer (without
sp_executesql) with the value of @.P1 substituted in it
returns immediately (after less than one second), which I
would guess eliminates obvious problems like missing
indexes. The tables in use are reasonably small (a million
records in P, in the order of 1000 records in the other
tables), so in any case an execution time of an hour is way
over the top.
If I execute the statement on a structurally identical
database with fewer records in the tables it completes
immediately. There are no apparent consistency problems in
the first database; DBCC CHECKDB comes up with nothing.
If I reduce the row count to 8, remove the joins on the P
and Q tables or eliminate some of the constraints in the
WHERE clause the statement completes immediately. Replacing
the value of @.P1 with 'false' makes the query complete in
about a minute.
I realize that coming up with constructive input is
extremely difficult without access to the actual database in
question, but I'm open to any suggestions that you might
have about how I could look further into this. Any hints
about how I might eliminate the livelock would be very much
appreciated.
Best regards,
Mikkel Lauritsen
*** Sent via Developersdex http://www.examnotes.net ***Mike
Have you seen that an optimizer is able to use indexes defined on the
table/s?
"Mikkel Lauritsen" <renard@.nospam.dk> wrote in message
news:ucDEqJ1lFHA.1044@.tk2msftngp13.phx.gbl...
> Hi all,
> I am facing a performance problem with a piece of dynamic
> SQL (on SQL Server 2000 SP4), and it really has me stumped.
> The execution times for the same query executed using
> sp_executesql and directly from Query Analyzer differ by
> many orders of magnitude, and I have so far been unable to
> find out why.
> The statement in question looks as follows:
> sp_executesql N'SET ROWCOUNT 9; SELECT [SID] FROM [S] WHERE EXISTS
> (SELECT * FROM [A] INNER JOIN [Q] ON [A].[AID] = [Q].[AID] INNER JOIN
> [P] ON [Q].[QID] = [P].[QID] WHERE ([A].[X] <= 0 OR @.P1 = ''false'') AND
> ([A].[SID] = [S].[SID]))', N'@.P1 nvarchar(4000)', N'true'
> This is actually a heavily reduced version of a real life
> statement, but the necessary parts to display the problem
> have been preserved - I'm aware that the query can be
> optimized. The use of sp_executesql is mandated by the
> application server framework.
> Executing the statement above makes the database enter what
> looks like a livelock state with the CPU fully loaded. So
> far I have killed the process when it has executed for more
> than an hour on a reasonably powerful machine (3 GHz, 4
> GB ram).
> If I execute the query from Query Analyzer (without
> sp_executesql) with the value of @.P1 substituted in it
> returns immediately (after less than one second), which I
> would guess eliminates obvious problems like missing
> indexes. The tables in use are reasonably small (a million
> records in P, in the order of 1000 records in the other
> tables), so in any case an execution time of an hour is way
> over the top.
> If I execute the statement on a structurally identical
> database with fewer records in the tables it completes
> immediately. There are no apparent consistency problems in
> the first database; DBCC CHECKDB comes up with nothing.
> If I reduce the row count to 8, remove the joins on the P
> and Q tables or eliminate some of the constraints in the
> WHERE clause the statement completes immediately. Replacing
> the value of @.P1 with 'false' makes the query complete in
> about a minute.
> I realize that coming up with constructive input is
> extremely difficult without access to the actual database in
> question, but I'm open to any suggestions that you might
> have about how I could look further into this. Any hints
> about how I might eliminate the livelock would be very much
> appreciated.
> Best regards,
> Mikkel Lauritsen
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Mikkel,
A couple of sugestions:
- Qualify the tables with the owner
dbo.[A], dbo.[Q], dbo.[S]
- Why to declare @.P1 as nvarchar(4000), is not it enough nvarchar(5)?
- If @.P1 is nvarchar then use an nvarchar constant in the comparison:

> @.P1 = ''false''
@.P1 = N''false''
AMB
"Mikkel Lauritsen" wrote:

> Hi all,
> I am facing a performance problem with a piece of dynamic
> SQL (on SQL Server 2000 SP4), and it really has me stumped.
> The execution times for the same query executed using
> sp_executesql and directly from Query Analyzer differ by
> many orders of magnitude, and I have so far been unable to
> find out why.
> The statement in question looks as follows:
> sp_executesql N'SET ROWCOUNT 9; SELECT [SID] FROM [S] WHERE EXISTS
> (SELECT * FROM [A] INNER JOIN [Q] ON [A].[AID] = [Q].[AID] INNER JOIN
> [P] ON [Q].[QID] = [P].[QID] WHERE ([A].[X] <= 0 OR @.P1 = ''false'') AND
> ([A].[SID] = [S].[SID]))', N'@.P1 nvarchar(4000)', N'true'
> This is actually a heavily reduced version of a real life
> statement, but the necessary parts to display the problem
> have been preserved - I'm aware that the query can be
> optimized. The use of sp_executesql is mandated by the
> application server framework.
> Executing the statement above makes the database enter what
> looks like a livelock state with the CPU fully loaded. So
> far I have killed the process when it has executed for more
> than an hour on a reasonably powerful machine (3 GHz, 4
> GB ram).
> If I execute the query from Query Analyzer (without
> sp_executesql) with the value of @.P1 substituted in it
> returns immediately (after less than one second), which I
> would guess eliminates obvious problems like missing
> indexes. The tables in use are reasonably small (a million
> records in P, in the order of 1000 records in the other
> tables), so in any case an execution time of an hour is way
> over the top.
> If I execute the statement on a structurally identical
> database with fewer records in the tables it completes
> immediately. There are no apparent consistency problems in
> the first database; DBCC CHECKDB comes up with nothing.
> If I reduce the row count to 8, remove the joins on the P
> and Q tables or eliminate some of the constraints in the
> WHERE clause the statement completes immediately. Replacing
> the value of @.P1 with 'false' makes the query complete in
> about a minute.
> I realize that coming up with constructive input is
> extremely difficult without access to the actual database in
> question, but I'm open to any suggestions that you might
> have about how I could look further into this. Any hints
> about how I might eliminate the livelock would be very much
> appreciated.
> Best regards,
> Mikkel Lauritsen
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi Alejandro,
thanks a lot for the suggestions - I really appreciate your
prompt feedback on this.
I have tried making the suggested changes, but unfortunately
they have no effect on the livelock that I'm seeing.
The size of the parameter is beyond my immediate control,
because the entire statement is generated by the application
server framework - I have only defined a prepared statement,
and the SQL is then generated for me at runtime.
And I have to admit that I missed out on the constant not
being nvarchar (good catch!), but as mentioned above
changing that doesn't make any difference.
So far I have to say that I'm leaning more and more towards
thinking that this is caused by a bug in the execution
planner, or perhaps an inconsistency in one of the tables
which isn't detected by DBCC CHECKDB.
Best regards,
Mikkel
*** Sent via Developersdex http://www.examnotes.net ***|||Mikkel -
We are running into the same issue did you ever find a solution or workaroun
d?
Thanks
Mike