It is well known and understood that SQL Server requires a substantial amount of memory. SQL Server will also try to consume as much memory as possible from the available system memory – if you let it. Sometimes, there will be some contention / pressure with the memory.
When contention occurs, the users will probably start screaming because performance has tanked and deadlines are about to be missed. There are many different ways (e.g. here or here) to try and observe the memory conditions and even troubleshoot memory contention. Extended Events (XE) gives one more avenue to try and troubleshoot problems with memory.
Using XE to observe memory conditions is a method that is both geeky/fun and an advanced technique at the same time. If nothing else, it will certainly serve as a divergence from the mundane and give you an opportunity to dive down a rabbit hole while exploring some SQL Server internals.
Diving Straight In
I have a handful of events that I have picked for an event session to track when I might be running into some memory problems. Or I can run the session when I suspect there are memory problems to try and provide me with a “second opinion.” Here are the pre-picked events.
|
1 2 3 4 5 |
SELECT xo.name AS EventName, xo.capabilities_desc, xo.description FROM sys.dm_xe_objects xo WHERE xo.name IN ('large_cache_memory_pressure','buffer_manager_page_life_expectancy' ,'buffer_node_page_life_expectancy' ,'buffer_pool_eviction_thresholds_recalculated','server_memory_change'); |
Investigating those specific events a little further, I can determine if the payload is close to what I need.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT oc.OBJECT_NAME AS EventName ,oc.name AS column_name, oc.type_name ,oc.column_type AS column_type ,oc.column_value AS column_value ,oc.description AS column_description ,ca.map_value AS SearchKeyword ,ch.Channel --added because large_cache_memory_pressure has no category/search keyword FROM sys.dm_xe_object_columns oc OUTER APPLY (SELECT TOP 1 mv.map_value FROM sys.dm_xe_object_columns occ INNER JOIN sys.dm_xe_map_values mv ON occ.type_name = mv.name AND occ.column_value = mv.map_key AND oc.object_package_guid = mv.object_package_guid WHERE occ.name = 'KEYWORD' AND occ.object_name = oc.object_name) ca INNER JOIN (SELECT c.object_name AS EventName,c.object_package_guid AS PkgGuid, v.map_value AS Channel FROM sys.dm_xe_object_columns c INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name AND c.column_value = CAST(v.map_key AS NVARCHAR) WHERE c.name = 'channel') ch ON ch.EventName = oc.object_name AND ch.PkgGuid = oc.object_package_guid WHERE oc.object_name IN ('large_cache_memory_pressure','buffer_manager_page_life_expectancy' ,'buffer_node_page_life_expectancy','buffer_pool_eviction_thresholds_recalculated' ,'server_memory_change') AND oc.column_type <> 'readonly' ORDER BY EventName,oc.column_id; |
That is a small snippet of the payload for all of the pre-picked events. Notice that the large_cache_memory_pressure event has no “SearchKeyword” / category defined for it. There are a few other events that also do not have a category assigned which makes it a little harder to figure out related events. That said, from the results, I know that I have some “server” and some “memory” tagged events, so I can at least look at those categories for related events.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
/* Keyword search */ DECLARE @Keyword VARCHAR(64) = 'memory' --memory --server /* note there are a few more interesting events buffer_node_database_pages buffer_manager_database_pages buffer_manager_target_pages memory category query_memory_grant_usage memory_manager_stolen_server_memory allocation_failure bad_memory_detected */ SELECT oc.OBJECT_NAME AS EventName ,oc.name AS column_name, oc.type_name ,oc.column_type AS column_type ,oc.column_value AS column_value ,oc.description AS column_description ,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName ,olm.file_version ,xp.name AS PackageName ,mv.map_value AS SearchKeyword ,ch.Channel FROM sys.dm_xe_object_columns oc INNER JOIN sys.dm_xe_map_values mv ON oc.type_name = mv.name AND oc.column_value = mv.map_key AND oc.object_package_guid = mv.object_package_guid AND oc.name = 'KEYWORD' INNER JOIN sys.dm_xe_packages xp ON oc.object_package_guid = xp.guid INNER JOIN sys.dm_os_loaded_modules olm ON xp.module_address = olm.base_address INNER JOIN (SELECT c.object_name AS EventName,c.object_package_guid AS PkgGuid, v.map_value AS Channel FROM sys.dm_xe_object_columns c INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name AND c.column_value = CAST(v.map_key AS NVARCHAR) WHERE c.name = 'channel') ch ON ch.EventName = oc.object_name AND ch.PkgGuid = oc.object_package_guid WHERE mv.map_value = @Keyword ORDER BY oc.object_name; |
This query will yield results similar to the following.
If you look closely at the script, I included a note about some additional interesting events that are related to both categories “server” and “memory.”
After all of the digging and researching, now it’s time to pull it together and create a session that may possibly help to identify various memory issues as they arise or to at least help confirm your sneaking suspicion that a memory issue is already present.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
USE master; GO -- Create the Event Session IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'BuffMemPressure' ) DROP EVENT SESSION BuffMemPressure ON SERVER; GO EXECUTE xp_create_subdir 'C:\Database\XE'; GO CREATE EVENT SESSION [BuffMemPressure] ON SERVER ADD EVENT sqlos.large_cache_memory_pressure ( ACTION ( sqlserver.database_id, sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username, sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname) ), ADD EVENT sqlserver.buffer_manager_page_life_expectancy ( ACTION ( sqlserver.database_id, sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username, sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname) ), ADD EVENT sqlserver.buffer_node_page_life_expectancy ( ACTION ( sqlserver.database_id, sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username, sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname) ), ADD EVENT sqlserver.buffer_pool_eviction_thresholds_recalculated ( ACTION ( sqlserver.database_id, sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username, sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname) ), ADD EVENT sqlserver.server_memory_change ( ACTION ( sqlserver.database_id, sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username, sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname) ) ADD TARGET package0.event_file ( SET filename = N'C:\Database\XE\BuffMemPressure.xel' , max_rollover_files = ( 5 ) ) WITH ( MAX_MEMORY = 4096 KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 5 SECONDS , MAX_EVENT_SIZE = 0 KB , MEMORY_PARTITION_MODE = NONE , TRACK_CAUSALITY = ON , STARTUP_STATE = ON ); GO ALTER EVENT SESSION BuffMemPressure ON SERVER STATE = START; |
When running this session for a while, you will receive a flood of events as they continue to trigger and record data to your trace file. You will want to keep a steady eye on the trace files and possibly only run the session