Re: Poor performance after upgrading to sql server 2005

Are you saying that I'm not supposed to use table variables in join
statements in SQL 2005 code? If so, that would be a major limitation.

I am not saying that at all. What I was stating is that by using a table
variable you limit to a degree how much the optimizer knows about the data
in that table variable and thus it may not always make the optimal decision
vs. if it did have the missing stats.


Anyway, all of this is beside the point, which is that the exact same code
in the exact same database with the exact same indexes runs a lot slower
in
SQL 2005 on a machine which is much, much faster in every way than the SQL
2000 machine. I'm starting to get the feeling we're going to have to
re-write
our database code for SQL 2005.

As I stated the optimizer has changed between the two versions and in most
ways is much smarter on how it handles things. As such most things should
work as good or better than in 2000 but there will be times when that is the
opposite. You may have simply been lucky that the optimizer guessed
correctly in 2000 where as in 2005 it guessed wrong.

I highly suggest you open a case with MS PSS so they can work directly with
you to see if there is anything they can do. If this is a case in which the
optimizer is not smart enough for a particular case they will want to know
about it so they can make it better. We can't solve this in a newsgroup post
when we don't have all the information and especially if we are dealing with
pseudo code. This sounds like an optimizer issue and if so your best bet is
direct contact so the support engineers can give the dev team all the right
data to get to the bottom of this.


--
Andrew J. Kelly SQL MVP


"Rich Wood" <RichWood@xxxxxxxxxxxxxxxx> wrote in message
news:0EDB38CF-BB34-47B3-B41D-20B02579BE7F@xxxxxxxxxxxxxxxx
Andrew,

This is an extract from a function which returns a table variable. I
hard-coded the 'D' for purposes of the example. Normally it is an argument
to
the function, as are the other variables I "hard-coded".

Are you saying that I'm not supposed to use table variables in join
statements in SQL 2005 code? If so, that would be a major limitation.

Anyway, all of this is beside the point, which is that the exact same code
in the exact same database with the exact same indexes runs a lot slower
in
SQL 2005 on a machine which is much, much faster in every way than the SQL
2000 machine. I'm starting to get the feeling we're going to have to
re-write
our database code for SQL 2005.

Rich Wood

"Andrew J. Kelly" wrote:


Rich,

You have several areas in which it makes it hard to determine a proper
query
plan based on the way you are coding this. One is that you use table
variables which do not keep statistics. Since you join on at least one of
them the optimizer will not have a clue as to if you have 1 row or 10K
rows
in that table. The other is what I mentioned yesterday about the
@Frequency
variable. Since it is a variable the optimizer does not know the real
value
and takes a guess. From what I can see in this code you are hard coding
it
to 'D' so why bother with a variable in the first place?
--
Andrew J. Kelly SQL MVP


"Rich Wood" <RichWood@xxxxxxxxxxxxxxxx> wrote in message
news:928E91C0-0634-4046-8964-7D2612CBE37E@xxxxxxxxxxxxxxxx
I'm not sure how helpful this code will be without the database itself,
but
here is a sample. I've included the SQL 2005 execution plan but I do
not
know
of a way to send the SQL 2000 execution plan. This query takes 0
milliseconds
in SQL 2000 and 233 milliseconds in SQL 2005, even after running it a
few
times. The lines of code that cause the slow-down in SQL 20005 are:

AND 1 = CASE WHEN @FREQUENCY = 'W'
THEN ( SELECT 1 WHERE CURR.DATA_ID IN (SELECT DATA_ID FROM
fdINDDATA_DAILY D (NOLOCK)))
ELSE ( SELECT 1)
END


SQL Code:

DECLARE
@SEC_IDS VARCHAR(5000),
@DATE DATETIME,
@FREQUENCY CHAR(1),
@PREVIOUS_DAY DATETIME

DECLARE @SEC_LIST TABLE( SEC_ID INT)
DECLARE @IND_DATA TABLE( SEC_ID INT, DATE DATETIME, PRICE FLOAT,
PRICE_PREV
FLOAT, DIVIDEND FLOAT)

SET @SEC_IDS = '2'
SET @DATE = '2006-02-17 08:39:20'
SET @FREQUENCY = 'D'

IF @SEC_IDS = ''
INSERT INTO @SEC_LIST
SELECT SEC_ID
FROM fdSecuritiesMasterList SML (NOLOCK)
WHERE TYPE_ID = 1
AND SEC_ID IN (SELECT ID_DEP_VAR FROM mNAV_REGRESSION WHERE FREQUENCY =
@FREQUENCY)
ELSE
INSERT INTO @SEC_LIST
SELECT ItemValue
FROM dbo.fn_SplitIn2Rows( @SEC_IDS, ',')


-- ** US Tickers ** --
SELECT TOP 1 @PREVIOUS_DAY = CDAY FROM calUSA (NOLOCK) WHERE WDC =
(SELECT
WDC - 1 FROM calUSA (NOLOCK) WHERE CDAY = CONVERT(VARCHAR(10), @DATE,
101))
ORDER BY CDAY
-- Get data for indepent index variables
INSERT INTO @IND_DATA( SEC_ID, DATE, PRICE, PRICE_PREV, DIVIDEND)
SELECT DISTINCT R.ID_IND_VAR AS SEC_ID, CURR.[DATE], CURR.VALUE,
PREV.VALUE, ISNULL(RD.AMOUNT, 0)
FROM mvNAV_REGRESSION R (NOLOCK)
JOIN @SEC_LIST L ON R.ID_DEP_VAR = L.SEC_ID
JOIN fdSecuritiesMasterList SML (NOLOCK) ON R.ID_IND_VAR = SML.SEC_ID
JOIN fdEXCHANGES EXCH (NOLOCK) ON SML.EXCH_ID = EXCH.EXCH_ID
JOIN fdINDDATA CURR (NOLOCK) ON SML.SEC_ID = CURR.SEC_ID
JOIN fdINDDATA_DAILY PREV (NOLOCK) ON SML.SEC_ID = PREV.SEC_ID AND
PREV.[DATE] = @PREVIOUS_DAY
LEFT JOIN
(fdSECINFO SI (NOLOCK) JOIN fdDIV RD (NOLOCK) ON SI.SEC_ID = RD.SEC_ID)
ON SML.SEC_ID = SI.SEC_RELATED AND ISNULL(SI.SEC_RELATED, -1) != 1 AND
RD.XDATE = CONVERT(VARCHAR(10), @DATE, 101)
WHERE EXCH.CALENDAR = 'USA'
AND CURR.[DATE] = @DATE
AND CURR.VALUE IS NOT NULL AND PREV.VALUE IS NOT NULL
AND R.FREQUENCY = @FREQUENCY
AND 1 = CASE WHEN @FREQUENCY = 'W'
THEN ( SELECT 1 WHERE CURR.DATA_ID IN (SELECT DATA_ID FROM
fdINDDATA_DAILY D (NOLOCK)))
ELSE ( SELECT 1)
END



Execution Plan:

<?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.1399.06"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";>
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="10" StatementEstRows="1"
StatementId="2" StatementOptmLevel="FULL"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
StatementSubTreeCost="0.0132875" StatementText="INSERT INTO @SEC_LIST
SELECT ItemValue
FROM dbo.fn_SplitIn2Rows( @SEC_IDS, ',')


-- ** US Tickers ** --
" StatementType="INSERT">
<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="20">
<RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Sequence"
NodeId="0" Parallel="false" PhysicalOp="Sequence"
EstimatedTotalSubtreeCost="0.0132875">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sequence>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06"
EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Table-valued function" NodeId="1" Parallel="false"
PhysicalOp="Table-valued function"
EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0"
ActualRebinds="1"
ActualRewinds="0" ActualRows="0" ActualEndOfScans="0"
ActualExecutions="1"
/>
</RunTimeInformation>
<TableValuedFunction>
<DefinedValues />
<Object Database="[FundData]" Schema="[dbo]"
Table="[fn_SplitIn2Rows]" />
<ParameterList>
<ScalarOperator ScalarString="[@SEC_IDS]">
<Identifier>
<ColumnReference Column="@SEC_IDS" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="','">
<Const ConstValue="','" />
</ScalarOperator>
</ParameterList>
</TableValuedFunction>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1E-06"
EstimateIO="0.01"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Insert"
NodeId="4" Parallel="false" PhysicalOp="Table Insert"
EstimatedTotalSubtreeCost="0.0132843">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update>
<Object Table="[@SEC_LIST]" />
<SetPredicate>
<ScalarOperator ScalarString="[SEC_ID] =
[Expr1006]">
<ScalarExpressionList />
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="11" EstimateCPU="1E-07"
EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top"
EstimatedTotalSubtreeCost="0.0032833">
<OutputList>
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0"
ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false"
WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="1E-07"
EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Compute Scalar" NodeId="6" Parallel="false"
PhysicalOp="Compute
Scalar" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator
ScalarString="CONVERT_IMPLICIT(int,[FundData].[dbo].[fn_SplitIn2Rows].[ItemValue],0)">
<Convert DataType="int" Style="0"
Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[FundData]" Schema="[dbo]" Table="[fn_SplitIn2Rows]"
Column="ItemValue" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="511"
EstimateCPU="0.0001581"
EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0"
EstimateRows="1" LogicalOp="Table Scan" NodeId="7" Parallel="false"
PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[FundData]"
Schema="[dbo]" Table="[fn_SplitIn2Rows]" Column="ItemValue" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0"
ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false"
ForcedIndex="false"
NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[FundData]"
Schema="[dbo]" Table="[fn_SplitIn2Rows]" Column="ItemValue" />
</DefinedValue>
</DefinedValues>
<Object Database="[FundData]"
Schema="[dbo]"
Table="[fn_SplitIn2Rows]" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</Sequence>
</RelOp>
<ParameterList>
<ColumnReference Column="@SEC_IDS"
ParameterRuntimeValue="'2'"
/>
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



"Andrew J. Kelly" wrote:

The optimizer has changed between 2000 and 2005 so there will
certainly
be
some types of queries that are better optimized and some that are not.
If
either of you can provide the exact code and the execution plans for
both
(preferably xml for 2005) I can give them to the dev team to look at.
If
there is a problem with how the optimizer is dealing with these it
they
will
want to know about it and there will be a better chance of fixing it.
That
is if it is broke to begin with. It is quite possible a matter of
implementation.

--
Andrew J. Kelly SQL MVP


"Damian" <damiank@xxxxxxxxxxxxxxx> wrote in message
news:1141354358.202211.278990@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
We have just upgraded our development server to sql sever 2005. Dual
processor, 4GB RAM, 64 bit etc.

We also noticed an incredible difference in the execution of stored
procedures that are using correlated subqueries. An interesing note
is
that while rewriting one of these stored procedures I created an
outer
join to a derived table (the replacement) and while verifying the
results, my performance was back, and the data was correct in both
instances. Comment out the derived table, performance deserts us.

Another interesting thing was pointed out to me today by a
co-developer
(as I was going through and re-writing a stack of these) is that an
ad
hoc query built and then executed with the EXEC command did not
suffer
from the same performance problem.


.

Relevant Pages

  • Re: Poor performance after upgrading to sql server 2005
    ... in the exact same database with the exact same indexes runs a lot slower ... SQL 2005 on a machine which is much, much faster in every way than the SQL ... As I stated the optimizer has changed between the two versions and in most ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Can relational alegbra perform bulk operations?
    ... learn is to refrain from coding in such a way as to gove implicit "hints" to the optimizer in the way they express their queries. ... I do cringe a little when I see all those fancy "hints" which I've never really seen the occasion to use. ... Well, as I agreed with you, one shouldn't really be fiddling with the engine, but as I remarked in an earlier post if we were to port a complex database project from say, SQL Server to Oracle, and even if we didn't use any vendor-specific features, several SQL rewriting may be warranted simply because of performance differences due to the differences of how a SQL statement is parsed & optimized between two engines. ...
    (comp.databases.theory)
  • Performance: Query optimizer producing sub-optimal result?
    ... As part of a suite of SQL statements I am trying to write a data integrity ... SELECT UID, fnCheck ... The optimizer seems to move the "DISTINCT" action in the subquery to the end ... Define a View which performs the SELECT DISTINCT part of option A. My SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: Poor performance after upgrading to sql server 2005
    ... We didn't just "throw the code at the optimizer", ... have to do the same thing for SQL 2005? ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Poor performance after upgrading to sql server 2005
    ... Andrew J. Kelly SQL MVP ... problem if the optimizer could not handle subqueries but I know for a ...
    (microsoft.public.sqlserver.setup)