January 7, 2011 at 10:05 am
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_descriptionindex_keys
IX_FirstIP nonclustered located on ORION_IDX_FGFirstIPInMessage, DateTime(-)
IX_Mac nonclustered located on ORION_IDX_FGMacInMessage, DateTime(-)
IX_SysLog nonclustered located on ORION_IDX_FGDateTime(-), IP, MessageType, Acknowledged
IX_SysLog_1nonclustered located on ORION_IDX_FGDateTime(-), SysLogSeverity, SysLogFacility, IP
IX_SysLogNodeIDnonclustered located on ORION_IDX_FGNodeID
PK_SysLog clustered, unique, primary key located on PRIMARYMsgID(-)
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?
January 7, 2011 at 10:20 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2011 at 10:29 am
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' )
January 7, 2011 at 10:36 am
A seek is a costly operation. Even 3% of the table is enough to justify a scan (can be even much less than that).
January 7, 2011 at 10:41 am
Does the same thing happen if you use exactly the same date and time for both queries?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 7, 2011 at 10:44 am
3% is treshold, assuming this is an heavy log table, then it could have swapped.
The other option is an implicit cast that forces a scan. I'd need to see the exec plans to confirm.
It could also be plan reuse in your case.
January 7, 2011 at 11:08 am
Yeah, it could be an implicit conversion on the date causing the issue. Either way, because the strings are different and the values are different, you can't guarantee that you'll get the same plan. It doesn't take much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply