Recently there was a question of why a query runs under a second on one environment and another environment runs the same query for 90 seconds and times out the application. It was observed the memory on the second system was running at 94% and so it was put down to that and ‘get more memory’.
But there’s a whole 6% memory free! What more could SQL want, right?
So the query was taken and ran against the two systems and true to form the timings were indeed delayed on the second system. Lets pop on the actual execution plan and see what’s actually going on here. The second plan showed us the culprit, a warning that the system was waiting all that time for a memory grant.
It’s probably worth noting at this point that that these systems weren’t large scale production servers, they were client applications backed with a local SQL Express instance, all bundled on hardware with only 4gb of memory (do they still make these systems?).
If there are issues with memory grants being available in the environment then we’d expect to see the RESOURCE_SEMAPHORE wait type to be relatively high for the environment – and so it was, standing out above IO, log write or any locking waits:
SELECT TOP 50 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
If you’re running on a production system and are monitoring with the First Responder Kit, this is one of the waits that will get pulled up as a poison wait and even though the memory isn’t completely maxed out, it’s more than enough to be a real bottleneck for the SQL engine.
So if the system was having issues with memory why would it want to try and get even more? Well, it thought it could…
SELECT name, value
FROM sys.configurations
WHERE name = 'max server memory (MB)';
Maximum memory of 2,147,483,647mb? Ouch!
So we’ve got this environment sitting there with a little bit of memory left for the OS to deal with and SQL thinks it can go to town with all the memory it needs for it’s queries. This shouldn’t really be left as a default in any environment so given the constraints we were seeing, a low ceiling was put into place to see if it would help out:
/* Yep, we've all got larger memory sticks, lets start small */
EXEC sys.sp_configure 'max server memory (MB)', '256';
RECONFIGURE;
Once the configuration was updated we tried to run the same query again and it’s back in line with normal performance. With the hard limit imposed on the memory for the engine it’s able to better manage it’s memory and it can choose what to clear out to make way for the query rather than trying to grant anything extra.
Now whilst the change ‘fixed’ this one instance, the memory configuration should be based on the environment and what else the database engine needs to be sharing it’s resources with. In this type of setup it’s for the vendor to be advising but at least we’ve been able to support the process and provide the evidence and help steer their approach rather than ‘it runs slower, needs more memory’.
Andy Brownsword 