Excessive memory grants are extremely problematic in SQL Server. These excessive grants do not just happen out of the blue. Memory grants are directly linked to the queries. When you write a bad query and you can kill your server. One of the reasons bad queries kill servers is because they try to steal all of the memory. This article will help find these hog queries so they can be tuned.
There is a lot of science and art that is mixed together to help performance tune. Performance tuning is rather enjoyable because the rewards are usually very satisfying. There is nothing like the feeling of making a query go turbo speed.
When I say turbo speed, there is a bit of a tongue in cheek there. However, there is some truth to it. The causes of slow performance vary from query to query but the results of tuning usually all result in significantly faster query performance.
One such example of this happened when I encountered a query with a 118GB memory grant. I had to double check that figure multiple times just to make sure. Clearly there was a complaint that the query was way too slow. Sadly, the client only recognized the pain of the query when trying to migrate to AWS. Prior to the migration, they had oversized bare metal servers with top-shelf hardware that could conceal the worst of performance issues.
memory – You’ll understand what happiness is
Digging into this particular query, I found a great deal of wrong with how it was written. I won’t digress into all of the bad anti-patterns, suffice it say there were plenty. These anti-patterns helped contribute to the 118GB memory grant. When tuning was sufficient and the query was within acceptable performance timings, the memory grant was down to 1GB. Despite the huge win, that 1GB is still too large for my tastes but there were bigger fires.
In this example, the client already knew the specific query that was causing great pain. What do we do when we don’t know what the specific problem is? How do we go about investigating for performance issues possibly related to huge memory grants caused by piss poor queries (or data design)? The answer is simple – Extended Events (XEvents). Through the use of XEvents, you too will remember what happiness is.
For the sake of posterity, I am also adding this to the MASSIVE collection of Extended Events articles.
Memory Grants
An interest concept in SQL Server is query memory grant and the interpretations of what it is. Unfortunately, there are varying interpretations – of which some are wrong. So, the first order of business is to first clarify what a memory grant is – in simple terms.
A memory grant is quite simply the amount of memory that is necessary to store data rows. This starts with an estimate based on data size and quantity (and query structure). If there is insufficient memory, then there will be a penalty incurred. This penalty means more disk based IO and ultimately slower performance. The memory grant is volatile and only lives through the completion of the query.
Clear as mud? Good!
Memory Grants Monitoring
As I mentioned previously, an ideal way to monitor for queries gone wrong (and causing bad memory grants) is to use XEvents. There are multiple events available to use for monitoring memory grants. I have included the following in the session script which is to follow.
| PackageName | EventName | EventDescription |
| sqlserver | query_memory_grant_blocking | Occurs when a query is blocking other queries while waiting for memory grant |
| sqlserver | query_memory_grant_usage | Occurs at the end of query processing for queries with memory grant over 5MB to let users know about memory grant inaccuracies |
| sqlserver | query_memory_grant_resource_semaphores | Occurs at every 5 minute intervals for each resource governor resource pool |
| sqlserver | query_memory_grants | Occurs at every 5 minute intervals for each query with memory grant |
| sqlserver | excessive_non_grant_memory_used | Occurs when too much non-grant memory is used in query execution. |
And here is that session script.
Memory Session Script
USE master; GO -- Create the Event Session IF EXISTS ( SELECT* FROMsys.server_event_sessions WHEREname = 'MemoryGrantInfo' ) DROP EVENT SESSION MemoryGrantInfo ON SERVER; GO EXECUTE xp_create_subdir 'E:DatabaseXE'; GO /* excessive_non_grant_memory_used query_memory_grant_blocking query_memory_grant_wait_begin query_memory_grant_wait_end query_memory_grant_resource_semaphores query_memory_grants */CREATE EVENT SESSION [MemoryGrantInfo] ON SERVER ADD EVENT sqlserver.query_memory_grant_usage (ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) , ADD EVENT sqlserver.excessive_non_grant_memory_used (ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) , ADD EVENT sqlserver.query_memory_grant_blocking (ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) , ADD EVENT sqlserver.query_memory_grant_resource_semaphores (ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) , ADD EVENT sqlserver.query_memory_grants (ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) ADD TARGET package0.event_file (SET FILENAME = N'E:DatabaseXEMemoryGrantInfo.xel' , max_file_size = (50) , MAX_ROLLOVER_FILES = (6)) WITH ( MAX_MEMORY = 4090KB , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 4 SECONDS , MAX_EVENT_SIZE = 2048KB , MEMORY_PARTITION_MODE = PER_NODE , TRACK_CAUSALITY = ON , STARTUP_STATE = OFF ); GO ALTER EVENT SESSION MemoryGrantInfo ON SERVER STATE = START;
Memory Session Results
Running this session (even if just for a few hours) would produce some very revealing results. Here are some samples of results that could be retrieved from this session.
The preceding image illustrates the cycling of memory up and down as demands are changing in the server. The following image shows potential crap queries that should be tuned. The first query to target would be the one highlighted in red!
Put a bow on it
In this article, I shared an easy method to help isolate crap queries to help improve performance. The metric being evaluated this time was memory grants. Memory grants are essential indicators to performance.
Bad queries will cause bad memory grants. Learning to identify where those bad queries are by their bad memory grants is a huge win in the fight for better performance.
Interested in learning more deep technical information? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the sixth article in the 2021 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post Memory – How to Find when Excess is a Bad Thing first appeared on SQL RNNR.