May 19, 2011 at 1:45 pm
Back story: We have a production reporting database. There are a number of queries that are requesting 6+ gb of query memory/workspace memory (36 gb total allocated to this instance) and as a result when someone runs more than one of these reports we get resource_semaphore waits. These heavy reports are sitting queued waiting to be granted the requested memory. I wrote a quick select that gives us an idea of the processes that are queued waiting on memory grants and what are the offending SQL queries.
select
prc.spid,
t.text,
mem.request_time,
mem.grant_time,
mem.requested_memory_kb / 1024 as requested_memory_mb,
mem.granted_memory_kb / 1024 as granted_memory_mb,
mem.required_memory_kb / 1024 as required_memory_mb,
mem.query_cost,
prc.*
from
sys.sysprocesses prc
inner join sys.dm_exec_query_memory_grants mem on prc.spid = mem.session_id
CROSS APPLY sys.dm_exec_sql_text(prc.sql_handle) AS t
where spid>50
order by request_time
We are slowly working to clean up/improve these identified queries, but in the mean time we want to put something into place that will email us an alert when we have the situation described above occur so that we can be aware before we get a call from our users.
Let's assume we can't buy any commercial products and our shop does not have any commercial products.
My thought was to create a scheduled job that would run every x minutes that would essentially check the results of the above query and determine if we past some "business rule threshold" and if so email an alert that included the offending SQL.
My questions are:
1) Have you done anything like this before?
2) How did you implement your solution (high level)?
3) Any tools/resources you would recommend (commercial or free)?
May 20, 2011 at 6:33 am
You can set this up pretty easily by using SQL Agent. You can query for the processes that are in place using sys.dm_exec_requests. With that you can get the session and it's start time something like this:
SELECT der.start_time
,der.session_id
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.is_user_process = 1
Record that and compare every X amount of time, to see what has been sitting around too long. Or look at the wait times there, whatever works. Then you can use SQL Agent to send an alert.
Or, if you want to look at a 3rd party tool, I recommend Red Gate SQL Monitor. It does this type of thing right out of the box in addition to a number of other monitoring solutions (disclaimer, I work there)
"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
May 20, 2011 at 11:30 am
Grant, thanks for the reply! I've got your blog in my Google reader and love it and I am reading SQL Server 2008 Query Performance Tuning Distilled.
Yeah I think something like that will work and we will need to come up with some business rules and times that are "agreed upon" by management. I will also take a look at Red Gate SQL Monitor.
May 20, 2011 at 11:45 am
Thanks. Glad to hear that stuff is useful. That's the goal.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply