SQL - Space Issue

 

1 Log Space - dbcc sqlperf(logspace) 1

1.1 step 1: minimize the log space usage. 1

1.2 step 2: truncate the log file   [perform backup of the database log] 2

1.3 step 3: release empty pages from log file 2

1.4 step 4: consider increase in size of log space 2

1.5 step 5: consider to reset the recovery model 2

1.5.1 Recovery Models 2

1.5.1.1 Full 2

1.5.1.2 Simple 2

1.5.1.3 bulk logged 2

1.6 rebuild log file  (during maintenance hours) 2

2 TEMPDB space issue 3

2.1 STEP 1: MINIMISE THE USE OF TEMPDB. 3

2.2 STEP 2: RELEASE EMPTY PAGES FROM TEMPDB 3

2.3 STEP 3: CONSIDER TO INCREASE SIZE OF TEMPDB DATA FILES 3

2.4 STEP 4: CONSIDER TO INCREASE THE NUMBER OF TEMPDB DATA FILES 3

2.5 REBUILD TEMPDB file  (during maintenance hours) 3

3 Memory Space Issue 3

3.1 MONITOR MEMORY USAGE 3

3.2 Steps to solve Memory Issue 4

3.2.1 STEP 1: MINIMISE THE USE OF MEMORY, IF POSSIBLE. 4

3.2.2 STEP 2: RELEASE EMPTY PAGES FROM MEMORY 4

3.2.3 STEP 3: CONSIDER TO INCREASE MEMORY CAPACITY 4

1. Log Space - dbcc sqlperf(logspace)

• select * into testable from sysmessages 

• -- to audit log space usage:

• dbcc sqlperf(logspace) -- database consistency check command

1. step 1: minimize the log space usage. 

• avoid open transactions if possible

• avoid long running queries

• avoid the use of profiler tool

• avoid the use of dta tool

• ensure no query blocking

• 
  

2. step 2: truncate the log file   [perform backup of the database log]

• backup log retaildatabase to disk = 'logbackup.trn'

3. step 3: release empty pages from log file

• shrinking is a mechanism to identify the empty pages from the given file / database and also to release those identified empty pages to the operating sytem.

• use retaildatabase

• go

• dbcc shrinkfile('retaildatabase_log', 20) -- release all pages leaving atleast 20% pages

4. step 4: consider increase in size of log space 

It is recommended to increase the size of the log by 25% of current size

alter database retaildatabase

modify file 

(name = 'retaildatabase_log', size = 1250 mb)

5. step 5: consider to reset the recovery model

• every database operations are automatically monitored in log files.

• recovery models are database option to control the level of logging by database log file.

1. Recovery Models 

1. Full

• every operation is completely logged. used for easy data recovery

2. Simple

• every operation is minimally logged. used for faster data access

• for slow query response rate, set the recovery model to "simple" if possible.

• alter database retaildatabase set recovery simple

3. bulk logged

• every operation is completely logged except for bulk operations

• all bulk operations (import..export) are minimally logged.

6. rebuild log file  (during maintenance hours)

use master

go

alter database retaildatabase set emergency with rollback immediate  -- means to disconnect all users

go

alter database retaildatabase

rebuild log on  

(name = 'retaildatabase_log', filename = 'e:\newfile.ldf')

2. TEMPDB space issue

-- AUDIT & RESOLVE TEMPDB SPACE ISSUES:

SELECT * FROM TEMPDB.SYS.dm_db_file_space_usage 

SELECT * FROM TEMPDB.SYS.dm_db_session_space_usag

-- GIVEN A SESSION ID, HOW TO KNOW THE CONNECTION DETAILS ?

EXEC SP_WHO2

1. STEP 1: MINIMISE THE USE OF TEMPDB.

• AVOID USING TEMPDB FOR INDEXES. 

• AVOID USING DTA TOOL & PROFILER TOOL

• MINIMIZE THE USE OF TEMP TABLES AND TABLE VARIABLES.

2. STEP 2: RELEASE EMPTY PAGES FROM TEMPDB

DBCC SHRINKDATABASE ('TEMPDB', 20)

3. STEP 3: CONSIDER TO INCREASE SIZE OF TEMPDB DATA FILES 

ALTER DATABASE TEMPDB MODIFY FILE (NAME = 'TEMPDEV', SIZE = 1250 MB)

4. STEP 4: CONSIDER TO INCREASE THE NUMBER OF TEMPDB DATA FILES

RECOMMENDATION : NUMBER OF TEMPDB DATA FILE SHOULD BE EQUAL TO NUMBER OF PROCESSOR NODES

USE TEMPDB 

GO

SELECT * FROM SYSFILES

5. REBUILD TEMPDB file  (during maintenance hours)

FOR THIS, WE NEED TO RESTART SERVICE. DURING MAINTENANCE HOURS.

3. Memory Space Issue

• FACTORS IMPACTING MEMORY USAGE: 

• INDEXES, PROFILER & DTA TOOLS, VARIABLES, CTEs, IN-MEMORY TABLES

1. MONITOR MEMORY USAGE

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS 

SELECT SUM(SIZE_IN_BYTES) AS TOTAL_MEMORY_IN_USE

FROM SYS.DM_EXEC_CACHED_PLANS

SELECT SUM(SIZE_IN_BYTES) / 1024 AS TOTAL_MEMORY_IN_kb

FROM SYS.DM_EXEC_CACHED_PLANS

SELECT SUM(SIZE_IN_BYTES) / (1024 * 1024 ) AS TOTAL_MEMORY_IN_USE_mb

FROM SYS.DM_EXEC_CACHED_PLANS

SELECT count(*) AS cached_pages_count FROM sys.dm_os_buffer_descriptors 

select * from sys.dm_os_memory_objects -- items stored in memory: queries, result, sort @ index

select * from sys.dm_os_memory_pools -- group of memory buckets used to cache queries, results

select * from sys.dm_os_memory_nodes -- allocation objects by OS AND VAS [VIRTUAL ADDRESS SAPCE]

select * from sys.dm_os_memory_cache_entries-- used to cache the queries in procedure cache

select * from sys.dm_os_memory_cache_hash_tables   -- used internally for query joins

select (committed_kb * 8192)/ (1024*1024) as bpool_committed_mb

, (cast(committed_TARGET_kb as bigint) * 8192) / (1024*1024) as bpool_target_mb,

(VISIBLE_TARGET_KB * 8192) / (1024*1024) as bpool_visible_mb

from sys.dm_os_sys_info

select physical_memory_kb/(1024) as phys_mem_mb, 

virtual_memory_kb/(1024) as user_virtual_address_space_size -- 134217727

from sys.dm_os_sys_info

SELECT p.size_in_bytes

        ,usecounts

        ,objtype

        ,LEFT(sql.[text], 100) AS [text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql

WHERE usecounts < 2

ORDER BY size_in_bytes DESC;

SELECT * FROM SYS.dm_os_performance_counters 

where counter_name in ('Batch Requests/sec', 'SQL Compilations/sec' , 'SQL Re-Compilations/sec')

-- recommended value for Batch Requests / Sec : Within 1000

-- recommended value for SQL Compilations/sec : within 10% of Batch Requests / Sec

-- recommended value for SQL Re-Compilations/sec : within 10% of SQL Compilations/sec

2. Steps to solve Memory Issue 

1. STEP 1: MINIMISE THE USE OF MEMORY, IF POSSIBLE.

2. STEP 2: RELEASE EMPTY PAGES FROM MEMORY

DBCC FREEPROCCACHE() -- TO CLEAN OR RELEASE UNUSED PAGES FROM PROCEDURE CACHE

DBCC FREESESSIONCACHE() -- TO CLEAN OR RELEASE UNUSED PAGES FROM CURRENT SESSION (QUERY WINDOW)

DBCC FREESYSTEMCACHE('All') -- TO CLEAN OR RELEASE UNUSED PAGES FROM COMPLETE SQL SERVER BUFFER

DBCC DROPCLEANBUFFERS -- TO RELEASE EMPTY PAGES FROM MEMORY THAT RESULT IN DELETES, TRUNCATES

3. STEP 3: CONSIDER TO INCREASE MEMORY CAPACITY

EXEC sp_configure 'SHOW ADVANCED OPTIONS', 1

GO

EXEC sys.sp_configure N'max server memory (MB)', N'400000'

GO

RECONFIGURE WITH OVERRIDE -- THIS COMMAND IS TO AVOID RESTART OF YOUR SQL SERVER DB ENGINE SERVICE

GO

-- Memory Leaks: A scenario where already allocated memory is gradually released back to OS

or to a different program/application without consent of the underlying object/query

SELECT

       physical_memory_kb / 1024 AS physical_memory_MB,

       committed_kb / 1024 AS bpool_MB,

       committed_target_kb / 1024 AS bpool_target_MB,

       stack_size_in_bytes/1024 bpool_visible_MB

FROM sys.dm_os_sys_info