Task Manager not showing correct SQL Server memory usage

Task Manager is not showing the correct SQL Server memory usage. I have a SQL Server 2008 running on a Windows 2008 64 bit server.The task manager shows 100k , but I know I’n using 24 GB. What server configurations cause this problem?

Task Manager should not be used to report on SQL Server memory usage . Use the physical_memory_in_use_kb on the sys.dm_os_process_memory. Here’s the strange thing – this query will indicate whether locked pages is enabled. If locked pages is enabled ,Task Manager may not show all the memory used by SQL Server.

 

SELECT physical_memory_in_use_kb,locked_page_allocations_kb, 
       memory_utilization_percentage, 
  FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
   

 

To check if Lock Pages is enabled read Cannot use Large Page Extensions: lock memory privilege was not granted.

Note:  If the SQL Service Account is using a Local Administrator account, the Lock Pages in Memory is inherited as part of the elevated permissions in the OS.  By default,  Locked Pages in memory is off, but using a Local Administrator Account as a SQL Service Account is not a good idea and open to security breaches.

 

Read More

MEMORYCLERK_SQLGENERAL and sql memory memory usage

SQL Server – sys.dm_exec_sessions and troubleshooting sql memory usage

SQL Server Performance ,memory pressure and memory usage

Powershell,PSEexec and lock pages in memory – made easy

SQL Error 1204 - cannot obtain a LOCK resource at this time

A DBA reported the error message “Error: 1204, Severity: 19, State: 4. cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.”

The message appeared during the daily SQL Server Error log report which checks for SQL Server Error log messages of interest to DBAs.

The SQL error 1204 message appears when memory thresholds are met.

SQL Server cannot obtain more memory from the Operating System. The memory is used by other resources or SQL Server has max server memory configured.

Lock manager doesn’t use more than 60% of memory available to SQL Server

 Possible Fixes

1) Release other applications using resources on the OS – use Task Manager  to identify other applications using the largest amount of memory.  Most DBAs attempt to maintain SQL Server on dedicated servers , but management software can creep in – such as Anti-Virus , monitoring agents and security analysis.

2) Increase the SQL Server max memory setting, if extra memory is available .

3) Use the sys.dm_tran_locks view to identify the session holding the largest amount of locks and kill the process. Before you kill the transaction record the details to

Read More

SQL Server – Find sql server Locks - SQL Server DBA

SQL Server - Use sys.dm_tran_locks to find requests waiting for an exclusive lock

SQL Server Performance ,memory pressure and memory usage

MEMORYCLERK_SQLGENERAL and sql memory memory usage

Question: One question. I've run a query that returns top 10 consumers... there was a record as

MEMORYCLERK_SQLGENERAL 31345424

what is the  MEMORYCLERK_SQLGENERAL ?

Answer: The MEMORYCLERK_SQLGENERAL is a general memory clerk. It’s responsible for a number of SQL activities such as XML preparation. Other busier memory clerks , such as MEMORY_CLERK_SQLBUFFERPOOL  , get their own clerk.

Memory Clerks are consumers of memory in SQL Server. There are four kinds of clerks,  1) generic 2) cache store 3)object store 4) user store. The memory clerks use the memory node allocators to allocate memory.

SQL Server  predefines Memory Clerks. Each clerk relates allocation type required.

The sys.dm_os_memory_clerks  DMV and DBCC MEMORYSTATUS  reports the clerk information . The DMV sys.dm_os_memory_clerks - Returns the set of all memory clerks that are currently active in the instance of SQL Server

 

--Use the DMV to see how much memory relates to 'MEMORYCLERK_SQLGENERAL'

select * from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLGENERAL'

 

For a detailed breakdown Memory objects allocated by SQL Server

Read More

SQL Server – Min Memory Per query and performance

SQL Server - How much resource is a SQL database using - memory, disk, IOPS,CPU

 

Memory objects allocated by SQL Server

Once SQL Server memory pressure is identified – drill down into  what objects are SQL Server allocated . From this query , you can get clues , about which objects are using the highest amount of memory .

The DMV Sys.dm_os_memory_objects - Returns current SQL Server allocations of memory objects.

The sys.dm_os_memory_clerks Returns the set of all memory clerks that are currently active in the instance of SQL Server

Joined at the “page_allocator_address” . It’s a unique address for a memory clerk

SELECT SUM (omo.pages_allocated_count * omo.page_size_in_bytes)/1024 as 'KB_Used', 
omo.type as 'Memory Object Type', 
omc.type as 'Memory Clerk Type'

FROM sys.dm_os_memory_objects omo

join sys.dm_os_memory_clerks omc on omo.page_allocator_address=omc.page_allocator_address

GROUP BY omo.type, omc.type, omc.type

ORDER BY 1 DESC;

 Read More

SQL Server – Troubleshoot memory pressure with Default Trace and Server

SQL Server – Troubleshooting Internal Memory pressure – stolen pages and buffer count

SQL Server – Memory: Pages sec, memory pressure and thrashing

SQL Server Performance ,memory pressure and memory usage

 

SQL Server – Min Memory Per query and performance

Question: Will adjusting the  Min Memory Per query option improve SQL query performance?

Answer: According to SQL Server Books Online the min memory per query Option  will “specify the minimum amount of memory (in kilobytes (KB)) that are allocated for the execution of a query.”  

 If the min memory option is set at 1024 KB , the query receives as  minimum that amount of memory. In reality a query with a large SORT operation may receive more than the min memory setting.

 Use sp_configure to view the current Min Memory Per query

 Some notes:

1)It is possible increasing the min memory option will help query performance – but if the system is busy\large  and multiple transactions are occurring  on large datasets, this could lead to memory constraint issue elsewhere.

Keep in mind – the query must wait for memory allocation until it can secure the min memory before query execution

2) If  the query requires more memory , and the memory is available, SQL Server will grant the extra memory .As this feature exists – it is not advisable to adjust the Min Memory Per query option

Read More

 SQL Server Performance ,memory pressure and memory usage

SQL Server – SORT IN TEMPDB and Sort Warnings - SQL Server DBA

Performance Top Queries by Total CPU - SQL Server DBA

SQL Server – Measuring SQL memory usage - SQL Server DBA