April 17, 2003 at 3:43 am
I have a problem with the following select statement on 4 CPU server with SQL 2000 with SP3.
This select (field names are in our language and maybe will be strange for someone):
SELECT VRSTA_DOK, ID, D_DOK, D_ZAPAD, D_CARINJENJA, BREME, DOBRO, BREME_D, DOBRO_D, OBRAC_L, OBRAC_M, TECAJ, IND_ZAP, OPIS, ST_DOK_PART, SKLIC_MODEL, SKLIC_VSEBINA, UPORABNIK_ID, UPORABNIK_SPR, D_VNOSA, D_SPREMEMBE, DNEVNIK_SA, STATUS_DOK, KONTNI_PLAN, PLAN_ENOTA, STR_NOSILEC, ORG_ENOTA, STR_MESTO, T_T_VRSTA_DOK, T_T_ID, T_POZ_ID, POSL_PARTNER_PLA, KOMERCIALIST, POSL_PARTNER_POS, POSL_PARTNER_RAC, APLIKACIJA, VALUTA, UPORAB, I_ZAVEZANEC_DDV, POPUST, D_IZST_SPREJ, P_ODB_DEL_DDV, PRED_VRSTA_DOK, PRED_ID, DB_VRSTA_DOK, DB_ID, P_POSL_PARTNER, P_ID, UNIQUE_ID, I_PRENOSA_SA, DATUM_PREDPLAC, ZNESEK_PREDPLAC, AV_VRSTA_DOK, AV_ID, I_IZPISA, STATUS_OP, OBRACUN_OBR, D_PODPIS, PODPISNIK, REZ_BREME, REZ_DOBRO, D_KNJ_DDV, ECL_PARITETA, LOKACIJA, ECL, I_ECL_POZ, D_POVEZ_POS
FROM FIDOK_RAC
WHERE DNEVNIK_SA = '3031' AND (OBRAC_L = 2003) AND (OBRAC_M = 3) AND
(I_PRENOSA_SA = '1') AND VRSTA_DOK IN ('72', 'AP', 'BK', 'DK', '01', 'RK', 'RO', 'RY', 'TT')
ORDER BY VRSTA_DOK ASC , ID ASC
gives me this error:
Server: Msg 1205, Level 13, State 1, Line 1
Transaction (Process ID 73) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
When I use just one third fields or if I use fewer constants in the 'in' condition, it works.
Query Execution plan shows me, that SQL decide to use parallelism by sort. So sort is made through Gather Streams and all other data are collected through clustered index seek on primary key.
Executing plan like this:
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------------------------------------------------------------------------------------------
SELECT VRSTA_DOK, ID, D_DOK, D_ZAPAD, D_CARINJENJA, BREME, DOBRO, BREME_D, DOBRO_D, OBRAC_L, OBRAC_M, TECAJ, IND_ZAP, OPIS, ST_DOK_PART, SKLIC_MODEL, SKLIC_VSEBINA, UPORABNIK_ID, UPORABNIK_SPR, D_VNOSA, D_SPREMEMBE, DNEVNIK_SA, STATUS_DOK, KONTNI_PLAN, PL 2 1 0 NULL NULL 1 NULL 68.298843 NULL NULL NULL 1.9483585 NULL NULL SELECT 0 NULL
|--Parallelism(Gather Streams, ORDER BY:([FIDOK_RAC].[VRSTA_DOK] ASC, [FIDOK_RAC].[ID] ASC)) 2 3 1 Parallelism Gather Streams ORDER BY:([FIDOK_RAC].[VRSTA_DOK] ASC, [FIDOK_RAC].[ID] ASC) NULL 68.298843 0.0 3.3453066E-2 594 1.9483517 [FIDOK_RAC].[D_POVEZ_POS], [FIDOK_RAC].[I_ECL_POZ], [FIDOK_RAC].[ECL], [FIDOK_RAC].[LOKACIJA], [FIDOK_RAC].[ECL_PARITETA], [FIDOK_RAC].[D_KNJ_DDV], [FIDOK_RAC].[REZ_DOBRO], [FIDOK_RAC].[REZ_BREME], [FIDOK_RAC].[PODPISNIK], [FIDOK_RAC].[D_PODPIS], [FIDOK_RA NULL PLAN_ROW -1 1.0
|--Clustered Index Seek(OBJECT:([PIS].[dbo].[FIDOK_RAC].[PK_FIDOK_RAC]), SEEK:([FIDOK_RAC].[VRSTA_DOK]='01' OR [FIDOK_RAC].[VRSTA_DOK]='72' OR [FIDOK_RAC].[VRSTA_DOK]='AP' OR [FIDOK_RAC].[VRSTA_DOK]='BK' OR [FIDOK_RAC].[VRSTA_DOK]='DK' OR [FIDOK_RAC 2 4 3 Clustered Index Seek Clustered Index Seek OBJECT:([PIS].[dbo].[FIDOK_RAC].[PK_FIDOK_RAC]), SEEK:([FIDOK_RAC].[VRSTA_DOK]='01' OR [FIDOK_RAC].[VRSTA_DOK]='72' OR [FIDOK_RAC].[VRSTA_DOK]='AP' OR [FIDOK_RAC].[VRSTA_DOK]='BK' OR [FIDOK_RAC].[VRSTA_DOK]='DK' OR [FIDOK_RAC].[VRSTA_DOK]='RK' OR [FIDOK_RA [FIDOK_RAC].[D_POVEZ_POS], [FIDOK_RAC].[I_ECL_POZ], [FIDOK_RAC].[ECL], [FIDOK_RAC].[LOKACIJA], [FIDOK_RAC].[ECL_PARITETA], [FIDOK_RAC].[D_KNJ_DDV], [FIDOK_RAC].[REZ_DOBRO], [FIDOK_RAC].[REZ_BREME], [FIDOK_RAC].[PODPISNIK], [FIDOK_RAC].[D_PODPIS], [FIDOK_RA 68.298843 1.8311713 3.6224548E-2 594 1.8673958 [FIDOK_RAC].[D_POVEZ_POS], [FIDOK_RAC].[I_ECL_POZ], [FIDOK_RAC].[ECL], [FIDOK_RAC].[LOKACIJA], [FIDOK_RAC].[ECL_PARITETA], [FIDOK_RAC].[D_KNJ_DDV], [FIDOK_RAC].[REZ_DOBRO], [FIDOK_RAC].[REZ_BREME], [FIDOK_RAC].[PODPISNIK], [FIDOK_RAC].[D_PODPIS], [FIDOK_RA NULL PLAN_ROW -1 1.0
At the moment I solve this problem so, that I increase cost threshold for parallelism parameter to 25 seconds.
Exist what kind of better solution where cost threshold will be default 5 sec and that lock will not happen? Why SQL, in this case, lock SQL himself. Is this SQL bug?
April 17, 2003 at 5:07 am
I have wrestled with a similar problem before (on SQL 7).
If you add 'OPTION (MAXDOP 1)' to the end of the SQL statement, it forces SQL Server to not use a parallel plan. This only affects this query, not the rest of the server.
I know this isn't an explanation as to why this happens, but it solved our problem.
April 17, 2003 at 5:52 am
the option (maxdop 1) is a fix for that issue and the can't spawn enough threads problem we have run into from time to time. I would look at profiler when you run this and see what else is running when you issue this query, also look to see who is the other query in the deadlock with you. It may be a matter of pulling some rows out or breaking this up into two queries. Or, re-working both queries, this one and the one you are deadlocking with.
Wes
April 17, 2003 at 6:59 am
I've tested this problem about half day on a SQL Server with about 150 and more simultaneous processes. The lock problem has been all the time same regardless of how many active processes have been and what they are doing. I also haven’t noticed deadlocks or locks between SQL users. I have feeling, that SQL start sorting before it has finish reading data.
Last weekend we were transferred SQL server from 2 CPU machine to this 4 CPU machine and from then this problem begins. I never have suchlike problems before. So I think that this lock problem will happen in other SQL statements, also. Some statements I could change, but some I couldn’t, because of they have been generated from applications – like this one.
Toni
That is why I’m looking for solutions on system level. I they don’t exist, than I interesting just for explanation why this lock happens.
April 17, 2003 at 7:08 am
You can set MAXDOP on a whole server basis from the Processor tab on the Server Properties dialog in Enterprise Manager.
But it kind of defeats the object of having multiple processors really.
April 17, 2003 at 7:13 am
Run sp_configure 'max degree of parallelism ', 2 in your case to limit 2 processors to use in parallel plan execution at system level.
In order to get more dead lock information from your application and statements, start dbcc traceon(1204, 3605, -1) to log the dead locks into your SQL Server errorlog. Profiler is another option.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply