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
Description:
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

 GROUP BY TYPE

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

 GO


---Script to check buffer usage


WITH VAS_Summary AS

(

	SELECT Size = VAS_Dump.Size,

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

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

	FROM

	(

		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

		UNION

		SELECT

			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

		GROUP BY Size

	)

SELECT

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

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

FROM VAS_Summary WHERE FREE <> 0


---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,
                        dorb.TIMESTAMP
              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,
            DATEADD(ss,
                    (-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,
            xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]',
                     'bigint') AS AvailableVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]',
                     '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.

---Example:
Create clustered Index IDX_tablename_column on table(column)
WITH (SORT_IN_TEMPDB = ON)


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

http://raresql.com/2013/07/21/sql-server-hekaton-how-to-create-memory-optimized-table-in-an-existing-database/

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

http://blogs.msdn.com/b/joesack/archive/2009/01/08/find-non-buffer-pool-memory-memtoleave-in-private-bytes.aspx

http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/

http://sqlship.wordpress.com/2011/03/02/how-to-check-sql-server-is-facing-memory-pressure-using-windows-performance-monitor/

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: