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 ««123»»

Serious intermittent performance issues Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 8:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
GilaMonster (12/20/2013)
william.rees.howells (12/20/2013)

[quote]Well, if I use my load testing tool and load it up with about 20-30 concurrent queries running this SP then I can reliably recreate a load of RESOURCE_SEMAPHORE waits. These wait types indicate that this SP is chewing up the memory.


Not necesssarily. Resource Semaphore is about query workspace memory, in this case probably the space the optimizer needs to compile. The waits indicate that queries want workspace memory and aren't getting it, that can be caused by having lots and lots of concurrent queries wanting memory grants



One correction here, RESOURCE_SEMPHORE is execution workspace memory only, associated with hash, sort, bulk copy, or index creation operations. Compile memory is a completely separate resource and would trigger RESOURCE_SEMAPHORE_QUERY_COMPILE waits if compile memory resulted in pressure on one of the three internal gateways for compile memory. For a 32-bit instance the small gateway threshold is fixed at 250,000 bytes, and can support four times the number of CPUs for concurrent compilations before a new compile would have to wait. The medium and big gateways have dynamic thresholds that adjust based on current compile memory usage by the workload.



If the memory is being chewed up then am I right in saying that SQL Server will start ditching cached plans from the plan cache as it starts running out of memory. If this is correct then would this in turn cause all of these Compilations?


Maybe, maybe not. Workspace memory and plan cache aren't the same thing.



Gail is correct that workspace memory and plan cache aren't the same thing, but excessive workspace memory grants occurring concurrently would reserve pages from the buffer pool and could result in internal cache store pressure that clears plans during external clock sweeps of the cache entries. You would see this in the performance counters for the instance by a drop in Free Pages, associated with an increase in Reserved Pages/Granted Workspace Memory and then shortly after, a drop in Database Pages (data cache) and Stolen Pages (plan cache/other stores). You can track the plan cache activity with the SP:CacheHit, SP:CacheInsert, SP:CacheMiss, SP:CacheRemove, and SP:Recompile events, and the SP:CacheRemove event would let you know why it was being removed for further troubleshooting.

I've seen this behavior in other workloads where triggers were present, so one question I'd have is are there triggers associated with either of the object_id's? If you have a plan invalidation for an object, it would compile the trigger and if the procedure is long running, the OBJECT compile lock becomes the bottleneck.



Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1525023
Posted Friday, December 20, 2013 10:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
Jonathan Kehayias (12/20/2013)
One correction here, RESOURCE_SEMPHORE is execution workspace memory only, associated with hash, sort, bulk copy, or index creation operations. Compile memory is a completely separate resource and would trigger RESOURCE_SEMAPHORE_QUERY_COMPILE waits if compile memory resulted in pressure on one of the three internal gateways for compile memory.


Ack! I keep forgetting that. Only time I've faced those waits was on a SQL 2000 box, and the query compile wait was added in 2005. :-(



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1525092
Posted Saturday, December 21, 2013 3:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:49 AM
Points: 57, Visits: 250
Hi thanks for the response.

I have managed to replicate the compiles on our Dev box.

I set the max memory to 2GB the same as our production server (GilaMonster said that this is the max for 32 bit without /3GB switch and AWE enabled). I then ran 50 concurrent queries from my machine with the load testing tool. These queries were the query that is causing the RESOURCE_SEMAPHORE waits. I then loaded up the load testing tool on the server and started running about 30 concurrent queries. These were the additional queries that were running in the excel spread sheet attached to this discussion.

Once this was all running I looked at sys.dm_exec_requests and low and behold the additional queries that do not cause RESOURCE_SEMAPHORE waits have a mixture of the following:

METADATA: database_id = 5 SECURITY_CACHE($hash = 0x2532319d:0x1)
METADATA: database_id = 9 METADATA_CACHE($hash = 0x3317e3d:0x0)
OBJECT: 5:1417080505:0 [COMPILE]

(Ignore the IDs I just copied them from earlier in the thread).

Each time the production server starts becoming really unresponsive there is a massive queue in sys.dm_exec_requests for this query that is causing RESOURCE_SEMAPHORE waits. From my test I can clearly show that this stored proc can cause compiles on the server. This stored procedure is basically run on every web page on every page load so this is clearly an issue.

How come then, when this issue happened on production the other day, I commented out the SP and could clearly see the number of requests for this problem SP falling slowly to 0, however the compiles continued happening even after this SP was no longer showing in sys.dm_exec_requests.

The question here then is this: Could it be that the server resources are so severely limited that once this problem SP starts causing compiles that the load gets to much and the server basically just falls over and cannot reach equilibrium once more?
Post #1525250
Posted Saturday, December 21, 2013 3:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
Is there any way you can post that procedure? Obfuscate the table and column names, those aren't important, it's the form of the procedure (including any hints) that I'm interested in.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1525251
Posted Saturday, December 21, 2013 3:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:49 AM
Points: 57, Visits: 250
CREATE procedure [dbo].[Problem_Stored_Procedure] (
@Blank uniqueidentifier,
@Blank int,
@Blank bit = null,
@Blank bit = null,
@Blank datetime = null
)
as

if @Blank is null
begin
set @Now = getdate()
end

SELECT COUNT(distinct imr.column1) AS CountMessage
FROM Table1 imr
inner join Table2 im on
(
im.column1 = imr.column1
and im.column2 = @Blank
and im.column3 = @Blank
)
WHERE imr.column2 > CONVERT(DATETIME, @Blank , 103)
and case when @Blank is null then im.column4 else @Blank end = im.column4
and case when @Blank is null then case when imr.column3 is null then 0 else 1 end else @Blank end = case when imr.column3 is null then 0 else 1 end


Basically the point is to get the number of messages that a user has sent out, that have been read by the recipient. You have the variables left in as hints as well.

I am also getting this missing index hint

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON imr (column2)
INCLUDE (column1,column3)

I tried adding this on production but it actually made the problem worse (this SP started queuing up like crazy in exec_requests) even though it gives a much nicer execution plan and stops the REOURCE_SEMEPHORE wait types happening.
Post #1525257
Posted Sunday, December 22, 2013 11:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
I would recommend that you start off by doing a comprehensive read of the following two links:

http://www.sqlskills.com/blogs/kimberly/stored-procedure-parameters-giving-you-grief-in-a-multi-purpose-procedure/
http://www.sommarskog.se/dynamic_sql.html

Your nullable parameters are most likely a big part of the problem and this is a pattern that is routinely problemattic in the ways that you describe. I'd rebuild that procedure using the parameterized dynamic SQL execution by dynamically building the string to execute with parameterization and then calling it with sp_executesql, as shown in both of the above articles.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1525408
Posted Monday, December 23, 2013 4:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
Jonathan beat me to it (I started rewriting the procedure into dynamic SQL and got busy with other things). Here's another blog post on the subject http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1525467
Posted Monday, December 23, 2013 5:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:49 AM
Points: 57, Visits: 250
Hi thanks a lot for this answer. You two have been very helpful. I will definitely be rewriting this query in the suggested manner. Again thanks
Post #1525473
Posted Monday, December 23, 2013 5:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
It may be worth looking through the codebase for other similar procedures. I've typically found that if this kind of pattern is used in one place, it's used in a lot others too.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1525490
Posted Tuesday, December 24, 2013 11:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
GilaMonster (12/23/2013)
It may be worth looking through the codebase for other similar procedures. I've typically found that if this kind of pattern is used in one place, it's used in a lot others too.


+1, usually when this type of code exist in one place, it exists elsewhere as well, and it is worth fixing everywhere in one major push than combating performance issues one by one over time.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1525864
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse