쿼리나 프로시저를 만들어야할때 최대한의 적은 비용 + 응답속도 빠르게 만드는게 좋다.
mssql 에서는 이거를 실행 계획을 돌리고 어디서 몇퍼센트의 비용이 나왔는지 알수가 있는데
실행 계획은 mssql이 우리의 쿼리를 어떻게 실행하는지 보여주는 청사진입니다
다른 DB에서도 비슷한 기능을 제공하니 같이 보면 좋다.
실행 계획 보는 방법
먼저 실행 계획을 보는 방법입니다.
쿼리 창에 쿼리작성 후에
예상 실행 계획: Ctrl + L 또는 상단 메뉴의 "예상 실행 계획 표시" 아이콘 클릭
실제 실행 계획: Ctrl + M을 눌러 활성화한 후 쿼리 실행
2가지 방법으로 확인을 할수가 있다.
실제 실행 계획이 더 정확한 정보를 제공하므로 가능하면 실제 실행 계획을 확인하는 것이 좋습니다.
1. 가장 비용이 높은 연산자 찾기
실행 계획을 열면 여러 개의 상자(연산자)들이 화살표로 연결되어 있습니다. 각 상자 안에는 백분율이 표시되어 있는데, 이것이 바로 상대적 비용입니다.
가장 큰 백분율을 가진 연산자를 찾습니다
일반적으로 50% 이상의 비용을 차지하는 연산자가 있다면 그것이 병목 지점입니다
예시 가진에서는 다들 엇비슷하게 되어있긴하다
만약에
SELECT o.OrderID, c.CustomerName, o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01'이런 쿼리를 돌렸을때 Table Scan 이 연산이 메우 높다고 한다면 Orders 테이블을 처음부터 끝까지 모두 읽고 스캔을 하고있는것이다.
이거를 해결 하려면 OrderDate에 인덱스를 생성하여 Table Scan을 Index Seek로 변경 변경 해주면 된다.
인덱스를 하나 걸어주면 해결 된다.
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate)다시 실행 계획하면 매우 낮아지는걸 알수가 있다.
2. 경고 아이콘(느낌표) 확인
경고 아이콘은 SQL Server가 자동으로 감지한 잠재적 성능 문제를 표시합니다. 개발자가 놓치기 쉬운 문제들을 SQL Server가 친절하게 알려주는 것이죠.
주요 경고 유형
1.Type Conversion
가장 흔한 경고입니다. 컬럼 타입과 비교값의 타입이 달라서 SQL Server가 자동으로 변환을 수행하는 경우입니다.
-- CustomerID가 INT 타입인데 문자열로 비교
SELECT * FROM Customers WHERE CustomerID = '12345' -- x
SELECT * FROM Customers WHERE CustomerID = 12345 -- o2. Missing Index
테이블에 인덱스가 없어서 생기는 문제 이긴 한데 인덱스를 무조건 만들면 안된다 인덱스가 너무 많으면 INSERT/UPDATE 성능이 떨어질 수 있으니 자주 실행되는 중요한 쿼리에만 하는걸 추천
3. Excessive Grant
실제로 필요한 것보다 훨씬 많은 메모리를 할당받은 경우입니다. 이는 통계 정보가 오래되었을 때 주로 발생한다.
-- 통계를 업데이트 해주면 된다.
UPDATE STATISTICS TableName해결 방법
1.실행 계획에서 노란 삼각형 아이콘이 있는 연산자를 찾는다
2.해당 연산자에 마우스를 올려 툴팁을 확인
3.경고 내용을 읽고 제안사항을 검토
3. Estimated Rows vs Actual Rows 비교
Estimated Rows: SQL Server가 예상한 행 수
Actual Rows: 실제로 처리된 행 수
인데 SQL Server는 통계 정보를 바탕으로 최적의 실행 계획을 만들고 그대로 쿼리가 동작을 하는데 만약 예상과 실제가 크게 다르다면 SQL Server가 잘못된 판단을 하고 비효율적인 실행 계획으로 이어진다.
문제가 되는 경우
두 값의 차이가 10배 이상 나면 문제가 있는 것이다.
연산자를 마우스 오른쪽 클릭 → 속성 선택
속성 창에서 다음 값들을 확인:
Estimated Number of Rows
Actual Number of Rows
UPDATE STATISTICS TableName WITH FULLSCAN역시 통계를 업데이트 해서 균형을 잡아준다. 하지만 그래도 부정확한 경우가 있는데 이건 쿼리자체는 최적화가 필요하다.
아마 where 또는 join을 잘못 했거나 더 개선을 할수도있는 부분이있는지 체크를 해보자
