최적화하려는 SQL 쿼리가 있습니다.
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
AND SomeBit = 1
GROUP BY Id
MyTable
두 개의 색인이 있습니다.
CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)
CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)
위에서 기록한대로 쿼리를 실행하면 SQL Server는 첫 번째 인덱스를 검색하여 189,703 개의 논리적 읽기와 2-3 초의 지속 시간을 얻습니다.
@Id
변수를 인라인하고 쿼리를 다시 실행하면 SQL Server는 두 번째 인덱스를 검색하여 104 개의 논리적 읽기와 0.001 초의 지속 시간 (기본적으로 인스턴트) 만 발생합니다.
변수가 필요하지만 SQL이 좋은 계획을 사용하기를 원합니다. 임시 솔루션으로 쿼리에 인덱스 힌트를 넣고 쿼리는 기본적으로 즉각적입니다. 그러나 가능한 경우 색인 힌트를 피하려고합니다. 나는 일반적으로 쿼리 최적화 프로그램이 작업을 수행 할 수없는 경우 명시 적으로 수행 할 작업을 지시하지 않고 도움을주기 위해 수행 할 수있는 작업이 있다고 가정합니다.
그렇다면 변수를 인라인 할 때 SQL Server가 더 나은 계획을 세우는 이유는 무엇입니까?
답변
SQL Server에는 비 결합 술어의 세 가지 공통 양식이 있습니다.
A의 리터럴 값 :
SELECT COUNT(*) AS records
FROM dbo.Users AS u
WHERE u.Reputation = 1;
A의 매개 변수 :
CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
SELECT COUNT(*) AS records
FROM dbo.Users AS u
WHERE u.Reputation = @Reputation;
END;
A를 지역 변수 :
DECLARE @Reputation INT = 1
SELECT COUNT(*) AS records
FROM dbo.Users AS u
WHERE u.Reputation = @Reputation;
결과
리터럴 값 을 사용하고 계획이 a) 사소 하지 않고 b) 단순 매개 변수화 또는 c) 강제 매개 변수화 를 설정하지 않은 경우 옵티마이 저는 해당 값에 대해서만 매우 특별한 계획을 작성합니다.
매개 변수 를 사용 하면 옵티마이 저가 해당 매개 변수에 대한 계획을 작성하고 (이를 매개 변수 스니핑 이라고 함 ) 계획을 재사용하고, 재 컴파일 힌트가없고, 캐시 캐시 제거 등을 계획합니다.
당신이 사용하는 경우 지역 변수 의 최적화를위한 … 계획하게 뭔가 .
이 쿼리를 실행 한 경우 :
DECLARE @Reputation INT = 1
SELECT COUNT(*) AS records
FROM dbo.Users AS u
WHERE u.Reputation = @Reputation;
계획은 다음과 같습니다.
해당 지역 변수의 예상 행 수는 다음과 같습니다.
쿼리가 4,744,427의 수를 반환하더라도
알려지지 않은 지역 변수는 카디널리티 추정에 히스토그램의 ‘좋은’부분을 사용하지 않습니다. 밀도 벡터를 기반으로 추측을 사용합니다.
SELECT 5.280389E-05 * 7250739 AS [poo]
즉,주지 382.86722457471
(가) 최적화 차종을 추측이다.
이 알려지지 않은 추측은 일반적으로 매우 나쁜 추측이며, 종종 잘못된 계획과 잘못된 인덱스 선택으로 이어질 수 있습니다.
고치세요?
일반적으로 다음과 같은 옵션이 있습니다.
- 취성 인덱스 힌트
- 잠재적으로 비싼 재 컴파일 힌트
- 매개 변수화 된 동적 SQL
- 저장 프로 시저
- 현재 지수 개선
옵션은 구체적으로 다음과 같습니다.
현재 색인을 개선한다는 것은 조회에 필요한 모든 열을 포함하도록 색인을 확장한다는 의미입니다.
CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);
Id
값이 합리적으로 선택 된다고 가정하면 좋은 계획을 세우고 옵티 마이저에게 ‘명백한’데이터 액세스 방법을 제공함으로써 도움이됩니다.
더 많은 독서
매개 변수 포함에 대한 자세한 내용은 여기를 참조하십시오.
- Paul White의 매개 변수 스니핑, 포함 및 권장 옵션
- 저장 프로 시저를 잘못 조정하는 이유 (로컬 변수의 문제) , Kendra Little
답변
데이터가 기울어졌고 쿼리 힌트를 사용하여 옵티마이 저가 수행 할 작업을 강요하고 싶지 않으며 가능한 모든 입력 값에 대해 좋은 성능을 가져야한다고 가정합니다 @Id
. 다음 색인 쌍 (또는 동등한 색인)을 기꺼이 작성하려는 경우 가능한 입력 값에 대해 몇 가지 논리적 읽기를 요구하는 쿼리 계획을 얻을 수 있습니다.
CREATE INDEX GetMinSomeTimestamp ON dbo.MyTable (Id, SomeTimestamp) WHERE SomeBit = 1;
CREATE INDEX GetMaxSomeInt ON dbo.MyTable (Id, SomeInt) WHERE SomeBit = 1;
아래는 내 테스트 데이터입니다. 13 M 행을 테이블에 넣고 그중 절반 '3A35EA17-CE7E-4637-8319-4C517B6E48CA'
이 Id
열 값을 갖도록했습니다 .
DROP TABLE IF EXISTS dbo.MyTable;
CREATE TABLE dbo.MyTable (
Id uniqueidentifier,
SomeTimestamp DATETIME2,
SomeInt INT,
SomeBit BIT,
FILLER VARCHAR(100)
);
INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT NEWID(), CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT '3A35EA17-CE7E-4637-8319-4C517B6E48CA', CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
이 쿼리는 처음에는 조금 이상하게 보일 수 있습니다.
DECLARE @Id UNIQUEIDENTIFIER = '3A35EA17-CE7E-4637-8319-4C517B6E48CA'
SELECT
@Id,
st.SomeTimestamp,
si.SomeInt
FROM (
SELECT TOP (1) SomeInt, Id
FROM dbo.MyTable
WHERE Id = @Id
AND SomeBit = 1
ORDER BY SomeInt DESC
) si
CROSS JOIN (
SELECT TOP (1) SomeTimestamp, Id
FROM dbo.MyTable
WHERE Id = @Id
AND SomeBit = 1
ORDER BY SomeTimestamp ASC
) st;
몇 가지 논리적 읽기로 최소 또는 최대 값을 찾기 위해 인덱스 순서를 활용하도록 설계되었습니다. 는 CROSS JOIN
대한 매칭되는 열이 없을 때 정확한 결과를 얻을 수있다 @Id
값. 테이블에서 가장 인기있는 값 (6.5 백만 행 일치)을 필터링하더라도 8 개의 논리적 읽기 만 얻습니다.
테이블 ‘MyTable’. 스캔 횟수 2, 논리적 읽기 8
쿼리 계획은 다음과 같습니다.
두 인덱스 모두 0 또는 1 개의 행을 찾습니다. 매우 효율적이지만 두 개의 인덱스를 만드는 것은 시나리오에 너무 과도 할 수 있습니다. 대신 다음 색인을 고려할 수 있습니다.
CREATE INDEX CoveringIndex ON dbo.MyTable (Id) INCLUDE (SomeTimestamp, SomeInt) WHERE SomeBit = 1;
이제 원래 쿼리에 대한 쿼리 계획 (선택적 MAXDOP 1
힌트 포함)이 약간 다르게 보입니다.
키 조회는 더 이상 필요하지 않습니다. 모든 입력에 대해 잘 작동하는 더 나은 액세스 경로를 통해 밀도 벡터로 인해 옵티마이 저가 잘못된 쿼리 계획을 선택하는 것에 대해 걱정할 필요가 없습니다. 그러나 인기있는 @Id
값 을 찾는 경우이 쿼리 및 인덱스는 다른 쿼리와 인덱스보다 효율적이지 않습니다 .
테이블 ‘MyTable’. 스캔 카운트 1, 논리적 읽기 33757
답변
왜 여기에 대답 할 수는 없지만 쿼리가 원하는 방식으로 실행되도록하는 빠르고 더러운 방법은 다음과 같습니다.
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable WITH (INDEX(IX_MyTable_Id_SomeBit_Includes))
WHERE Id = @Id
AND SomeBit = 1
GROUP BY Id
이로 인해 나중에 테이블이나 인덱스가 변경되어이 최적화 기능이 제대로 작동하지 않을 위험이 있지만 필요한 경우 사용할 수 있습니다. 이 대안 대신 요청한대로 누군가가 근본 원인 답변을 제공 할 수 있기를 바랍니다.