헝그리개발자

MSSQL 서버 버전 확인방법으로, SQL Server Managerment Studio 에서

속성을 통해서 확인할 수 도 있지만, 쿼리문을 통해서 확인하는 방법이다.

1. @@version 을 통한 방법

출력예)

Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
 Jun 12 2012 13:05:25
 Copyright (c) Microsoft Corporation
 Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

2. SERVERPROPERTY 를 이용한 방법

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

출력예)

productversion 은 제품 버전을 뜻하고,

productlevel 은 제품 수준을 뜻하고,

edition 은 에디션을 뜻한다.

productversion 에 나오는 값은 아래와 같이 정의할 수 있다.

만나이는 년도로 나이를 계산후에, 생일이 안지났으면 1을 빼주면되는, 간단한 공식이다.

테이블 컬럼에 생년월일 컬럼이 있다면, 더 심플하게 구현이 가능하고,
음력생일 이라면, 양력생일로 변환후에 위의 로직으로 하면된다.

음력을 양력으로 변환하는 것은, DB에서 처리할때는 음/양 맵핑 테이블로 구현하는것이 빠르다.

여기서는 회원테이블의 주민번호로 만나이를 구할때,
매번 주민번호 읽어서, 계산을 거치는 것보다는,
computed column 을 통해서 간단히 처리하는것이 좋다.

회원테이블에 FullAge 라는 computed column 을 아래와 같이 추가하면 끝이다.

-- 만나이컬럼: 계산된 열, 주민번호로 계산한다.

MSSQL 캐쉬삭제 방법입니다.

-- 버퍼 풀에서 빈버퍼 모두 제거
DBCC DROPCLEANBUFFERS
GO

-- 프로시저 캐시에서 모든 요소 제거
DBCC FREEPROCCACHE
GO

데이터베이스가 오래되면, 인덱스에도 조각화(Fragmentation)가 되고,
어떨때는 쿼리플랜으로 보면, 인덱스가 선택되지 않는 경우도 있고,
이래저래 데이터베이스가 좀 느려졌다 싶을때,

가끔 인덱스를 REBUILD 해주면, 속도가 빨라지곤한다.
데이터 양에 따라 시간이 많이 걸리기 때문에,
사용자가 없는 새벽이나 유휴시간에 작업해야 한다.

데이터베이스 안에 많은 양의 테이블이 있기 때문에 일일이, 테이블을 지정해서 해주면,
노가다가 되기 때문에 자동화해서, 한번의 실행으로 모든 테이블의 인덱스를 REBUILD 해주는 방법이다.

1.
데이터베이스 안에 모든 테이블 목록을 가져오는 쿼리다.

SELECT '[' + table_catalog + '].[' + table_schema + '].[' + table_name + ']' as tableName
FROM DB명.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE' AND table_name not like 'sys%'

2.
이를 커서를 이용해서, 모든 테이블에 대해 아래의 인덱스 리빌드 쿼리를 실행할 것이다.

ALTER INDEX ALL ON [DB명].[dbo].[TABLE명] REBUILD WITH ( fillfactor = 90)

아래는 전체 풀 소스 이며, 커서를 통해, 모든 테이블을 처리하며,
동적쿼리문을 작성해서 실행한다.

DECLARE @db_nm  varchar(500) -- DB명
DECLARE @table_nm varchar(1000) -- 테이블명
DECLARE @cmd  nvarchar(4000) -- 동적쿼리
DECLARE @FillFactor int  -- 인덱스 채우기비율

SET @FillFactor = 90   -- 인덱스의 90%를 채운다.

SET @db_nm = 'nv_blog'

-- DB에서 테이블목록을 불러서 커서에 저장 (sys로 시작하는 테이블은 제외)
-- 테이블명 구조 : [DB명].[dbo].[TABLE명]
SET @cmd = 'DECLARE curTB cursor for SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName
FROM ' + @db_nm + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'' AND table_name not like ''sys%''';

EXEC (@cmd)

-- TABLE 커서 오픈
OPEN curTB

FETCH NEXT FROM curTB INTO @table_nm
WHILE @@fetch_status = 0
begin
 -- 테이블 별 인덱스를 리빌드함
 SET @cmd = 'ALTER INDEX ALL ON ' + @table_nm + ' REBUILD WITH ( fillfactor = ' + convert(varchar(3), @FillFactor ) + ')';

 print 'Work - ' + @cmd

 EXEC (@cmd)

 print 'Done - ' + @cmd

 FETCH NEXT FROM curTB INTO @table_nm
end

-- TABLE 커서 종료
CLOSE curTB
DEALLOCATE curTB

PS. 이 쿼리는 MS-SQL 2012 이후 버전에서 실행되었습니다.

MS-SQL 의 데이터베이스 물리적 파일인 .MDF .LDF 파일의 용량이 갈수록 커지면서,
하드디스크 용량을 많이 차지 한다면, 아래와 같은 방법으로 용량을 줄여보자.

ALTER DATABASE DB명 SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(DB명_Data)
go
DBCC SHRINKFILE(DB명_Log)
go
ALTER DATABASE DB명 SET RECOVERY FULL
go

위의 쿼리로 MDF, LDF 파일의 용량을 모두 줄일수 있지만,
MDF 는 데이터 파일이라, 용량이 그다지 줄지는 않을것이다.
아래와 같이 Log 파일만 줄여도 상관이 없다.

ALTER DATABASE DB명 SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(DB명_Log)
go
ALTER DATABASE DB명 SET RECOVERY FULL
go

데이터 파일과 로그파일의 논리적 이름을 적어줘야 하는데, 어떤걸로 되어 있는지 모를경우,
데이터베이스 속성에서 아래와 같이 논리적 이름을 확인할 수 있다.

또는,
sp_helpdb DB명

명령으로도 쉽게 확인이 가능하다.

쿼리의 실행전,실행후 용량 비교이다.

PS. 이 쿼리는 MS-SQL 2012 이후 버전에서 실행되었습니다.