Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compile lock problems on a specific SP


Compile lock problems on a specific SP

Author
Message
ERIC CRUDELI
ERIC CRUDELI
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 503
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
ERIC CRUDELI
ERIC CRUDELI
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 503
Hello mate,

No track Hehe

Thanks,

Eric
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38985
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38985
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
ERIC CRUDELI
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 503
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
ERIC CRUDELI
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 503
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search