SQL Server 2008에 sys.dm_exec_procedure_stats DMV가 추가되었습니다.
2008이전 버젼에서는 SP별로 실행정보를 보기 위해서는 statment단위로 저장된 데이터를
GROUP BY해서 확인했지만, SQL Server 2008부터는 해당 DMV가 추가됨에 따라
쉽게 바로 확인이 가능합니다.
SELECT * FROM (
SELECT TOP 10
db_name(qt.dbid) AS 'db_name'
,OBJECT_NAME(qt.objectid,qt.dbid) as sp_name
,qp.query_plan
,execution_count as execution_count
, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS 'Calls/Second'
, DATEDIFF(Minute, qs.cached_time, GetDate()) AS 'Age in Cache(min)'
,qs.cached_time
,qs.last_execution_time
, ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0 AS 'avg_Elapsed_Time(ms)'
, qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'
, max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'
, ISNULL(qs.total_worker_time/qs.execution_count, 0)/1000.0 AS 'avg_worker_time(ms)'
, total_worker_time/1000.0/1000.0 as 'total_worker_time(sec)'
, qs.max_worker_time/1000.0 as 'max_worker_time(msdb)'
, ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'
, total_logical_reads as total_logical_reads
, qs.max_logical_reads as max_logical_reads
, ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'
, total_physical_reads as total_physical_reads
, qs.max_physical_reads as max_physical_reads
, ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'
, qs.total_logical_writes as total_logical_writes
, qs.max_logical_writes as max_logical_writes
FROM sys.dm_exec_procedure_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
where db_name(qt.dbid) is not null
)X
order by
[avg_worker_time(ms)]
DESC
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
실행계획을 보면 예상행수라는 항목이 있습니다. 이 수치는 플랜을 생성시 통계 정보를
참조하여 구하게 되며 실행계획 생성시 많은 영향을 주게 됩니다.
만약 플랜 생성시 예상행수가 100만개라면 랜덤IO가 발생하는 작업 보다 순차IO가 발생하는
작업이 보다 효율적이라 판단하여 테이블을 SCAN하도록 풀릴 수 있습니다. 만약 1건이라면
랜덤IO로 풀릴 수 있습니다.
이렇듯이 예상행수는 적절한 플랜을 생성하기 위해 중요한 데이터 중 하나입니다.
그럼 이 데이터를 사용자가 조작할 수 있는지 그리고 지정할 필요가 있는지에 대해서
살펴보도록 하겠습니다.
1. 내 맘대로 예상행수를 지정하자.
BOL을 보면 예상행수를 지정할 수 있는 힌트는 제공되지 않습니다.
하지만 힌트중 FAST라는 힌트가 있습니다.
BOL에는 아래와 같이 설명하고 있습니다.
FAST number_rows
첫 번째 음수가 아닌 정수 number_rows를 빨리 검색하기 위해 쿼리를 최적화하도록 지정합니다.
첫 번째 number_rows를 반환한 후에 쿼리는 계속 실행하여 전체 결과 집합을 만듭니다.
BOL에서 설명하고 있듯이, FAST힌트를 사용하면 특정 N개의 행이 가장 빠르게 리턴 될 수
있도록 플랜을 생성하게 됩니다. 예를 들면 리턴된 결과가 10000개의 행인데,
힌트를 안 줄 경우는 Hash Join으로 풀리지만 FAST 10을 줄 경우는 LOOP JOIN으로 처리
될 수 있습니다. LOOP JOIN은 인덱스만 적절하게 있다면 일부 데이터에 대해서는 바로
리 턴할 수 있기 때문입니다. 간단하게 FAST힌트는 N개의 값이 가장 빠르게 리턴 될 수 있는
플랜을 생성하라는 힌트입니다. 그렇다고 해서 FAST 힌트를 준다고 해서 빠른것은 아닙니다.
N개의 행은 빠르게 나오겠지만, 모든 결과가 완료되는 것은 더 느릴 수 있습니다.
이러한 FAST힌트의 N값은 플랜의 예상행수로 처리하여 플랜을 생성하게 됩니다.
이점을 이용하여 예상행수를 변경해서 플랜을 생성해야 할 때 사용 할 수 있습니다.
아래 테스트는 FAST힌트에 따른 예상행수가 변경되는지 테스트 했습니다.
처음은 FAST힌트가 없는 경우는 실제 행수인 249와 근접한 234의 예상행수를 볼 수 있으며
FAST 힌트에 1을 추가하게 되면 예상행수가 1로 변경되는 것을 알 수 있습니다.
만약 실제행수보다 FAST힌트에 많은 값을 입력하게 입력된 값을 무시하고
통계 정보를 기반으로 예상 행수를 산정하게 됩니다.
select *,sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID]
from tbl10 with(forceseek) where col2 < 1000
select *,sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID]
from tbl10 with(forceseek) where col2 < 1000
option(fast 1)
select *,sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID]
from tbl10 with(forceseek) where col2 < 1000
option(fast 100)
2. 왜 통계가 있는데 예상행수를 지정할 필요가 있을까?
그럼 왜 통계정보를 관리하고 있는데~ 굳이 예상행수를 내가 입력해야 하는 경우가 있을까요?
전에 설명했듯이 예상행수는 통계데이터를 참조하여 구하게 됩니다.
"예상행수" 말에서도 알 수 있듯이 실제 행수가 아닌 예상수치이며 이 데이터는 통계를 기반으로
하였기에 부정확할 수 있습니다. 그럼 왜 통계는 정확할 수 없을까요?
크기가 크지 않은 테이블이라면 통계 히스토그램에서 어느 정도 일치하는 행수를 알 수
있으나 큰 테이블이라면 통계 정보가 부정확해질 수 있습니다.
왜냐하면 DB에서 자동으로 발생하는 통계 업데이트는 샘플링으로 처리되며
또한 히스토그램의 스텝은 200개 밖에 안되기 때문입니다.
“그럼 통계 히스토그램의 스텝을 많이 늘리면 이런 문제를 개선할 수 있지 않을까?”
라는 의문이 들 수 있습니다.
맞습니다. 히스토그램의 스텝이 많아 질수록 통계데이터는 더 정확하게 사용될 수 있습니다.
하지만 스텝이 늘어남에 따라 관리 비용 또한 증가할 것이며, 얼마만큼 늘려야 할지도 의문입니다.
예전 SQL Server에서는 스텝이 300개였는데~ 버전업이 되면서 200개로 줄어들었다는 건~
무언가의 성능적인 이유 또는 100개정도의 차이가 통계데이터 정확성에 큰 영향을 주지
않았을까 하는 개인적인 생각이 듭니다.
만약 SQL Server 2008을 사용하신다면, 필터된 통계를 생성할 수 있기에 200개로 제한된
통계히스토그램을 원하는 만큼 늘릴 수는 있습니다.
이번에 이야기 하고자 한 내용이 통계는 아니기에~ 통계에 대한 이야기는 여기서 마치겠습니다.
다음 기회에 통계에 대해서 조금 더 깊이 살펴보도록 하겠습니다.
3. FAST힌트와 인덱스 힌트
그럼 다시 돌아와서~ FAST힌트로 인한 실행계획이야기를 다시 해보겠습니다.
위에서 보았듯이 통계는 한계를 가지고 있어 적절하지 못한 실행계획이 생성되어 서비스에 영향을
줄 수 있습니다. 이럴 때 인덱스 힌트 또는 쿼리 힌트를 사용하여 실행계획을 고정하고 있습니다.
예를 들어보면
~ 하나의 테이블은 1000만 건이 있습니다.
~ 800만 건은 모두 고유하지만
~ 나머지 200만 건은 모두 NULL입니다.
~ 또한 통계데이터가 부정확합니다.
이러한 경우 사용자는 데이터의 분포도를 알고 있기에 NULL인 경우는 클러스터 인덱스를
SCAN, 아닌 경우에는 넌 클러스터 인덱스SEEK라고 인덱스 힌트를 통해서 처리 할 수 있습니다.
하지만 인덱스 힌트는 관리적인 이슈가 있습니다. 인덱스 힌트는 인덱스 이름 또는 인덱스 ID값을
입력해서 사용게되는데 이때 사용한 인덱스 이름 또는 ID값이 변경되게 되면 SP는 오류가
발생하게 됩니다. SP오류로 인해서 서비스에 영향을 줄 수 있기에 인덱스 힌트를 사용한 SP의 경우는
따로 관리해야할 이슈가 생깁니다. 하지만 인덱스 힌트가 아닌 FAST힌트를 통해서도 처리할 수
있습니다. NULL인 경우는 FAST 200만, NULL이 아닌 경우는 FAST 1로 지정한다면 원하는
플랜으로 처리 할 수 있습니다.
위와 같이 FAST힌트를 사용하면 예상행수를 변경할 수 있고 이러한 동작으로 일부 인덱스
힌트를 대체 할 수 있다는 것을 살펴보았습니다. 하지만 모든 경우에서 FAST힌트가 인덱스
힌트를 대신할 수 없기에 상황에 맞춰 적절하게 사용해야 합니다.
sqler.com // sqlleader.com
hyoksong.tistory.com
SQL Server 2005부터 H/W NUMA를 본격적으로 지원하고 있습니다.
NUMA란 Non-Uniform Memory Access으로 간단하게 특정 CPU 그룹 단위로
전용 메모리 공간을 가지는 것을 말합니다.
기존 SMP환경에서는 다수의 CPU가 동일한 메모리를 바라보게 되어
메모리 엑세스 경합이라는 문제가 발생하여 이러한 문제를 극복하고자 NUMA 아키텍쳐가
소개되었습니다.
기존 x86, x64 프로세스에서는 AMD의 옵테론 정도만 H/W NUMA를 지원하고 있었으나,
요즘 INTEL에서 발표된 네할렘 CPU도 메모리 컨트롤러가 CPU 내부로 들어가면서
CPU 당 NUMA 노드 하나씩 설정 할 수 있게 되었습니다.
그렇다면 SQL Server에서 NUMA를 사용하는 것이 기존 SMP환경보다 더 좋을까요?
여러 CPU가 하나의 메모리를 바라보지 않아도 되고, NUMA 노드별로 Lazy Write 프로세스가
독립적으로 생겨 성능상 이점을 줄 수 있습니다. 하지만 NUMA 또한 단점이 있습니다.
CPU 노드별로 NUMA 노드가 생기는 경우를 예로 들어보면
내가 어떠한 쿼리를 수행하기 위해서 필요한 데이터가 로컬 메모리에 있을 수도 있고,
리모트 메모리에 있을 수도 있습니다. 만약 모든 데이터가 로컬 메모리에 존재한다면
특별한 추가적인 작업 없이 데이터를 엑세스 할 수 있을 것이니다. 하지만 리모트에
데이터가 있다면 추가적인 오버헤드가 발생하게 됩니다. 하지만 일반적으로 메모리가 워낙 빨라
로컬 메모리에서 읽든, 리모트에서 읽든 사용자는 체감하기는 쉽지 않습니다.
그럼 과연 NUMA를 사용하는 SQL Server환경에서 리모트 메모리 엑세스와 로컬 메모리 엑세스가
눈에 보이는 성능 차이가 발생할지 또 어느정도 차이가 발생할지 살펴보겠습니다.
테스트는 두 가지로 샘플 테이블을 만들고 테이블을 쭉 읽는 SCAN 작업과 넌 클러스터 인덱스에서
데이터 페이지를 Lookup하도록 하여 테스트 하였습니다. 노드별 시간을 측정하기 위해 우선 NUMA
노드별로 데이터를 올리기 위해 TCP/IP 포트와 NUMA노드를 매핑하여 테스트 하였습니다.
1000번 포트로 연결하면 0번 NUMA 노드로, 2000번 포트로 연결하면 1번 NUMA 노드만을
사용하도록 세팅하였습니다. 모든 데이터 페이지를 비운 후 1000번 포트로 접근하여, 0번 노드의
Freepage에 데이터 페이지를 채운 후 두 개의 쿼리를 각각 5번씩 수행하였습니다.
처음으로 살펴볼 것은 lookup이 발생하는 쿼리 입니다.
SET STATISTICS TIME은 ms 단위이기에 두 경우가 비슷하게 나오는 경향이 있어 DMV를 통해
응답시간을 비교했습니다.
아래 표에서 볼 부분은 avg_Elapsed_Time(ms) 입니다.
로컬 메모리 엑세스와 리모트 메모리 엑세스의 평균 수행시간이 12ms정도 차이가 나는 것을 볼 수 statement_text ExecutionCount avg_Elapsed_Time(ms) max_Elapsed_Time(ms) avg_Worker_Time(ms) avg_logical_reads 로컬(0번 노드) SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK,index=2) OPTION(MAXDOP 1) 5 391.406 404.296 391.406 106541 리모트(1번 노드) SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK,index=2) OPTION(MAXDOP 1) 5 403.906 413.085 403.906 106541
있습니다.
두 번째로 살펴볼 것은 SCAN하는 쿼리입니다.
로컬 메모리 엑세스와 리모트 메모리 엑세스의 평균 수행시간이 4ms정도 차이가 나는 것을 볼 수
있습니다.
|
statement_text |
ExecutionCount |
avg_Elapsed_Time(ms) |
max_Elapsed_Time(ms) |
avg_Worker_Time(ms) |
avg_logical_reads | |
|
로컬(0번 노드) |
SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK) OPTION(MAXDOP 1) |
5 |
293.554 |
303.71 |
293.554 |
100031 |
|
리모트(1번 노드) |
SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK) OPTION(MAXDOP 1) |
5 |
297.851 |
304.687 |
297.851 |
100031 |
이러한 결과를 볼 때 분명 리모트 메모리 엑세스는 분명 로컬 메모리 엑세스 보다 느린 것을 볼 수
있습니다. 그렇다면 이러한 문제를 해결 할 수 있는 방법은 없을까? 아니 보다 효율적으로 사용할 수
있는 방법은 없을까에 대한 고민은 다음에 정리하도록 하겠습니다.
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
SQL Server 2008에 재미있는 옵티마이저 기능이 추가 되었습니다.
하지만 버그로 인해서 SP1 또는 RTM CU4부터는 이 기능을 사용할 수 없었지만,
얼마 전에 나온 SP1 CU5에서 버그를 수정하여 이 기능을 다시 제공하고 있습니다.
Parameter Embedding Optimization이라고 불리며, OPTION(RECOMPILE)을 사용하는 쿼리의 경우 실행계획을 컴파일 할 때 실제 매개변수의 값을 가지고 컴파일 하는 기능입니다. 그렇다면 분명 기존보다 효율적인 플랜을 만들어줄 것 입니다. 뭐 당연하다고 생각할 수 도 있을 것 같습니다. 플랜 캐시를 재사용을 포기하며 recompile옵션도 추가했는데~ 플랜까지 이상하다면 좀 이상하겠죠!
이 기능이 도움을 줄 수 있는 경우는 쿼리가 변수에 따라서 플랜이 변경되어야 하는 경우 입니다.
입력되는 변수에 따라 WHERE 조건이 변경되는 경우 SQL Server 2008 이전 버전을 사용했을 때 적절한 플랜으로 처리 되기 위해서는 모든 경우에 따라 분기 문 또는 SP를 분리하여 사용하거나, 쿼리 문자열을 조합하는 형태의 동적 쿼리를 사용할 수 밖에 없었습니다. 동적 쿼리는 플랜 재사용도 거의 안되면서 플랜캐시에 상주할 수 있기에 플랜 캐시가 늘어나는 문제와 보안적인 문제를 가질 수 있습니다. 경우의 수만큼 분기 문을 사용한 쿼리는 유지 보수를 하기가 참~ 어려웠습니다.
그럼 이 기능으로 플랜이 어떻게 변경되는지 확인해 보겠습니다.
SQL Server 2008 SP1 + CU5와 SQL Server 2008 SP1 버전으로 비교해 보았습니다.
[테스트 1] 입력되는 변수 값에 따라 WHERE 조건이 변경되는 경우
SP1과 SP1 + CU5환경에서 실행하면 아래와 같은 실행계획을 확인 할 수 있습니다.
SP1의 경우는 TABLE SCAN으로 풀렸으며, SP1+CU5의 경우는 Index Seek + RID Lookup을 사용하였습니다.
아래 플랜에서 노란색으로 표시된 부분을 보면 SP1의 경우 조건 절에서 변수로 비교하며, CU5의 경우 상수로 비교하고 있습니다.
그래서 플랜이 서로 다른 모습을 보여주고 있으며, CU5가 파라미터의 값을 가지고 플랜을 생성하여 보다 효율적인 쿼리 플랜을 생성했습니다.
SQL Server 2008 SP1
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WHERE (col1 = @a AND @a IS NOT NULL) OR (col2 = @a2 AND @a2 IS NOT NULL) OR (col3 = @a3 AND @a3 IS NOT NULL) OR (col4 = @a4 AND @a4 IS NOT NULL) OR (col5 = @a5 AND @a5 IS NOT NULL) OR (col6 = @a6 AND @a6 IS NOT NULL) OPTION(RECOMPILE) |
|
1 |
1 |
|--Table Scan(OBJECT:([test].[dbo].[tbl90]), WHERE:([test].[dbo].[tbl90].[col1]=[@a] AND [@a] IS NOT NULL OR [test].[dbo].[tbl90].[col2]=[@a2] AND [@a2] IS NOT NULL OR [test].[dbo].[tbl90].[col3]=[@a3] AND [@a3] IS NOT NULL OR [test].[dbo].[tbl90].[col4]=[@a4] AND [@a4] IS NOT NULL OR [test].[dbo].[tbl90].[col5]=[@a5] AND [@a5] IS NOT NULL OR [test].[dbo].[tbl90].[col6]=[@a6] AND [@a6] IS NOT NULL)) |
SQL Server 2008 SP1 + CU5
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WHERE (col1 = @a AND @a IS NOT NULL) OR (col2 = @a2 AND @a2 IS NOT NULL) OR (col3 = @a3 AND @a3 IS NOT NULL) OR (col4 = @a4 AND @a4 IS NOT NULL) OR (col5 = @a5 AND @a5 IS NOT NULL) OR (col6 = @a6 AND @a6 IS NOT NULL) OPTION(RECOMPILE) |
|
1 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |
|
1 |
1 |
|--Index Seek(OBJECT:([tempdb].[dbo].[tbl90].[ix_tbl903]), SEEK:([tempdb].[dbo].[tbl90].[col3]=(1)) ORDERED FORWARD) |
|
1 |
1 |
|--RID Lookup(OBJECT:([tempdb].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |
DROP TABLE tbl90
CREATE TABLE tbl90 (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)
INSERT INTO tbl90
SELECT TOP 100000
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1))
FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3
CREATE INDEX ix_tbl90 ON tbl90 (col1)
CREATE INDEX ix_tbl902 ON tbl90 (col2)
CREATE INDEX ix_tbl903 ON tbl90 (col3)
CREATE INDEX ix_tbl904 ON tbl90 (col4)
CREATE INDEX ix_tbl905 ON tbl90 (col5)
-- CREATE INDEX ix_tbl906 ON tbl90 (col6)
GO
CREATE PROC UP_90
@a INT = NULL
,@a2 INT = NULL
,@a3 INT = NULL
,@a4 INT = NULL
,@a5 INT = NULL
,@a6 INT = NULL
AS
SELECT * FROM tbl90
WHERE
(col1 = @a AND @a IS NOT NULL)
OR (col2 = @a2 AND @a2 IS NOT NULL)
OR (col3 = @a3 AND @a3 IS NOT NULL)
OR (col4 = @a4 AND @a4 IS NOT NULL)
OR (col5 = @a5 AND @a5 IS NOT NULL)
OR (col6 = @a6 AND @a6 IS NOT NULL)
OPTION(RECOMPILE)
GO
SET STATISTICS PROFILE ON
exec up_90 @a3 = 3
[테스트 2] 입력되는 변수 값에 따라 조회하는 테이블이 변경되는 경우
UNION ALL을 통해서 변수 값에 조회할 테이블을 선택할 수 있는 경우 입니다.
많이 사용되는 쿼리 중 하나인데요, SP1 + CU5버전에서 RECOMPILE옵션을
추가하면 입력된 변수 값에 따라 실질적으로 읽어야 할 테이블에 대해서만 조회를 하는 것을 볼 수 있습니다.
SQL Server 2008 SP1 + CU5
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1 UNION ALL SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1 OPTION(RECOMPILE) |
|
0 |
0 |
|--Compute Scalar(DEFINE:([Union1008]=[tempdb].[dbo].[tbl90].[col1], [Union1009]=[tempdb].[dbo].[tbl90].[col2], [Union1010]=[tempdb].[dbo].[tbl90].[col3], [Union1011]=[tempdb].[dbo].[tbl90].[col4], [Union1012]=[tempdb].[dbo].[tbl90].[col5], [Union1013]=[tempdb].[dbo].[tbl90].[col6])) |
|
1 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |
|
1 |
1 |
|--Index Seek(OBJECT:([tempdb].[dbo].[tbl90].[ix_tbl90]), SEEK:([tempdb].[dbo].[tbl90].[col1]=(1)) ORDERED FORWARD) |
|
1 |
1 |
|--RID Lookup(OBJECT:([tempdb].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |
SQL Server 2008 SP1
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1 UNION ALL SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1 OPTION(RECOMPILE) |
|
1 |
1 |
|--Concatenation |
|
1 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |
|
1 |
1 |
| |--Filter(WHERE:(STARTUP EXPR([@a] IS NOT NULL))) |
|
1 |
1 |
| | |--Index Seek(OBJECT:([TEST2].[dbo].[tbl90].[ix_tbl90]), SEEK:([TEST2].[dbo].[tbl90].[col1]=[@a]) ORDERED FORWARD) |
|
1 |
1 |
| |--RID Lookup(OBJECT:([TEST2].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |
|
0 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004])) |
|
0 |
1 |
|--Filter(WHERE:(STARTUP EXPR([@a1] IS NOT NULL))) |
|
0 |
0 |
| |--Index Seek(OBJECT:([TEST2].[dbo].[tbl91].[ix_tbl91]), SEEK:([TEST2].[dbo].[tbl91].[col1]=[@a1]) ORDERED FORWARD) |
|
0 |
0 |
|--RID Lookup(OBJECT:([TEST2].[dbo].[tbl91]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD) |
DROP TABLE tbl91
CREATE TABLE tbl91 (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)
INSERT INTO tbl91
SELECT TOP 100000
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1))
FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3
CREATE INDEX ix_tbl91 ON tbl91 (col1)
CREATE INDEX ix_tbl912 ON tbl91 (col2)
CREATE INDEX ix_tbl913 ON tbl91 (col3)
CREATE INDEX ix_tbl914 ON tbl91 (col4)
CREATE INDEX ix_tbl915 ON tbl91 (col5)
CREATE INDEX ix_tbl916 ON tbl91 (col6)
GO
CREATE PROC UP_93
@a INT = NULL
,@a1 INT = NULL
AS
SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1
UNION ALL
SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1
OPTION(RECOMPILE)
GO
EXEC UP_93 @a = 1
추가적인 정보는 아래 링크를 참조하세요.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244298
http://support.microsoft.com/kb/976603/
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
제목 그대로 SQL Server 2005에서 다른 서버의 MSDB를 새로운 머신으로
복구할 경우에 AGENT의 JOB이 제대로 수행되지 않는 문제가 발생할 수 있습니다.
이때 AGENT JOB의 오류 로그를 보면 아래와 같은 오류가 발생하는 것을 확인 할 수 있습니다.
2009-10-22 16:46:23 - ! [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)
문제의 원인은 AGENT에서 사용하는 DLL파일의 경로를 msdb.dbo.syssubsystems에 저장하고 있으며,
다른 서버로 이전되면서 SQL이 설치된 경로가 변경되는 경우에는 DLL의 위치를 확인 할 수 없어
오류가 발생하게 됩니다. 이때 UPDATE를 통해서 경로를 변경해서 처리 할 수도 있으며 아래와 같이
시스템 SP를 사용 할 수 도 있습니다.
use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go
SQL Server 에이전트 재시작.
자세한 내용은 아래 KB를 참고 하시길 바랍니다.
msdb 데이터베이스 또는 SQL Server 2005의 syssubsystems 테이블을 변경할 때 복원하거나 때 오류 메시지: "하위 시스템 % 로드할 수 없습니다."
http://support.microsoft.com/kb/914171
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
SQLIOSIM은 SQL서버와 비슷한 패턴의 IO를 발생시켜, IO 시스템에 내 결함성 문제가 없는지 체크 해줄 수 있는 도구 입니다.
신규 도입되는 IO시스템에 대해 SQLIOSIM 도구를 사용하여, 미리 문제를 확인한 경험도 있습니다.
가능하다면, 신규 IO시스템을 도입 시 반듯이 수행하는 것이 좋아 보입니다.
하지만 이 도구의 결과는 XML형태로 제공 되기에 비교 또는 관리하기 어려웠습니다.
그래서 이번에 소개하는 도구는 SQLIOSIM XML로그를 EXCEL파일로 파싱해주는 도구를 소개 하고자 합니다.
자세한 내용은 아래 블로그를 참조하세요.
http://blogs.msdn.com/jimmymay/archive/2009/09/27/sqliosim-parser-by-jens-suessmeyer-yours-truly.aspx
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
전체 백업 실패 후 차등 백업을 하게 되면, 기존 차등 백업 보다 파일 크기가 커지는 현상이 발생할 수 있습니다.
DCM페이지(Differential Changed Map)는 최근 전체 백업 이후에 변경된 페이지만을 마킹하게 되며
차등 백업은 DCM페이지에 마킹된 페이지만을 백업하게 됩니다.
그렇다면 전체 백업을 할 때 기존에 마킹 되어있던 DCM페이지를 초기화 해야 하는 작업이 필요 하며,
이러한 작업은 비문서화된 TRACE FLAG를 통해 확인할 수 있습니다.
DBCC TRACEON(3004, 3605,3213,3604, -1) 을 통해 아래와 같이 확인이 가능합니다.
이 TRACE FLAG의 정보를 통해 백업 동작 방식에 대해 조금 더 깊게 이해할 수 있습니다.
Backup: Clearing differential bitmaps
Backup: Bitmaps cleared
그렇다면, 전체 백업 단계 중 위 작업이 완료 되고, 전체 백업이 실패한다면?
과연 어떻게 될까 하는 생각에 테스트를 진행하였으며 결과는 차등 백업이 전체 백업 정도와 동일한 크기로 생성되게 됩니다.
뭐 이렇게 동작하는 이유는 단순하게 차등 백업은 DCM만을 활용하는 것이 아닌
트랜잭션 로그 또는 DB 부트 페이지 정보와 비교해서 판단하는 것이 아닐까 생각됩니다.
혹시 아시는 분 있으시면 공유 해주시면 감사하겠습니다.
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 6.1 <X86> (Build 7100: )
BACKUP DATABASE TEST to disk = 'c:\1.bak'
파일 1에서 데이터베이스 'TEST', 파일 'TEST'에 대해 15232개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'TEST', 파일 'TEST_log'에 대해 1개의 페이지를 처리했습니다 .
SELECT TOP 1000
row_number() over(order by (select 1)) as col1
,cast('' as char(5000)) as col2
INTO tbl11
FROM sys.sysindexes A,sys.sysindexes A1,sys.sysindexes A2,sys.sysindexes A3
BACKUP DATABASE TEST to disk = 'c:\2.bak' with DIFFERENTIAL
파일 1에서 데이터베이스 'TEST', 파일 'TEST'에 대해 1128개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'TEST', 파일 'TEST_log'에 대해 4개의 페이지를 처리했습니다 .
SELECT TOP 1000
row_number() over(order by (select 1)) as col1
,cast('' as char(5000)) as col2
INTO tbl12
FROM sys.sysindexes A,sys.sysindexes A1,sys.sysindexes A2,sys.sysindexes A3
BACKUP DATABASE TEST to disk = 'c:\3.bak' --fail
메시지 3204, 수준 16, 상태 1, 줄 2
백업 또는 복원 작업이 중단되었습니다.
메시지 3013, 수준 16, 상태 1, 줄 2
BACKUP DATABASE이(가) 비정상적으로 종료됩니다.
사용자가 쿼리를 취소했습니다.
BACKUP DATABASE TEST to disk = 'c:\4.bak' with DIFFERENTIAL
파일 1에서 데이터베이스 'TEST', 파일 'TEST'에 대해 17232개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'TEST', 파일 'TEST_log'에 대해 1개의 페이지를 처리했습니다 .
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
1. 물리적 CPU 리소스 분산이 필요한 경우
하나의 SQL Server에서 다른 타입의 쿼리가 수행되는 경우에
원활한 서비스를 위해 물리적인 리소스(CPU)를 분산 시켜 주어야 하는 이슈가 있을 수 있습니다.
예를 들어보면, 하나는 CPU자원을 많이 사용하지는 않지만, 많이 요청되는 OLTP성 쿼리,
다른 하나는 많이 수행되지는 않지만 CPU자원을 많이 사용하는 배치성 쿼리인 환경을 볼 수 있습니다.
이러한 경우에 배치성 쿼리로 인해 OLTP 쿼리 성능에 영향을 줄 수 있습니다.
이때 해결 할 수 있는 방법으로는 각각의 쿼리 타입마다 물리적인 리소스(CPU)를 할당하여
같은 다른 타입의 형태의 쿼리가 같은 CPU를 사용하지 못하게 한다면, 위와 같은 문제를 처리할 수 있습니다.
2. 해결할 수 있는 방법들
가장 먼저 떠오르는 방법은,
하나의 물리적인 머신에 두 개의 인스턴스를 올리고, affinity mask를 설정하는 것입니다.
또한 물리적인 메모리를 각각의 인스턴스에서 설정하여, 메모리 크기도 조절할 수 있는
장점이 있습니다. 하지만, 인스턴스가 다른 관계로 데이터를 동기화 해주어야 하는 부담이
존재하게 되고 전체적인 운영비용이 높아질 수 밖에 없습니다.
뭐~ 다른 방법으로 그냥 MAXDOP를 줘서 CPU사용을 제한 하는 것도 방법일 수 있지만,
MAXDOP의 경우 물리적인 CPU 지정 및 사용량을 지정을 할 수 없기에
배치가 수행되는 CPU에서 수행되는 OLTP성 쿼리는 여전히 문제가 될 수 있습니다.
음~ 그럼 SQL Server 2008의 리소스 관리자를 생각할 수 있습니다.
하지만 SQL Server 2008 Enterprise에서만 지원하는 제약이 있습니다.
3. 또 하나의 방법 NUMA
여기서 소개하려는 방법은 NUMA 를 이용한 방법 입니다.
상황에 따라 최선의 솔루션은 달라지기에 모든 상황에 대한 최선의 솔루션이라고는 볼 수 없습니다.
그리고 여기서는 NUMA를 소개하고자 하는 것이
아니기에 NUMA에 대한 소개는 건너뛰겠습니다.
요즘 판매되는 네할렘 기반의 프로세스는 메모리 컨트롤러가 CPU내부에 위치하고 있어
기본적으로 2CPU 이상인 경우 NUMA를 지원하고 있습니다.
만약 NUMA를 지원하지 않는 환경이라도 soft NUMA를 설정 할 수 있기에 크게 상관은 없습니다.
그럼 NUMA를 가지고 어떻게 할 수 있을까요?
BOL을 보면 NUMA 노드 별로 TCP/IP 포트를 설정할 수 있는 방법을 소개 하고 있습니다.
바로 이 설정으로 쿼리 타입별 CPU 리소스를 분리 할 수 있습니다.
만약 하드웨어 NUMA로 지원되는 노드가 아니라 더 세분하게 CPU 리소스를 분산하고자 한다면
soft NUMA를 추가적으로 설정해서 처리할 수 있습니다.
간단하게 예를 들면 1000포트는 0번 NUMA 노드, 2000번 포트는 1번 NUMA 노드,
3000번 포트는 모든 NUMA를 사용하게 설정 할 수 있습니다
이렇게 설정하게 된 후 각각의 클라이언트에서 각 쿼리 타입별로 사용 포트를 변경해서
사용하면 CPU 리소스 간섭 없이 처리 할 수 있습니다.
간단한 구성 방법은 아래를 참고하시길 바랍니다.
[soft NUMA 설정 방법] http://msdn.microsoft.com/ko-kr/library/ms345357.aspx
Soft NUMA를 설정하거나, NUMA를 지원하는 장비라면 sp_readerrorlog에서 아래와 같은 메시지를 확인할 수 있습니다.
2009-08-28 15:10:23.940 서버 Node configuration: node 0: CPU mask: 0x000000aa Active CPU mask: 0x000000aa.
2009-08-28 15:10:23.940 서버 Node configuration: node 1: CPU mask: 0x00000055 Active CPU mask: 0x00000055.
TCP/IP의 포트를 NUMA노드에 맵핑 후 재시작 하면 SP_READERORLOG 에서는 아래와 같이 확인할 수 있습니다.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 3000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000003.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 1000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000001.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 2000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 1 of a multi-node (NUMA) server configuration with node affinity mask 0x00000002.
각각의 포트별로 스트레스를 주고 작업관리자를 확인해 보면, NUMA 노드 만을 사용하는 것을 확인할 수 있습니다
|
1000번 포트 사용 |
2000번 포트 사용 |
3000번 포트 사용 |
|
|
|
|
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
SQL Server 2008 R2 CTP(10.50.1092.20)얼마 전에 공개 되었습니다.
혹시나 추가된 XEVENT의 이벤트가 있는지 비교해 봤더니, 아래와 같이 총 5개의 이벤트가 추가된 것을 확인 할 수 있습니다.
나머지 4개는 이름만 봐도 대충 의미를 이해할 수 있는데,
group_commit_value_changed 항목은 과연 무엇을 뜻하는지!! 궁금하네요~
혹시 아시는 분이 있으시면 공유 부탁 드립니다.
|
package |
event |
keyword |
channel |
description |
|
sqlserver |
group_commit_value_changed |
transactions |
Debug |
Number of group commit waiters has changed |
|
sqlserver |
latch_acquire_time |
latch |
Debug |
Time taken to acquire a latch |
|
sqlserver |
log_block_allocated |
transactions |
Debug |
Log block was allocated |
|
sqlserver |
log_block_freed |
transactions |
Debug |
Log block was freed |
|
sqlserver |
log_flush_requested |
transactions |
Debug |
Log flush was requested |
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
SQL Server 2008 SSMS에 향상된 기능 중 하나는 개체 탐색기 정보 창입니다.
아래 그림과 같이 개체 탐색기에 관련 데이터베이스, 테이블 및 SP등의 오브젝트의
추가적인 속성을 선택 하여 볼 수 있으며, 또한 오브젝트 이름으로 검색도 할 수 있습니다.
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
