Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Why am I getting deadlocks? Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 12:41 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,121, Visits: 11,397
greg.cormier (1/16/2013)
8755 rows, 2 megs of data, 1 meg of index.



That table definition can't be right, it has two primary keys defined.

Post #1408038
Posted Wednesday, January 16, 2013 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:04 AM
Points: 10, Visits: 51
Sorry, yes, ignore the duplicate.

I scripted the table def, then scripted each key. I should have skipped the PK.
Post #1408040
Posted Wednesday, January 16, 2013 12:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,121, Visits: 11,397
As a start, I suggest changing the primary key ,[isel_pk], to clustered with a fill factor of 80 and changing the index [isel1_dx] to a fillfactor of 80.

Also, one of the processes in the dead lock was running with an isolation level of read uncommitted. It is better if you never use that isolation level in a production application.

Post #1408047
Posted Wednesday, January 16, 2013 1:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:04 AM
Points: 10, Visits: 51
Yeah, unfortunately it's out of my control with the vendor, maybe I can yell at them a bit.

I did change a bunch of heaps over, but I figured as this one is 1 megabyte, it shouldn't make a difference. I'll give it a shot and see what happens.
Post #1408052
Posted Wednesday, January 16, 2013 1:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
Agreed on changing the pk to clustered.

Can't tell without lots of analysis, but single column nonclustered indexes are typically not very useful. Maybe check how often those are used?
The index isel1_dx should be widened as follows:

Index key: (assyst_usr_id, isel_name, field_name)




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1408054
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse