examining server actions while running a query

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 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_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,
> Paul
>
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="[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_invocation_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_profile_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="[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_invocation_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_profile_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:
|||> 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/subscriptions/managednewsgroups/default.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/subscriptions/support/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, Paul,
Unfortunately I have not got a response from the product team.
After I discussed this issue with our Tech Lead, we believed that this
issue should be caused by SQL Server optimization. As you had tried, if you
used a stored procedure, then everything worked fine. Both the queries had
the same execution plan and time. As a workaround, we recommend that you
use stored procedure to avoid such problems.
From my research, there is an article talking about a similar issue. Please
refer to:
Troubleshooting Poor Query Performance: Constant Folding and Expression
Evaluation During Cardinality Estimation
http://msdn2.microsoft.com/en-us/library/ms175933.aspx
Also, If there is any response from the product team in future, I will let
you know. Sorry for bringing you any inconvenience.
If you have any other questions or concerns, please feel free to let me
know.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
================================================== ====