December 20, 2013 at 5:21 am
Hi,
We are getting intermittent performance issues on our SQL server that happen once or twice a month. When it happens the whole sql server becomes unresponsive and I have to use the dedicated administrator connection to connect. The last two times that it has happened I have looked in sys.dm_exec_requests and could see that there is one stored procedure in particular that has about 70 entries. Out of the last 2 times it happened, the first time we just failed over to another node so I didn't really have time to look into the issue however the second time (yesterday) I made sure that we gathered some info before failing over.
I found that again there were about 70 lines in sys.dm_exec_requests for the stored procedure in question. I thought that I would comment out the SP and sure enough the requests for this SP started to dissapear, however this did not fix the problem overall. We still had about 70 lines in sys.dm_exec_requests for various SPs.
In the wait resource for each of these many have command EXECUTE, wait_resource OBJECT: 5:1417080505:0 [COMPILE], wait_type LCK_M_X and sql_handle and plan_hanlde of 0x000000000000000000000000000000000000000000000000. These ones have the highest wait times of all the requests.
Many other lines in here have wait_type LCK_M_SCH_M and wait_resource of either METADATA: database_id = 5 SECURITY_CACHE($hash = 0x2532319d:0x1) or METADATA: database_id = 5 METADATA_CACHE($hash = 0x4ae22766:0x0).
As a side note we do have one SP that calles OPEN SYMMETRIC KEY and CLOSE SYMMETRIC KEY in the SP. This stored procedure was running at the time the issue was happening. When this was running there were a few exec requests as follows: OBJECT: 9:366624349:0 [COMPILE]. Using SQL Load generator on our dedicated Dev instance and loading it up with 20 concurrent requests running this SP I can get it to cause the OBJECT: 9:366624349:0 [COMPILE] wait_resource.
There are no jobs running that would be modifying schema however we do have a job running every 5 minutes that does a bulk insert of data for each customer. Maybe this contributed the the LCK_M_SCH_M locks??
Does anybody have any idea as to what could be the issue here as any help would be really great.
I have attached an excel dump of the exec requests if that helps.
I can provide more info if I have not been clear enough.
Our setup is 32bit, dual core xeon 3ghz, 6gb ram (AWE not enabled)
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Enterprise Edition <X86> (Build 6002: Service Pack 2)
December 20, 2013 at 5:44 am
Please don't cross post. It just wastes people's time and fragments replies. No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1524934-3387-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply