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. 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 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. 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. 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 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'
Do a Google search for "parameter sniffing"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your reply. First of all, sorry for the brilliant typo in my
subject. I only noticed that now. ;-)
I see where you're going with your answer. I know that Sql Server caches the
execution plan of a query and that you might sometimes get a slower response
if it happens to use an execution plan that is not optimal for your
parameters.
However, I wonder if there's not something more going on here. The reason
why I'm saying this is that my table hardly contains any data. The biggest
table involved in my query contains a few dozen records. I mean that
literally: 61 records to be exact. There are a few other tables involved in
the view but they contain even fewer records. Most less than 10. So, even if
the execution plan would be completely wrong and Sql Server would use table
scans for everything, it should still finish instantaneously. Could the
impact really be this bad that it takes +20 minutes to query and join a few
tables that all contain less than 100 rows?
By the way, I have tried using RECOMPILE and the query does seem to run at
normal speed now.
Regards,
Michael
"Tracy McKibben" wrote:
> 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. 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. 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 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'
> Do a Google search for "parameter sniffing"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> Hi Tracy,
> Thanks for your reply. First of all, sorry for the brilliant typo in my
> subject. I only noticed that now. ;-)
> I see where you're going with your answer. I know that Sql Server caches the
> execution plan of a query and that you might sometimes get a slower response
> if it happens to use an execution plan that is not optimal for your
> parameters.
> However, I wonder if there's not something more going on here. The reason
> why I'm saying this is that my table hardly contains any data. The biggest
> table involved in my query contains a few dozen records. I mean that
> literally: 61 records to be exact. There are a few other tables involved in
> the view but they contain even fewer records. Most less than 10. So, even if
> the execution plan would be completely wrong and Sql Server would use table
> scans for everything, it should still finish instantaneously. Could the
> impact really be this bad that it takes +20 minutes to query and join a few
> tables that all contain less than 100 rows?
> By the way, I have tried using RECOMPILE and the query does seem to run at
> normal speed now.
20 minutes does seem excessive for such little data, but it's still not
impossible to believe. Worst case, as you said, is that the execution
plan is so bad that it falls down to doing full table scans on all the
involved tables. That makes you vulnerable to a couple of things:
1. Blocking - you don't say how busy these tables are, so this is
speculation. Assuming that other processes are using these tables, your
table scans could be blocked. Even SELECTs can place locks on a table,
and those locks will interfere with a scan.
2. Resource contention - without knowing your hardware specs, this is
also speculation. If the server is extremely tight on memory, these
tables might have to be read from disk when the scan occurs. If your
drives are I/O bound, these reads will be affected.
There could be something other than parameter sniffing at work here, but
based on the symptoms, I'm going to stick with that as my answer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Today, I wanted to update my code to include the RECOMPILE-option in the
query but the first time I executed the query with the RECOMPILE-option on, I
noticed that it again took forever to run. I have let it run for over two and
a half hours now and it is still busy, so I'm guessing that something else is
going on.
Some more info:
1. The query runs against the Sql Server on my laptop. I have plenty of
memory available when the query starts and my Sql Server is not used at all
except to run this query. No other users are connected.
2. Before the query starts, Sql Server is using about 150MB of RAM (of the
1,5GB that I have on my laptop) and after a couple of minutes, the memory
used by Sql Server increases up to more than 1GB. So, memory usage to run
this particular query is enormous. This is weird because as I've said it
queries tables that almost don't contain any records and it should return a
single record in the end.
3. I have checked in Activity Monitor while the query is running and there
are no blocking or blocked processes.
4. Also, the Sql Server process is constantly using CPU, with an average of
about 25% during the last two hours.
5. I have constant disc activity while the query is running
6. My laptop is simply not usable anymore while the query is running. Sql
Server is using so much memory and disc activity that anything I try to do
takes ages.
Any other insights on what might be causing this problem?
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > Hi Tracy,
> >
> > Thanks for your reply. First of all, sorry for the brilliant typo in my
> > subject. I only noticed that now. ;-)
> >
> > I see where you're going with your answer. I know that Sql Server caches the
> > execution plan of a query and that you might sometimes get a slower response
> > if it happens to use an execution plan that is not optimal for your
> > parameters.
> >
> > However, I wonder if there's not something more going on here. The reason
> > why I'm saying this is that my table hardly contains any data. The biggest
> > table involved in my query contains a few dozen records. I mean that
> > literally: 61 records to be exact. There are a few other tables involved in
> > the view but they contain even fewer records. Most less than 10. So, even if
> > the execution plan would be completely wrong and Sql Server would use table
> > scans for everything, it should still finish instantaneously. Could the
> > impact really be this bad that it takes +20 minutes to query and join a few
> > tables that all contain less than 100 rows?
> >
> > By the way, I have tried using RECOMPILE and the query does seem to run at
> > normal speed now.
> 20 minutes does seem excessive for such little data, but it's still not
> impossible to believe. Worst case, as you said, is that the execution
> plan is so bad that it falls down to doing full table scans on all the
> involved tables. That makes you vulnerable to a couple of things:
> 1. Blocking - you don't say how busy these tables are, so this is
> speculation. Assuming that other processes are using these tables, your
> table scans could be blocked. Even SELECTs can place locks on a table,
> and those locks will interfere with a scan.
> 2. Resource contention - without knowing your hardware specs, this is
> also speculation. If the server is extremely tight on memory, these
> tables might have to be read from disk when the scan occurs. If your
> drives are I/O bound, these reads will be affected.
> There could be something other than parameter sniffing at work here, but
> based on the symptoms, I'm going to stick with that as my answer.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> Today, I wanted to update my code to include the RECOMPILE-option in the
> query but the first time I executed the query with the RECOMPILE-option on, I
> noticed that it again took forever to run. I have let it run for over two and
> a half hours now and it is still busy, so I'm guessing that something else is
> going on.
> Some more info:
> 1. The query runs against the Sql Server on my laptop. I have plenty of
> memory available when the query starts and my Sql Server is not used at all
> except to run this query. No other users are connected.
> 2. Before the query starts, Sql Server is using about 150MB of RAM (of the
> 1,5GB that I have on my laptop) and after a couple of minutes, the memory
> used by Sql Server increases up to more than 1GB. So, memory usage to run
> this particular query is enormous. This is weird because as I've said it
> queries tables that almost don't contain any records and it should return a
> single record in the end.
> 3. I have checked in Activity Monitor while the query is running and there
> are no blocking or blocked processes.
> 4. Also, the Sql Server process is constantly using CPU, with an average of
> about 25% during the last two hours.
> 5. I have constant disc activity while the query is running
> 6. My laptop is simply not usable anymore while the query is running. Sql
> Server is using so much memory and disc activity that anything I try to do
> takes ages.
> Any other insights on what might be causing this problem?
>
You should probably try to get a Profiler trace to see what SQL is doing
while the machine is getting hammered...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I don't believe it... I wanted to run a more detailed profiler trace today to
get some more information. I had already run a default trace earlier, but all
that got me was an RPC Starting-event with the Sql statement that was in my
report. Today, I activated ALL events in profiler and I started the query.
Guess what? I can't reproduce the problem anymore! Nothing has changed as far
as data, structure, queries... I'm guessing that Sql Server automatically
updated some statistics which causes it to use a different execution plan.
This morning, I said to myself to take a backup of the database while the
problem is still there, but apparently I'm too late. We'll see in the coming
days if the problem comes back. Thanks for your help so far!
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > Today, I wanted to update my code to include the RECOMPILE-option in the
> > query but the first time I executed the query with the RECOMPILE-option on, I
> > noticed that it again took forever to run. I have let it run for over two and
> > a half hours now and it is still busy, so I'm guessing that something else is
> > going on.
> >
> > Some more info:
> > 1. The query runs against the Sql Server on my laptop. I have plenty of
> > memory available when the query starts and my Sql Server is not used at all
> > except to run this query. No other users are connected.
> > 2. Before the query starts, Sql Server is using about 150MB of RAM (of the
> > 1,5GB that I have on my laptop) and after a couple of minutes, the memory
> > used by Sql Server increases up to more than 1GB. So, memory usage to run
> > this particular query is enormous. This is weird because as I've said it
> > queries tables that almost don't contain any records and it should return a
> > single record in the end.
> > 3. I have checked in Activity Monitor while the query is running and there
> > are no blocking or blocked processes.
> > 4. Also, the Sql Server process is constantly using CPU, with an average of
> > about 25% during the last two hours.
> > 5. I have constant disc activity while the query is running
> > 6. My laptop is simply not usable anymore while the query is running. Sql
> > Server is using so much memory and disc activity that anything I try to do
> > takes ages.
> >
> > Any other insights on what might be causing this problem?
> >
> You should probably try to get a Profiler trace to see what SQL is doing
> while the machine is getting hammered...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> I don't believe it... I wanted to run a more detailed profiler trace today to
> get some more information. I had already run a default trace earlier, but all
> that got me was an RPC Starting-event with the Sql statement that was in my
> report. Today, I activated ALL events in profiler and I started the query.
> Guess what? I can't reproduce the problem anymore! Nothing has changed as far
> as data, structure, queries... I'm guessing that Sql Server automatically
> updated some statistics which causes it to use a different execution plan.
> This morning, I said to myself to take a backup of the database while the
> problem is still there, but apparently I'm too late. We'll see in the coming
> days if the problem comes back. Thanks for your help so far!
>
If you have auto-stats enabled, or run a scheduled stats update, that
very well could be what happened. Or, it could have been an inefficient
query plan that was cached, and has since dropped out of the cache.
Tracy McKibben
MCDBA
http://www.realsqlguy.com