December 22, 2014 at 1:56 pm
Our SQL Server 2012 has 128GB memory. We configured to use 110,000. But around 10:30 PM when our maintenance plan runs within 5 minutes it peaks to 95% memory and it's not releasing. I restart the services around 6AM to release the memory and during our day its consistently between 50% to 55%. Today based on another forum everyone was saying the SQL Server using the memory available is ok so I didn't restart. But around 8:30 AM users started complaining the queries are running low and I have to restart to make users feel normal speed.
I attached our maintenance plan...
When I ran
select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
at memory is 95% the values are
Memory Used by SqLServer: 119329
Locked Pages Used SQLServer : 105969
Total VAS in MB : 8388607
Process Physical Memory Low : 0
Process Virtual Memory Low : 0
Any Suggestion..
In the other MSDN sql forum DBCC checkdb was the culprit and they asked me to not to do that then there is index and update statistics which will also create iese... Again the maintenance plan is simple and standated on,,
December 22, 2014 at 10:41 pm
Hi,
There could be a number of reason for High memory utilization. Lets start with the obvious:
Update Stats Task: You are using the default values for Update Stats Task i.e. For All objects and with Full Scan. Which means you are updating all the stats (Column and indexes) and SQL server will have to do a fullscan for updating these stats which will cause a LOT Of IO.
Solution: Instead of using a Inbuilt maintenance plan use a custom made script for index and stat maintenance. I personally love "ola hallengren" script.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html">
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
This script avoid creating stats for the indexes which are just rebuilt.
Memory Configuration: SSRS also consumes a lot of IO, Do you have set a memory cap for SSRS?
Lets start with these and check if it makes a significant difference.
Hope it Helps..!!!
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 23, 2014 at 1:40 am
There is nothing unusual about SQL using most of the memory on the server, that's how it's designed to work. You need to ensure that you have set max server memory to a sensible value based on the memory in the server and whatever else is running on the server.
110GB on a 128 GB server should be fine, but if you think it's using too much memory, drop max server memory down slightly.
Not doing CheckDB is not an option, that was a silly suggestion from whoever posted that MSDN post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply