Re: Query execution plan different between production/test - same

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aaron (Aaron_at_discussions.microsoft.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 06:15:06 -0700

2) Tweaked Query and execution plan on Prod server that runs fast:

SELECT
        Shifts.dtShiftDate,
        Shifts.chMachineID,
        Shifts.siShiftNo,
        Shifts.vchOperation,
        Parts.vchPlatform,
        Parts.vchOpening,
        Parts.vchPlatform+' '+vchopening,
        rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),
        Shifts.intPressCycles,
        Shifts.intPressCycles*bitIncludeInMachineHrs,
        intPressCycles*bitIncludeInMachineHrs*bitIncludePressCycles,
        Shifts.intTimeDown,
        Shifts.intScrap, Shifts.intRings,
        60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs*bitIncludePressCycles,
        60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs,
        60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs*bitIncludePressCycles,
        60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,
        ltrim(datepart(month,dtshiftdate))+' - '+rtrim(datepart(year,dtshiftdate)),
        Shifts.OperatorTimeMins,
        Shifts.MachineHrs*bitIncludeInMachineHrs,
        (Shifts.intPressCycles*bitIncludeInMachineHrs-intRings)*(1-ftyieldgoal/100),
        (intPressCycles*bitIncludeInMachineHrs-intRings)*(1-PartMetrics.ftYieldBud/100),
        MachineHrs*(1-PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,
        PartMetrics.ftCrewSizeBud,
        PartMetrics.intCycleTimeBud,
        Parts.intCycleTimeGoal,
        PartMetrics.ftYieldBud,
        Parts.ftYieldGoal,
        PartMetrics.ftUptimeBud,
        Parts.ftUptimeGoal,
        Parts.ftCrewSizeGoal,
        ftCrewSizeBud*MachineHrs*bitIncludeInMachineHrs,
        ftCrewSizeGoal*MachineHrs*bitIncludeInMachineHrs,
        MachineHrs*(1-ftUptimeGoal/100)*bitIncludeInMachineHrs,
        Shifts.dtShiftDate-datepart(w,dtShiftDate-1)+1,
        PartMachineAvailability.bitIncludePressCycles

FROM ShopFloor.dbo.Operations Operations

INNER JOIN ShopFloor.dbo.vShifts Shifts ON Operations.vchOperation = Shifts.vchOperation AND Shifts.vchOperation<>'DEVELOPMENT'
INNER JOIN ShopFloor.dbo.Parts Parts ON Parts.chPartID = Shifts.chPartID
INNER JOIN ShopFloor.dbo.PartMetrics PartMetrics ON PartMetrics.chPartID = Shifts.chPartID AND (PartMetrics.dtMonthYear=dtshiftdate-datepart(day,dtShiftDate)+1)
INNER JOIN ShopFloor.dbo.PartMachineAvailability PartMachineAvailability
 ON (PartMachineAvailability.chMachineID = Shifts.chMachineID AND PartMachineAvailability.chPartID = Shifts.chPartID)

WHERE (Shifts.dtShiftDate>dateadd(m,-5,getdate()-datepart(d,getdate())+1)-1)

ORDER BY Shifts.dtShiftDate, Shifts.chMachineID

--------------------------------------------------------------------------------------------

 |--Compute Scalar(DEFINE:([Expr1043]=[Parts].[vchPlatform]+' '+[Parts].[vchOpening], [Expr1044]=rtrim([Parts].[vchDescription])+' - '+rtrim(ltrim(Convert([Parts].[chPartID]))), [Expr1045]=[Shifts].[intPressCycles]*Convert([Operations].[bitIncludeInMachin
       |--Sort(ORDER BY:([Shifts].[dtShiftDate] ASC, [Shifts].[chMachineID] ASC))
            |--Compute Scalar(DEFINE:([Expr1027]=isnull([Shifts].[intTimeDown], 0), [Expr1028]=isnull([Shifts].[intScrap], 0), [Expr1029]=isnull([Shifts].[intRings], 0), [Expr1032]=[Expr1032], [Expr1040]=If (Convert([Operations].[bitIncludeInMachineHrs])=1
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Shifts].[chPartID], [Shifts].[dtShiftDate]) WITH PREFETCH)
                      |--Hash Match(Inner Join, HASH:([PartMachineAvailability].[chPartID], [PartMachineAvailability].[chMachineID])=([Shifts].[chPartID], [Shifts].[chMachineID]), RESIDUAL:([Shifts].[chPartID]=[PartMachineAvailability].[chPartID] AND [Part
                      | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[PartMachineAvailability].[PK_PartMachineAvailability] AS [PartMachineAvailability]))
                      | |--Hash Match(Inner Join, HASH:([Parts].[chPartID])=([Shifts].[chPartID]), RESIDUAL:([Parts].[chPartID]=[Shifts].[chPartID]))
                      | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[Parts].[PK__Parts__7D78A4E7] AS [Parts]))
                      | |--Hash Match(Inner Join, HASH:([Operations].[vchOperation])=([Shifts].[vchOperation]), RESIDUAL:([Operations].[vchOperation]=[Shifts].[vchOperation]))
                      | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[Operations].[PK__Operations__76969D2E] AS [Operations]))
                      | |--Nested Loops(Inner Join, WHERE:(IsFalseOrNull(Convert([Operations].[bitIncludeInMachineHrs])=1))OUTER REFERENCES:([Shifts].[chShiftID]))
                      | |--Hash Match(Right Outer Join, HASH:([OperatorLogins].[chShiftID])=([Shifts].[chShiftID]), RESIDUAL:([OperatorLogins].[chShiftID]=[Shifts].[chShiftID]))
                      | | |--Compute Scalar(DEFINE:([Expr1032]=If ([Expr1102]=0) then NULL else [Expr1103]))
                      | | | |--Hash Match(Aggregate, HASH:([OperatorLogins].[chShiftID]), RESIDUAL:([OperatorLogins].[chShiftID]=[OperatorLogins].[chShiftID]) DEFINE:([Expr1102]=COUNT_BIG(datediff(minute, [OperatorLogins].[dtLogin],
                      | | | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[OperatorLogins].[PK_OperatorLogins]), WHERE:([OperatorLogins].[dtLogout]<>NULL))
                      | | |--Hash Match(Right Outer Join, HASH:([Operations].[vchOperation])=([Shifts].[vchOperation]), RESIDUAL:([Operations].[vchOperation]=[Shifts].[vchOperation]))
                      | | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[Operations].[PK__Operations__76969D2E]))
                      | | |--Hash Match(Right Outer Join, HASH:([PartMetrics].[chPartID], [PartMetrics].[dtMonthYear])=([Shifts].[chPartID], [Expr1100]), RESIDUAL:([PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dt
                      | | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616]))
                      | | |--Compute Scalar(DEFINE:([Expr1100]=dateadd(day, -datepart(day, Convert([Shifts].[dtShiftDate]))+1, [Shifts].[dtShiftDate])))
                      | | |--Hash Match(Right Outer Join, HASH:([Union1021])=([Shifts].[intShiftCoordinator]), RESIDUAL:([Union1021]=[Shifts].[intShiftCoordinator]))
                      | | |--Sort(DISTINCT ORDER BY:([Union1018] ASC, [Union1019] ASC, [Union1020] ASC, [Union1021] ASC, [Union1022] ASC, [Union1023] ASC))
                      | | | |--Concatenation
                      | | | |--Compute Scalar(DEFINE:([Expr1012]=rtrim([Employees].[FirstName])+' '+[Employees].[LastName], [Expr1013]=Convert([Employees].[Emp #])+replicate('*', 18-len(Convert([Employees].[
                      | | | | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[Employees].[PK_Employees]), WHERE:(Convert([Employees].[Terminated])<>1))
                      | | | |--Compute Scalar(DEFINE:([Expr1016]=rtrim([IntroAssociates].[FName])+' '+[IntroAssociates].[LName], [Expr1017]=Convert([IntroAssociates].[TempID])+replicate('*', 18-len(Convert([
                      | | | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[IntroAssociates].[PK_IntroAssociates]), WHERE:([IntroAssociates].[Status]='Interim'))
                      | | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[Shifts].[PK__Shifts__0425A276]), WHERE:([Shifts].[vchOperation]<>'DEVELOPMENT' AND Convert([Shifts].[dtShiftDate])>dateadd(month, -5, getd
                      | |--Hash Match(Cache, HASH:([Shifts].[chShiftID]), RESIDUAL:([Shifts].[chShiftID]=[Shifts].[chShiftID]))
                      | |--Stream Aggregate(DEFINE:([Expr1037]=MAX([OperatorLogins].[dtLogout]), [Expr1038]=MIN([OperatorLogins].[dtLogin])))
                      | |--Index Spool(SEEK:([OperatorLogins].[chShiftID]=[Shifts].[chShiftID]))
                      | |--Clustered Index Scan(OBJECT:([ShopFloor].[dbo].[OperatorLogins].[PK_OperatorLogins]))
                      |--Clustered Index Seek(OBJECT:([ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616] AS [PartMetrics]), SEEK:([PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dtMonthYear]=[Shifts].[dtShiftDate]-Convert(datepart(

"Andrew J. Kelly" wrote:

> Can you show the query? You say you tweaked it? How did you do that? Is
> the data the exact same on both machines? What does the query plan look like
> for the slow one compared to the fast one?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Aaron" <Aaron@discussions.microsoft.com> wrote in message
> news:33F423DF-1D9C-4227-B1F4-2CAF5142BD9C@microsoft.com...
> > I have a query that runs much much slower on my production server than my
> test server. By tweaking the query I can make it run fast in production. Why
> can't I make prod act like test? The difference in execution time is 15
> minutes! (test - 5 seconds/prod - 15 minutes). I need to make PROD more like
> TEST, or at least TEST should be slower than PROD (what is the point of load
> testing?)
> >
> > My test server has the same SQL version/patch level and OS version/patch
> level as my production server. The data is a replica. The query execution
> plans are different. The production server has more ram, faster CPUs (dual),
> and faster disk. Production is under light utilization (<20%). It is
> reindexed each night. I have updated statistics on all tables involved. I
> ran a server configuration and schema configuration comparison with RedGate
> software and all items are indenticle (save ram/cpu/names of jobs). The prod
> server has 2 cpu so I tried restricting parallelism to 1. When the slow
> query runs on production it chews one of the CPU's @50% for all 15 minutes.
> The problem has slowly gotten worse of the past month. The database is only
> 120mb and is the only db so far on this server. The DB server is dedicated,
> there are no other uses for it. Its paired app server is under very low
> utilization also. As I said, by tweaking the query the query time goes from
> 15 minutes down to 4 seconds without changing the result set. The main
> gotcha is that my test server runs the fast execution plan with or without
> tweaking the query. I need it to run fast without tweaking so my users can
> run ad hoc queries without me having to get involved. I know it is possible,
> look at my test box!!
> >
> > I am stumped on this performance problem. Please help if you can with any
> thoughts or suggestions for getting MSSQL to run consistant with respect to
> execution plans between my two servers. Our Oracle DBA is laughing...
> >
>
>
>

Relevant Pages

  • Re: Query execution plan different between production/test - same
    ... query to get the correct results and then work from there. ... the query and execution plan that run quickly on my production server ...
    (microsoft.public.sqlserver.server)
  • Re: Adding new row with default values.
    ... The solution based on acquiring default values from the server seems for me ... effectively performs the insert operation using "INSERT INTO" SQL statement. ... > is query the server for the default values of the table, ... > inner join syscolumns as scol on so.id = scol.id ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Query execution plan different between production/test - same
    ... Slow query and execution plan from Prod: ... >> I have a query that runs much much slower on my production server than my ...
    (microsoft.public.sqlserver.server)
  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Query execution plan different between production/test - same
    ... the query and execution plan that runs slowly on my production server. ...
    (microsoft.public.sqlserver.server)