February 21, 2019 at 2:45 pm
I was working today with the internal infrastructure admin and their alarms started ringing as the memory was spiking > 90%. I know people say use dmv's such as sys.dm_os_memory_clerks etc. to troubleshoot the issue but knowing what is in the buffer pool or for that matter the result set from these dmv's doesn't give much insight or make sense when you're fire fighting. Usually, when looking into task manager it just says sqlservr.exe is consuming memory which is fair and it will consume memory as this is how the product is designed unless you place a cap with maxmemory setting...so basically how would one end up troubleshooting such a scenario when you don't exactly know what is causing a memory churn.
February 21, 2019 at 3:03 pm
Just looking at free memory on the server is a very poor metric to evaluate performance issues. A more useful metric would be page life expectancy.
February 21, 2019 at 3:27 pm
If you have memory capped at about 3/4 of the total available and PLE is ok (over 300) then i say the issue is some other process outside of SQL.
February 21, 2019 at 3:33 pm
SQL Server will use all the memory you allow it to if it can, it buffers data in memory.
You should limit the amount of memory SQL Server can use to allow enough memory for the operating system and any other applications that are running on the server.
I limit SQL Server to use 28 GB of memory on our servers that have 32 GB of memory total.
Just right click the server in SSMS object explorer and select memory and limit the Maximum server memory to a few GB below the total memory on the server.
February 22, 2019 at 9:00 am
February 22, 2019 at 12:28 pm
Okay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.
Thanks
February 22, 2019 at 12:35 pm
Feivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
Why don't you just limit the max memory SQL Server uses?
February 22, 2019 at 12:45 pm
Feivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
Why do you think SQL Server using memory means it's doing anything wrong?
February 22, 2019 at 12:47 pm
Feivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
If you have SQL Data Collections setup you can run the Query Statistics History report. It is a simple way to start troubleshooting in all versions of SQL Server from 2008 onwards. Also look into Extended Events sessions for high CPU as in this post: https://www.sqlskills.com/blogs/paul/tracking-expensive-queries-with-extended-events-in-sql-2008
MattF
February 22, 2019 at 1:36 pm
Feivel - Thursday, February 21, 2019 2:45 PMI was working today with the internal infrastructure admin and their alarms started ringing as the memory was spiking > 90%. I know people say use dmv's such as sys.dm_os_memory_clerks etc. to troubleshoot the issue but knowing what is in the buffer pool or for that matter the result set from these dmv's doesn't give much insight or make sense when you're fire fighting. Usually, when looking into task manager it just says sqlservr.exe is consuming memory which is fair and it will consume memory as this is how the product is designed unless you place a cap with maxmemory setting...so basically how would one end up troubleshooting such a scenario when you don't exactly know what is causing a memory churn.
Memory spiking at 90% is not necessarily a problem and in many cases for SQL Server would be considered normal. If the operating system is doing a lot of paging then there could be a problem because SQL Server wants to manage its own memory usage, and if something is out in an OS Swap file that SQL Server thinks is already in RAM then you are doing extra writes and reads to disk that you don't want to do.
If you do already have a reasonable maximum server memory setting for your instance, then here are some ways to see what SQL Server has in memory.
Start by looking high level at: sys.dm_os_memory_clerksSELECT [type],
SUM(pages_kb) / 1024 AS size_MB --SUM(single_pages_kb + multi_pages_kb) / 1024 AS size_MB
FROM sys.dm_os_memory_clerks
GROUP BY [type]
HAVING SUM(pages_kb) >= 1024
ORDER BY size_MB DESC
Most likely the top type of clerk consuming memory is: MEMORYCLERK_SQLBUFFERPOOL
To see what is in the buffer pool, take a look at: sys.dm_os_buffer_descriptors-- memory by database
SELECT
CASE database_id WHEN 32767 THEN 'ResourceDB' ELSE db_name(database_id) END AS Database_name,
--page_type,
COUNT(*) AS cached_pages_count,
COUNT(*)/128 AS cached_MB,
SUM(CAST([free_space_in_bytes] AS BIGINT)) / 1048576 AS empty_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id--, page_type
ORDER BY cached_MB DESC;-- memory by object
;WITH src AS(
SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.type_desc AS object_type, i.name AS index_name, i.type_desc AS index_type, p.[object_id], p.index_id, au.allocation_unit_id
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0)
SELECT TOP 10 src.object_name, src.object_type, src.index_name, src.index_type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id
WHERE b.database_id = DB_ID()
GROUP BY src.object_name, src.object_type, src.index_name, src.Index_Type
ORDER BY buffer_pages DESC;
February 22, 2019 at 1:36 pm
Feivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
This is not a problem. It is how SQL is designed to work.
You need to determine what the correct max server memory setting may be for your server, and adjust it accordingly. Jonathan Kehayas's article posted above is the best place to start.
You can then determine what queries are consuming the most resources, and perform addition tuning, But unless you limit the amount of memory allocated to SQL, you will not see a drop in memory usage.
And, if your infrastructure admin wants to see the database servers running at 10-15 percent memory like a file server or a web server, then he needs some education.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 22, 2019 at 1:42 pm
Jonathan AC Roberts - Friday, February 22, 2019 12:35 PMFeivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
Why don't you just limit the max memory SQL Server uses?
Why should I limit it?
February 22, 2019 at 1:49 pm
Michael L John - Friday, February 22, 2019 1:36 PMFeivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
This is not a problem. It is how SQL is designed to work.
You need to determine what the correct max server memory setting may be for your server, and adjust it accordingly. Jonathan Kehayas's article posted above is the best place to start.You can then determine what queries are consuming the most resources, and perform addition tuning, But unless you limit the amount of memory allocated to SQL, you will not see a drop in memory usage.
And, if your infrastructure admin wants to see the database servers running at 10-15 percent memory like a file server or a web server, then he needs some education.
Max memory is already set to a decent value but the issue is that the memory is pegged at 90% and there is no way I could bring it down except to clear the cache or restart the SQL instance and let it build up but these are not worth the time because I want to know what is causing it to be remain pegeed in that state. Max memory is a ceiling and we not necessarily want SQL server to grab all of that memory and remain in that state forever.
February 22, 2019 at 1:51 pm
MattF
February 22, 2019 at 1:59 pm
Feivel - Friday, February 22, 2019 1:49 PMMichael L John - Friday, February 22, 2019 1:36 PMFeivel - Friday, February 22, 2019 12:28 PMOkay so the next step is if we know that sql server is the culprit then how do we basically get to the query that is causing memory spikes. I understand the dmv's help in figuring out what is the cause but we need to basically find out who is behind the cause. How would you end up finding that rogue query. Can you'll share some insights into the troubleshooting process.Thanks
This is not a problem. It is how SQL is designed to work.
You need to determine what the correct max server memory setting may be for your server, and adjust it accordingly. Jonathan Kehayas's article posted above is the best place to start.You can then determine what queries are consuming the most resources, and perform addition tuning, But unless you limit the amount of memory allocated to SQL, you will not see a drop in memory usage.
And, if your infrastructure admin wants to see the database servers running at 10-15 percent memory like a file server or a web server, then he needs some education.Max memory is already set to a decent value but the issue is that the memory is pegged at 90% and there is no way I could bring it down except to clear the cache or restart the SQL instance and let it build up but these are not worth the time because I want to know what is causing it to be remain pegeed in that state. Max memory is a ceiling and we not necessarily want SQL server to grab all of that memory and remain in that state forever.
That is the natural behavior of SQL server, it takes memory up to it's maximum allowed and keeps it. Is this actually causing an issue?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply