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 ««123»»

Resolving a deadlock Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 1:18 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 4,348, Visits: 6,157
My vote is for the huge covering index being the culprit here...

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1427607
Posted Wednesday, March 6, 2013 1:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
These are the two indexes involved in the deadlock:

CREATE UNIQUE CLUSTERED INDEX [CI_Patient_PracticeID_PatientID] ON [dbo].[Patient] 
(
[PracticeID] ASC,
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Patient]...
...
CONSTRAINT [PK_Patient] PRIMARY KEY NONCLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1427610
Posted Wednesday, March 6, 2013 1:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
One of my thoughts on the indexes was to Make the Primary Key PK_Patient the Clustered index and then make the Practice_patientid index only a nonclustered index on the practiceID.
Post #1427623
Posted Wednesday, March 6, 2013 3:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
Sorry About that here is the code:
CREATE PROCEDURE dbo.PatientDataProvider_UpdatePatient
@patient_id INT,
@prefix VARCHAR(16),
@first_name VARCHAR(32),
@middle_name VARCHAR(32),
@last_name VARCHAR(32),
@suffix VARCHAR(16),
@address_1 VARCHAR(128) = NULL,
@address_2 VARCHAR(128) = NULL,
@city VARCHAR(32) = NULL,
@state VARCHAR(2) = NULL,
@country VARCHAR(32) = NULL,
@zip VARCHAR(9) = NULL,
@gender VARCHAR(1) = 'U',
@marital_status VARCHAR(1) = 'U',
@home_phone VARCHAR(10) = NULL,
@home_phone_x VARCHAR(10) = NULL,
@work_phone VARCHAR(10) = NULL,
@work_phone_x VARCHAR(10) = NULL,
@dob DATETIME = NULL,
@ssn CHAR(9) = NULL,
@email VARCHAR(128) = NULL,
@sendEmailCorrespondence BIT = 1,
@guarantor_different BIT = 0,
@employment_status CHAR(1) = NULL,
@referrer_id INT = NULL,
@primary_provider_id INT = NULL,
@patient_referral_source_id INT = NULL,
@patient_default_location_id INT = NULL,
@employer_id INT = NULL,
@medical_record_number VARCHAR(128) = NULL,
@mobile_phone VARCHAR(10) = NULL,
@mobile_phone_x VARCHAR(10) = NULL,
@primary_care_physician_id INT = NULL,
@modified_user_id INT = 0,
@active BIT,
@PhonecallRemindersEnabled BIT = 0,
@EmergencyName VARCHAR(128) = NULL,
@EmergencyPhone VARCHAR(10) = NULL,
@EmergencyPhoneExt VARCHAR(10) = NULL
AS
BEGIN

IF @email IS NULL OR LTRIM(@email) = ''
BEGIN
SET @sendEmailCorrespondence = 0 ;
END

UPDATE dbo.Patient
SET Prefix = @prefix,
FirstName = @first_name,
MiddleName = @middle_name,
LastName = @last_name,
Suffix = @suffix,
AddressLine1 = @address_1,
AddressLine2 = @address_2,
City = @city,
State = @state,
Country = @country,
ZipCode = @zip,
Gender = @gender,
MaritalStatus = @marital_status,
HomePhone = @home_phone,
HomePhoneExt = @home_phone_x,
WorkPhone = @work_phone,
WorkPhoneExt = @work_phone_x,
DOB = @dob,
SSN = @ssn,
EmailAddress = RTRIM(LTRIM(@email)),
SendEmailCorrespondence = @sendEmailCorrespondence,
ResponsibleDifferentThanPatient = @guarantor_different,
EmploymentStatus = @employment_status,
ReferringPhysicianID = @referrer_id,
PrimaryProviderID = @primary_provider_id,
PatientReferralSourceID = @patient_referral_source_id,
DefaultServiceLocationID = @patient_default_location_id,
EmployerID = @employer_id,
MedicalRecordNumber = @medical_record_number,
MobilePhone = @mobile_phone,
MobilePhoneExt = @mobile_phone_x,
PrimaryCarePhysicianID = @primary_care_physician_id,
ModifiedDate = GETDATE(),
ModifiedUserID = @modified_user_id,
Active = @Active,
PhonecallRemindersEnabled = @PhonecallRemindersEnabled,
EmergencyName = @EmergencyName,
EmergencyPhone = @EmergencyPhone,
EmergencyPhoneExt = @EmergencyPhoneExt
WHERE PatientID = @patient_id ;
END

Post #1427656
Posted Wednesday, March 6, 2013 3:20 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 4,348, Visits: 6,157
So, you update the DOB with the sproc UPDATE statement, and then the trigger updates the DOB too (after removing the time)? Uh, maybe just strip off the time before you do the sproc UPDATE and eliminate the trigger?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1427666
Posted Wednesday, March 6, 2013 3:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
That would be ideal. The problem is there are many places that update or insert into the patient table. And no one can tell me all the places. They may not all be in stored procs. So If I don't have the trigger it could be missed somewhere and cause other issues.
Post #1427668
Posted Wednesday, March 6, 2013 3:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Just had this same question on another thread with a trigger and doing a large insert...almost verbatim from the other thread:

One thing I can say is that if you were to alter your trigger to skip the update and you supplied the correct DOB in your INSERT statement you would save yourself a ton of I/O. You would need to do some impact analysis however to ensure a change like this would not compromise your data should other inserts or updates supply invalid values for that column thereby circumventing the usefulness of the trigger. If that were a concern there are other things you could do with CONTEXT_INFO to skip the work in the trigger for only your batch process.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1427669
Posted Wednesday, March 6, 2013 3:34 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
How do you determine the Context_info? I don't understand how I could use that to avoid the trigger?
Post #1427672
Posted Wednesday, March 6, 2013 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
You would modify your proc to set CONTEXT_INFO before you did the UPDATE.

SET CONTEXT_INFO 0x1256698456

Then in your trigger you would check CONTEXT_INFO and if you found it had the same value you set in your proc you would skip the UPDATE:

SET @var = CONTEXT_INFO();
IF @var != 0x1256698456
BEGIN
-- do update as usual
END

http://msdn.microsoft.com/en-us/library/ms180125(v=sql.90).aspx



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1427675
Posted Wednesday, March 6, 2013 4:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
Thats worth a shot. I'll do some testing. Thanks
Post #1427680
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse