Re: Poor performance after upgrading to sql server 2005

I know we can change our code to work with SQL 2005, but I don't think there
is anything unusual about our database code -- subqueries are not uncommon or
even discouraged by Microsoft and table variables are actually encouraged
over temporary tables -- not to mention using simple data type variables in
sql queries. I'm sure a lot of people will run into performance problems like
ours. This could be a real problem for Microsoft.

Rich Wood

"Andrew J. Kelly" wrote:


http://www.support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS

They will require a credit card for a charge of around $225.00 US but it
will be refunded if this is a bug.

--
Andrew J. Kelly SQL MVP


"Rich Wood" <RichWood@xxxxxxxxxxxxxxxx> wrote in message
news:8E66D323-C73B-4440-8272-3317F74F6DF3@xxxxxxxxxxxxxxxx
That would be great -- how do I open a case with MS PSS? Thanks again for
your suggestions.

Rich Wood

"Andrew J. Kelly" wrote:

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"
.

Relevant Pages