Query doesn't hangs when run using parameters

I have a weird problem in Sql Server 2005 that I need some help on. Below
you'll find two queries. Both queries are exactly the same; they query the
same database, query for the same info, same sql statement, same where
clause... The first query (the one with all the parameters included in the
sql statement) runs perfectly against my database in less than a second. The
second query is as I've said exactly the same, the only difference is that
the parameters are now passed using the SqlCommand.Parameters-collection. Th
e
problem with this query is that it seems to cause Sql Server to hang. It
seems to execute my query (CPU varies between 70% and 100%) but it never
finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
this behavior? Is there something that I can do about it? For what it's
worth, my database hardly contains any data. Since it never seems to finish,
I'm not able to generate a execute plan for it to see what the problem is.
The good query:
SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
a.[ModifiedBy],
a.[Tag], a.[FileCount]
FROM tblRECORDS a
WHERE a.[ID] IN (
SELECT a.[ID]
FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
ON a.[Id] = f.[RecordId]
WHERE
(
(
(
(
b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
b.[Keywords] LIKE 'Dit %' ESCAPE '~'
)
AND
(
c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
c.[Keywords] LIKE 'de %' ESCAPE '~'
)
AND
(
d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
)
AND
(
e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
e.[Keywords] LIKE 'eerste %' ESCAPE '~'
)
AND
(
f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
f.[Keywords] LIKE 'optie %' ESCAPE '~'
)
)
OR
(
(
b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
b.[Keywords] LIKE 'Dit %' ESCAPE '~'
)
AND
(
c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
c.[Keywords] LIKE 'de %' ESCAPE '~'
)
AND
(
d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
)
AND
(
e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
e.[Keywords] LIKE 'eerste %' ESCAPE '~'
)
AND
(
f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
f.[Keywords] LIKE 'optie %' ESCAPE '~'
)
)
) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
)
)
The bad query:
exec sp_executesql N'
SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
a.[ModifiedBy], a.[Tag], a.[FileCount]
FROM tblRECORDS a
WHERE a.[ID] IN
(
SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
ON a.[Id] = f.[RecordId]
WHERE
(
(
(
(
b.[LanguageId] = @.__0
AND
b.[Keywords] LIKE @.__1 ESCAPE ''~''
)
AND
(
c.[LanguageId] = @.__2
AND
c.[Keywords] LIKE @.__3 ESCAPE ''~''
)
AND
(
d.[LanguageId] = @.__4
AND
d.[Keywords] LIKE @.__5 ESCAPE ''~''
)
AND
(
e.[LanguageId] = @.__6
AND
e.[Keywords] LIKE @.__7 ESCAPE ''~''
)
AND
(
f.[LanguageId] = @.__8
AND
f.[Keywords] LIKE @.__9 ESCAPE ''~''
)
)
OR
(
(
b.[LanguageId] = @.__10
AND
b.[Keywords] LIKE @.__11 ESCAPE ''~''
)
AND
(
c.[LanguageId] = @.__12
AND
c.[Keywords] LIKE @.__13 ESCAPE ''~''
)
AND
(
d.[LanguageId] = @.__14
AND
d.[Keywords] LIKE @.__15 ESCAPE ''~''
)
AND
(
e.[LanguageId] = @.__16
AND
e.[Keywords] LIKE @.__17 ESCAPE ''~''
)
AND
(
f.[LanguageId] = @.__18
AND
f.[Keywords] LIKE @.__19 ESCAPE ''~''
)
)
)
AND
a.[ID] = @.__20
)
)',
N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
nvarchar(4),
@.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
nvarchar(8),
@.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
nvarchar(5),
@.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
nvarchar(13),
@.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
nvarchar(7),
@.__20
uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
@.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
@.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
@.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
@.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
@.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
@.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
@.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
@.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
@.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
@.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'Michael Vanhoutte wrote:
> I have a weird problem in Sql Server 2005 that I need some help on. Below
> you'll find two queries. Both queries are exactly the same; they query the
> same database, query for the same info, same sql statement, same where
> clause... The first query (the one with all the parameters included in the
> sql statement) runs perfectly against my database in less than a second. T
he
> second query is as I've said exactly the same, the only difference is that
> the parameters are now passed using the SqlCommand.Parameters-collection.
The
> problem with this query is that it seems to cause Sql Server to hang. It
> seems to execute my query (CPU varies between 70% and 100%) but it never
> finishes. Last time I cancelled it after +20 minutes. Does anybody why I s
ee
> this behavior? Is there something that I can do about it? For what it's
> worth, my database hardly contains any data. Since it never seems to finis
h,
> I'm not able to generate a execute plan for it to see what the problem is.
> The good query:
> SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn
], a.[ModifiedBy],
> a.[Tag], a.[FileCount]
> FROM tblRECORDS a
> WHERE a.[ID] IN (
> SELECT a.[ID]
> FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> ON a.[Id] = f.[RecordId]
> WHERE
> (
> (
> (
> (
> b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> )
> AND
> (
> c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> c.[Keywords] LIKE 'de %' ESCAPE '~'
> )
> AND
> (
> d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> )
> AND
> (
> e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> )
> AND
> (
> f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> f.[Keywords] LIKE 'optie %' ESCAPE '~'
> )
> )
> OR
> (
> (
> b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> )
> AND
> (
> c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> c.[Keywords] LIKE 'de %' ESCAPE '~'
> )
> AND
> (
> d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> )
> AND
> (
> e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> )
> AND
> (
> f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> f.[Keywords] LIKE 'optie %' ESCAPE '~'
> )
> )
> ) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> )
> )
> The bad query:
> exec sp_executesql N'
> SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedO
n],
> a.[ModifiedBy], a.[Tag], a.[FileCount]
> FROM tblRECORDS a
> WHERE a.[ID] IN
> (
> SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> ON a.[Id] = f.[RecordId]
> WHERE
> (
> (
> (
> (
> b.[LanguageId] = @.__0
> AND
> b.[Keywords] LIKE @.__1 ESCAPE ''~''
> )
> AND
> (
> c.[LanguageId] = @.__2
> AND
> c.[Keywords] LIKE @.__3 ESCAPE ''~''
> )
> AND
> (
> d.[LanguageId] = @.__4
> AND
> d.[Keywords] LIKE @.__5 ESCAPE ''~''
> )
> AND
> (
> e.[LanguageId] = @.__6
> AND
> e.[Keywords] LIKE @.__7 ESCAPE ''~''
> )
> AND
> (
> f.[LanguageId] = @.__8
> AND
> f.[Keywords] LIKE @.__9 ESCAPE ''~''
> )
> )
> OR
> (
> (
> b.[LanguageId] = @.__10
> AND
> b.[Keywords] LIKE @.__11 ESCAPE ''~''
> )
> AND
> (
> c.[LanguageId] = @.__12
> AND
> c.[Keywords] LIKE @.__13 ESCAPE ''~''
> )
> AND
> (
> d.[LanguageId] = @.__14
> AND
> d.[Keywords] LIKE @.__15 ESCAPE ''~''
> )
> AND
> (
> e.[LanguageId] = @.__16
> AND
> e.[Keywords] LIKE @.__17 ESCAPE ''~''
> )
> AND
> (
> f.[LanguageId] = @.__18
> AND
> f.[Keywords] LIKE @.__19 ESCAPE ''~''
> )
> )
> )
> AND
> a.[ID] = @.__20
> )
> )',
> N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
> nvarchar(4),
> @.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
> nvarchar(8),
> @.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
> nvarchar(5),
> @.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
> nvarchar(13),
> @.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
> nvarchar(7),
> @.__20
> uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %
',
> @.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
> @.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
> @.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
> @.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
> @.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
> @.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
> @.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
> @.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
> @.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
> @.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'
Do a Google search for "parameter sniffing"
Tracy McKibben
MCDBA
http://www.realsqlguy.com