October 2022 - ozdotnet - codify.mailman3.com

Fwd: Platform x64 argument
by Greg Keogh Oct. 13, 2022

Oct. 13, 2022

> > Do you actually support/ship your app to run on x86-only or ARM > environments? If not, it probably doesn’t matter and I would probably give > this one and save the battles for other things. > All machines involved everywhere are x64. I will not concede defeat in this battle!! -- *Greg*

1 0

Platform x64 argument
by Greg Keogh Oct. 13, 2022

Oct. 13, 2022

Folks, one of my colleagues insists on compiling everything as platform x64 mainly because he thinks "it's an x64 world and it creates a better impression". For a year I've tried to convince him that for managed code that it's a complete waste of time. I've told him that ildasm.exe shows that for x64 and AnyCPU the generated IL and the manifests are identical, I even told him that dumpbin.exe shows the only non trivial difference in the PE headers is a couple of flags that show x86/x64 and PE32/PE32+, but they don't affect the loading and running of a PE containing IL and metadata. Does anyone have paradigm-shattering evidence I can give my colleague to break his habit? (I'm hoping I'm right of course!!) *Greg*

7 6

Resolving a SQL server deadlock
by Tony Wright Oct. 11, 2022

Oct. 11, 2022

Hi there, I am currently trying to resolve a sql server deadlock issue. Someone in the team is adamant that the deadlock is caused by a new index we've added. I'm not sure I've ever heard of an index causing a deadlock, but I'm always interested in something new to me, anyway! I have attached a deadlock graph from SolarWinds Sql Sentry Plan Explorer and I'm hoping someone else can help me understand it. There are essentially 2 queries. (I've renamed all objects) The first query is along the lines of: SELECT DISTINCT * FROM dbo.Shipping WHERE StatusID=@StatusID AND IsLocked=@IsLocked AND TypeID=@TypeID AND ShipDate < @ShipDate. The field list is expanded and there are lots of fields. It was generated inside a third party tool. The second query is an UPDATE statement in a stored proc, along the lines of: UPDATE dbo.Shipping SET IsLocked=@IsLocked, LockedBy=CASE WHEN @Locked=1 THEN @PersonWhoLocked ELSE NULL END, LockedDate=CASE WHEN @Locked=1 THEN GetUtcDate() ELSE ModificationDate END WHERE ShippingID=@ShippingID Process 1 (126) is the SELECT, which is the deadlock victim Process 2 (133) is the UPDATE stored proc Process 3 (139) is the UPDATE stored proc [image: deadlock.png] I'm going to take a stab at what might be happening. Hopefully someone can fill the gaps in my knowledge. A deadlock occurs when Process A has a lock on Resource 1 and wants to acquire a lock on Resource 2, while Process B has a lock on Resource 2 and wants to acquire a lock on Resource 1. So reading this graph, it looks like: The 2 resources are the Page Lock on dbo.Shipping, and the Key Lock on the shipping Primary Key. So on the graph it looks to me like Process 1 holds a lock on the Page Lock resource and wants a lock on the Key Lock. Process 3 holds a lock on the Key lock resource and wants a lock on the Page Lock resource So: (1) The SELECT occurs on the shipping table and it takes a Page lock (S) (2) The first UPDATE occurs (133) and that requires an exclusive lock, but it can't get it because there's already a Shared lock, so it puts in an Intent to get an Exclusive lock (IX) (3) The second UPDATE occurs and it acquires an Exclusive lock (X) on the Primary Key Index. (4) Not sure what happens here and how this is relevant (5) The second UPDATE requests an exclusive lock on the Page of Shipping records. (6) Meanwhile the SELECT tries to get a Shared lock on one of the keys that are locked by the second UPDATE statement Can anyone help me understand what is going on here and how this is going to help me prevent further deadlocks? Kind regards, Tony

2 2

SQL server composite key column order
by Tony Wright Oct. 9, 2022

Oct. 9, 2022

Hi peeps, When setting up composite key indexes in SQL server, I have always thought that it is best to put the column that has the most variation first in the index. I've recently seen articles that claim this is a myth. So does anyone know the the facts here? Is column order important? Regards, Tony

2 1