I have a very strange problem with my SQL Server database that I cannot
explain. Suppose I have the following stored procedure:
CREATE PROCEDURE sp_TMS_PRJ_Test
@.TestplanVersionId uniqueidentifier = NULL
AS
SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT [TMS_PRJ_TestplanTestcases].[Id],[TMS_PRJ_TestResults].[Id]
FROM [TMS_PRJ_TestplanTestcases]
INNER JOIN [TMS_TCH_SpecificationTestcases] ON
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[Id]
INNER JOIN [TMS_TCH_SpecificationConditionSets] ON
[TMS_PRJ_TestplanTestcases].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[Id]
INNER JOIN [TMS_PRJ_TestplanVersions] ON
[TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
[TMS_PRJ_TestplanVersions].[Id]
INNER JOIN [TMS_PRJ_Testplans] ON
[TMS_PRJ_TestplanVersions].[Testplan_Ref] = [TMS_PRJ_Testplans].[Id]
INNER JOIN [TMS_PRJ_TestResults] ON [TMS_PRJ_TestResults].[Project_Ref]
= [TMS_PRJ_Testplans].[Project_Ref]
INNER JOIN [TMS_SWP_TestcaseParameters] ON
[TMS_PRJ_TestResults].[Parameter_Ref] = [TMS_SWP_TestcaseParameters].[Id]
INNER JOIN [TMS_SWP_SoftwarePackageTestcases] ON
[TMS_SWP_TestcaseParameters].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
INNER JOIN [TMS_SWP_UniformTestcases] ON
[TMS_SWP_UniformTestcases].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
WHERE [TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
@.TestplanVersionId
AND [TMS_SWP_UniformTestcases].[UniformTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[UniformTestcase_Ref]
AND [TMS_PRJ_Testplans].[Active] <> 0
AND [TMS_PRJ_TestResults].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[ConditionSet_Ref]
When I run this stored procedure then it takes over 4 minutes to complete
and takes almost 62 million read
operations to return 1295 rows. Suppose I rewrite this procedure like this:
CREATE PROCEDURE sp_TMS_PRJ_Test
AS
DECLARE @.TestplanVersionId AS uniqueidentifier
SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT [TMS_PRJ_TestplanTestcases].[Id],[TMS_PRJ_TestResults].[Id]
FROM [TMS_PRJ_TestplanTestcases]
INNER JOIN [TMS_TCH_SpecificationTestcases] ON
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[Id]
INNER JOIN [TMS_TCH_SpecificationConditionSets] ON
[TMS_PRJ_TestplanTestcases].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[Id]
INNER JOIN [TMS_PRJ_TestplanVersions] ON
[TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
[TMS_PRJ_TestplanVersions].[Id]
INNER JOIN [TMS_PRJ_Testplans] ON
[TMS_PRJ_TestplanVersions].[Testplan_Ref] = [TMS_PRJ_Testplans].[Id]
INNER JOIN [TMS_PRJ_TestResults] ON [TMS_PRJ_TestResults].[Project_Ref]
= [TMS_PRJ_Testplans].[Project_Ref]
INNER JOIN [TMS_SWP_TestcaseParameters] ON
[TMS_PRJ_TestResults].[Parameter_Ref] = [TMS_SWP_TestcaseParameters].[Id]
INNER JOIN [TMS_SWP_SoftwarePackageTestcases] ON
[TMS_SWP_TestcaseParameters].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
INNER JOIN [TMS_SWP_UniformTestcases] ON
[TMS_SWP_UniformTestcases].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
WHERE [TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
@.TestplanVersionId
AND [TMS_SWP_UniformTestcases].[UniformTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[UniformTestcase_Ref]
AND [TMS_PRJ_Testplans].[Active] <> 0
AND [TMS_PRJ_TestResults].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[ConditionSet_Ref]
In the second example the entire operation is finished in 1.3 seconds, so
this is much faster and takes only
5500 reads (and of course also returns 1295 rows). The execution plans
differ for both queries. As you can
see from the execution trees below, the fast version uses hash matches and
the slow version uses nested
loops. Can anyone explain the difference between the two queries?
PS: If I remove the '[TMS_PRJ_Testplans].[Active] <> 0' clause from the slow
query then it is fast again.
This is weird, because it only needs to check one record in this table.
Execution Tree (slow)
--
Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref],
[TMS_SWP_UniformTestcases].[UniformTestcase_Ref]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_SWP_TestcaseParameters]
.[SoftwarePackageTestcase_Ref]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Parameter_Ref]))
| | |--Nested Loops(Inner Join,
WHERE:([TMS_PRJ_Testplans].[Id]=[TMS_PRJ_TestplanVersions].[Testplan_Ref]))
| | | |--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanVersions].[PK_TMS_PRJ_TestplanVersions]),
SEEK:([TMS_PRJ_TestplanVersions].[Id]=[@.TestplanVersionId]) ORDERED FORWARD)
| | | |-- Filter(WHERE:(Convert([TMS_PRJ_Testplans
].[Active])<>0))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1008]),
OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Project_Ref]))
| | | |--Hash Match(Inner Join,
HASH:([TMS_TCH_SpecificationConditionSet
s].[ConditionSet_Ref])=([TMS_PRJ_Tes
tResults].[ConditionSet_Ref]),
RESIDUAL:([TMS_PRJ_TestResults]. [ConditionSet_Ref]=[TMS_TCH_Specificatio
nCon
ditionSets].[ConditionSet_Ref]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanTestcases].[ConditionSet_Ref]))
| | | | | |--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanTestcases].[IX_TMS_PRJ_TestplanTestcases_Tes
tplanVersionSpecificationTestcaseConditi
onSet]),
SEEK:([TMS_PRJ_TestplanTestcases]. [TestplanVersion_Ref]=[@.TestplanVersionI
d]
)
ORDERED FORWARD)
| | | | | |--Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationConditionSets].[IX_TMS_TCH_Specificatio
nConditionSets_ID]),
SEEK:([TMS_TCH_SpecificationConditionSet
s].[Id]=[TMS_PRJ_TestplanTestcases].[Con
ditionSet_Ref]) ORDERED FORWARD)
| | | | |--Clustered Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestResults].[PK_TMS_PRJ_TestResults]))
| | | |--Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans]. [IX_TMS_PRJ_Testplans_MilestoneType_Name
]
),
SEEK:([TMS_PRJ_Testplans].[Project_Ref]=[TMS_PRJ_TestResults].[Project_Ref])
ORDERED FORWARD)
| | |--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_TestcaseParameters].[PK_TMS_SWP_TestcaseParameters])
,
SEEK:([TMS_SWP_TestcaseParameters].[Id]=[TMS_PRJ_TestResults].[Parameter_Ref]) O
RDERED FORWARD)
| |--Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_UniformTestcases].[IX_TMS_SWP_UniformTestcases_Softw
arePackageTestcaseUniformTestcase]),
SEEK:([TMS_SWP_UniformTestcases]. [SoftwarePackageTestcase_Ref]=[TMS_SWP_T
est
caseParameters].[SoftwarePackageTestcase_Ref]) ORDERED FORWARD)
|--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationTestcases].[PK_TMS_TCH_SpecificationTes
tcases]),
SEEK:([TMS_TCH_SpecificationTestcases].[Id]=[TMS_PRJ_TestplanTestcases].[Specifi
cationTestcase_Ref]),
WHERE:([TMS_SWP_UniformTestcases]. [UniformTestcase_Ref]=[TMS_TCH_Specifica
ti
onTestcases].[UniformTestcase_Ref]) ORDERED FORWARD)
Execution Tree (fast)
--
Hash Match(Inner Join, HASH:([TMS_TCH_SpecificationConditionSet
s].[Id],
[TMS_TCH_SpecificationTestcases].[Id])=([TMS_PRJ_TestplanTestcases].[ConditionSe
t_Ref],
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref]),
RESIDUAL:([TMS_TCH_SpecificationConditio
nSets].[Id]=[TMS_PRJ_TestplanTestcases].
[ConditionSet_Ref]
AND
[TMS_TCH_SpecificationTestcases].[Id]=[TMS_PRJ_TestplanTestcases].[Specification
Testcase_Ref]))
|--Hash Match(Inner Join,
HASH:([TMS_SWP_UniformTestcases]. [UniformTestcase_Ref])=([TMS_TCH_Specifi
cat
ionTestcases].[UniformTestcase_Ref]),
RESIDUAL:([TMS_SWP_UniformTestcases].[UniformTestcase_Ref]=[TMS_TCH_Specific
ationTestcases].[UniformTestcase_Ref]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_SWP_TestcaseParameters]
.[SoftwarePackageTestcase_Ref]))
| | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Parameter_Ref]))
| | | |--Hash Match(Inner Join,
HASH:([TMS_TCH_SpecificationConditionSet
s].[ConditionSet_Ref])=([TMS_PRJ_Tes
tResults].[ConditionSet_Ref]),
RESIDUAL:([TMS_PRJ_TestResults]. [ConditionSet_Ref]=[TMS_TCH_Specificatio
nCon
ditionSets].[ConditionSet_Ref]))
| | | | |--Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationConditionSets].[IX_TMS_TCH_Specificatio
nConditionSets_ID]))
| | | | |--Nested Loops(Inner Join,
WHERE:([TMS_PRJ_TestResults].[Project_Ref]=[TMS_PRJ_Testplans].[Project_Ref]
))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanVersions].[Testplan_Ref]))
| | | | | |--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanVersions].[PK_TMS_PRJ_TestplanVersions]),
SEEK:([TMS_PRJ_TestplanVersions].[Id]=[@.TestplanVersionId]) ORDERED FORWARD)
| | | | | |--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans].[PK_TMS_PRJ_Testplans]),
SEEK:([TMS_PRJ_Testplans].[Id]=[TMS_PRJ_TestplanVersions].[Testplan_Ref]),
WHERE:(Convert([TMS_PRJ_Testplans].[Active])<>0) ORDERED FORWARD)
| | | | |--Clustered Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestResults].[PK_TMS_PRJ_TestResults]))
| | | |--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_TestcaseParameters].[PK_TMS_SWP_TestcaseParameters])
,
SEEK:([TMS_SWP_TestcaseParameters].[Id]=[TMS_PRJ_TestResults].[Parameter_Ref]) O
RDERED FORWARD)
| | |--Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_UniformTestcases].[IX_TMS_SWP_UniformTestcases_Softw
arePackageTestcaseUniformTestcase]),
SEEK:([TMS_SWP_UniformTestcases]. [SoftwarePackageTestcase_Ref]=[TMS_SWP_T
est
caseParameters].[SoftwarePackageTestcase_Ref]) ORDERED FORWARD)
| |--Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationTestcases].[IX_TMS_TCH_SpecificationTes
tcases_SpecificationVersionUniformTestca
se]))
|--Clustered Index
S
(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanTestcases].[IX_TMS_PRJ_TestplanTestcases_Tes
tplanVersionSpecificationTestcaseConditi
onSet]),
SEEK:([TMS_PRJ_TestplanTestcases]. [TestplanVersion_Ref]=[@.TestplanVersionI
d]
)
ORDERED FORWARD)Google for "parameter sniffing".
Also consider using indexed views when low-selectivity status columns exist
in the tables. If [TMS_PRJ_Testplans].[Active] is such a column, then rather
than indexing the column, consider using an indexed view for each status
value.
Although in your case a simple "[TMS_PRJ_Testplans].[Active] = 1" (if value
1 is appropriate) might help.
ML
http://milambda.blogspot.com/|||Ramon de Klein (RamondeKlein@.discussions.microsoft.com) writes:
> I have a very strange problem with my SQL Server database that I cannot
> explain. Suppose I have the following stored procedure:
> CREATE PROCEDURE sp_TMS_PRJ_Test
> @.TestplanVersionId uniqueidentifier = NULL
> AS
> SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
>...
> CREATE PROCEDURE sp_TMS_PRJ_Test
> AS
> DECLARE @.TestplanVersionId AS uniqueidentifier
> SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
>...
> In the second example the entire operation is finished in 1.3 seconds, so
> this is much faster and takes only
> 5500 reads (and of course also returns 1295 rows). The execution plans
> differ for both queries. As you can
> see from the execution trees below, the fast version uses hash matches and
> the slow version uses nested
> loops. Can anyone explain the difference between the two queries?
This is something that baffles about anyone at some point in his SQL
Server career, but once you know how SQL Server builds query plans, it is
less mysterious.
Say that would hard-code the GUID into the query. In this case, SQL
Server knows exactly what you are after, and can look at the statistics
to be able to make an estimate of how many rows it will hit.
On the other hand, when you use a variable, SQL Server does not know
which value the variable will have at run-time, so it makes a standard
assumption.
For procedure parameters there is yet a choice. When you call the
procedure the first time, SQL Server uses the actual parameter value
and builds the plan according to that. Tbis means that if the first
call is for an atypical value, or, as in this, you assign a default
value, you're taking SQL Server to the cleaners.
For procedures where you need to have a parameter with a NULL default
value which in reality means something else, for instance "today" for
a date parameter, you should always copy the parameter into a local
variable, so that the optimizer does not act on incorrect information.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I rewrite the stored procedure, so it doesn't the parameter at all then
it is still slow. In this case I hardcoded the GUID
'E340196B-D701-4814-8E75-04ABFD2777A5' into the query, but it is still slow.
When I use an intermediate variable to push the GUID into the SELECT query
then it is fast again, so this query is fast (1 second)
SELECT @.Parameter = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT X,Y
FROM ...
INNER JOIN ...
WHERE Clause = @.Parameter
And this one is slow (over 4 minutes):
SELECT X,Y
FROM ...
INNER JOIN ...
WHERE Clause = 'E340196B-D701-4814-8E75-04ABFD2777A5
It doesn't have anything to do with stored procedures, because when I run it
manually in SQL Query Analyser then the results are the same. I think I have
hit a bug in SQL Server 2000.
--
Greetings,
Ramon de Klein|||Ramon de Klein (RamondeKlein@.discussions.microsoft.com) writes:
> When I rewrite the stored procedure, so it doesn't the parameter at all
> then it is still slow. In this case I hardcoded the GUID
> 'E340196B-D701-4814-8E75-04ABFD2777A5' into the query, but it is still
> slow. When I use an intermediate variable to push the GUID into the
> SELECT query then it is fast again, so this query is fast (1 second)
> SELECT @.Parameter = 'E340196B-D701-4814-8E75-04ABFD2777A5'
> SELECT X,Y
> FROM ...
> INNER JOIN ...
> WHERE Clause = @.Parameter
> And this one is slow (over 4 minutes):
> SELECT X,Y
> FROM ...
> INNER JOIN ...
> WHERE Clause = 'E340196B-D701-4814-8E75-04ABFD2777A5
> It doesn't have anything to do with stored procedures, because when I
> run it manually in SQL Query Analyser then the results are the same. I
> think I have hit a bug in SQL Server 2000.
Maybe. Or maybe not.
The likely problem here is that the statistics indicate that the
query will hit more rows for this GUID that it actually does. First
try UPDATE STATISTICS WITH FULLSCAN for this table. If you still get
the same result, post the output from DBCC SHOW_STATISTICS for this
index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
