Memory Issues
It can be external or internal.
Physical – External -
Physical RAM may be low and SQL Server detects and reduce the commit charge of the buffer pool.
Internal –Responding to external memory pressure
Changed memory settings
Changes in internal distribution
Virtual – External -Running low in Page file.
Internal – Running low on VAS due to fragmentation/Allocation.
External physical memory pressure
Open Task Manager in Performance view and check the Physical Memory section, Available value. If the available memory amount is low, external memory pressure may be present. The exact value depends on many factors, however you can start looking into this when the value drops below 50-100 MB. External memory pressure is clearly present when this amount is less than 10 MB.
Memory: Available Bytes counter in System Monitor.
If external memory pressure exists and you are seeing memory-related errors, you will need to identify major consumers of the physical memory on the system. To do this, look at Process: Working Set performance counters or the Mem Usage column on the Processes tab of Task Manager and identify the largest consumers.
sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.
External virtual memory pressure
Check the Commit Charge section. If Total is close to the Limit, then there exists the potential that page file space may be running low.
Paging File: %Usage, Paging File: %Usage Peak.
If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”.
Internal physical memory pressure
To determine the amount of memory that belongs to the buffer pool, we can take a look at the DBCC MEMORYSTATUS output. In the Buffer Counts section, look for the Target value
System Monitor (Perfmon)
You can also check the following counters for signs of memory pressure (see SQL Server Books Online for detailed description):
SQL Server: Buffer Manager object
• Low Buffer cache hit ratio
• Low Page life expectancy
• High number of Checkpoint pages/sec
• High number Lazy writes/sec
Internal virtual memory pressure
VAS consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV. VAS summary can be queries using the following view.
The following list outlines general steps that will help you troubleshoot memory errors.
1. Verify if the server is operating under external memory pressure. If external pressure is present, try resolving it first, and then see if the problem/errors still exist.
2. Start collecting performance monitor counters for SQL Server: Buffer Manager, SQL Server: Memory Manager.
3. Verify the memory configuration parameters (sp_configure), min memory per query, min/max server memory, awe enabled, and the Lock Pages in Memory privilege. Watch for unusual settings. Correct them as necessary. Account for increased memory requirements for SQL Server 2005.
4. Check for any nondefault sp_configure parameters that might indirectly affect the server.
5. Check for internal memory pressures.
6. Observe DBCC MEMORYSTATUS output and the way it changes when you see memory error messages.
7. Check the workload (number of concurrent sessions, currently executing queries).
DBCC FREESYSTEMCACHE -Releases all unused cache entries from all caches.
DBCC FREESESSIONCACHE-Flushes the distributed query connection cache
DBCC FREEPROCCACHE-Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle.
701 - There is insufficient system memory to run this query.
802 - There is insufficient memory available in the buffer pool.
8628 - A time out occurred while waiting to optimize the query. Rerun the query.
8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query.
Slow running queries
The cause of the blocking can be a poor application design, bad query plans, the lack of useful indexes, and an SQL Server instance that is improperly configured for the workload.
Blocking
sys.dm_os_wait_statistics for overall and cumulative waits for SQL Server to the session-specific sys.dm_os_waiting_tasks that breaks down waits by session
To find sessions that have been granted locks or waiting for locks, you can use the sys.dm_tran_locks DMV
sp_block.
For example, a 200-second blocked process threshold can be configured in SQL Server Management Studio as follows:
8. Execute Sp_configure ‘blocked process threshold’, 200
9. Reconfigure with override
Once the blocked process threshold is established, the next step is to capture the trace event. The trace events of blocking events that exceed the user configured threshold can be captured with SQL Trace or Profiler.
10. If using SQL Trace, use sp_trace_setevent and event_id=137.
11. If using SQL Server Profiler, select the Blocked Process Report event class (under the Errors and Warnings object). See Figure 2.
