Why execution plan change because of date format

• egpotus

  SSCommitted

  Points: 1654

  More actions

  January 7, 2011 at 10:05 am

  #230146

  Hi,

  Can someone tells me why my execution plan changes between this 2 requests :

  UPDATE SysLog SET Hostname ='eub-dc-psesx-07'

  from syslog

  WHERE (DateTime >= 'Jan 2 2011 2:40:50:443AM')

  AND (IP = '10.20.98.156') AND (Hostname = 'eub-dc-psesx-07') AND (EngineID = 1 )

  and

  UPDATE SysLog SET Hostname ='eub-dc-psesx-07'

  from syslog

  WHERE (DateTime >= '2011-01-07 17:25:36.933')

  AND (IP = '10.20.98.156') AND (Hostname = 'eub-dc-psesx-07') AND (EngineID = 1 )

  list of indexes :

  index_name index_description index_keys

  IX_FirstIP nonclustered located on ORION_IDX_FG FirstIPInMessage, DateTime(-)

  IX_Mac nonclustered located on ORION_IDX_FG MacInMessage, DateTime(-)

  IX_SysLog nonclustered located on ORION_IDX_FG DateTime(-), IP, MessageType, Acknowledged

  IX_SysLog_1 nonclustered located on ORION_IDX_FG DateTime(-), SysLogSeverity, SysLogFacility, IP

  IX_SysLogNodeID nonclustered located on ORION_IDX_FG NodeID

  PK_SysLog clustered, unique, primary key located on PRIMARY MsgID(-)

  1st update : clustered index scan 87% on PK_syslog

  2nd update : index seek IX_syslog_1 20% and Key lookup on PK_syslog 20%

  I would like the first update to behave like the 2nd one as far as execution plan is concerned.

  any ideas?

  ---

  Kindest Regards,

  egpotus DBA
  

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  January 7, 2011 at 10:20 am

  #1270771

  Yeah, that's an easy one:

  UPDATE SysLog SET Hostname ='eub-dc-psesx-07'

  from syslog

  WHERE (DateTime >= 'Jan 2 2011 2:40:50:443AM')

  AND (IP = '10.20.98.156') AND (Hostname = 'eub-dc-psesx-07') AND (EngineID = 1 )

  and

  UPDATE SysLog SET Hostname ='eub-dc-psesx-07'

  from syslog

  WHERE (DateTime >= '2011-01-07 17:25:36.933')

  AND (IP = '10.20.98.156') AND (Hostname = 'eub-dc-psesx-07') AND (EngineID = 1 )

  What you're getting are new plans because of those strings. Since nothing is a parameter, SQL Server can't reuse the plan it created for the first query, so the second query creates a new plan. Chances are, the data distribution between those two values is very different, so you're getting a different plan for the second set of values.

  ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

• egpotus

  SSCommitted

  Points: 1654

  More actions

  January 7, 2011 at 10:29 am

  #1270778

  sorry I was not clear, the first update is generated by our monitoring system (ORION) and is slow because of the clustered index scan.

  I realize that by changing the format of the date then the execution plan was better but I don't understand what SQL is doing.

  Event the following update still do clustered index scan

  UPDATE SysLog SET Hostname ='eub-dc-psesx-07'

  from syslog

  WHERE (DateTime >= cast('Jan 2 2011 2:40:50:443AM' as datetime))

  AND (IP = '10.20.98.156') AND (Hostname = 'eub-dc-psesx-07') AND (EngineID = '1' )

  ---

  Kindest Regards,

  egpotus DBA
  

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 7, 2011 at 10:36 am

  #1270781

  A seek is a costly operation. Even 3% of the table is enough to justify a scan (can be even much less than that).

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 7, 2011 at 10:41 am

  #1270784

  Does the same thing happen if you use exactly the same date and time for both queries?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]