Less Server Memory? – Technical Steps to avoid increasing more memory

“Century Gothic” font & Memory chapter in regards to SQL Server is one of my favorite & I always preferred to reduce the CPU & memory pressure by analysing queries running for long time

It has been proved that there is no 100% performance benefit just by adding more memory.

As I remember, earlier we often used to enable AWE or add 3G/PAE switches in boot.ini, in 4GB scenario.

Now, there are two type of Memory Pressure –

1. External (OS level):
1. Physical – Dynamic memory pressure – Windows runs low on available RAM.
2. Physical – Static memory pressure – System runs out of page file.

Log Name:      System
Source:        Microsoft-Windows-Resource-Exhaustion-Detector
Event ID:      2004
Task Category: Resource Exhaustion Diagnosis Events
Level:         Warning
Keywords:      Events related to exhaustion of system commit limit (virtual memory).
User:          SYSTEM
Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlserver.exe (4820) consumed 11282399232 bytes, svchost.exe (868) consumed 590950400 bytes, and w3wp.exe (21092) consumed 562757632 bytes.

2. Internal (Application level):
3. Physical memory pressure – Due to (1) & (2) or DBA set max memory
4. VAS memory pressure – VAS fragmentation or consuming whole VAS

Error message.

FIX: “There is insufficient system memory in resource pool ‘internal’ to run this query”

error message when you run a full-text query that uses compound words in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2

Date Time spidID Error: 701, Severity: 17, State: 123.
Date Time spidID There is insufficient system memory in resource pool ‘internal’ to run this query.


Step 1

List of Databases taking more CPU.

Step 2

Collect the list of databases taking more memory/using more buffer. Here is the query

---Script to check if CPU pressure is high or low – Signal Waits for Instance
-- Signal Waits above 10-15% is usually a sign of CPU pressure

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) 
ASNUMERIC(20,2))AS [%signal (cpu) waits],CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) 
/ SUM(wait_time_ms) AS NUMERIC(20,2))AS [%resource waits]FROM sys.dm_os_wait_stats OPTION (RECOMPILE);

--- SQL Server Script to check the Internal and External memory Pressure

 SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressure

 FROM sys.dm_os_memory_clerks


 ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC


---Script to check buffer usage



	SELECT Size = VAS_Dump.Size,

	Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 0 ELSE 1 END),




		SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],

			region_allocation_base_address [Base]

			FROM sys.dm_os_virtual_address_dump

		WHERE region_allocation_base_address <> 0

		GROUP BY region_allocation_base_address



			CONVERT(VARBINARY, region_size_in_bytes) [Size],

			region_allocation_base_address [Base]

		FROM sys.dm_os_virtual_address_dump

		WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump




	SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],

	CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]


---Identifying External Memory Pressure with dm_os_ring_buffers and RING_BUFFER_RESOURCE_MONITOR

WITH    RingBuffer
          AS (SELECT    CAST(dorb.record AS XML) AS xRecord,
              FROM      sys.dm_os_ring_buffers AS dorb
              WHERE     dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
    SELECT  xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
            xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
            xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
                    (-1 * ((dosi.cpu_ticks / CONVERT (FLOAT, (dosi.cpu_ticks / dosi.ms_ticks)))
                           - rb.TIMESTAMP) / 1000), GETDATE()) AS RmDateTime,
            xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
            xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
            xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
            xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
            xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
            xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
            xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
            xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
            xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
            xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
            xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
                     'bigint') AS AvailableVirtualAddressSpace,
                     'bigint') AS AvailableExtendedVirtualAddressSpace
    FROM    RingBuffer AS rb
            CROSS APPLY rb.xRecord.nodes('Record') record (xr)
            CROSS JOIN sys.dm_os_sys_info AS dosi
    ORDER BY RmDateTime DESC;

Step 3

Analyse the selective resource taking databases.

Step 4

Review the windows/sql log.

Step 5

Capture the Internal & External Memory usage, Performance Counters, Waittype & CPU percent [during business hours, keep data of atleast 4 hours] before doing improvement changes, to be able to compare. Use below queries.

Step 6

Virtual Memory should be atleast 150% of total physical memory in server.

Step 7

Reduce the number of VLF in log file (DBCC log info) for all these databases. should be less than 50.

Step 8

Analyse high CPU/Memory used by queries [only for those databases selected in step 1 & 2]. Here is the query.

Step 9

Review Execution plan & apply missing indexes or correct bad query plan.

Step 10

Remove Exact Duplicate Indexes & Unused Statistics.

Step 11

Analyse Index Fragmentation after couple of hours when maintenance jobs completed.

–   Schedule the maintenance plan for index & statistics accordingly.

Step 12

When you rebuild an index, the database engine creates “intermediate sort result.” These results are like chunks that are combined to become your index and creating these chunks requires space. If it can the database engine will actually sort the index in memory; but if not it will store the intermediate results in the destination file group.

Index Build strategy in SQL Server may vary depending on users needs, building Index, storing the “intermediate sort result” in user’s database vs. storing in tempdb database (SORT_IN_TEMPDB):

When you create or rebuild Index Using the SORT_IN_TEMPDB option, it asks SQL Server to store these intermediate results in TempDB instead of rebuilding them in the source database. When the TempDB resides on a different disk to the source database this can help the performance of the rebuild task and can reduce the performance impact on your database.

– SORT_IN_TEMPDB index option is used to set the desirable behavior. When set to ON, the sort results are stored in tempdb. When OFF, the sort results are stored in the filegroup or partition scheme in which the resulting index is stored.

Create clustered Index IDX_tablename_column on table(column)

A requirement of rebuilding indexes is that you must have sufficient space (wherever you decide to build the index) to hold the largest index in your database: this in your example is 10GB. Which, if you use TempDb you should quite easily have.

Note:- If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Step 13

Disable Auto Shrink DB option.

Step 14

Move heavy read-write indexes to different filegroup to have extent uniformity [probably we may need to rebuild].

Step 15

Split the backup for atleast 4 files for the large databases [more than 100GB]

Step 16

Change the default fillfactor setting to 80%. Use SP_Configure

Step 17

Analyse the large DELETE operations/Procedures and modify in small chunk/batches to avoid table lock for long time. [this would also issue checkpoint on commit, so logs are getting cleared from tempdb/memory continuously], SET Auto_update_statistics option OFF before running these procedures and re-enable after completion.

Step 18

Analyse the large tables to implement table partitioning.

Step 19

Create Memory Optimized Table (Recent SQL Server version).

Step 20

Tempdb – Add number of datafiles per CPU.

Step 21

Re-schedule backup & maintenance jobs to ensure there is no conflict with application jobs.

–   Check if we can take DIFF backup instead of daily FULL.

Step 22

Again capture the Internal & External Memory usage, Waittype & CPU percent [during business hours, keep data of atleast 4 hours] one week after making these changes or after sql restart to compare.

Step 23

Standard SQL Server Drive’s Layout

C: System/Programme files (local drive)
D: SQL install drive, System DBs (Master, MSDB) (local drive)
E: Backup drive (RAID 10 preferred, RAID 6 alt.)
F: User DB data files (RAID 10 preferred, RAID 6 alt.)
G: User DB log files (RAID 10 preferred, RAID 6 alt.)
H: tempdb DB files (RAID 10/1 preferred, RAID 6 alt.)

We need to make sure to have atleast Tempdb on different physical drive



Phase 2

Analyse Disk related issue/latency etc. Performance Counters.

Great Articles


Find Non-Buffer Pool Memory (MemToLeave) in “Private Bytes”






Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: