September 25, 2017 at 7:35 am
Hi All :),
I wonder if anyone can shed some light on the following issue I experienced earlier today.
All users reported the applications were either slow, unresponsive, or they couldn’t log in. They were getting the following error:
“Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all thepooled connections were in use an max pool size was reached”
I checked SQL server activity using Adam Machanic’s “sp_whoisacive” tool and this showed lots of queries backing up behind one blocked query. This query was a simple SELECT and was waiting on ASYNC_NETWORK_IO so I assumed the app has crashed and somehow the query hadn't cancelled. (it had been waiting for 3days - all weekend) I therefore KILLed this process and expected everything to kick in. It didn’t so I had to KILL another process and also KILL a REINDEX maintenance task that had been blocked all weekend. Anyway all blocking then disappeared however all new queries started waiting on RESOURCE_SEMAPHORE_QUERY_COMPILE and the CPU’s(4 cores) were all pegged at roughly 100%.
After googling I found this wait could relate to a possible memory pressure so I checked page life expectancy and this was around 45,000+.The situation continued for approx. 30mins or so and suddenly everything returned to normal and its been fine ever since. I've not noticed any problems on this server before, this was totally out of the blue.
Could this just have been the volume of connections\queries coming into SQL server after the blocking episode? Unfortunately I didn't check for the number of connections. If not any ideas what was causing this wait. Is there anyway I can check anything retrospectively that would help? Unfortunately I don’t have any third party monitoring tools on there.
I checked SQL logs and windows logs and this didn’t show anything odd either.
Version info is:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0(X64)
Oct 202015 15:36:27
Copyright(c) Microsoft Corporation
StandardEdition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Many thanks for your assistance and guidance
September 25, 2017 at 1:50 pm
PLE is useless for this issue. It is data pages only. There are LOTS of other memory consumers that can get bottle necked. run DBCC MEMORYSTATUS to see them. It's a shame you didn't run it while the issue was ongoing.
Did you collect ALL of the processes that were involved in the blocking chain and analyze the order and the queries in play? Did you note the locks taken/requested? Without such data you can't prevent this from happening again.
If you are virtualized, do you have reservations so that your host NEVER takes memory back from SQL Server (after ensuring that you are NEVER over provisioned)? The issue could have happened, and magically cleared, simply because your virtualization system took memory away from the SQL Server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 26, 2017 at 2:12 am
Hi Kevin,
Thank you very much for taking the time to read and respond.
It’s funny you mention DBCC MEMORYSTATUS as I was about to run this when everything kicked back in. Not because of any great insight but because one of the many blogs I was frantically reading recommended it. Its great to know this would have helped so I’ll make sure to do this if it ever happens again. I’ll also ensure I gather all the processes and analyse them as per your suggestion.
With regards virtualisation I’ll ask the question to makesure its configured correctly. Do you have any articles or blogs you can recommend for this?
Once again thanks for your assistance, its very much appreciated.
September 26, 2017 at 10:30 am
David Klee and Denny Cherry have material on configuring/performance tuning for virtualized SQL Server workloads. VMWare has a huge document on configuring VMWare for SQL Server that was just updated in March 2017.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 27, 2017 at 3:59 am
Thanks Kevin. Much appreciated
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply