examining server actions while running a query

Hello,
I'm having some trouble explaining the performance difference I'm seeing in
a couple of queries. I'm running against an XP Pro desktop installation of
Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86) Mar 2 2007 20:01:28
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
Here is the SQL I'm running:
declare @.cmpl_invocation_id numeric(10)
set @.cmpl_invocation_id = 261
declare @.cmpl_profile_rule_id numeric(10)
set @.cmpl_profile_rule_id = 9992185
declare @.d datetime
select @.d = getdate()
if not exists
(
select 1 from cmpl_res_rule_result
where cmpl_invocation_id = 261 and cmpl_profile_rule_id = 9992185
)
begin
select 'No existo'
end
select datediff(ms, @.d, getdate()) / 1000.0
select @.d = getdate()
if not exists
(
select 1 from cmpl_res_rule_result
where cmpl_invocation_id = @.cmpl_invocation_id and
cmpl_profile_rule_id = @.cmpl_profile_rule_id
)
begin
select 'No existo'
end
select datediff(ms, @.d, getdate()) / 1000.0
The first existence query, using constants, consistently runs about 10x
faster than the second, using local variables. To me this is not completely
unexpected, as the optimizer should be able to make better use of statistics
for the first query. What I'd like to know is how I can see the effects of
this, so that I can completely explain the difference in performance.
First, I checked the query plans. They are identical:
StmtText
----
----
----
--
-
|--Compute Scalar(DEFINE[Expr1004]=CASE WHEN [Expr1005] IS NULL TH
EN (1)
ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE[Expr1005] = [PROBE VALUE]))
|--Constant Scan
|--Index
Seek(OBJECT[mydb].[dbo].[cmpl_res_rule_result].[cmpl_res_r
ule_result_ix0]),
SEEK[mydb].[dbo].[cmpl_res_rule_result].[cmpl_invocation_i
d]=(261.)),
WHERE[mydb].[dbo].[cmpl_res_rule_result].[cmpl_profile_rul
Next, I looked at statistics io. Also identical:
Table 'cmpl_res_rule_result'. Scan count 1, logical reads 5451, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Then I fired up profiler and started watching scan events, and the reads
column of SQL:StmtCompleted events. Still no difference.
I did finally notice a difference when I added the Lock:Acquired event. The
longer running query acquires many more locks than the faster query. Still,
what is it doing? Why do all the other results looks identical?
I would greatly appreciate any pointers as to where I should look next, as I
am eager to understand exactly what is happening.
Thank you,
PaulIt is common that you could get different execution plans and performance
because the exact search values are known in the first query are but unknown
in the second instance. SQL Server will estimate what values might be
supplied for the second query based on available stats. However, I can't
explain how the same plan can yield such a difference in performance.
Are you certain the plans are identical? Try running the script from SSMS
with 'include actual execution plan'. Right-click in the execution plan
result window and save to a file. Then past the file xml text and post
here. Also include the DDL (create table) for the cmpl_res_rule_result
table.
Hope this helps.
Dan Guzman
SQL Server MVP
"LdsPaulF" <LDSPaulF@.noemail.noemail> wrote in message
news:AD9D396A-5427-4A17-B9F2-D20A8BA3A921@.microsoft.com...
> Hello,
> I'm having some trouble explaining the performance difference I'm seeing
> in
> a couple of queries. I'm running against an XP Pro desktop installation
> of
> Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86) Mar 2 2007
> 20:01:28
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows
> NT 5.1 (Build 2600: Service Pack 2)
>
> Here is the SQL I'm running:
> declare @.cmpl_invocation_id numeric(10)
> set @.cmpl_invocation_id = 261
> declare @.cmpl_profile_rule_id numeric(10)
> set @.cmpl_profile_rule_id = 9992185
> declare @.d datetime
> select @.d = getdate()
> if not exists
> (
> select 1 from cmpl_res_rule_result
> where cmpl_invocation_id = 261 and cmpl_profile_rule_id = 9992185
> )
> begin
> select 'No existo'
> end
> select datediff(ms, @.d, getdate()) / 1000.0
> select @.d = getdate()
> if not exists
> (
> select 1 from cmpl_res_rule_result
> where cmpl_invocation_id = @.cmpl_invocation_id and
> cmpl_profile_rule_id = @.cmpl_profile_rule_id
> )
> begin
> select 'No existo'
> end
> select datediff(ms, @.d, getdate()) / 1000.0
>
> The first existence query, using constants, consistently runs about 10x
> faster than the second, using local variables. To me this is not
> completely
> unexpected, as the optimizer should be able to make better use of
> statistics
> for the first query. What I'd like to know is how I can see the effects
> of
> this, so that I can completely explain the difference in performance.
> First, I checked the query plans. They are identical:
> StmtText
> ----
----
----
--
--
> |--Compute Scalar(DEFINE[Expr1004]=CASE WHEN [Expr1005] IS NULL
THEN
> (1)
> ELSE (0) END))
> |--Nested Loops(Left Semi Join, DEFINE[Expr1005] = [PROBE
VALUE]))
> |--Constant Scan
> |--Index
> Seek(OBJECT[mydb].[dbo].[cmpl_res_rule_result].[cmpl_res
_rule_result_ix0]),
> SEEK[mydb].[dbo].[cmpl_res_rule_result].[cmpl_invocation
_id]=(261.)),
> WHERE[mydb].[dbo].[cmpl_res_rule_result].[cmpl_profile_r
ul
>
> Next, I looked at statistics io. Also identical:
> Table 'cmpl_res_rule_result'. Scan count 1, logical reads 5451, physical
> reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
> lob
> read-ahead reads 0.
>
> Then I fired up profiler and started watching scan events, and the reads
> column of SQL:StmtCompleted events. Still no difference.
> I did finally notice a difference when I added the Lock:Acquired event.
> The
> longer running query acquires many more locks than the faster query.
> Still,
> what is it doing? Why do all the other results looks identical?
> I would greatly appreciate any pointers as to where I should look next, as
> I
> am eager to understand exactly what is happening.
> Thank you,
> Paul
>|||Hi, Paul,
I understand that your first query with constant parameters ran 10 times
faster than your second query with local variable parameters.
If I have misunderstood, please let me know.
I tried to reproduce your issue at my side by using AdventureWorks
database; however I did not reproduce it at last. Both of them ran very
fast. The actual execution plans were identical. Since both of the results
were 0.000, I could not see the 10 times speed issue.However, this may be
related to the number of records is not as large as yours. Also, I would
like to know what the result is if you use stored procedure to execute the
query.
If it is convenient for you, I recommend that you export the related tables
to a test database and mail the database backup file to me
(changliw_at_microsoft_dot_com) for further research.
NOTE: Our managed newsgroup is focused on break fix issues that are neither
urgent, nor complex. For urgent and performance related issues, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Dan,
Thank you for the reply. Below is DDL for the table and its indices, plus
two XML plans. The first xml is for the "fast" query, with constants, and
the second is for the "slow" query, with variables.
I look forward to your response, and I appreciate your time!
Regards,
Paul
/****** Object: Table [dbo].[cmpl_res_rule_result] Script Date:
03/19/2007 10:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cmpl_res_rule_result](
[cmpl_res_rule_result_id] [numeric](10, 0) NOT NULL,
[cmpl_invocation_id] [numeric](10, 0) NOT NULL,
[account_id] [numeric](10, 0) NOT NULL,
[cmpl_profile_rule_id] [numeric](10, 0) NULL,
[cmpl_rule_status_id] [numeric](10, 0) NOT NULL,
[profile_rule_comment] [varchar](255) NULL,
[calc_most_severe_status_id] [numeric](10, 0) NULL,
[calc_most_severe_error_level] [tinyint] NULL,
CONSTRAINT [cmpl_res_rule_result_pk] PRIMARY KEY CLUSTERED
(
[cmpl_res_rule_result_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Index [cmpl_res_rule_result_pk] Script Date: 03/19/2
007
10:13:59 ******/
ALTER TABLE [dbo].[cmpl_res_rule_result] ADD CONSTRAINT
[cmpl_res_rule_result_pk] PRIMARY KEY CLUSTERED
(
[cmpl_res_rule_result_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
/****** Object: Index [cmpl_res_rule_result_ix0] Script Date: 03/19/
2007
10:13:43 ******/
CREATE UNIQUE NONCLUSTERED INDEX [cmpl_res_rule_result_ix0] ON
[dbo].[cmpl_res_rule_result]
(
[cmpl_invocation_id] ASC,
[account_id] ASC,
[cmpl_profile_rule_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCK
S
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
========================================
====================================
=====================================
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0"
Build="9.00.3050.00"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1"
StatementOptmLevel="FULL" StatementSubTreeCost="5.66214" StatementText="if
not exists
(
select 1 from cmpl_res_rule_result
where cmpl_invocation_id = 261 and cmpl_profile_rule_id = 9992185
)" StatementType="COND WITH QUERY">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false"
NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan DegreeOfParallelism="0" CachedPlanSize="10"
CompileTime="35" CompileCPU="4" CompileMemory="168">
<MissingIndexes>
<MissingIndexGroup Impact="99.8008">
<MissingIndex Database="[my_db]" Schema="[dbo]"
Table="[cmpl_res_rule_result]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[cmpl_invocation_id]" ColumnId="2" />
<Column Name="[cmpl_profile_rule_id]" ColumnId="4" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute
Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar"
EstimatedTotalSubtreeCost="5.66214">
<OutputList>
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="CASE WHEN [Expr1005] IS
NULL THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Sem
i
Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops"
EstimatedTotalSubtreeCost="5.66214">
<OutputList>
<ColumnReference Column="Expr1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
</DefinedValue>
</DefinedValues>
<ProbeColumn>
<ColumnReference Column="Expr1005" />
</ProbeColumn>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06"
EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant
Scan" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ConstantScan />
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="1.14493"
EstimateIO="4.01767" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1
"
LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek"
EstimatedTotalSubtreeCost="5.1626">
<OutputList>
<ColumnReference Database="[my_db]" Schema="[dbo]"
Table="[cmpl_res_rule_result]" Column="cmpl_profile_rule_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD"
ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[my_db]"
Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_profile_r
ule_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[my_db]" Schema="[dbo]"
Table="[cmpl_res_rule_result]" Index="[cmpl_res_rule_result_ix0]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[my_db]"
Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_invocatio
n_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(261.)">
<Const ConstValue="(261.)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator
ScalarString="[my_db].[dbo].[cmpl_res_rule_result].[cmpl_pro
file_rule_id]=(9992185.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[my_db]"
Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_profile_r
ule_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(9992185.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
========================================
====================================
=====================================
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0"
Build="9.00.3050.00"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="9" StatementEstRows="1" StatementId="2"
StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound
"
StatementSubTreeCost="0.00781216" StatementText="if not exists
(
select 1 from cmpl_res_rule_result
where cmpl_invocation_id = @.cmpl_invocation_id and
cmpl_profile_rule_id = @.cmpl_profile_rule_id
) " StatementType="COND WITH QUERY">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false"
NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan DegreeOfParallelism="0" CachedPlanSize="10"
CompileTime="2" CompileCPU="2" CompileMemory="168">
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute
Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar"
EstimatedTotalSubtreeCost="0.00781216">
<OutputList>
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="CASE WHEN [Expr1005] IS
NULL THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Sem
i
Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops"
EstimatedTotalSubtreeCost="0.00781206">
<OutputList>
<ColumnReference Column="Expr1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
</DefinedValue>
</DefinedValues>
<ProbeColumn>
<ColumnReference Column="Expr1005" />
</ProbeColumn>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06"
EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant
Scan" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ConstantScan />
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.456111"
EstimateIO="1.60164" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1
"
LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek"
EstimatedTotalSubtreeCost="0.00740688">
<OutputList>
<ColumnReference Database="[my_db]" Schema="[dbo]"
Table="[cmpl_res_rule_result]" Column="cmpl_profile_rule_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD"
ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[my_db]"
Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_profile_r
ule_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[my_db]" Schema="[dbo]"
Table="[cmpl_res_rule_result]" Index="[cmpl_res_rule_result_ix0]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[my_db]"
Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_invocatio
n_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[@.cmpl_invocation_id]">
<Identifier>
<ColumnReference
Column="@.cmpl_invocation_id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator
ScalarString="[my_db].[dbo].[cmpl_res_rule_result].[cmpl_pro
file_rule_id]=[@.cmpl_profile_rule_id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[my_db]"
Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_profile_r
ule_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference
Column="@.cmpl_profile_rule_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
<ParameterList>
<ColumnReference Column="@.cmpl_profile_rule_id"
ParameterRuntimeValue="(9992185.)" />
<ColumnReference Column="@.cmpl_invocation_id"
ParameterRuntimeValue="(261.)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
"Dan Guzman" wrote:

> It is common that you could get different execution plans and performance
> because the exact search values are known in the first query are but unkno
wn
> in the second instance. SQL Server will estimate what values might be
> supplied for the second query based on available stats. However, I can't
> explain how the same plan can yield such a difference in performance.
> Are you certain the plans are identical? Try running the script from SSMS
> with 'include actual execution plan'. Right-click in the execution plan
> result window and save to a file. Then past the file xml text and post
> here. Also include the DDL (create table) for the cmpl_res_rule_result
> table.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LdsPaulF" <LDSPaulF@.noemail.noemail> wrote in message
> news:AD9D396A-5427-4A17-B9F2-D20A8BA3A921@.microsoft.com...
--[vbcol=seagreen]
>|||Hi Charles,
Thank you for your reply. Your understanding is correct: the query using
constants runs about 10x faster than the query using local variables.
I tried creating a stored procedure that took the two numbers as parameters.
It ran as fast as the version with constants.
Also, I sent you an email about obtaining a backup of the database.
Many thanks!
Regards,
Paul
"Charles Wang[MSFT]" wrote:

> Hi, Paul,
> I understand that your first query with constant parameters ran 10 times
> faster than your second query with local variable parameters.
> If I have misunderstood, please let me know.
> I tried to reproduce your issue at my side by using AdventureWorks
> database; however I did not reproduce it at last. Both of them ran very
> fast. The actual execution plans were identical. Since both of the results
> were 0.000, I could not see the 10 times speed issue.However, this may be
> related to the number of records is not as large as yours. Also, I would
> like to know what the result is if you use stored procedure to execute the
> query.
> If it is convenient for you, I recommend that you export the related table
s
> to a test database and mail the database backup file to me
> (changliw_at_microsoft_dot_com) for further research.
> NOTE: Our managed newsgroup is focused on break fix issues that are neithe
r
> urgent, nor complex. For urgent and performance related issues, it is
> recommended that you contact Microsoft Customer Support Services (CSS) via
> telephone so that a dedicated Support Professional can assist you in a mor
e
> efficient manner. Please be advised that contacting phone support will be
a
> charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> If you have any other questions or concerns, please feel free to let me
> know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>
>
>
>|||Thanks for the additional info, Paul. I don't see anything obvious that
would explain why one query would run 10x faster than the other, unless
there is a run-time issue like blocking.
However, I see that the seek predicate is using only the cmpl_invocation_id
column of the composite cmpl_res_rule_result_ix0 index. The logical reads
are so high because all of the index rows with cmpl_invocation_id value 261
must be scanned until the cmpl_profile_rule_id value 9992185 is found.
To optimize the query, consider creating a non-clustered index on
cmpl_invocation_id and cmpl_profile_rule_id. Alternatively, you could
change the the existing index column sequence to cmpl_invocation_id,
cmpl_profile_rule_id and account_id but that might negatively affect other
queries that find the existing index useful. I believe both queries will
run in a few milliseconds with the index change.
Hope this helps.
Dan Guzman
SQL Server MVP
"LdsPaulF" <LDSPaulF@.noemail.noemail> wrote in message
news:F06DC51F-FA1B-4D35-BBD7-B593CE7AE7D3@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Thank you for the reply. Below is DDL for the table and its indices, plus
> two XML plans. The first xml is for the "fast" query, with constants, and
> the second is for the "slow" query, with variables.
> I look forward to your response, and I appreciate your time!
> Regards,
> Paul
> /****** Object: Table [dbo].[cmpl_res_rule_result] Script Date
:
> 03/19/2007 10:13:04 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[cmpl_res_rule_result](
> [cmpl_res_rule_result_id] [numeric](10, 0) NOT NULL,
> [cmpl_invocation_id] [numeric](10, 0) NOT NULL,
> [account_id] [numeric](10, 0) NOT NULL,
> [cmpl_profile_rule_id] [numeric](10, 0) NULL,
> [cmpl_rule_status_id] [numeric](10, 0) NOT NULL,
> [profile_rule_comment] [varchar](255) NULL,
> [calc_most_severe_status_id] [numeric](10, 0) NULL,
> [calc_most_severe_error_level] [tinyint] NULL,
> CONSTRAINT [cmpl_res_rule_result_pk] PRIMARY KEY CLUSTERED
> (
> [cmpl_res_rule_result_id] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> /****** Object: Index [cmpl_res_rule_result_pk] Script Date:
> 03/19/2007
> 10:13:59 ******/
> ALTER TABLE [dbo].[cmpl_res_rule_result] ADD CONSTRAINT
> [cmpl_res_rule_result_pk] PRIMARY KEY CLUSTERED
> (
> [cmpl_res_rule_result_id] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
> OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
> ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> /****** Object: Index [cmpl_res_rule_result_ix0] Script Date:
> 03/19/2007
> 10:13:43 ******/
> CREATE UNIQUE NONCLUSTERED INDEX [cmpl_res_rule_result_ix0] ON
> [dbo].[cmpl_res_rule_result]
> (
> [cmpl_invocation_id] ASC,
> [account_id] ASC,
> [cmpl_profile_rule_id] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
> OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS
> = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>
> ========================================
==================================
=======================================
> <?xml version="1.0" encoding="utf-16"?>
> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0"
> Build="9.00.3050.00"
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
> <BatchSequence>
> <Batch>
> <Statements>
> <StmtSimple StatementCompId="4" StatementEstRows="1"
> StatementId="1"
> StatementOptmLevel="FULL" StatementSubTreeCost="5.66214" StatementText="if
> not exists
> (
> select 1 from cmpl_res_rule_result
> where cmpl_invocation_id = 261 and cmpl_profile_rule_id = 9992185
> )" StatementType="COND WITH QUERY">
> <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false"
> ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false"
> NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
> <QueryPlan DegreeOfParallelism="0" CachedPlanSize="10"
> CompileTime="35" CompileCPU="4" CompileMemory="168">
> <MissingIndexes>
> <MissingIndexGroup Impact="99.8008">
> <MissingIndex Database="[my_db]" Schema="[dbo]"
> Table="[cmpl_res_rule_result]">
> <ColumnGroup Usage="EQUALITY">
> <Column Name="[cmpl_invocation_id]" ColumnId="2" />
> <Column Name="[cmpl_profile_rule_id]" ColumnId="4"
/>
> </ColumnGroup>
> </MissingIndex>
> </MissingIndexGroup>
> </MissingIndexes>
> <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0"
> EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
> LogicalOp="Compute
> Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar"
> EstimatedTotalSubtreeCost="5.66214">
> <OutputList>
> <ColumnReference Column="Expr1004" />
> </OutputList>
> <ComputeScalar>
> <DefinedValues>
> <DefinedValue>
> <ColumnReference Column="Expr1004" />
> <ScalarOperator ScalarString="CASE WHEN [Expr1005]
IS
> NULL THEN (1) ELSE (0) END">
> <IF>
> <Condition>
> <ScalarOperator>
> <Compare CompareOp="IS">
> <ScalarOperator>
> <Identifier>
> <ColumnReference Column="Expr1005" />
> </Identifier>
> </ScalarOperator>
> <ScalarOperator>
> <Const ConstValue="NULL" />
> </ScalarOperator>
> </Compare>
> </ScalarOperator>
> </Condition>
> <Then>
> <ScalarOperator>
> <Const ConstValue="(1)" />
> </ScalarOperator>
> </Then>
> <Else>
> <ScalarOperator>
> <Const ConstValue="(0)" />
> </ScalarOperator>
> </Else>
> </IF>
> </ScalarOperator>
> </DefinedValue>
> </DefinedValues>
> <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0"
> EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left
> Semi
> Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops"
> EstimatedTotalSubtreeCost="5.66214">
> <OutputList>
> <ColumnReference Column="Expr1005" />
> </OutputList>
> <RunTimeInformation>
> <RunTimeCountersPerThread Thread="0" ActualRows="1"
> ActualEndOfScans="1" ActualExecutions="1" />
> </RunTimeInformation>
> <NestedLoops Optimized="false">
> <DefinedValues>
> <DefinedValue>
> <ColumnReference Column="Expr1005" />
> </DefinedValue>
> </DefinedValues>
> <ProbeColumn>
> <ColumnReference Column="Expr1005" />
> </ProbeColumn>
> <RelOp AvgRowSize="9" EstimateCPU="1.157E-06"
> EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
> LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant
> Scan" EstimatedTotalSubtreeCost="1.157E-06">
> <OutputList />
> <RunTimeInformation>
> <RunTimeCountersPerThread Thread="0" ActualRows="1"
> ActualEndOfScans="1" ActualExecutions="1" />
> </RunTimeInformation>
> <ConstantScan />
> </RelOp>
> <RelOp AvgRowSize="16" EstimateCPU="1.14493"
> EstimateIO="4.01767" EstimateRebinds="0" EstimateRewinds="0"
> EstimateRows="1"
> LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek"
> EstimatedTotalSubtreeCost="5.1626">
> <OutputList>
> <ColumnReference Database="[my_db]" Schema="
1;dbo]"
> Table="[cmpl_res_rule_result]" Column="cmpl_profile_rule_id" />
> </OutputList>
> <RunTimeInformation>
> <RunTimeCountersPerThread Thread="0" ActualRows="0"
> ActualEndOfScans="1" ActualExecutions="1" />
> </RunTimeInformation>
> <IndexScan Ordered="true" ScanDirection="FORWARD"
> ForcedIndex="false" NoExpandHint="false">
> <DefinedValues>
> <DefinedValue>
> <ColumnReference Database="[my_db]"
> Schema="[dbo]" Table="[cmpl_res_rule_result]"
> Column="cmpl_profile_rule_id" />
> </DefinedValue>
> </DefinedValues>
> <Object Database="[my_db]" Schema="[dbo]"
> Table="[cmpl_res_rule_result]" Index="[cmpl_res_rule_result_ix0]"
/>
> <SeekPredicates>
> <SeekPredicate>
> <Prefix ScanType="EQ">
> <RangeColumns>
> <ColumnReference Database="[my_db]"
> Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_invocat
ion_id"
> />
> </RangeColumns>
> <RangeExpressions>
> <ScalarOperator ScalarString="(261.)">
> <Const ConstValue="(261.)" />
> </ScalarOperator>
> </RangeExpressions>
> </Prefix>
> </SeekPredicate>
> </SeekPredicates>
> <Predicate>
> <ScalarOperator
> ScalarString="[my_db].[dbo].[cmpl_res_rule_result].[cmpl_p
rofile_rule_id]=(9992185.)">
> <Compare CompareOp="EQ">
> <ScalarOperator>
> <Identifier>
> <ColumnReference Database="[my_db]"
> Schema="[dbo]" Table="[cmpl_res_rule_result]"
> Column="cmpl_profile_rule_id" />
> </Identifier>
> </ScalarOperator>
> <ScalarOperator>
> <Const ConstValue="(9992185.)" />
> </ScalarOperator>
> </Compare>
> </ScalarOperator>
> </Predicate>
> </IndexScan>
> </RelOp>
> </NestedLoops>
> </RelOp>
> </ComputeScalar>
> </RelOp>
> </QueryPlan>
> </StmtSimple>
> </Statements>
> </Batch>
> </BatchSequence>
> </ShowPlanXML>
>
>
> ========================================
==================================
=======================================
> <?xml version="1.0" encoding="utf-16"?>
> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0"
> Build="9.00.3050.00"
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
> <BatchSequence>
> <Batch>
> <Statements>
> <StmtSimple StatementCompId="9" StatementEstRows="1"
> StatementId="2"
> StatementOptmLevel="FULL"
> StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
> StatementSubTreeCost="0.00781216" StatementText="if not exists
> (
> select 1 from cmpl_res_rule_result
> where cmpl_invocation_id = @.cmpl_invocation_id and
> cmpl_profile_rule_id = @.cmpl_profile_rule_id
> ) " StatementType="COND WITH QUERY">
> <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false"
> ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false"
> NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
> <QueryPlan DegreeOfParallelism="0" CachedPlanSize="10"
> CompileTime="2" CompileCPU="2" CompileMemory="168">
> <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0"
> EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
> LogicalOp="Compute
> Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar"
> EstimatedTotalSubtreeCost="0.00781216">
> <OutputList>
> <ColumnReference Column="Expr1004" />
> </OutputList>
> <ComputeScalar>
> <DefinedValues>
> <DefinedValue>
> <ColumnReference Column="Expr1004" />
> <ScalarOperator ScalarString="CASE WHEN [Expr1005]
IS
> NULL THEN (1) ELSE (0) END">
> <IF>
> <Condition>
> <ScalarOperator>
> <Compare CompareOp="IS">
> <ScalarOperator>
> <Identifier>
> <ColumnReference Column="Expr1005" />
> </Identifier>
> </ScalarOperator>
> <ScalarOperator>
> <Const ConstValue="NULL" />
> </ScalarOperator>
> </Compare>
> </ScalarOperator>
> </Condition>
> <Then>
> <ScalarOperator>
> <Const ConstValue="(1)" />
> </ScalarOperator>
> </Then>
> <Else>
> <ScalarOperator>
> <Const ConstValue="(0)" />
> </ScalarOperator>
> </Else>
> </IF>
> </ScalarOperator>
> </DefinedValue>
> </DefinedValues>
> <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0"
> EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left
> Semi
> Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops"
> EstimatedTotalSubtreeCost="0.00781206">
> <OutputList>
> <ColumnReference Column="Expr1005" />
> </OutputList>
> <RunTimeInformation>
> <RunTimeCountersPerThread Thread="0" ActualRows="1"
> ActualEndOfScans="1" ActualExecutions="1" />
> </RunTimeInformation>
> <NestedLoops Optimized="false">
> <DefinedValues>
> <DefinedValue>
> <ColumnReference Column="Expr1005" />
> </DefinedValue>
> </DefinedValues>
> <ProbeColumn>
> <ColumnReference Column="Expr1005" />
> </ProbeColumn>
> <RelOp AvgRowSize="9" EstimateCPU="1.157E-06"
> EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
> LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant
> Scan" EstimatedTotalSubtreeCost="1.157E-06">
> <OutputList />
> <RunTimeInformation>
> <RunTimeCountersPerThread Thread="0" ActualRows="1"
> ActualEndOfScans="1" ActualExecutions="1" />
> </RunTimeInformation>
> <ConstantScan />
> </RelOp>
> <RelOp AvgRowSize="16" EstimateCPU="0.456111"
> EstimateIO="1.60164" EstimateRebinds="0" EstimateRewinds="0"
> EstimateRows="1"
> LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek"
> EstimatedTotalSubtreeCost="0.00740688">
> <OutputList>
> <ColumnReference Database="[my_db]" Schema="
1;dbo]"
> Table="[cmpl_res_rule_result]" Column="cmpl_profile_rule_id" />
> </OutputList>
> <RunTimeInformation>
> <RunTimeCountersPerThread Thread="0" ActualRows="0"
> ActualEndOfScans="1" ActualExecutions="1" />
> </RunTimeInformation>
> <IndexScan Ordered="true" ScanDirection="FORWARD"
> ForcedIndex="false" NoExpandHint="false">
> <DefinedValues>
> <DefinedValue>
> <ColumnReference Database="[my_db]"
> Schema="[dbo]" Table="[cmpl_res_rule_result]"
> Column="cmpl_profile_rule_id" />
> </DefinedValue>
> </DefinedValues>
> <Object Database="[my_db]" Schema="[dbo]"
> Table="[cmpl_res_rule_result]" Index="[cmpl_res_rule_result_ix0]"
/>
> <SeekPredicates>
> <SeekPredicate>
> <Prefix ScanType="EQ">
> <RangeColumns>
> <ColumnReference Database="[my_db]"
> Schema="[dbo]" Table="[cmpl_res_rule_result]" Column="cmpl_invocat
ion_id"
> />
> </RangeColumns>
> <RangeExpressions>
> <ScalarOperator
> ScalarString="[@.cmpl_invocation_id]">
> <Identifier>
> <ColumnReference
> Column="@.cmpl_invocation_id" />
> </Identifier>
> </ScalarOperator>
> </RangeExpressions>
> </Prefix>
> </SeekPredicate>
> </SeekPredicates>
> <Predicate>
> <ScalarOperator
> ScalarString="[my_db].[dbo].[cmpl_res_rule_result].[cmpl_p
rofile_rule_id]=[@.cmpl_profile_rule_id]">
> <Compare CompareOp="EQ">
> <ScalarOperator>
> <Identifier>
> <ColumnReference Database="[my_db]"
> Schema="[dbo]" Table="[cmpl_res_rule_result]"
> Column="cmpl_profile_rule_id" />
> </Identifier>
> </ScalarOperator>
> <ScalarOperator>
> <Identifier>
> <ColumnReference
> Column="@.cmpl_profile_rule_id" />
> </Identifier>
> </ScalarOperator>
> </Compare>
> </ScalarOperator>
> </Predicate>
> </IndexScan>
> </RelOp>
> </NestedLoops>
> </RelOp>
> </ComputeScalar>
> </RelOp>
> <ParameterList>
> <ColumnReference Column="@.cmpl_profile_rule_id"
> ParameterRuntimeValue="(9992185.)" />
> <ColumnReference Column="@.cmpl_invocation_id"
> ParameterRuntimeValue="(261.)" />
> </ParameterList>
> </QueryPlan>
> </StmtSimple>
> </Statements>
> </Batch>
> </BatchSequence>
> </ShowPlanXML>
>
>
>
> "Dan Guzman" wrote:
>
--[vbcol=seagreen]|||Hi Dan,
Thanks for reviewing the plans. On one hand I'm glad that I didn't miss an
obvious difference between them, but on the other hand I kind of wish that I
had so that the performance difference were more easily explained!
I agree about the index; that is an effective solution to the real problem
my customer had. However I still am very curious about how to explain the
run-time difference of the two queries.
Also, I was the only user on that server when I ran these, so we can rule
out blocking.
Unfortunately I feel that I've reached the end of my knowledge here; I'm not
sure where to look next to see what the server is up to. Any suggestions?
Thanks,
Paul
"Dan Guzman" wrote:
[vbcol=seagreen]
> Thanks for the additional info, Paul. I don't see anything obvious that
> would explain why one query would run 10x faster than the other, unless
> there is a run-time issue like blocking.
> However, I see that the seek predicate is using only the cmpl_invocation_i
d
> column of the composite cmpl_res_rule_result_ix0 index. The logical reads
> are so high because all of the index rows with cmpl_invocation_id value 26
1
> must be scanned until the cmpl_profile_rule_id value 9992185 is found.
> To optimize the query, consider creating a non-clustered index on
> cmpl_invocation_id and cmpl_profile_rule_id. Alternatively, you could
> change the the existing index column sequence to cmpl_invocation_id,
> cmpl_profile_rule_id and account_id but that might negatively affect other
> queries that find the existing index useful. I believe both queries will
> run in a few milliseconds with the index change.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LdsPaulF" <LDSPaulF@.noemail.noemail> wrote in message
> news:F06DC51F-FA1B-4D35-BBD7-B593CE7AE7D3@.microsoft.com...|||> I agree about the index; that is an effective solution to the real problem
> my customer had. However I still am very curious about how to explain the
> run-time difference of the two queries.
I was able to to reproduce your problem after loading several thousand rows
of test data with the same cmpl_invocation_id value.
As you mentioned in your initial post, the second query acquires many more
locks than the first even though the execution plans are the same. In my
test, the first query immediately acquired page locks but the second
acquired only row locks. I think SQL Server chose row locks for the second
query because the estimated row count didn't exceed the threshold for page
locks.

> Unfortunately I feel that I've reached the end of my knowledge here; I'm
> not
> sure where to look next to see what the server is up to. Any suggestions?
As we discussed, the biggest performance gain is realized by the index
add/change. Additionally, make sure you keep stats updated so that SQL
Server has good information to estimate row counts.
Hope this helps.
Dan Guzman
SQL Server MVP
"LdsPaulF" <LDSPaulF@.noemail.noemail> wrote in message
news:2999B293-F1DF-4A41-9E1E-D15D2033694C@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Thanks for reviewing the plans. On one hand I'm glad that I didn't miss
> an
> obvious difference between them, but on the other hand I kind of wish that
> I
> had so that the performance difference were more easily explained!
> I agree about the index; that is an effective solution to the real problem
> my customer had. However I still am very curious about how to explain the
> run-time difference of the two queries.
> Also, I was the only user on that server when I ran these, so we can rule
> out blocking.
> Unfortunately I feel that I've reached the end of my knowledge here; I'm
> not
> sure where to look next to see what the server is up to. Any suggestions?
> Thanks,
> Paul
>
> "Dan Guzman" wrote:
>|||Hi, Paul,
This is a quick note to let you know that I have reproduced your issue. Now
I am consulting the product team on the different Execution Plans. The
process may need a long time, but I will let you know the result
immediately once I get the answer. Appreciate your patience.
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thank you, Charles. I will await your reply.
Regards,
Paul
"Charles Wang[MSFT]" wrote:

> Hi, Paul,
> This is a quick note to let you know that I have reproduced your issue. No
w
> I am consulting the product team on the different Execution Plans. The
> process may need a long time, but I will let you know the result
> immediately once I get the answer. Appreciate your patience.
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>