얼마 전 IODrive를 지엔오테크롤로지의 협찬을 받아서 일주일 동안 IO관련 테스트를 진행하였습니다.

서버용 제품으로, 벤더에서 제공하는 스펙만 본다면 기존에 테스트 했던 버텍스 또는 엠트론 보다 월등히 뛰어난 IOPS 수치를 보여주고 있습니다. 또한 특이한 것은 다른 SSD와 다르게 SATA 인터페이스가 아닌 PCI-E 인터페이스를 채택 하였습니다.
역시 서버용 제품이라, 가격은 생각보다 비쌌지만 서버에서 사용하는 엔터프라이즈 SAS디스크의 가격도 수십 만원에서 수백 만원 정도하기에, 적은 공간에 아주 많은 IOPS를 요구하는 비즈니스라면 일반 DISK를 사용하는 것 보다 적은 비용으로 구현 할 수 있을 것 입니다.  

이번에 테스트한 제품은 IODrive 80GB 입니다.

스펙 상으로는 읽기, 쓰기에 대략 10만 IOPS을 제공해 준다고 하며, 최대 bandwidth는 읽기 700MB/s, 쓰기는 550MB/s 을 정도 입니다.
이 정도의 Bandwidth는 일반 서버용 DISK 몇 장만 RAID를 구성하면 충분히 뽑아 낼 수 있기에 비싼 가격과 적은 용량을 가진 SSD만의 작업이라고 볼 수 없습니다.

하지만 랜덤 IOPS에서는 이야기가 달라집니다.
보통 15K rpm 엔터프라이즈 SAS 인터페이스 DISK도 300~400정도의 IOPS를 보여주고 있기에, IODrive가 제공하는 IOPS만큼을 디스크로 구성 하기 위해서는 대략 250장 정의 DISK가 필요하게 됩니다. 이렇게 비교해 보면 SSD의 랜덤 IOPS가 얼마나 많은 IOPS를 제공해 주는지 알 수 있습니다.

[ioDrive 스펙]

[IODrive 모습]

• 테스트 시작

처음 IO테스트를 진행할 때 80GB제품 두 개를 RAID0으로 묶어서 테스트할 예정이었으나, 하드웨어 문제가 있어 부득이하게 싱글로 테스트를 진행하였습니다.

 간단한 테스트한 머신 스펙은 아래와 같습니다.

• CPU : Intel Xeon E54xx * 2 (Quad-Core)
• MEMORY: 2GB

테스트는 총 4가지로 테스트 하였습니다.

1. SQLIO
2. IOMeter
3. SQL Server – Index Scan vs Index Lookup
4. SQL Server – 물리적 조각화에 따른 SCAN성능 비교

• 테스트 1 : SQLIO

  SQLIO는 단일형태의 IOPS를 산정할 수 있는 도구 입니다.
  SQL Server에서 주로 사용되는 8KB랜덤, 256KB 순차 읽기/쓰기로 테스트를 진행하였습니다. 간단히 결과를 알아본다면 랜덤 읽기의 경우 대략 65,000정도 / 쓰기 46,000정도의 IOPS를 보여주고 있습니다. 대략 초당 전송속도(Bandwidth)로 환산해 본다면 대략 500MB정도입니다.

   이 정도의 수치라면 기존 엔터프라이즈 디스크와 비교하면 읽기의 경우 대략 220배 정도 쓰기의 경우 150배 정도 많은 IOPS를 제공해 주고 있습니다.
  아래 표에서는 보여지지 않지만 대부분의 IO응답시간은 1ms이하로 매우 빠른 속도를 보여 주었습니다.
  혹시 위 벤더에서 제공하는 스펙과 차이가 많이 난다고 생각하신다면, 위 테스트는 4K단위로 테스트 하였고, 여기서는 8K단위 테스트 했기 때문입니다.

• 테스트 2 : IOMETER

  SQL Server를 사용하는 서비스에 대해 IO-Sub System에 대한 성능 측정을 해야 한다면,SQL Server를 기반으로 테스트를 진행하면 가장 효과적이고 정확한 테스트 결과를 볼 수 있을 것입니다. 하지만 SQL Server 의 IO패턴을 자유자재로 조작할 수 없기에 이렇게 테스트 쉬운 작업이 아닐 것입니다.  

  이번에 테스트하는 IOMETER는 여러 가지 IO패턴을 만들 수 있는 기능이 있어 SQL Server의 IO패턴을 비슷하게 흉내 낼 수 있습니다. 그래서 이번에는 SQL Server의 IO패턴을 비슷하게 구현하여 IO 성능 테스트를 진행하였습니다.  

  우선 테스트를 하기 전 SQL Server의 IO패턴에 대해서 알아볼 필요가 있습니다.
  SQL Server의 IO패턴은 트랜잭션 로그를 제외하면 IO 패턴은 크게 두 가지로 볼 수 있습니다.

  • Buffer Pool(이하 BP)에 없는 데이터를 읽기 위한 Read IO
  • BP에서 디스크로 플러시 되지 않은 Dirty page를 디스크로 쓰는 checkpoint프로세스

   [디스크에서 분당 만 페이지씩 쓰기 작업]
  

우선 디스크에서 테스트한 결과를 살펴보면,

테스트 환경은 15K SAS 디스크 10장 (RAID10) 구성되었으며, 분당 만 페이지씩 쓰기 IO가 발생하게 됩니다. 간단히 그래프를 살펴보면 평균 읽기는 대략 3700쯤 되다가 쓰기IO가 수행되면 읽기 IO가 대략 2000정도 까지 떨어지는 현상을 보입니다. 쓰기 캐시가 장착된 모델이라 쓰기 시점에 쓰기 응답시간은 매우 빠르나 읽기 응답시간은 느려지는 것을 확인 할 수 있습니다.    

     [SSD에서 10초당 만 페이지씩 쓰기 작업]
   

SSD 에서의 테스트의 경우 위에서 테스트한 SQLIO로 생각 이상의 수치를 보여주었기에, 위 디스크와 다르게 10초당 만 페이지씩 쓰기 작업을 하였습니다.  
간단히 그래프를 살펴보면 평균읽기는 42000정도 되다가 쓰기IO가 수행되면, 읽기IO가 대략 33000정도 까지 떨어지는 현상을 보입니다. PCI-E 인터페이스 이고 제품에도 따로 Write Back 캐시 역할을 해 줄 수 있는 것이 없기에, 쓰기IO 작업 시 위와 디스크에서 테스트한 결과와 다르게 쓰기 응답시간이 느려지는 것을 볼 수 있습니다. 하지만 느려져도 1ms정도이기에 문제가 있는 수치라고 볼 수 없습니다. 또 하나 위 디스크와 다른 부분은 읽기 응답시간입니다. 쓰기 IO가 발생해도 꾸준히 대략 0.4ms 이하 수준을 보여주고 있습니다.

• 테스트 3 : SQL Server – Index Scan vs Index Lookup

 대량의 데이터를 읽어야 하는 경우라면, 인덱스를 룩업하는 것보다 인덱스를 스캔하는 것이 빠르며, 그 속도 차이는 수배에서 수십 배 빨랐습니다. SSD는 위 테스트에서 살펴보았듯이, 많은 랜덤 IOPS를 제공해 주고 있으며, 응답시간 또한 기존디스크에서 볼 수 없는 속도를 내주게 됩니다. 그렇다면 SSD환경에서 모든 데이터를 읽어야 하는 경우에 룩업으로 처리시 어느 정도 성능을 보여줄지 궁금합니다.  

아래 테스트는 7GB정도의 데이터를 입력 후 인덱스 힌트를 사용하여 인덱스 스캔과 인덱스 룩업에 대해 응답시간을 비교하였습니다.  

간단히 결론을 알아 본다면 SCAN작업은 10초, 룩업 작업은 15초 정도에 끝났습니다.

역시나 SCAN이 Lookup보다 빨랐습니다. 하지만 시간이 1.5배 정도밖에 차이가 나지 않은 점을 주목해야 할 것 입니다.  

SELECT COUNT(*) FROM TBL with(index(1),nolock)
/*
테이블'tbl'. 검색수9, 논리적읽기수1002491, 물리적읽기수14078, 미리읽기수1002490, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. 
SQL Server 실행시간:

CPU 시간= 2485밀리초, 경과시간= 10404밀리초
*/

SELECT COUNT(col2) FROM tbl WITH(INDEX(2),nolock)
/*
테이블'tbl'. 검색수9, 논리적읽기수12043916, 물리적읽기수2790, 미리읽기수1006198, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
테이블'Worktable'. 검색수0, 논리적읽기수0, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

CPU 시간= 31797밀리초, 경과시간= 15898밀리초
*/

• 테스트 4 : SQL Server – 물리적 조각화에 따른 SCAN성능 비교

  예전에 테스트한 물리적 조각화에 따라 SCAN 성능이 SSD환경에서는 어떨지 궁금해서 테스트 하였습니다. SSD는 기존의 디스크와 다르게 어떠한 데이터를 읽더라도 모두 동일한 시간에 읽을 수 있습니다. 하지만 기존 DISK는 물리적인 회전을 통해 데이터를 읽어야 하기에 데이터가 쓰여진 위치에 따라 응답시간이 차이가 발생하였습니다.
  그래서 같은 파일을 인접한 곳에 저장하는 조각모음이 필요했었습니다.
  하지만 SSD는 데이터가 어디에 있든 모두 동일한 시간으로 데이터를 가져올 수 있기에 따로 조각모음이 필요  없다고 합니다.

  디스크 환경에서 테스트한 스크립트를 이용하여 SSD에 동일하게 테스트를 진행하였습니다. 아래 수치를 보면 SSD는 물리적 조각화에 의해 전혀 성능적 영향을 받지 않았다고 볼 수 있습니다.
  어느 정도의 차이는 있지만, 테스트 환경에 따라 이 정도의 오차는 충분히 무시할 수 있는 수치로 보여집니다.
  SSD를 사용하면 더 이상 물리적 조각화로 인해 조각모음은 필요 없어 보입니다.

  뭐. SSD에 물리적 조각화라는 말이 안 어울리죠?

USE [master]
GO
CREATE DATABASE [TEST] ON PRIMARY
( NAME = N'TEST', FILENAME = N'F:\DBData\TEST.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'TEST_log', FILENAME = N'D:\DBlog\TEST_log.ldf' , MAXSIZE = 2048GB , FILEGROWTH = 10%)

CREATE DATABASE [TEST1] ON PRIMARY
( NAME = N'TEST1', FILENAME = N'F:\DBData\TEST1.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'TEST1_log', FILENAME = N'D:\DBlog\TEST1_log.ldf' , MAXSIZE = 2048GB , FILEGROWTH = 10%)

CREATE DATABASE [TEST2] ON PRIMARY
( NAME = N'TEST2', FILENAME = N'F:\DBData\TEST2.mdf' ,SIZE=5GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'TEST2_log', FILENAME = N'D:\DBlog\TEST2_log.ldf' , SIZE = 5GB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

DECLARE @I VARCHAR(100)
SET @I= '4'
WHILE 1=1
BEGIN 
    EXEC ('ALTER DATABASE TEST MODIFY FILE(NAME = ''TEST'',SIZE='+@I+')') 
    EXEC ('ALTER DATABASE TEST1 MODIFY FILE(NAME = ''TEST1'',SIZE='+@I+')')

     SET @I=@I+1
IF @I=5000 BREAK
END

/*
테스트를위해4000만건대략5GB 정도의데이터를입력한다.
*/

SELECT TOP 40000000
row_number() over(order by (select 1)) as col1
,cast('' as char(100)) col2
INTO test..tbl
FROM master.dbo.spt_values A,master.dbo.spt_values A1,master.dbo.spt_values A2,master.dbo.spt_values A3

SELECT TOP 40000000
row_number() over(order by (select 1)) as col1
,cast('' as char(100)) col2
INTO test2..tbl
FROM master.dbo.spt_values A,master.dbo.spt_values A1,master.dbo.spt_values A2,master.dbo.spt_values A3

exec sp_msforeachtable 'checkpoint 1'
dbcc dropcleanbuffers

SET STATISTICS TIME ON
SET STATISTICS IO ON

-- 조각화 상태
SELECT COUNT(*) FROM test.dbo.tbl
테이블 'tbl'. 검색 수 9, 논리적 읽기 수 579712, 물리적 읽기 수 7258, 미리 읽기 수 579711, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
CPU 시간 = 10359밀리초, 경과 시간 = 6005밀리초

-- 조각화 상태 아님
SELECT COUNT(*) FROM test2.dbo.tbl
테이블 'tbl'. 검색 수 9, 논리적 읽기 수 579712, 물리적 읽기 수 7190, 미리 읽기 수 579711, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
CPU 시간 = 9891밀리초, 경과 시간 = 5991밀리초

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com

제목 : 프로시저 캐시의 특정 타입만(SQLCP) 초기화 시키자.

64bit환경에서의 native한 대용량 메모리 지원으로 프로시저 캐시 영역도 따라 늘어났습니다.

분명 이러한 변화된 부분은 이전의 32bit 환경 보다 보다 좋은 성능을 가져올 수 있게 해주었습니다.
하지만, 프로시저 캐시 크기가 증가됨에 따라 예전에 보기 힘들었던 문제가 발생할 수가 있습니다.

아래의 표로 프로시저 캐시의 공간을 계산 할 수 있습니다.
만약 8GB의 가용 메모리를 가지고 있는 SQL Server 2005 SP2 시스템인 경우 대략 3.4GB를 프로시저 캐시로 사용할 수 있습니다.
많은 ad-hoc쿼리가 발생되어 이 공간 메모리를 모두 사용하고 있고, 저장된 계획을 재사용하지 못한다면  쓸데없는 메모리 낭비 및 계속 컴파일이 발생하게 됩니다. 이것은 전체적인 시스템으로 본다면 비효율적이라고 볼 수 있습니다.

위와 같은 환경이라면 당연히 SP 등을 이용하여 컴파일된 실행계획을 가지게 모든 쿼리를 변경하는 것을 고려해야 합니다.

하지만 지금 당장 할 수 있는 작업이 아니기에, 다른 방법을 찾아봐야 합니다.

<SQL 버전별 최대 프로시저 캐시 메모리 크기 구하기>

다른 방법에 대해 찾기 위해서는 우선 프로시저 캐시 저장 구조에 대해서 조금 알아 볼 필요가 있습니다. 간단하게 프로시저 캐시에 대해서 알아보도록 하겠습니다.

프로시저 캐시는 아래와 같이 4가지 타입으로 저장됩니다.

l  CACHESTORE_OBJCP(Object Plans)

-       SP 함수의 컴파일된 계획

l  CACHESTORE_SQLCP(SQL Plans)

-       prepared, ad-hoc쿼리의 컴파일된 계획

l  CACHESTORE_PHDR(Bound Trees)

-       뷰, 제약조건, 기본값 등

l  CACHESTORE_XPROC (Extended Stored Procedures)

-       확장 저장 프로시저

프로시저 캐시의 대부분의 용량을 차지하는것은 CACHESTORE_OBJCP 또는 CACHESTORE_SQLCP입니다. 위에서 설명하고 있듯이 CACHESTORE_OBJCP는 SP 및 함수 입니다. 즉 매번 재 컴파일을 하지 않아도 되는 계획입니다. 하지만 CACHESTORE_SQLCP의 경우 ad-hoc쿼리이기에 매번 재컴파일 할 수 있는 가능성이 매우 크게 됩니다.

대부분 프로시저 캐시가 커져서 문제가 되는 경우는 CACHESTORE_SQLCP 크기가 늘어남으로써 문제가 되고 있습니다. 그렇다면, 재컴파일도 하지 않고 적정한 크기를 가지는 CACHESTORE_OBJCP는 그냥 두고 매번 재컴파일하여 비효율 적인 CACHESTORE_SQLCP만을 프로시저 캐시에서 초기화 하는 것이 보다 효율적인 방법일 수 있습니다.
 

우선 서버에 타입별 프로시저 캐시 크기를 메모리 관련 DMV로 확인 할 수 있습니다.

select * from sys.dm_os_memory_clerks

where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

이 DBCC 구문은 모든 프로시저 캐시를 초기화 하게 됩니다.

하지만 이것은 비효율일 수 있습니다. 대부분의 메모리를 차지하고 있는건 SQLCP이고

초기화 시켜야 할 것도 SQLCP이기에, OBJCP까지 같이 초기화 되기 때문입니다.

그럼, SQLCP만 초기화 시키는 방법은?

아래를 참고 하세요.

초기화 이전에는 CACHESTORE_SQLCP의 single_pages_kb가 44088KB였으나,
DBCC FREESYSTEMCACHE 구문을 실행하면 552KB로 변경된 것을 확인 할 수 있습니다.

select * from sys.dm_os_memory_clerks

where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

DBCC FREESYSTEMCACHE('SQL Plans')

select * from sys.dm_os_memory_clerks

where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

다른 프로시저 캐시에 대해서도 내릴 수 있습니다.

DBCC FREESYSTEMCACHE('OBJECT Plans')

DBCC FREESYSTEMCACHE('BOUND Trees')

오랜만에 Checkpoint관련 글을 포스팅합니다.
오늘 쓰려고 하는 내용은 이전에 올린 글에서 잠깐 언급 하였습니다.

Checkpoint는 인접해 있는 페이지들을 하나의 IO로 처리 할 수 있습니다.
하나의 IO로 여러 페이지를 처리하여, 보다 효율적으로 dirty page를 물리적 디스크로 플러쉬 할 수 있습니다. SQL Server 2000의 경우에는 최대 16개의 페이지 였지만 2005의 경우에는 최대 32개의 페이지를 하나의 IO로 처리 할 수 있습니다.

하지만 이렇게 32개 페이지를 하나의 IO로 플러쉬 하기 위해서는 checkpoint로 내려야 할 Dirty page가 순차적으로 존재해야 합니다. 데이터가 랜덤한 페이지상에 자주 변경되는 비즈니스라면 이러한 동작 방식이 도움을 줄 수 없을것입니다. 하지만 인접해 있는 페이지가 변경 되는 경우라면 보다 적은 Writes로 많은 페이지를 플러쉬 할 수 있을 것입니다. 하지만 인접해 있는 데이터가 변하지만 페이지 조각화로 인해 위와 같이 동작 하지 못할 수도 있습니다.

그렇다면 정말 위에서 말한것 처럼 동작하는지 테스트 하였습니다.
보다 명확하게 checkpoint의 IO양과 초당 처리속도를 확인해야 하기에 추적 플래그 3502,3504 를 사용하였으며, 데이터 변경을 하여 자동 checkpoint가 발생시에 errorlog에 있는 데이터로 비교하였습니다.

USE [master]

GO

CREATE DATABASE [test] ON  PRIMARY

( NAME = N'test_Data', FILENAME = N'F:\test_Data.MDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),

 FILEGROUP [test_Data1]

( NAME = N'test_Data1_01', FILENAME = N'F:\test_Data1_01.mdf' , SIZE = 5GB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )

,( NAME = N'test_Data1_03', FILENAME = N'G:\test_Data1_03.mdf' , SIZE = 5GB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )

 LOG ON

( NAME = N'test_Log', FILENAME = N'E:\test_Log.LDF' , SIZE = 5GB , MAXSIZE = 2048GB , FILEGROWTH = 51200KB )

ALTER DATABASE [test] SET RECOVERY SIMPLE

use test

go

drop table tbl1

/*

테스트를 위해 하나의 페이지에 한행만 존재하도록 테이블을 만든다.

*/

SELECT

TOP 4000000

       ROW_NUMBER() OVER(ORDER BY(SELECT 1)) as col1

       ,cast(ROW_NUMBER() OVER(ORDER BY(SELECT 1)) as char(5000)) as col2

INTO tbl1

FROM sys.sysindexes A,sys.sysindexes A1,sys.sysindexes A2,sys.sysindexes A3,sys.sysindexes A4

create unique clustered index ixa on tbl1(col1)

--sp_spaceused tbl1

/*
             정확한 테스트를 위해 기존에 BP에 있는 메모리 날리기.
*/
checkpoint 1

dbcc dropcleanbuffers

DBCC TRACEON(3504,3502,-1) 

/*

순차적으로 데이터를 변경한다.

하나의 IO작업에 16개페이지(1572070/97851)를 내리는 것을 확인 할 수 있으며

초당 280메가의 Dirty page를 내리는 것을 볼수 있다.

*/

update tbl1

set col2 = newid()

where col1  < 2000000

2008-12-16 02:52:28.340 spid10s      Ckpt dbid 11 started (8)

2008-12-16 02:52:28.340 spid10s      About to log Checkpoint begin.

2008-12-16 02:52:28.340 spid10s      Ckpt dbid 11 phase 1 ended (8)

2008-12-16 02:53:12.060 spid10s      FlushCache: cleaned up 1572070 bufs with 97851 writes in 43719 ms (avoided 162901 new dirty bufs)

2008-12-16 02:53:12.060 spid10s                  average throughput: 280.93 MB/sec, I/O saturation: 72605

2008-12-16 02:53:12.060 spid10s                  last target outstanding: 1600

2008-12-16 02:53:12.060 spid10s      About to log Checkpoint end.

2008-12-16 02:53:12.080 spid10s      Ckpt dbid 11 complete

/*

랜덤하게 데이터를 변경한다.

하나의 IO작업에 1개페이지(147576/147573)를 내리는 것을 확인 할 수 있으며

초당 56메가의 Dirty page를 내리는 것을 볼수 있다.

*/

update tbl1

set col2 = newid()

where col1%2 = 0

2008-12-16 02:56:04.760 spid10s      Ckpt dbid 11 started (8)

2008-12-16 02:56:04.760 spid10s      About to log Checkpoint begin.

2008-12-16 02:56:04.760 spid10s      Ckpt dbid 11 phase 1 ended (8)

2008-12-16 02:56:25.200 spid10s      FlushCache: cleaned up 147579 bufs with 147573 writes in 20438 ms (avoided 1466 new dirty bufs)

2008-12-16 02:56:25.200 spid10s                  average throughput:  56.41 MB/sec, I/O saturation: 55153

2008-12-16 02:56:25.200 spid10s                  last target outstanding: 1600

2008-12-16 02:56:25.200 spid10s      About to log Checkpoint end.

2008-12-16 02:56:25.200 spid10s      Ckpt dbid 11 complete

위와 같은 결과를 보면, 순차적인 데이터 변경으로 발생된 dirty page가 보다 효과적으로 디스크로 플러쉬 되는 것을 확인 할 수 있습니다.


특정 테스트 환경이기에 위 테스트 결과를 절대적으로 신뢰할 수는 없습니다.

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com

SQL Server에서 조각화는 크게 3가지로 구분지을 수 있습니다.

첫번째 논리적 조각화

두번째 익스텐트 조각화

세번째 물리적 조각화
 

논리적 조각화, 익스텐트 조각화 상태는 DBCC SHOWCONTIG 구문으로 확인 할 수 있으며,
주기적으로 인덱스 재구성 작업등으로 이러한 문제를 해결하고 있습니다.
(SQL Server 2005 이후부터는 sys.dm_db_index_physical_stats)

하지만 물리적 조각화는 확인 할 수 없기에, 많은 운용환경에서 물리적 조각화 상태에 대해 크게 고민하지 않고 사용하는 경우가 많습니다.

대부분의 OLTP환경에서는 물리적 조각화로 인한 성능 차이는 크지 않을 것입니다.
하지만 대용량의 데이터를 읽어야 하는 DW환경이라면 물리적 조각화로 인한 성능차이가 
있을 것입니다. 

그래서 이번에는 물리적 조각화로 인한 SQL Server의 성능적 영향에 대해서 알아보았습니다.
 

랜덤 IO작업의 경우는 조각화로 인한 성능 차이는 순차 IO작업보다 크지 않기에,

이번에는 물리적 조각화로 인한 순차 읽기 작업을 성능 비교 하였습니다.

테스트 결과
조각화 상태인 경우 테이블 SCAN작업이 11초 정도 걸렸으나,

조각모음을 한 후에는 대략 6초 정도에 끝나는 것을 확인 할 수 있습니다.

이러한 편차는 환경마다 달라 질 수 있습니다

여담으로, 이론상 물리적 조각화로 인한 영향도가 전혀 없는 SSD가 많은 환경에 보급된다면

이러한 고민도 사라지겠죠?
 

자세한건 아래 스크립트를 참고 하세요~

/*

       버전

*/

select @@version

Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86)

       Aug  5 2008 01:01:05

       Copyright (c) 1988-2005 Microsoft Corporation

       Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

/*

       순차적으로 두개의 DB의 파일을 1MB 단위씩 증가 시켜 물리적 조각화 상태를 만듬

       물리적 조각화가 발생

*/

DECLARE @I VARCHAR(100)

SET @I= '4'

WHILE 1=1

BEGIN

EXEC ('ALTER DATABASE TEST MODIFY FILE(NAME = ''TEST'',SIZE='+@I+')')

EXEC ('ALTER DATABASE TEST1 MODIFY FILE(NAME = ''TEST1'',SIZE='+@I+')')

SET @I=@I+1

IF @I=5000  BREAK

END

/*

       테스트를 위해 4000만건 대략 5GB 정도의 데이터를 입력한다.

*/

USE TEST

GO

SELECT TOP 40000000

row_number() over(order by (select 1)) as col1

,cast('' as char(100)) col2

INTO tbl

FROM master.dbo.spt_values A,master.dbo.spt_values A1,master.dbo.spt_values A2,master.dbo.spt_values A3

/*

       SHOWCONTIG를 통해 논리적 조각화 상태를 본다.

       방금 만들어서, 깔끔하다!!

*/

dbcc showcontig(tbl)

/*

DBCC SHOWCONTIG scanning 'tbl' table...

Table: 'tbl' (2105058535); index ID: 0, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 579712

- Extents Scanned..............................: 72465

- Extent Switches..............................: 72464

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [72464:72465]

- Extent Scan Fragmentation ...................: 0.10%

- Avg. Bytes Free per Page.....................: 23.0

- Avg. Page Density (full).....................: 99.72%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

/*

In-memory로 인해 테스트에 영향을 줄 수 있으니~ BP에 있는건 모두 날리자.

*/

exec sp_msforeachtable 'checkpoint 1'

dbcc dropcleanbuffers

--2

SET STATISTICS TIME ON

SET STATISTICS IO ON

SELECT COUNT(*) FROM tbl

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

/*

       처음 데이터를 SCAN(물리적 조각화 상태)

*/

Table 'tbl'. Scan count 9, logical reads 579712, physical reads 0, read-ahead reads 579647, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CPU time = 9875 ms,  elapsed time = 10829 ms.

/*

       물리적 조각 모음 후

       논리적 조각모음은 여전하다.

*/

dbcc showcontig(tbl)

/*

DBCC SHOWCONTIG scanning 'tbl' table...

Table: 'tbl' (2105058535); index ID: 0, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 579712

- Extents Scanned..............................: 72465

- Extent Switches..............................: 72464

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [72464:72465]

- Extent Scan Fragmentation ...................: 0.10%

- Avg. Bytes Free per Page.....................: 23.0

- Avg. Page Density (full).....................: 99.72%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

Table 'tbl'. Scan count 9, logical reads 579712, physical reads 0, read-ahead reads 579327, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CPU time = 10922 ms,  elapsed time = 6452 ms.

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com

요즘 출시되는 인텔의 제온, AMD의 옵테론 프로세스는 64bit(x64)를 기본적으로 지원되고 있고 있으며,  SQL Server도 2005부터는 x64 를 지원하고 있습니다.(IA64는 SQL Server 2000도 지원함)

기존 x86의 AWE메모리의 한계와 IA64 도입에 대한 높은 도입 비용으로 인해 x64 환경이 점차 늘어나고 있는 상황입니다.

32bit에서 4GB이상의 메모리를 사용하기 위해서는 AWE 옵션을 Enable해서 64GB까지 사용할 수 있지만,

AWE로 확장된 메모리 영역은 데이터&인덱스 페이지로만 구성되기에 다른 영역의 메모리는 기존처럼 2GB(/3GB 추가시 3GB) 공간만을 사용할 수 밖에 없습니다.

AWE에서 확장된 메모리를 map, unmap을 하기 위한 Window도 이 2GB에 포함되기에 잠금, 플랜 캐쉬, 연결, 쓰레드 스택에 대해 메모리 부족현상이 발생할 수 있습니다.

이러한 x86의 한계를 근본적으로 해결해 줄 수 있는 것이 64bit 시스템입니다.

또한 x64의 경우는 IA64보다 현저히 저렴하게 구성할 수 있기에 적은 투자비용으로 위와 같은 문제를 해결 할 수 있습니다.

기존의 여러 개의 SQL Server 2000으로 구성된 DB를 메모리 제한이 없는 SQL server 2005 x64 standard edition으로 Scale-up을 계획하시거나 사용 중이시라면 주의하게 살펴봐야 할 것 중에 하나는 바로 “Lock pages in memory” 정책의 활성화 여부입니다.

즉 Standard edition(x64) 은 “Lock pages in memory” 정책을 지원하지 않습니다.(x86 Standard는 “Lock pages in memory” 정책 지원함)

이 정책이 뭐길래~ edition에 따라 지원 여부가 달라질까요?

BOL에서는 Lock pages in memory 정책을 아래와 같이 설명 하고 있습니다.

x64환경에서 제공하는 거의 무한대의 VAS영역으로 인해서 프로세스의 Working Set이 paged out될 가능성이 x86환경보다 높습니다.

그리고 이러한 paged out이 SQL Server서비스가 올라가 있는 장비에서 발생한다면 SQL Server는 아래와 같은 메시지와 함께 성능적인 문제가 발생할 것입니다.

Enterprise edition이라면 이 정책을 사용하여 문제를 해결 할 수 있지만, Standard edition에서는 이러한 문제가 발생할 경우 이 정책을 사용할 수 없게 되어서 문제가 될 수 있습니다.

이러한 문제가 발생 할 가능성이 있다면 도입 전 테스트 등의 확인 작업이 필요할 것으로 보입니다.

2008-08-08 13:53:56.240 spid1s       A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 337 seconds. Working set (KB): 2393904, committed (KB): 6292600, memory utilization: 38%.

2008-08-08 13:58:23.660 spid1s       A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 604 seconds. Working set (KB): 2844740, committed (KB): 6292344, memory utilization: 45%.

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com // nexondbteam.tistory.com 

보통 busy한 서버에 대해서 프로필러와 같은 클라이언트 추적을 사용하지 않는 것이 권고 사항입니다.
클라이언트 추적으로 인해서 전체적인 서비스에 영향을 주며 특정경우에는 SQL 서비스가 중지가 되는 현상도 있었습니다.

이런 일반적인 내용에 대해서 정말 busy한 서버에 프로필러를 뛰우면 어떻게 되는지 테스트를 했습니다.

초당 16000정도의 요청을 받는 SQL서버에 프로필러를 사용해서 추적을 걸어본 결과
2번째 그림에서 볼수 있듯이 Batch Request/Sec이 0로 떨어지면서 프로필러도 응답이 없는 상태로 되었습니다.
만약 이게 실 서비스였다면 잠시나마 모든 요청에 대해 처리를 못해 큰 문제가 될 수 있습니다.

아래 사래와 같은 일이 실 서비스에서도 발생 할 수 있으니 클라이언트 추적을 사용시 보다 주의가 필요할 것으로 보입니다.

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com // nexondbteam.tistory.com

<프로필러를 수행전>