반응형

SSMS 에서 Ctrl + M 을 눌러서 '실제 실행 계획 포함' 이 활성화 된 상태에서

쿼리를 실행 시키고 나면 실행계획이 함께 나타납니다.

실행계획 상의 특정 노드에 마우스를 가져가면 상세한 내용이 나타 납니다.

위 그림에서 '예상 행 수' 가 2.77684로 되어 있어 옵티마이저가 2.7행을 예상 했다고 생각할 수 있으나

실제 예상 행 수는 '예상 실행 횟수'를 곱한 값이 됩니다.

따라서 해당 노드에서 반환되는 총 예상 행 수는 2.77684 × 6985.3104807 = 19397.08956 입니다.

이 총 예상 행 수 값이 노드 하단의 분모 값 입니다.

그리고 분자 값은 '실제 행 수' 입니다. 

괄호 안의 %값은 총 예상 행 수 대비 실제 행 수의 백분율 입니다. 이 백분율 값이 100%에 가까울 수록

통계값에 의한 적절한 실행계획이 수립되었다고 볼 수 있습니다.

 

그러나 아래의 경우 처럼 이 값이 과도하게 적거나 많을 경우 통계가 제대로 갱신되지 않아서 엉뚱한 실행계획이

수립되었을 가능성이 있습니다.

이 경우 UPDATE STATISTICS 로 최신 통계로 업데이트가 필요 합니다.


※ [2020.10.29] 추가.
SSMS 버전 18.5 이후에 실행계획 항목(속성) 일부의 명칭이 바뀌고, 추가된 것으로 확인 됩니다.

SSMS v18.6

기존 '실제 행 수'가 '모든 실행에 대한 실제 행 수'로, 기존 '예상 행 수'가 '실행당 예상 행 수'로 
각각 명칭이 변경 되었습니다. 그리고 '모든 실행에 대한 예상 행 수' 속성이 추가 되었습니다.

같은 실행계획을 이전의 SSMS 에서 확인했을 때는 다음과 같았습니다.

SSMS v12.0

반응형
반응형
아시는 바와 같이 임시 테이블은 #(로컬) 및 ##(전역)이 prefix로 붙은 테이블 입니다.
그리고 테이블변수는 TABLE 타입으로 DECLARE된 로컬변수 입니다.
 
둘 간에 여러가지 차이점이 있지만 여기서는 성능적인 이슈만 언급 하겠습니다.
테이블변수의 문제는 SQL서버가 통계정보를 생성하지 않기 때문에 적절한 실행계획 수립이 안될 수 있다는 점 입니다.
 
아래는 테이블변수를 사용한 특정 SP의 실행계획 입니다 (처리속도는 14초).

NL JOIN 으로 처리되어 과도한 Execute, Rows가 나왔습니다. SQL 서버가 NL을 선택한 것은 예상 행수가 모두 1 이기 때문 입니다.
예상 행수가 1:1 이기 때문에 NL로 처리해도 아무 문제가 없다고 판단이 되었습니다.
실제 테이블변수의 행은 1067 입니다.
 
이제 다른것은 그대로 두고 테이블변수를 임시테이블로만 변경한 후, 다시 실행계획을 확인 해봅니다 (처리속도는 6초).

이번에는 HASH JOIN 으로 처리 되었습니다. 이유는 예상행수가 1067, 617 이기 때문 입니다.
1067건과 617건을 NL JOIN 으로 처리하면 부하가 크다고 판단하고 HASH로 JOIN 전략을 변경한 겁니다.
 
결론적으로 일반적으로 행수가 적은 경우는 테이블변수를 사용해도 무관하지만, 크기가 큰 경우는 임시테이블을 사용해야 성능이슈가 발생하지 않게 됩니다.
 
 

 

반응형
반응형

SP는 최초(처음) 실행 하는 시점에 실행계획을 수립하고 이후에는 실행계획을 계속 재사용 하게 됩니다

(이것이 SP를 사용하는 중요한 이유 중 하나 입니다).

이 최초 실행계획 수립시, 인수로 넘겨진 파라미터(매개변수)를 기준으로 통계를 통해 예상 행수를 추측하게 됩니다.

그런데 넘겨진 파라미터가 아닌 로컬 변수를 이용해서 쿼리를 실행하면 옵티마이저가 행수 추측이 실패하게 되어

원하지 않는 실행 계획이 생성 될 수 있습니다.

이로 인하여 SP 성능이 저하 될 수 있습니다.

 

예를 들어 다음과 같이 SP 가 되어 있을 때,

create proc Proc1 (
@p_date varchar(8)
)
as
SELECT
   *
FROM 
   SDSaleMaster a
WHERE
   a.SaleDate = @p_date

SP를 호출 하면,

EXEC Proc1 @p_date = '20190601'

실행계획에서 행수를 1326으로 예측 합니다.

이는 통계와 일치하는 제대로 된 예상 행 수 이기 때문에, 실행계획이 적합하게 나올 가능성이 높습니다.

그런데 다음과 같이 매개변수가 아닌 로컬 변수를 사용 하게 되어 있을 때,

create proc proc1 (
@p_date varchar(8)
)
as
declare
   @v_date varchar(8)

set @v_date = convert(varchar(8), dateadd(d, -1, @p_date), 112)  -- 하루 전

SELECT
   *
FROM 
   SDSaleMaster a
WHERE
   a.SaleDate = @v_date

SP를 호출 하면,

 EXEC Proc1 @p_date = '20190602'

옵티마이저가 예상 행수를 제대로 추정할 수가 없어서 549로 나오는 데, 이는 해당 테이블의 평균 밀도값을 이용한 것 입니다.

평균밀도

예상 행수 = 밀도 * 테이블 전체 row 수

 

이 글이 도움이 되셨으면 좋아요 눌러 주시거나 댓글을 남겨 주세요.

반응형
반응형

SQL 서버에서 서로 다른 데이터 타입간에 연산을 하게 되면
연산 전에 데이터 형식을 묵시적으로 형변환하게 되며,
이는 최종 결과 값의 데이터 형식이 된다.
그런데 여기에는 우선순위가 있기 때문에 때때로 연산결과 값에서 소수점이 사라지거나 오차가 발생 할 수 있으므로 주의해야 한다.

우선순위는 다음과 같다 (참조: https://docs.microsoft.com/ko-kr/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017)

  1. 사용자 정의 데이터 형식(가장 높음)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar(nvarchar(max) 포함)
  26. nchar
  27. varchar (varchar(max) 포함)
  28. char
  29. varbinary (varbinary(max) 포함)
  30. binary(가장 낮음)

그리고 피연산자에 숫자 상수값이 들어가는 경우 상수의 표현 방식에 따라 데이터형이 결정 되므로 역시 주의 해야 한다.

  1. 이진상수 : 0x 접미사 (16진수)
  2. integer 상수: 소수점 없는 정수
  3. 10진수(decimal, numeric) 상수: 숫자에 소수점이 포함
  4. float 및 real 상수: 과학 표기법 (예: 0.5E-1)

함수의 결과 값도 원래 컬럼의 데이터타입과 다른 경우가 있으므로 주의 해야 한다.
  1. SUM() :  decimal에 대한 계산 결과는 무조건 decimal(38, s) 가 된다.
                 s는 원래 decimal 타입 값의 소수점이하 자리 수.
                 전체 자리수가 무조건 38이 되므로 주의!
  2. CEILING(), FLOOR() : decimal에 대한 결과는 decimal(p, 0)이 된다.
                                p는 원래 decimal 타입 값의 전체 자리 수.

decimal 타입간의 연산 결과는 두 decimal 타입 중 더 넓은 범위의 값을 저장할 수 있는 타입이 우선순위를 가지는 것으로 보인다.
예를 들어 decimal(38, 5) 와 decimal(38, 2) 의 결과는 decimal(38, 2)로 된다.
소수점 이하 정밀도 보다는 정수 부분이 더 큰/작은 넓은 범위의 숫자를 저장할 수 있는 타입에 우선순위가 있는  것 같다.
특히 decimal에 대한 SUM()의 결과는 무조건 decimal(38, s)이 되고 CEILING()은 decimal(p, 0)이 되므로
만약 SUM()에 의하여 decimal(38, 5)가 되고, CEILING()에 의해 decimal(38, 0)이 된 경우 그 결과는 decimal(38, 0)이 되어
데이터에서 소수점이하 값이 사라질 수 있으므로 주의 해야 한다.

SELECT * 
INTO   #c 
FROM   (SELECT Sum(CONVERT(DECIMAL(38, 5), 123.45)) AS x 
        UNION 
        SELECT Ceiling(CONVERT(DECIMAL(38, 5), 456.78)) AS x) a 
go 

SELECT * 
FROM   #c 



반응형

+ Recent posts