October 1, 2015 at 8:24 am
hi friends,
Can you please help me in configuring sql alert for low sql memory.
I have configured the memory for sql server as below
min : 512 MB
Max :13500 MB
Total RAM on the server is 16GB
I want to recieve email from sql server informing that sql server is running on low memory ( less than 200 MB free)
so that I do not get out of memory issues and the sql server continues to run without fail.
Regards,
Tauseef
October 1, 2015 at 8:28 am
What memory? Memory of the server as a whole? Memory that is allocated to SQL?
October 2, 2015 at 1:37 am
One way is to create an alert in the SQL Server Agent with the type SQL Server Performance Condition Alert. Set the Object, Counter, Instance and type of alert condition. Add an operator to be notified when the alert is triggered.
October 2, 2015 at 3:55 am
SQL Surfer '66 (10/2/2015)
One way is to create an alert in the SQL Server Agent with the type SQL Server Performance Condition Alert. Set the Object, Counter, Instance and type of alert condition. Add an operator to be notified when the alert is triggered.
Thanks for the solution. I managed to create the alert as you mentioned. I also tried the method given here http://blog.oraylis.de/2014/02/automatic-handling-of-external-memory-pressure-on-a-sql-server.
But i got a WMI error stating that my namespace was not correct. any help in that case is appreciated
October 2, 2015 at 5:43 am
Did you set the type to WMI event alert? I don't know much about WMI namespaces. I use the type SQL Server Performance Condition Alert instead, which uses performance counters.
October 2, 2015 at 7:33 am
SQL Surfer '66 (10/2/2015)
Did you set the type to WMI event alert? I don't know much about WMI namespaces. I use the type SQL Server Performance Condition Alert instead, which uses performance counters.
I want to create an alert to notify me that my sql server has consumed more than 85% of allocated memory.
PLEASE HELP
October 2, 2015 at 7:48 am
Whats the amount of RAM in the box and the max amount of RAM allocated to SQL?
October 2, 2015 at 7:55 am
Physical RAM on the box = 24 Gb
MAXmemory allocated to SQL server = 20 GB
Min memory to sql server = 512 MB.
Still SQL server is freezing.
October 2, 2015 at 7:55 am
tauseef.jan (10/2/2015)
SQL Surfer '66 (10/2/2015)
Did you set the type to WMI event alert? I don't know much about WMI namespaces. I use the type SQL Server Performance Condition Alert instead, which uses performance counters.I want to create an alert to notify me that my sql server has consumed more than 85% of allocated memory.
PLEASE HELP
I have configured the memory for sql server as below
min : 512 MB
Max :13500 MB
Total RAM on the server is 16GB
Not only is it normal, it's advantageous for SQL Server to consume the full maximum of memory allocated to it. The bulk of the memory allocated to SQL Server is sub-allocated to the page buffer cache. Pages in memory have a limited lifespan and get swapped in and out based on their frequency of usage. Memory available to SQL Server is only an issue if queries have to start waiting for memory grants or if page life expectancy is low, meaning which is the proportion of pages read by your query relative to the number that were fetched from memory cache.
http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx
If your queries frequently page scan 20 GB sized tables on a server with only 16 GB allocated to SQL Server, then maybe it's time to add more memory to the server. But it's normal on any server, even a server that's utilized a few times a day by a single user, to buffer previously read pages into the cache until it reaches the maximum allocation. Nothing bad happens when the max is reached, the pages are held there for re-use by other queries, until they are simply swapped out to make room for pages required by subsequent queries.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 2, 2015 at 8:01 am
tauseef.jan (10/2/2015)
Physical RAM on the box = 24 GbMAXmemory allocated to SQL server = 20 GB
Min memory to sql server = 512 MB.
Still SQL server is freezing.
How is SQL freezing, what issues are you getting? What have you diagnosed to detail its a memory issue?
October 2, 2015 at 8:06 am
i am getting out of memory pop ups and there are many ad hoc queries running on the server.
The only solution seems to be restarting the server, but IT IS PRODUCTION...:crying:
October 2, 2015 at 8:09 am
Out of memory popups in SSMS? The App?
Please post screenshots.
Please remember we cant see what you can see, so please provide as much detail as you can.
October 2, 2015 at 9:40 am
Read this article. It will tell you for each active session how to get the KB memory requested by running query, actual KB granted, and KB considered ideal by optimizer for the query plan. This will give you idea about whether the amount of memory available to SQL Server is sufficient to cover the actual queries running.
Memory Grant Affecting Query Performance
http://www.theboreddba.com/Categories/queryTuning/Memory-Grant-Affecting-Query-Performance.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 2, 2015 at 9:58 am
Run this to get a breakdown of how much memory is allocated to each memory cache, compare page buffer cache (CACHESTORE_OBJCP) to query plan cache (CACHESTORE_SQLCP).
Select [type],SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from sys.dm_os_memory_cache_counters
Group By [type]
Order By SUM([single_pages_kb])+ SUM([multi_pages_kb]) desc;
This will breakdown memory allocated for each type of query plan:
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs – USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
For servers that run a lot of ad-hoc queries, the sql plan cache (CACHESTORE_SQLCP) can get quite large, even rivaling the page buffer cache on relatively small servers like yours with only 20 GB of RAM installed. What happens is that you could end up with thousands, or hundreds of thousands of query plans cached that are only used once.
There is a server option called 'optimize for ad hoc workloads'. Normally a DBA wants to think carefully about whether or not to turn this on in production, but if you're having to re-boot the server anyhow just to keep it from bogging down, then you may want to skim over this and consider giving it a shot. Assuming of course that the above queries reveal that it's your plan cache that consuming most or even half of your memory.
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 2, 2015 at 5:24 pm
tauseef.jan (10/2/2015)
i am getting out of memory pop ups and there are many ad hoc queries running on the server.
SQL Server won't give you popups, it's a server process, it doesn't have a UI. So, where are the errors being thrown from and by what application?
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply