September 18, 2015 at 7:03 am
Hi
There is one OLTP SQL Server 2008 database affected deadlock issues on daily basis..
Here I have attached system health session xml report,
How come know what type of deadlock raised in database? and How to resolve this issues?
Could anyone give me guideline and suggestion.
Thanks
September 18, 2015 at 8:38 am
Could you post the definitions of the procedures with IDs 263724042 and 594153212 in database 5?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2015 at 7:45 am
Without seeing the queries, it's just general advise. Make sure the objects are accessed in the same order. If you're doing reads then updates, possibly try using UPDLOCK on the reads in order to avoid lock escalation. Make sure the queries are tuned. If they run fast, they won't deadlock.
"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
September 22, 2015 at 3:52 am
Hi.. I am really sorry for late updating due to some another migration task.
Please find the attached these procedures definition which are raised deadlock.
Thanks for your valuable suggestion.
September 22, 2015 at 7:11 am
Plenty of scope for optimisation here - and the faster a batch completes, the less likely it is to cause a deadlock. I'd start with the nested cursor. Resolve your prescriptions string right out into a table, possibly a local temp table, and use it in a set-based fashion. This is what I mean by resoving it right out:
DECLARE @Presc VARCHAR(8000) = 'Tab|Crocin|1-1|Null|Null|3#Tab|Crocin 250|1-5|Null|Null|4'
SELECT d.ID, d.Item, e.*
FROM [dbo].[Split](@Presc,'#') d
CROSS APPLY (
SELECT
col1 = MAX(CASE WHEN ID = 1 THEN Item END),
col2 = MAX(CASE WHEN ID = 2 THEN Item END),
col3 = MAX(CASE WHEN ID = 3 THEN Item END),
col4 = MAX(CASE WHEN ID = 4 THEN Item END),
col5 = MAX(CASE WHEN ID = 5 THEN Item END),
col6 = MAX(CASE WHEN ID = 6 THEN Item END)
FROM [dbo].[il_SplitStringArray](d.Item,'|')
) e
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 24, 2015 at 2:04 am
Hi
Today user reported to me, there is summary report not generated and application side throwing deadlock victim error by using as below view in application side..
So I have created one another view with same content and adding UPLOCK, ROWLOCK hint in wherever mention TABLE NAME and JOIN condition.
After Mentioned that tables HINTS data are fetched and there is no Deadlock issues happened but another transaction got BLOCKED where transaction involving these tables. Pl. guide me how to resolve that BLOCKING issues whenever executing that summary report.
USE [HMS]
GO
/****** Object: View [dbo].[Test_Data_View] Script Date: 09/15/2015 17:32:10 ******/
ALTER VIEW TEST_DATA_VIEW_NEW as
Select
Haemoglobin, Total_WBC_Count, DC, RBC_Count, Platelet_count, HCT, MCV, MCH,
MCHC, Fasting_Blood_Sugar, PP_Blood_sugar, PP_Urine_sugar, Random_Bood_sugar, Random_Urine_sugar, Urine_Acetone,
Fating_blood_sugar, Cholesterol, Triglyceride, HDL_Cholesterol, LDL_Cholesterol, VLDL_Cholesterol, CholHDL_Ratio,
LDLHDL_Ratio, Apolipoprotein_A1, Apolipoprotein_B, Serum_Creatinine, Serum_Uric_acid, BUN, Blood_Urea,
Total_Bilirubin, Direct_Bilirubin, Indirect_Bilirubin, SGOT, SGPT, Alkaline_Phosphatase, Gama_GT, Total_Protein,
Albumin, Globulin, AG_Ratio, S_Calcium, Prostate_Specific_Antigen, Urine_Albumin, Urine_Sugar, Neutrophil_Per,
Lymphocyte_Per, Mid_cell_Per, ESR, FVC_Result, FEV1_Result, PEFR_Result, FEV1_FVC, RDWC, LDWC, ColorVision,
ECGStatus, AudioStatus, XRayStatus, ECG_Status, Audio_Status, VisionStatus, XRay_Status, UrineStatus, LiverStatus,
LipidStatus, RenalStatus,
DO.Height,DO.Weight,
Case When DO.Height Is Not Null And DO.Weight Is Not Null Then
Case When IsNumeric(DO.Height) = 1 And IsNumeric(DO.Weight) = 1
Then
Case When IsNull(DO.Height,0) > 0
Then
Convert(Numeric(10,2),Convert(Numeric(10,2),IsNull(DO.Weight,0))*1.0/Power( Convert(Numeric(10,2),IsNull(DO.Height,0))*1.0/100,2) )
Else 0 End
Else
0
End
Else
0
End
As BMI,
DT.Systolic,DT.Diastolic,DT.Pulse,
DD.CaseNo, DD.RegnNo, DD.Check_DateTime, Convert(Varchar(17),DD.Check_DateTime,113) as Check_Date, Year(DD.Check_DateTime) as CheckYear, Convert(Varchar(17),DD.Ext_Check_Date,113) as ExtChkDate,
PM.Ptn_Name, PM.PtnType, PT.Description as PtnTypeName, PM.Sex, DateDiff(year, PM.dt_birth, GetDate()) As Age, RM.Description as Relation, PM.Dt_Birth,
PM.Alergy, PM.KnownCase,
Case When PM.KnownCase Is Not Null And PM.KnownCase <> '' Then DBO.GetKnownCase_New(PM.KnownCase) Else Null End as KnownCaseDesc,
--DO.KnownCase as KnownCase_Date,
Case When DO.KnownCase Is Not Null And DO.KnownCase <> '' Then DBO.GetKnownCase_Year(PM.CaseNo,Year(DD.Check_DateTime)) Else Null End as KnownCaseDesc_Year,
PM.SpecialRemark, PM.OnTreatment,
CM.Description as ContName, DD.RegLocation, LM.Description as RegLocationName,
DD.RegHospital, DD.CaseType, CT.Description as CaseTypeName,
DD.ExamType, EM.Description as ExamTypeName,
PM.Emp_No, EH.EmpName,EH.Cur_Cadre, EH.Cur_Grade, EH.Company, EH.Divn_Cd, EH.Plant_Cd, EH.Dept_Cd, EH.Section, EH.Sector, EH.Site, EH.Email_ID, EH.Dt_Join,
Case When PD.RegnNo Is Not Null Then 'Yes' Else 'No' End as PME_Decl_Save,
PD.Phone_Dept, PD.Resi_Addr, PD.Resi_Phone, PD.MobileNo, PD.Health_Problem,PD.OtherInfo,PD.Smoking, PD.tob, PD.alc,
BG.Description as BloodGroup,
UM.Login_ID as LoginID, UM.User_Name as CheckBy, HM.Site as RegSite, DG.Description + ' ' + DO.FreeDiag as Diagnosis,
DG.Description as Final_Diagnosis, DO.FreeDiag as Free_Diagnosis,
isnull(hs.description,'') + case when hssub.description is null or hssub.description = '' or hssub.description = 'None' then '' else ' - ' + '' + '
' End +
isnull(DO.HSRemark,'') as HealthStatus, DD.RegType,
Stress_Score, Stress_Level, PFT_Status, TMT_Status, EH.Dt_Leave as ResignDate,
DD.Package_Code, PKM.Package_Name,
--Case When YEAR(DD.Check_DateTime) > 2010 And DD.ExamType = 1 And DD.CaseType = 1 Then (Select Top 1 HS_Score From dbo.Get_Health_Score(DD.RegnNo) Where SrNo=99) Else 0 End as Health_Score,
Test_Data.Health_Score as Health_Score,
--Cl.Test_Result as Contact_Lens,
--Null as Contact_Lens,
(Select Top 1 Test_Result From dbo.test_Result_detail TRD WITH (UPDLOCK, ROWLOCK) Join Test_Result T WITH (UPDLOCK, ROWLOCK) ON T.RegnNo = TRD.RegnNo And T.Sample_No = TRD.Sample_No Where T.RegnNo = DD.RegnNo And (T.Dept_Code In (108,2108,3108,4108,5108,6108,7108,8108,9108,10108,11108,12108,13108,14108,15108,16108,17108,18108,19108,20108,21108,110,2110,3110,4110,5110,6110,7110,8110,9110,10110,11110,12110,13110,14110,15110,16110,17110,18110,19110,20110,21110)) And TRD.Para_Code = 158) as Contact_Lens,
(Select Top 1 Test_Result From dbo.test_Result_detail TRD WITH (UPDLOCK, ROWLOCK) Join Test_Result T WITH (UPDLOCK, ROWLOCK)ON T.RegnNo = TRD.RegnNo And T.Sample_No = TRD.Sample_No Where T.RegnNo = DD.RegnNo And (T.Dept_Code In (1,2001,3001,4001,5001,6001,7001,8001,9001,10001,11001,12001,13001,14001,15001,16001,17001,18001,19001,20001,21001)) And TRD.Para_Code = 35) as Glycosylated_Hb,
(Select Top 1 Test_Result From dbo.test_Result_detail TRD WITH (UPDLOCK, ROWLOCK) Join Test_Result T WITH (UPDLOCK, ROWLOCK) ON T.RegnNo = TRD.RegnNo And T.Sample_No = TRD.Sample_No Where T.RegnNo = DD.RegnNo And (T.Dept_Code In (2,2002,3002,4002,5002,6002,7002,8002,9002,10002,11002,12002,13002,14002,15002,16002,17002,18002,19002,20002,21002)) And TRD.Para_Code = 13) as Fit_For_Respirator_Use,
PD.Location as Work_Location
From DailyCase_Det DD WITH (UPDLOCK, ROWLOCK)
Left Outer Join Test_Data WITH (UPDLOCK, ROWLOCK) On DD.RegnNo = Test_Data.RegnNo
Left Outer Join DailyCase_OPD DO WITH (UPDLOCK, ROWLOCK)
Join dbo.Code_Master DG WITH (UPDLOCK, ROWLOCK) on DG.Hosp_Code = 0
And DG.Type_Code = 23
And DG.Code = DO.DiagCode
Join dbo.Code_Master HS WITH (UPDLOCK, ROWLOCK) on HS.Hosp_Code = 0
And HS.Type_Code = 21
And HS.Code = DO.HealthStatus
Join dbo.Code_Master hssub WITH (UPDLOCK, ROWLOCK) on hssub.Hosp_Code = 0
And hssub.Type_Code = 22
And hssub.Code = DO.HSSub
Join User_Master UM WITH (UPDLOCK, ROWLOCK) ON UM.Login_ID = DO.Modify_By
ON do.regnno = dd.regnno
Left Outer Join PME_Declaration PD WITH (UPDLOCK, ROWLOCK) ON PD.RegnNo = DD.RegnNo
Left Outer Join dbo.DailyCase_TPRBP DT WITH (UPDLOCK, ROWLOCK)ON DT.regnno = dd.regnno
Left Outer Join dbo.Package_master PKM WITH (UPDLOCK, ROWLOCK) ON PKM.Hosp_Code = DD.RegHospital And PKM.Package_Code = dd.Package_Code
Join dbo.Patient_Master PM WITH (UPDLOCK, ROWLOCK) ON PM.CaseNo = DD.CaseNo
Left Outer Join EmpMst_H EH WITH (UPDLOCK, ROWLOCK) ON PM.Emp_No = EH.Emp_No
Join dbo.Code_Master EM WITH (UPDLOCK, ROWLOCK) ON EM.Hosp_Code = DD.RegHospital And EM.Type_Code = 8 And EM.Code = DD.ExamType
Join Hospital_Master HM WITH (UPDLOCK, ROWLOCK) ON HM.Hosp_Code = DD.RegHospital
Left Outer Join dbo.Code_Master CM WITH (UPDLOCK, ROWLOCK) on CM.Hosp_Code = DD.RegHospital
And CM.Type_Code = 10
And CM.Code = PM.ContCD
Join dbo.Code_Master LM WITH (UPDLOCK, ROWLOCK) on LM.Hosp_Code = DD.RegHospital
And LM.Type_Code = 9
And LM.Code = DD.RegLocation
Join dbo.Code_Master CT WITH (UPDLOCK, ROWLOCK) on CT.Hosp_Code = 0
And CT.Type_Code = 2
And CT.Code = DD.CaseType
Join dbo.Code_Master RM WITH (UPDLOCK, ROWLOCK) on RM.Hosp_Code = 0
And RM.Type_Code = 7
And RM.Code = PM.Relation
Join dbo.Code_Master BG WITH (UPDLOCK, ROWLOCK) on BG.Hosp_Code = 0
And BG.Type_Code = 1
And BG.Code = IsNull(PM.Bloodgroup,3)
Join dbo.Code_Master PT WITH (UPDLOCK, ROWLOCK) on PT.Hosp_Code = 0
And PT.Type_Code = 3
And PT.Code = PM.PtnType
GO
select * from TEST_DATA_VIEW_NEW where Check_DateTime >= '01-09-2015' and Check_DateTime <= '23-09-2015' and reghospital = 5
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply