[MSSQL] TEMPDB 용량 축소, 관리 구문

SELECT 시에 TEMPDB를 많이 사용하는 케이스에서 여러 사용자가 동일한 작업을 수행 했을때
TEMPDB경합으로 인해 DB 성능이 나오지 않는 경우가 있습니다.

보통 TEMPDB는 CPU 논리프로세스개수까지 나누어 관리하는게 병합을 막을 수 있는 조치이며
경우에 따라서는 격리수준을 조정해서 해결가능했습니다.

--TEMPDB경합발생 https://learn.microsoft.com/ko-kr/troubleshoot/sql/database-engine/performance/recommendations-reduce-allocation-contention
--TEMPDB는 CPU개수 정도로 잡는게 좋음
--프로시저 캐시
DBCC FREEPROCCACHE
GO

-- Buffer Pool에 존재하는 데이터 페이지를 제거
DBCC DROPCLEANBUFFERS
Go

--사용 하지 않는 모든 캐시 삭제
DBCC FREESYSTEMCACHE ('ALL')
GO

exec sp_helpdb tempdb
/*
  select DB_NAME(mf.database_id) database_name
    , mf.name logical_name, mf.file_id
    , CONVERT (DECIMAL (20,2)
    , (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
    , CASE mf.is_percent_growth
    WHEN 1 THEN 'Yes'    ELSE 'No'    END AS [is_percent_growth]
    , CASE mf.is_percent_growth
    WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
    WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
    END AS [growth_in_increment_of]
    , CASE mf.is_percent_growth
    WHEN 1 THEN CONVERT(DECIMAL(20,2)
    ,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
    WHEN 0 THEN CONVERT(DECIMAL(20,2)
    , (CONVERT(DECIMAL, growth)/128))
    END AS [next_auto_growth_size_MB]
    , physical_name from sys.master_files mf
    where database_id =2 and type_desc= 'rows'

 
*/
dbcc shrinkfile (tempdev,1024)
dbcc shrinkfile (templog,1024)
dbcc shrinkfile (temp2,1024)
dbcc shrinkfile (temp3,1024)
dbcc shrinkfile (temp4,1024)
dbcc shrinkfile (temp5,1024)
dbcc shrinkfile (temp6,1024)
dbcc shrinkfile (temp7,1024)
dbcc shrinkfile (temp8,1024)

--삭제하려면
--DBCC SHRINKFILE (LogicalName, EMPTYFILE);
--GO
--ALTER DATABASE tempdb REMOVE FILE LogicalFileName;
--GO

 

사용했던 구문을 정리해두었습니다.

TEMPDB 용량을 축소하거나 삭제할때 
캐시를 비우지 않거나 사용중인 경우 처리되지 않는 경우가 있으니 참고하시기바랍니다.