SSMS 17.3 - Execution Plan rendering bug
Using: SSMS 17.3 (version 14.0.17199.0), SQL Server 2017 RTM-CU1 (14.0.3006.16), AdventureWorks2012 sample database.
Also tested with: SSMS 17.3 (version 14.0.17199.0), SQL Server 2017 RTM-CU2 (14.0.3008.27), AdventureWorks2012 sample database.
Query:
INSERT INTO Person.Address
(AddressLine1,
AddressLine2,
City,
StateProvinceID,
PostalCode,
rowguid,
ModifiedDate)
SELECT AddressLine1 + 'x',
AddressLine2,
City,
StateProvinceID,
PostalCode,
NEWID(),
ModifiedDate
FROM Person.Address;
Request an estimated execution plan. OR execute the query with actual execution plan included.
The result has intersecting lines.
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 5
<=-=Dec 1 2017 10:48AM=-=>I produced the same result on SSMS 17.2
<=-=Dec 1 2017 11:03AM=-=>The issue has to do with padding applied to object names. Larger object names receive large amounts of padding, and so if a larger name is rendered above a smaller name, the larger one gets more padding and a correspondingly smaller arrow. This creates the “overlapping” effect. Interestingly since this kind of update plan’s sequence takes place based on the order in which the indexes were created, we can control the overlapping by creating the indexes in a different order. (Feature!)
Setup:
use tempdb
go
create table p
(
i int,
j int,
k int,
l int
)
—Bigger index names before smaller index names…
create index j_bigname_xxx_yyy_zzz_aaa_bbb_ccc_ddd_eee_fff on p (j)
create index k_smaller_xxx_yyy_zzz_aaa on p (k)
create index l_smaller_xxx on p (l)
create index i_xxx on p (i)
create index x on p (i,k)
INSERT p select top(1000000)
a.number,a.number,a.number,a.number
from master..spt_values as a, master..spt_values as b
where a.number > 0
create table p1
(
i int,
j int,
k int,
l int
)
—same indexes, opposite order!
create index x on p1 (i,k)
create index i_xxx on p1 (i)
create index l_smaller_xxx on p1 (l)
create index k_smaller_xxx_yyy_zzz_aaa on p1 (k)
create index j_bigname_xxx_yyy_zzz_aaa_bbb_ccc_ddd_eee_fff on p1 (j)
INSERT p1 select top(1000000)
a.number,a.number,a.number,a.number
from master..spt_values as a, master..spt_values as b
where a.number > 0
create table q (i int)
insert q select number from master..spt_values
-
Now observe the plans from the following logically-identical queries against logically-identical tables [p] and [p1].
delete p
from p as p
where exists (select * from q as q where q.i = p.i)
option (maxdop 1)
delete p1
from p1 as p
where exists (select * from q as q where q.i = p.i)
option (maxdop 1)
-