Compile lock problems on a specific SP

Question

Post reply

Compile lock problems on a specific SP

ERIC CRUDELI

SSCommitted

Points: 1786
More actions
February 27, 2014 at 6:21 am

#282953

Hi,
Sometime, we have been experienced compile lock problems on a specific SP (About 70 locks but changed all the time). The SP's duration is about 30 sec.
The SP doesn't use the option with recompile. The SP is called with parameters (execute DPLAN_V3_sPlanningDetailsListe @tri=2, @pose_verrou=0, @int_id=7416, @date_deb='25/02/2014 06:00:00', @date_fin='25/02/2014 14:00:00', @range='2039952_25/02/2014 10:00:00_0_0&').
spid blocked waittype waittime lastwaittype waitresource
---- ------- -------- -------- ------------ -------------------------
221 29 0x000e 2141 LCK_M_X TAB: 6:834102 [[COMPILE]]
228 29 0x000e 2235 LCK_M_X TAB: 6:834102 [[COMPILE]]
29 214 0x000e 3937 LCK_M_X TAB: 6:834102 [[COMPILE]]
13 214 0x000e 1094 LCK_M_X TAB: 6:834102 [[COMPILE]]
68 214 0x000e 1968 LCK_M_X TAB: 6:834102 [[COMPILE]]
214 0 0x0000 0 LCK_M_X TAB: 6:834102 [[COMPILE]]
I checked the query plan and the part takes time (98%) is :
update @secto_pat
set SECT_ID = s.SECT_ID
, ORDRE = s.ORDRE
, PAT_NOM = s.PAT_NOM
, PAT_PRE = s.PAT_PRE
, PAT_DDN = s.PAT_DDN
, SIT_COD = s.SIT_COD
, UF_COD = s.UF_COD
, NUM_LIT = s.NUM_LIT
, SECT_NOM = s.sect_nom
, PAT_IPP = s.PAT_IPP
from @secto_pat p, NOYAU_VUE_SECTO_ACTIVE s WITH (NOLOCK)
-- SVE 19/06/2007 where s.int_id = p.int_id
where s.int_id = @int_id
and s.pat_id = p.pat_id
PS : NOYAU_VUE_SECTO_ACTIVE is a view (600 000 rows)
When the problem cames, I checked CPU, QUEUE CPU LENGHT, MEMORY and other parameters and I saw nothing special. The only option I have, it is to restart SQL server engine. After restart, the SP takes no more than 2 sec.
We have Physical server (16 CPUs, 64Go with 58Go allocated for SQL, Operating System W2K3 SP2 Enterprise Edition 64bits, SQL Server 2005 SP3 Enterprise Edition 64Bits).
The only thing I can say, when the problem occured we had 2200 SQL users connections and CPU increased to 85%. Honestly I'm losted.
I saw some explanation on Web and I modified some parameters on SQL server :
- min memory query 1024 to 2048
- max memory server 57000 to 58000
- max thread 704 (by default) to 1024
But I'm not sure if I found the solution. Could you help me ?
Thanks,
Eric

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 1

ERIC CRUDELI

SSCommitted

Points: 1786

February 28, 2014 at 2:06 am

#1692972

Hello mate,

No track :hehe:

Thanks,

Eric

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Do you do Index Maintenance and index rebuilds ? Also see if you can find any missing indexes.

Is this 32 bit or 64 bit ? What are your memory settings

--

SQLBuddy

Lowell SSC Guru Points: 323518 More actions · Answer 3

i believe it's bad syntax, which can result in an accidental cross join.

you are updating a table variable, but it is ALIASED elsewhere; because it is not explicitly named, there's an implied FROM statement in there.

edit: an update statement ALWAYS ignores a WITH NOLOCK hint, which is a bad habit in the first place.

from @secto_pat p, NOYAU_VUE_SECTO_ACTIVE s[,@secto_pat] WITH (NOLOCK)

since it's implied, but the WHERE statement doesn't limit the results, it's a cross join.

the correct syntax should update the Alias.

UPDATE p

SET p.SECT_ID = s.SECT_ID,

p.ORDRE = s.ORDRE,

p.PAT_NOM = s.PAT_NOM,

p.PAT_PRE = s.PAT_PRE,

p.PAT_DDN = s.PAT_DDN,

p.SIT_COD = s.SIT_COD,

p.UF_COD = s.UF_COD,

p.NUM_LIT = s.NUM_LIT,

p.SECT_NOM = s.sect_nom,

p.PAT_IPP = s.PAT_IPP

FROM

@secto_pat p,

NOYAU_VUE_SECTO_ACTIVE s,

[@secto_pat]

-- SVE 19/06/2007 where s.int_id = p.int_id

WHERE s.int_id = @int_id

AND s.pat_id = p.pat_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

also, if your table variable has more than a few hundred rows, switching it to a temp table allows SQL to use automatically created statistics on that table in order to improve performance. change @secto_pat to #secto_pat, and wherever else it's created in your procedure.

edit: just read that one of the tables is a view; that will create a crappy plan too, since the view is grabbing columns and creating joins that are probably not needed as far as this specific update.

; if you can update directly from the underlying tables, and eliminate unnecessary joins, that would speed this up too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 5

Do you do Index Maintenance and index rebuilds ? Yes, I do just before issue.

Also see if you can find any missing indexes. No missing indexes

Is this 32 bit or 64 bit ? 64bits

What are your memory settings ? Max memory setting fo SQL 50000 Mo

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 6

Hi Lowell,

Thanks for your Help guy.

I know if you use a complicated view (it's a case), you can get a crappy plan. Another thing, on view you can't create indexes, is it correct ?

I have to check number of rows used by the table variable and see if it's a good thing to switch to a temp table.

If I saw yours differents tracks, tuning sql server parameters (min memory query 1024 to 2048

and max thread 704 by default to 1024) bring nothing and don't solve my issue. Is it correct ?

Another thing, parameter sniffing could be generated this kind of issue. What do you thing ?

Regards,

Eric