November 25, 2018 at 6:48 pm
Hello Gurus,
I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
November 26, 2018 at 12:03 am
Hi,
what features are you using on the sql server? Only the database engine, or something like SSRS, SSIS, SSAS.
Not every feature is limited with the max memory setting.
Is there something else on the sql server running?
Kind regards,
Andreas
November 26, 2018 at 2:02 am
only DB engine , no SSIS SSRS OR SSAS
November 26, 2018 at 3:54 am
sqlguy80 - Sunday, November 25, 2018 6:48 PMHello Gurus,I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
If you mean SQL Server is using 90% of 50GB, then that's expected behavior - SQL Server will cache data and objects in RAM as and when they are used. Working with data already in memory is much faster than dragging it off disk first.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 26, 2018 at 6:36 am
ChrisM@Work - Monday, November 26, 2018 3:54 AMsqlguy80 - Sunday, November 25, 2018 6:48 PMHello Gurus,I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
If you mean SQL Server is using 90% of 50GB, then that's expected behavior - SQL Server will cache data and objects in RAM as and when they are used. Working with data already in memory is much faster than dragging it off disk first.
Just reinforcing this. SQL Server shouldn't be going beyond whatever limit you put on it in the OS. However, whatever that limit is, SQL Server will, by default, eventually use all of it. It's expected behavior and not a problem at all. In fact, it's a benefit because it's keeping the most commonly used data in memory, making the system faster.
I know I'm just repeating the very correct answer from Chris, but voting it up didn't seem adequate to help reinforce just how correct it is (also voted it up though).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2018 at 7:12 am
Grant Fritchey - Monday, November 26, 2018 6:36 AMChrisM@Work - Monday, November 26, 2018 3:54 AMsqlguy80 - Sunday, November 25, 2018 6:48 PMHello Gurus,I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
If you mean SQL Server is using 90% of 50GB, then that's expected behavior - SQL Server will cache data and objects in RAM as and when they are used. Working with data already in memory is much faster than dragging it off disk first.
Just reinforcing this. SQL Server shouldn't be going beyond whatever limit you put on it in the OS. However, whatever that limit is, SQL Server will, by default, eventually use all of it. It's expected behavior and not a problem at all. In fact, it's a benefit because it's keeping the most commonly used data in memory, making the system faster.
I know I'm just repeating the very correct answer from Chris, but voting it up didn't seem adequate to help reinforce just how correct it is (also voted it up though).
Thanks Grant, but the issue here is when I look task manager , memory utilization is 95% , and I reduce the max sql server memory , then over all memory utilization drops to say 90% and again goes up.. we get alerted from the OS , if the memory of the server exceeds 90%. I understand that changing the max memory for SQL server is bad as it will cause SQL Server to regenerate Exec plans and cache in memory.this is a dedicated VMware SQL database instance, so I should be seeing memory utilization at 50% or so but I see 90% . that's the issue.
November 26, 2018 at 8:01 am
sqlguy80 - Monday, November 26, 2018 7:12 AMGrant Fritchey - Monday, November 26, 2018 6:36 AMChrisM@Work - Monday, November 26, 2018 3:54 AMsqlguy80 - Sunday, November 25, 2018 6:48 PMHello Gurus,I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
If you mean SQL Server is using 90% of 50GB, then that's expected behavior - SQL Server will cache data and objects in RAM as and when they are used. Working with data already in memory is much faster than dragging it off disk first.
Just reinforcing this. SQL Server shouldn't be going beyond whatever limit you put on it in the OS. However, whatever that limit is, SQL Server will, by default, eventually use all of it. It's expected behavior and not a problem at all. In fact, it's a benefit because it's keeping the most commonly used data in memory, making the system faster.
I know I'm just repeating the very correct answer from Chris, but voting it up didn't seem adequate to help reinforce just how correct it is (also voted it up though).
Thanks Grant, but the issue here is when I look task manager , memory utilization is 95% , and I reduce the max sql server memory , then over all memory utilization drops to say 90% and again goes up.. we get alerted from the OS , if the memory of the server exceeds 90%. I understand that changing the max memory for SQL server is bad as it will cause SQL Server to regenerate Exec plans and cache in memory.this is a dedicated VMware SQL database instance, so I should be seeing memory utilization at 50% or so but I see 90% . that's the issue.
There's several things that could be causing what you're seeing.
Do you have multiple SQL instances, are you running Reporting services, Analysis Services, etc.
Also note, as pointed out here by Gail Shaw, max memory only limits the buffer pool, there are other parts of the SQL engine that will use memory outside that limit.
November 26, 2018 at 9:48 am
You're paying through the nose for the Enterprise Edition, you have 750 databases, and you're trying to do it all on 1/2 of 98GB? My recommendation would be to first buy some memory. Take it up to 256 GB total memory and allocate 200MB to the MAX memory.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2018 at 10:19 am
jasona.work - Monday, November 26, 2018 8:01 AMsqlguy80 - Monday, November 26, 2018 7:12 AMGrant Fritchey - Monday, November 26, 2018 6:36 AMChrisM@Work - Monday, November 26, 2018 3:54 AMsqlguy80 - Sunday, November 25, 2018 6:48 PMHello Gurus,I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
If you mean SQL Server is using 90% of 50GB, then that's expected behavior - SQL Server will cache data and objects in RAM as and when they are used. Working with data already in memory is much faster than dragging it off disk first.
Just reinforcing this. SQL Server shouldn't be going beyond whatever limit you put on it in the OS. However, whatever that limit is, SQL Server will, by default, eventually use all of it. It's expected behavior and not a problem at all. In fact, it's a benefit because it's keeping the most commonly used data in memory, making the system faster.
I know I'm just repeating the very correct answer from Chris, but voting it up didn't seem adequate to help reinforce just how correct it is (also voted it up though).
Thanks Grant, but the issue here is when I look task manager , memory utilization is 95% , and I reduce the max sql server memory , then over all memory utilization drops to say 90% and again goes up.. we get alerted from the OS , if the memory of the server exceeds 90%. I understand that changing the max memory for SQL server is bad as it will cause SQL Server to regenerate Exec plans and cache in memory.this is a dedicated VMware SQL database instance, so I should be seeing memory utilization at 50% or so but I see 90% . that's the issue.
There's several things that could be causing what you're seeing.
Do you have multiple SQL instances, are you running Reporting services, Analysis Services, etc.
Also note, as pointed out here by Gail Shaw, max memory only limits the buffer pool, there are other parts of the SQL engine that will use memory outside that limit.
only 1 sql server instance but 700 databases!
November 26, 2018 at 10:36 am
Jeff Moden - Monday, November 26, 2018 9:48 AMYou're paying through the nose for the Enterprise Edition, you have 750 databases, and you're trying to do it all on 1/2 of 98GB? My recommendation would be to first buy some memory. Take it up to 256 GB total memory and allocate 200MB to the MAX memory.
I am sure you meant 200GB to the MAX memory, not 200MB 🙂
November 26, 2018 at 11:10 am
sqlguy80 - Monday, November 26, 2018 10:19 AMjasona.work - Monday, November 26, 2018 8:01 AMThere's several things that could be causing what you're seeing.
Do you have multiple SQL instances, are you running Reporting services, Analysis Services, etc.
Also note, as pointed out here by Gail Shaw, max memory only limits the buffer pool, there are other parts of the SQL engine that will use memory outside that limit.only 1 sql server instance but 700 databases!
OK, we got that, but did you look at the comment in the link I posted?
The max server memory ONLY sets the memory for the buffer pool, that's it. As Gail posted, things like backup buffers, etc are outside that memory but they'll still "count" towards the total RAM used by the sqlsrvr.exe process.
And, as Jeff Moden pointed out, you're real trim on RAM for a box with the number of databases you've got, get with your VMWare team to see about shaking loose more RAM for that box.
November 26, 2018 at 1:04 pm
Sreekanth B - Monday, November 26, 2018 10:36 AMJeff Moden - Monday, November 26, 2018 9:48 AMYou're paying through the nose for the Enterprise Edition, you have 750 databases, and you're trying to do it all on 1/2 of 98GB? My recommendation would be to first buy some memory. Take it up to 256 GB total memory and allocate 200MB to the MAX memory.I am sure you meant 200GB to the MAX memory, not 200MB 🙂
Yes. Phat Phingering present. Thanks for the catch.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2018 at 12:40 pm
sqlguy80 - Sunday, November 25, 2018 6:48 PMHello Gurus,I had emailed earlier but didn't get any response and googled but didn't get enough info:
We have a prod SQL Server , sql server 2014 Ent Ed which has about 750 databases. Its a VMware virtual machine. 98 GB is allocated to the system and out of that I allocated 50 GB ( Max SQL Server memory ) so Utilization of SQL Server should be around 51%, However I got alerted several times that Memory of SQL Server is 90%. How is it possible?
What are the steps to keep memory utilization of SQL Server at 70% instead of 90%. Are there any steps other than reducing SQL Server Max memory or restarting SQL Server that can be done to reduce memory utilization?
Thanks in Advance.
Did you notice any memory consuming queries while the memory utilization was 90% of the allocated memory of sql server?
November 28, 2018 at 10:30 am
Just so every one knows , the sql server error log was the culprit which took about 36 GB memory , the IT guy used sysinternal tools to identify that. It first stores in memory before it writes to disk, something new for me with 8 years Experience :<. so though the Host had 96 GB memory and SQL server was only allocated 40 GB, it still showed 95% memory utilization. had to recycle sql server error log and purge the old file!
November 28, 2018 at 11:20 am
sqlguy80 - Wednesday, November 28, 2018 10:30 AMJust so every one knows , the sql server error log was the culprit which took about 36 GB memory , the IT guy used sysinternal tools to identify that. It first stores in memory before it writes to disk, something new for me with 8 years Experience :<. so though the Host had 96 GB memory and SQL server was only allocated 40 GB, it still showed 95% memory utilization. had to recycle sql server error log and purge the old file!
Purging the old file should not be necessary. That information is stored on disk. I also have a difficult time believing that the log file would consume 36GB of RAM but thanks for the heads up. It's worth checking into. If it turns out to be correct, that would seem to be a major flaw.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply