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

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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)

• tosscrosby

  Hall of Fame

  Points: 3140

  More actions

  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

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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

• David.Poole

  SSC Guru

  Points: 76000

  More actions

  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

• noeld

  SSC Guru

  Points: 96590

  More actions

  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

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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

• noeld

  SSC Guru

  Points: 96590

  More actions

  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

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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.

• AndrewMurphy

  SSCertifiable

  Points: 5586

  More actions

  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?

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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.

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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!

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  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

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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.

• Aaron Myers

  Ten Centuries

  Points: 1139

  More actions

  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