Post reply

Sargable condition slower than non-sargable...why?

  • July 22, 2004 at 10:32 am

    #515726

    Test server - the two queries look to have about the same execution plan. The Prod server sticks a sort order in as the second step where the other 3 plans do not.

    =============================================================

    slow

    StmtText

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

    SELECT

    Shifts.dtShiftDate,

    Shifts.chMachineID,

    Shifts.siShiftNo,

    Shifts.vchOperation,

    Parts.vchPlatform,

    Parts.vchOpening,

    Parts.vchPlatform+' '+vchopening,

    rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),

    Shifts.i

    (1 row(s) affected)

    StmtText

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

    |--Compute Scalar(DEFINE[Expr1044]=[Parts].[vchPlatform]+' '+[Parts].[vchOpening], [Expr1045]=rtrim([Parts].[vchDescription])+' - '+rtrim(ltrim(Convert([Parts].[chPartID]))), [Expr1046]=[Shifts].[intPressCycles]*Convert([Operations].[bitIncludeInMachin

    |--Compute Scalar(DEFINE[Expr1028]=isnull([Shifts].[intTimeDown], 0), [Expr1029]=isnull([Shifts].[intScrap], 0), [Expr1030]=isnull([Shifts].[intRings], 0), [Expr1033]=[Expr1033], [Expr1041]=If (Convert([Operations].[bitIncludeInMachineHrs])=1) the

    |--Nested Loops(Inner Join, OUTER REFERENCES[Shifts].[chPartID], [Shifts].[dtShiftDate]) WITH PREFETCH)

    |--Nested Loops(Inner Join, OUTER REFERENCES[Shifts].[chPartID], [Shifts].[chMachineID]))

    | |--Nested Loops(Inner Join, OUTER REFERENCES[Shifts].[chPartID]))

    | | |--Sort(ORDER BY[Shifts].[dtShiftDate] ASC, [Shifts].[chMachineID] ASC))

    | | | |--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, WHEREIsFalseOrNull(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[Expr1033]=If ([Expr1094]=0) then NULL else [Expr1095]))

    | | | | | |--Hash Match(Aggregate, HASH[OperatorLogins].[chShiftID]), RESIDUAL[OperatorLogins].[chShiftID]=[OperatorLogins].[chShiftID]) DEFINE[Expr1094]=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], [Expr1092]), RESIDUAL[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dt

    | | | | |--Clustered Index Scan(OBJECT[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616]))

    | | | | |--Compute Scalar(DEFINE[Expr1092]=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, [Union1024] 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]), WHEREConvert([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]), WHEREConvert([Shifts].[dtShiftDate])>dateadd(month, -5, getdate()-Convert(datepart(day, getdate()))+'Ja

    | | | |--Hash Match(Cache, HASH[Shifts].[chShiftID]), RESIDUAL[Shifts].[chShiftID]=[Shifts].[chShiftID]))

    | | | |--Stream Aggregate(DEFINE[Expr1038]=MAX([OperatorLogins].[dtLogout]), [Expr1039]=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].[Parts].[PK__Parts__7D78A4E7] AS [Parts]), SEEK[Parts].[chPartID]=[Shifts].[chPartID]) ORDERED FORWARD)

    | |--Clustered Index Seek(OBJECT[ShopFloor].[dbo].[PartMachineAvailability].[PK_PartMachineAvailability] AS [PartMachineAvailability]), SEEK[PartMachineAvailability].[chPartID]=[Shifts].[chPartID] AND [PartMachineAvailability].[chMa

    |--Clustered Index Seek(OBJECT[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616] AS [PartMetrics]), SEEK[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dtMonthYear]=[Shifts].[dtShiftDate]-Convert(datepart(day,

    (33 row(s) affected)

    ================================================================

    fast

    StmtText

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

    SELECT

    Shifts.dtShiftDate,

    Shifts.chMachineID,

    Shifts.siShiftNo,

    Shifts.vchOperation,

    Parts.vchPlatform,

    Parts.vchOpening,

    Parts.vchPlatform+' '+vchopening,

    rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),

    Shifts.i

    (1 row(s) affected)

    StmtText

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

    |--Compute Scalar(DEFINE[Expr1044]=[Parts].[vchPlatform]+' '+[Parts].[vchOpening], [Expr1045]=rtrim([Parts].[vchDescription])+' - '+rtrim(ltrim(Convert([Parts].[chPartID]))), [Expr1046]=[Shifts].[intPressCycles]*Convert([Operations].[bitIncludeInMachin

    |--Compute Scalar(DEFINE[Expr1028]=isnull([Shifts].[intTimeDown], 0), [Expr1029]=isnull([Shifts].[intScrap], 0), [Expr1030]=isnull([Shifts].[intRings], 0), [Expr1033]=[Expr1033], [Expr1041]=If (Convert([Operations].[bitIncludeInMachineHrs])=1) the

    |--Nested Loops(Inner Join, OUTER REFERENCES[Shifts].[chPartID], [Shifts].[dtShiftDate]) WITH PREFETCH)

    |--Nested Loops(Inner Join, OUTER REFERENCES[Shifts].[chPartID], [Shifts].[chMachineID]))

    | |--Nested Loops(Inner Join, OUTER REFERENCES[Shifts].[chPartID]))

    | | |--Sort(ORDER BY[Shifts].[dtShiftDate] ASC, [Shifts].[chMachineID] ASC))

    | | | |--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, WHEREIsFalseOrNull(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[Expr1033]=If ([Expr1096]=0) then NULL else [Expr1097]))

    | | | | | |--Hash Match(Aggregate, HASH[OperatorLogins].[chShiftID]), RESIDUAL[OperatorLogins].[chShiftID]=[OperatorLogins].[chShiftID]) DEFINE[Expr1096]=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], [Expr1094]), RESIDUAL[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dt

    | | | | |--Clustered Index Scan(OBJECT[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616]))

    | | | | |--Compute Scalar(DEFINE[Expr1094]=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, [Union1024] 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]), WHEREConvert([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(Convert([Shifts].[dtShiftDate])>dateadd(month, -5, getdate()-Convert(datepart(day, getdate()))+'J

    | | | |--Hash Match(Cache, HASH[Shifts].[chShiftID]), RESIDUAL[Shifts].[chShiftID]=[Shifts].[chShiftID]))

    | | | |--Stream Aggregate(DEFINE[Expr1038]=MAX([OperatorLogins].[dtLogout]), [Expr1039]=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].[Parts].[PK__Parts__7D78A4E7] AS [Parts]), SEEK[Parts].[chPartID]=[Shifts].[chPartID]) ORDERED FORWARD)

    | |--Clustered Index Seek(OBJECT[ShopFloor].[dbo].[PartMachineAvailability].[PK_PartMachineAvailability] AS [PartMachineAvailability]), SEEK[PartMachineAvailability].[chPartID]=[Shifts].[chPartID] AND [PartMachineAvailability].[chMa

    |--Clustered Index Seek(OBJECT[ShopFloor].[dbo].[PartMetrics].[PK__PartMetrics__1CBC4616] AS [PartMetrics]), SEEK[PartMetrics].[chPartID]=[Shifts].[chPartID] AND [PartMetrics].[dtMonthYear]=[Shifts].[dtShiftDate]-Convert(datepart(day,

    (33 row(s) affected)

  • July 22, 2004 at 1:46 pm

    #515758

    Aaron,

    Stupid question of the day! How do you get the showplan to appear in text format?


    Terry

  • July 22, 2004 at 3:08 pm

    #515774

    Run your query in query analyser as normal with text output, but wrap it with the following statements

    SET SHOWPLAN_TEXT ON

    GO

    SELECT ....

    GO

    SET SHOWPLAN_TEXT OFF

    GO

    It basically shows the plan without actually running the query. I don't know if this is the same thing as the execution plan or the estimated execution plan. I didn't see anything regarding execution costs.

    Aaron

  • July 23, 2004 at 1:20 am

    #515798

    I found that the best way of comparing text execution plans was to export them into text files then import them into source safe and use the compare function.

    Crude I know, but what the hell.

    LinkedIn Profile

  • July 26, 2004 at 6:53 pm

    #516185

    AAron Run UPDATE STATISTICS with fullscan on your Production BOX

    Run the queries and let me know


    * Noel

  • July 26, 2004 at 8:15 pm

    #516200

    Thanks for the tip, but no dice Noel. I tried that once before on all 5 joined tables, but I did it again just now WITH FULLSCAN to no avail.

    Aaron

  • July 27, 2004 at 11:34 am

    #516364

    Aaron I suggested that because of the fact that you mentioned that no index was present on the searched column. Usually after statistics are updated if the column is selective enough the execution plan gets better at assuming query costs. I will look in more detail at what you posted and let you know

     


    * Noel

  • July 30, 2004 at 8:03 am

    #517033

    Thanks for everyone's consideration. I might put a support call in to MS because I can't resolve it. Facts:

    1. Original Excel query always runs fast in test server, ~5 seconds
    2. Original Excel query used to run fast in prod server, now takes 17 minutes.
    3. Tweaked Excel query runs fast on prod server
    4. Execution plans from test and tweaked prod are the same
    5. GUI Execution plan for original Excel query in prod (slow) is missing a sort as the third step in execution plan...differs in other ways being more linear/horizontal.
    6. Test database restored from production with same size and attributes
    7. Prod database exhibits the same behavior with 1 CPU turned off
    8. Prod database exhibits same behavior with an added restore of the database, identical to one sent to test.
    9. Prod database exhibits same behavior with zero outside utilization.
    10. Prod and Test are both dedicated SQL servers of the same patch, OS, and OS patch versions.
    11. Updating the statistics with full scan on all base tables involved in query made no difference
    12. Rebuilding clustered indexes on the tables with zero free space and 10% free space all had negligible impact.

  • July 30, 2004 at 8:49 am

    #517045

    it is feasible....

    either to swop the test server into place of the prod server?

    or to cut another server to 'ghost' the test server configuration...with the production name...and just dump the problematic server....and write this off as a bad experience?

  • July 30, 2004 at 9:25 am

    #517056

    The worst part about this is that the prod server is BRAND NEW and this is the first of our plethora of SQL apps to consolidate onto the new hardware. Batting 0/1 so far lol. Plus we bought 2 per cpu SQL licenses, so one would be wasted by using the single CPU box.

    Currently we have shared app/db servers, and to reduce support overhead we are consolodating to a more controlled environment on the DB end. In theory it is the right thing to do but how can I justify it with this performance flakyness right off the bat. In reality support overhead has skyrocketed since I've spent the better part of this week troubleshooting this and still no good solution in sight.

  • August 19, 2004 at 7:16 am

    #519763

    Well, after 2 weeks of running on a new server with the same hardware (dual processors enabled) the performance problem has never returned. I still don't know what the problem was, must have been something in the config or a corrupt install of SQL itself. I sure hope that problem does not come back!

  • August 21, 2004 at 7:15 am

    #520090

    Same number of processors?

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • August 23, 2004 at 6:40 am

    #520150

    Yes, absolutely identicle hardware including same dual CPU setup, same speed and configuration of drives in the 8 disk scsi array, ram, etc.

  • September 7, 2004 at 7:37 am

    #521955

    To all:

    Thank you for your time and ideas. After Microsoft analyzed the issue and reproduced it on their end, this behavior was deemed a bug. The temporary work around is to disable Table Spooling using trace flag 8690. I'll let you know if there are any further developments.

    -Aaron

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply