未加星标

Checking your Memory with XE

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

Checking your Memory with XE
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.

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');
Checking your Memory with XE

Investigating those specific events a little further, I can determine if the payload is close to what I need.

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;
Checking your Memory with XE

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.

/* 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.


Checking your Memory with XE

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.

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 for short periods.

Here is an example of my session with events grouped by event name. Notice anything of interest between the groups?


Checking your Memory with XE

If the data in the session does not seem to be helpful enough, I recommend looking at adding the additional events I noted previously.

Here is another view on a system that has been monitoring these events for a while longer and does experience memory pressure.


Checking your Memory with XE
Here we can see some of the direct results of index operations on memory as well as the effects on memory for some really bad code. Really cool is that we can easily find what query(ies) may be causing the memory pressure issues and then directly tune the offending query(ies). The Wrap
Checking your Memory with XE
Diving in to the internals of SQL Server can be useful in troubleshooting memory issues. Extended Events provides a means to look at many memory related events that can be integral to solving or understanding some of your memory issues. Using Extended Events to dive into the memory related events is a powerful tool to add to the memory troubleshooting toolbelt.

Try it out on one or more of your servers and let me know how it goes.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little .

Interested in seeing the power of XE over Profiler? Check this one out !

This has been the ninth article in the 2018 “12 Days of Christmas” series. For a full listing of the articles, visit this page .

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Checking your Memory with XE
本站链接:https://www.codesec.net/view/628575.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(148)