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

Compile lock problems on a specific SP Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 6:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 68, Visits: 338
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


Post #1545828
Posted Friday, February 28, 2014 2:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 68, Visits: 338
Hello mate,

No track

Thanks,

Eric
Post #1546228
Posted Monday, March 3, 2014 11:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1547036
Posted Monday, March 3, 2014 11:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 12,909, Visits: 32,012
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1547040
Posted Monday, March 3, 2014 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 12,909, Visits: 32,012
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1547043
Posted Thursday, March 6, 2014 12:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 68, Visits: 338
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

Post #1548091
Posted Thursday, March 6, 2014 12:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 68, Visits: 338
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


Post #1548094
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse