June 18, 2008 at 9:07 am
We experienced several times now compile lock problems on a specific SP. Activity on the server is surging because of the delay caused by this(latch LCK_M_X). If we stop the service and restart it everything is back to normal, no compile locks anymore.
I've searched the Net but can not find a description of this behaviour other then normal explanations like . I have done some checking, the faulty SP is averagely called about 15-20 times per second, it is a parameter-controlled SP. This is running on a SQL2K server with the latest SP.
Greetz,
Hans Brouwer
June 18, 2008 at 9:41 am
Is this recompiling constantly? Or is it set to recompile on every call?
I haven't heard of this, but it might be a PSS call is needed.
June 18, 2008 at 10:03 am
It sounds like Steve nailed it in one.
We had a similar issue, waits for compiles, which lead to other waits for compiles, etc.
Our code was actually compiling due to changes in statistics (bad queries, lots of transactions, bad mix), but you might be seeing traditional recompiles due to interleaved ddl/dml, etc.
"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
June 18, 2008 at 10:59 am
If you can post a copy of the proc, we can probably help you figure out what's causing the recompiles.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 1:43 am
Tnx for answering all.
On posting the SP: it's basically a 'simple' select with 4 parameters. However, the select is on a view, built up from 3 other views, which each are built up again from 1 or more tables. It's a mess, and we have looked at the query with showplan. There were some improvements to be made(indexing, more efficient joins), which we have implemented. Also functionality has been rebuilt, so the SP is called now 2 times per second instead of 15-20 times.
I am very curious though what actually happened. We had this compile situation 3 times in 2 weeks time, no pattern or regular time to be noticed. There is not a significant change in the total of records to be searched. I can think why a (re)compile would happen, but I cannot think of a reason why this situation happens, and why things are back to normal after stopping and starting the service. The log does not help me in anyway.
On making a call to MS Helpdesk: I am not impressed with their expertise. In the last 10-12 years I have put in a call 5 or 6 times. Actually on 1 occasions they were of help and helped me solve a problem. The other times I had found out myself and actually told them what to do in a certain situation. I have not called them in the last 4-5 years. Maybe things have changed, but I have my misgivings.
Greetz,
Hans Brouwer
June 19, 2008 at 6:02 am
It really does sound like recompiles either from causes within the code or due to changes in the data. Especially since it seemed to happen over time and somewhat randomly, it sounds like data updates causing a recompile which causes blocking. From the sounds of it, the recompile time on the original query was excessive. Rewriting it as you've done was certainly one of the correct mitigations. If you run a trace, there is a recompile event that captures when that occurs. You should try it out for a time to see if or when it occurs again.
Microsoft's first line support really is meant to weed out stupid questions, not actually help someone with a legitimate high level problem. Premier Support has some amazing guys who really can figure out just about anything. They also have some total oxygen wasters who send you off to collect data over and over again. If you get involved with MS Support, at any level, make sure you fill out any evaluations they send you or point you to and be brutally honest. It'll help us all in the long run.
"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
June 20, 2008 at 8:39 am
several other things come to mind:
1) use the KEEPFIXED PLAN option that is available in 2000 (btw, you did say 2000 was your version, right? this is the 2005 forum).
2) create multiple sprocs with the same code and names such as mysproc_1, mysproc_2... Use some round-robin code on the front end or some other mechanism to spread out the calls. Someone online demonstrated significant throughput gains using this method, but I can't for the life of me find the link at the moment.
3) are you owner qualifying everything, including the sproc call and also are you declaring it to be a stored procedure execution and not a text execution? See here: http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2008 at 1:01 am
It's SQL2000 allright, I realize this is the wrong forum.
Your ideas are interesting, Guru, I'll give them a try.
Tnx all
Greetz,
Hans Brouwer
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply