February 22, 2019 at 2:03 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.
How much memory does the server have?
What is the setting for Max memory?
When you say "memory is pegged at 90%", does 90% represent what the max server memory setting is in relation to the total RAM on the server?
If so, then this is how SQL is supposed to work,. It will not release the memory back to the OS unless it has to. If there are frequent times where the OS is getting SQL to release memory, then you need to either add more memory or the max memory setting is set too high. 2 GB that Jonathan Roberts had recommended may be too low.
If you are expecting SQL to "go back" to the initial memory that it consumes after a re-boot or a restart of SQL, then that is not how it is designed to work.
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 2:13 pm
I believe I was looking something similar to this in order to get which session or code was taking up the whole chunk of memory due to maybe poorly written code etc.
SELECT TOP 5 * FROM sys.dm_exec_sessions
ORDER BY memory_usage DESC;
February 22, 2019 at 2:15 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.
But that's what SQL Server will do....grab the memory it can. You can read about how it will release some when it can and if needed by the OS but the reality is it will release memory when it feels like it. Eventually.
I get what you are saying about the importance of who and not necessarily what was being executed. Especially if you have users who are allowed to do ad hoc queries. Not sure about everything with your situation and the spikes but another option is you can query the sessions and see the resource usage by who and what the login session is using. It's by session so you need to take that into account but it could be along the lines of something you are looking for. It can be as simple as a select * from sys.dm_exec_sessions or you can just include the information you want, limit it to user sessions only...along the lines of: SELECT
session_id,
login_time,
program_name,
login_name,
status,
last_request_start_time,
last_request_end_time,
cpu_time,
memory_usage,
reads,
writes
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
But that's something you may want to play around with. You can add more information, include system and user processes. You can join to other DMVs and find out what statements are running, etc. I just threw that out as you seem to want something a bit more basic to find out who is using the resources and things seem to be moving away from the who.
You would want to follow up on the max memory settings though. SQL Server will hit that max and it's often not an issue when it does.
Sue
February 22, 2019 at 2:17 pm
ZZartin - Friday, February 22, 2019 1:59 PMFeivel - 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?
Yes it is causing alarm bells to ring and management wants to know the actual reason why it is pegged at 90%. It is difficult to make them understand why SQL does what it does but they are really up my sleeve as to why for say x number of days we didn't had the alarm go off as the memory usage was normal but now it is kinda held its ground at that threshold.
February 22, 2019 at 2:19 pm
Sue_H - Friday, February 22, 2019 2:15 PMFeivel - 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.
But that's what SQL Server will do....grab the memory it can. You can read about how it will release some when it can and if needed by the OS but the reality is it will release memory when it feels like it. Eventually.
I get what you are saying about the importance of who and not necessarily what was being executed. Especially if you have users who are allowed to do ad hoc queries. Not sure about everything with your situation and the spikes but another option is you can query the sessions and see the resource usage by who and what the login session is using. It's by session so you need to take that into account but it could be along the lines of something you are looking for. It can be as simple as a select * from sys.dm_exec_sessions or you can just include the information you want, limit it to user sessions only...along the lines of:SELECT
session_id,
login_time,
program_name,
login_name,
status,
last_request_start_time,
last_request_end_time,
cpu_time,
memory_usage,
reads,
writes
FROM sys.dm_exec_sessions
WHERE is_user_process = 1But that's something you may want to play around with. You can add more information, include system and user processes. You can join to other DMVs and find out what statements are running, etc. I just threw that out as you seem to want something a bit more basic to find out who is using the resources and things seem to be moving away from the who.
You would want to follow up on the max memory settings though. SQL Server will hit that max and it's often not an issue when it does.Sue
You got it Sue that is what I am after I need to know who and not necessarily what was being executed as that will chime in later during my investigation. Hopefully, I can catch the session and nail it down. I've seen cases where stored procs or adhoc sql statements that are not parameterized can cause severe memory pressure.
February 22, 2019 at 2:45 pm
Feivel - Friday, February 22, 2019 2:17 PMYes it is causing alarm bells to ring and management wants to know the actual reason why it is pegged at 90%. It is difficult to make them understand why SQL does what it does but they are really up my sleeve as to why for say x number of days we didn't had the alarm go off as the memory usage was normal but now it is kinda held its ground at that threshold.
I'd be more concerned that there is so much unused memory if it's 10%. What else is running on this server besides the operating system and SQL Server?
February 22, 2019 at 2:57 pm
Chris Harshman - Friday, February 22, 2019 2:45 PMFeivel - Friday, February 22, 2019 2:17 PMYes it is causing alarm bells to ring and management wants to know the actual reason why it is pegged at 90%. It is difficult to make them understand why SQL does what it does but they are really up my sleeve as to why for say x number of days we didn't had the alarm go off as the memory usage was normal but now it is kinda held its ground at that threshold.I'd be more concerned that there is so much unused memory if it's 10%. What else is running on this server besides the operating system and SQL Server?
Unfortunately, that is not how management thinks over here.
February 22, 2019 at 3:12 pm
Feivel - Friday, February 22, 2019 2:57 PMChris Harshman - Friday, February 22, 2019 2:45 PMFeivel - Friday, February 22, 2019 2:17 PMYes it is causing alarm bells to ring and management wants to know the actual reason why it is pegged at 90%. It is difficult to make them understand why SQL does what it does but they are really up my sleeve as to why for say x number of days we didn't had the alarm go off as the memory usage was normal but now it is kinda held its ground at that threshold.I'd be more concerned that there is so much unused memory if it's 10%. What else is running on this server besides the operating system and SQL Server?
Unfortunately, that is not how management thinks over here.
The easiest way to explain it is that if your databases are bigger than server memory SQL Server will eat up as much memory as you let it. This is actually a good thing as it is designed to minimize disk IO which is generally far worse for performance.
They simply need to turn off that alert on SQL server because it's not telling them anything relevant.
February 22, 2019 at 3:20 pm
ZZartin - Friday, February 22, 2019 3:12 PMFeivel - Friday, February 22, 2019 2:57 PMChris Harshman - Friday, February 22, 2019 2:45 PMFeivel - Friday, February 22, 2019 2:17 PMYes it is causing alarm bells to ring and management wants to know the actual reason why it is pegged at 90%. It is difficult to make them understand why SQL does what it does but they are really up my sleeve as to why for say x number of days we didn't had the alarm go off as the memory usage was normal but now it is kinda held its ground at that threshold.I'd be more concerned that there is so much unused memory if it's 10%. What else is running on this server besides the operating system and SQL Server?
Unfortunately, that is not how management thinks over here.
The easiest way to explain it is that if your databases are bigger than server memory SQL Server will eat up as much memory as you let it. This is actually a good thing as it is designed to minimize disk IO which is generally far worse for performance.
They simply need to turn off that alert on SQL server because it's not telling them anything relevant.
It could be better if it is minimizing disk io but not releasing memory is certainly not right and if this is caused because of some poorly written query which assuming is the actual reason behind it. Trimming the memory is not a solution as it might be the case that the query might page out to the disks and cause more pains spitting about memory pressure in the logs...so bottom line don't turn off the knob but act upon it and remedy the issue.
February 22, 2019 at 3:33 pm
Feivel - Friday, February 22, 2019 3:20 PMZZartin - Friday, February 22, 2019 3:12 PMFeivel - Friday, February 22, 2019 2:57 PMChris Harshman - Friday, February 22, 2019 2:45 PMFeivel - Friday, February 22, 2019 2:17 PMYes it is causing alarm bells to ring and management wants to know the actual reason why it is pegged at 90%. It is difficult to make them understand why SQL does what it does but they are really up my sleeve as to why for say x number of days we didn't had the alarm go off as the memory usage was normal but now it is kinda held its ground at that threshold.I'd be more concerned that there is so much unused memory if it's 10%. What else is running on this server besides the operating system and SQL Server?
Unfortunately, that is not how management thinks over here.
The easiest way to explain it is that if your databases are bigger than server memory SQL Server will eat up as much memory as you let it. This is actually a good thing as it is designed to minimize disk IO which is generally far worse for performance.
They simply need to turn off that alert on SQL server because it's not telling them anything relevant.
It could be better if it is minimizing disk io but not releasing memory is certainly not right and if this is caused because of some poorly written query which assuming is the actual reason behind it. Trimming the memory is not a solution as it might be the case that the query might page out to the disks and cause more pains spitting about memory pressure in the logs...so bottom line don't turn off the knob but act upon it and remedy the issue.
It's not a case of it not releasing memory, SQL Server is in fact still using the memory to keep the data it's pulled in memory and readily available. There is potentially no issue, even a server with perfect queries will eventually grab all the memory it can if it needs to.
February 22, 2019 at 3:47 pm
Michael L John - Friday, February 22, 2019 2:03 PMFeivel - 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.
How much memory does the server have?
What is the setting for Max memory?When you say "memory is pegged at 90%", does 90% represent what the max server memory setting is in relation to the total RAM on the server?
If so, then this is how SQL is supposed to work,. It will not release the memory back to the OS unless it has to. If there are frequent times where the OS is getting SQL to release memory, then you need to either add more memory or the max memory setting is set too high. 2 GB that Jonathan Roberts had recommended may be too low.If you are expecting SQL to "go back" to the initial memory that it consumes after a re-boot or a restart of SQL, then that is not how it is designed to work.
And you don't even have to restart SQL Server to change the amount of memory used. Just lower the Max Server Memory (this will decrease the amount used) then increase it again, then you've changed it without a restart.
February 23, 2019 at 7:42 am
Feivel - Friday, February 22, 2019 3:20 PMIt could be better if it is minimizing disk io but not releasing memory is certainly not right and if this is caused because of some poorly written query which assuming is the actual reason behind it. Trimming the memory is not a solution as it might be the case that the query might page out to the disks and cause more pains spitting about memory pressure in the logs...so bottom line don't turn off the knob but act upon it and remedy the issue.
I guess again I have to ask what else is running on that computer besides the operating system and SQL Server? Is there something else that needs the memory?
Also how much RAM is there in the computer?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply