Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to quickly troubleshoot memory faults in SQLServer". The editor shows you the operation process through actual cases, the operation method is simple and fast, and it is practical. I hope this article "how to quickly troubleshoot memory problems in SQLServer" can help you solve the problem.

There are many misunderstandings about SQL's use of memory (RAM) on physical servers. The most common situation I hear is that users worry that the server RAM is running out. SQL Server is designed to use as much memory as possible. The only limit is the amount of memory that the instance is set to the upper limit (maximum memory) and the amount of RAM actually used on the server.

For example, suppose your SQL server runs optimally with only 8GB memory, and the server shows that about 95% of the total RAM is in use. You can double the RAM on the machine, double the Max Memory setting of the SQL instance, and then watch the server slowly pick up to 95%. It's not necessarily a problem. SQL simply caches as much temporary data as possible based on a given content.

Here are my quick survey points to determine if there is a memory problem, or if SQL Server is just doing what it is supposed to do:

Verify the maximum memory setting from the instance properties and compare it with the total memory of the server. Try to give SQL, but each environment is different. There are many other factors to consider (number of instances, applications, workloads, cluster status, and so on). At a minimum, be sure to set aside some GB for the operating system. In addition, make sure there is anything else on the machine that needs it.

If your maximum memory is set to 2147483647, please change it now. This is the default value used when SQL is installed, telling it how much to use as needed. This can cause performance problems with the operating system and other applications on the server and slow everything down when bottlenecks are encountered.

Run the built-in memory consumption report from the instance properties. The health details to look for immediately are high PLE values and low memory grant undetermined values. The life expectancy of a page is the number of seconds the page will stay in the buffer pool before releasing memory on the server to "reuse" it. The general recommendation is 300 seconds or more, but this proposal increases exponentially when the amount of RAM on the server is large. Memory Grants Pending is the number of processes waiting for the workspace memory to grant. Zero is the best value because it means that everything running can run with the sufficient amount of memory it needs. Run the following query to check the current memory counter. The third result set displays the timestamp when the memory change occurs. Pay attention to any "low" memory alerts and determine whether memory pressure should be further investigated if the appropriate amount is used by SQL.

SQL:

SELECT @ @ SERVERNAME AS [Server Name], total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)], available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)], total_page_file_kb / 1024 AS [Total Page File Memory (MB)], available_page_file_kb / 1024 AS [Available Page File Memory (MB)], system_memory_state_desc AS [Available Physical Memory], CURRENT_TIMESTAMP AS [Current Date Time] FROM sys.dm_os_sys_memoryOPTION (RECOMPILE) GOSELECT physical_memory_in_use_kb / 1024 AS [Physical Memory In Use (MB)], locked_page_allocations_kb / 1024 AS [Locked Page In Memory Allocations (MB)], memory_utilization_percentage AS [Memory Utilization Percentage], available_commit_limit_kb / 1024 AS [Available Commit Limit (MB)], CASE WHEN process_physical_memory_low = 0 THEN'No Memory Pressure Detected' ELSE 'Memory Low' END AS' Process Physical Memory' CASE WHEN process_virtual_memory_low = 0 THEN'No Memory Pressure Detected' ELSE 'Memory Low' END AS' Process Virtual Memory',CURRENT_TIMESTAMP AS [Current Date Time] FROM sys.dm_os_process_memoryOPTION (RECOMPILE) GOWITH RingBufferAS (SELECT CAST (dorb.record AS XML) AS xRecord,dorb.TIMESTAMPFROM sys.dm_os_ring_buffers AS dorbWHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') SELECT xr.value (' (ResourceMonitor/Notification) [1]', 'varchar (75)') AS Notification,CASEWHEN xr.value ('(ResourceMonitor/IndicatorsProcess) [1]', 'tinyint') = 1THEN' High Physical Memory Available'WHEN xr.value ('(ResourceMonitor/IndicatorsProcess) [1]' 'tinyint') = 2THEN' Low Physical Memory Available'WHEN xr.value ('(ResourceMonitor/IndicatorsProcess) [1]', 'tinyint') = 4THEN' Low Virtual Memory Available'ELSE 'Physical Memory Available'END AS' Process Memory Status',CASEWHEN xr.value ('(ResourceMonitor/IndicatorsSystem) [1]', 'tinyint') = 1THEN' High Physical Memory Available'WHEN xr.value ('(ResourceMonitor/IndicatorsSystem) [1]', 'tinyint') = 2THEN' Low Physical Memory Available'WHEN xr.value ('(ResourceMonitor/IndicatorsSystem) [1]') 'tinyint') = 4THEN' Low Virtual Memory Available'ELSE 'Physical Memory Available'END AS' System-Wide Memory Status',DATEADD (ms,-1 * dosi.ms_ticks-rb.TIMESTAMP, GETDATE ()) AS NotificationDateTimeFROM RingBuffer AS rbCROSS APPLY rb.xRecord.nodes ('Record') record (xr) CROSS JOIN sys.dm_os_sys_info AS dosiORDER BY NotificationDateTime DESC This is the end of the introduction to "how to quickly troubleshoot memory problems in SQLServer". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.