연습문제

연구개발/DBA

연습문제

HEAD1TON 2012. 1. 16. 18:54

1번. 아래의 실행계획을 보고.. 어떻게 튜닝 할 것인지 적어주세요.

1번 답 :

좋은 예제가 있어 인용합니다. 이런 저런 상황에 따라 적용방법이 틀려지겠지만

대체적으로 이런 상황에서는 커버드 인덱스를 이용하는 것이 제일 효율적인 것 같네요.

USE Northwind;
GO

sp_helpindex OrdersTest

SET STATISTICS PROFILE ON
SET STATISTICS PROFILE OFF

DROP INDEX OrdersTest.Orders_x01
GO
DROP INDEX OrdersTest.Orders_x02
GO

SELECT CustomerID, ShippedDate, Freight
FROM OrdersTest
WHERE CustomerID LIKE 'BO%'

StmtText
SELECT CustomerID, ShippedDate, Freight
FROM OrdersTest
WHERE CustomerID LIKE 'BO%'
|--Table Scan(OBJECT:([Northwind].[dbo].[OrdersTest]), WHERE:([Northwind].[dbo].[OrdersTest].[CustomerID] like N'BO%'))

CREATE CLUSTERED INDEX Orders_x01 ON OrdersTest (EmployeeID, ShippedDate)
GO
CREATE NONCLUSTERED INDEX Orders_x02 ON OrdersTest (CustomerID)
GO

SELECT CustomerID, ShippedDate, Freight
FROM OrdersTest WITH (INDEX(Orders_x02))
WHERE CustomerID LIKE 'BO%'
GO

StmtText
SELECT CustomerID, ShippedDate, Freight
FROM OrdersTest WITH (INDEX(Orders_x02))
WHERE CustomerID LIKE 'BO%'
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [Northwind].[dbo].[OrdersTest].[EmployeeID], [Northwind].[dbo].[OrdersTest].[ShippedDate], [Expr1004]) WITH UNORDERED PREFETCH)
       |--Index Seek(OBJECT:([Northwind].[dbo].[OrdersTest].[Orders_x02]), SEEK:([Northwind].[dbo].[OrdersTest].[CustomerID] >= N'BO' AND [Northwind].[dbo].[OrdersTest].[CustomerID] < N'BP'), WHERE:([Northwind].[dbo].[OrdersTest].[CustomerID] like N'BO%') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[OrdersTest].[Orders_x01]), SEEK:([Northwind].[dbo].[OrdersTest].[EmployeeID]=[Northwind].[dbo].[OrdersTest].[EmployeeID] AND [Northwind].[dbo].[OrdersTest].[ShippedDate]=[Northwind].[dbo].[OrdersTest].[ShippedDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

EXEC sp_helpindex OrdersTest
--index_name    index_description                                index_keys
--Orders_x01    nonclustered located on PRIMARY    CustomerID
--Orders_x02    clustered located on PRIMARY    EmployeeID, ShippedDate

DROP INDEX OrdersTest.Orders_x02
GO

--커버드 인덱스 적용
CREATE NONCLUSTERED INDEX Orders_x02 ON OrdersTest (CustomerID) INCLUDE (Freight);
GO

SELECT CustomerID, ShippedDate, Freight
FROM OrdersTest WITH (INDEX(Orders_x02))
WHERE CustomerID LIKE 'BO%'
GO

StmtText
SELECT CustomerID, ShippedDate, Freight
FROM OrdersTest WITH (INDEX(Orders_x02))
WHERE CustomerID LIKE 'BO%'
|--Index Seek(OBJECT:([Northwind].[dbo].[OrdersTest].[Orders_x02]), SEEK:([Northwind].[dbo].[OrdersTest].[CustomerID] >= N'BO' AND [Northwind].[dbo].[OrdersTest].[CustomerID] < N'BP'), WHERE:([Northwind].[dbo].[OrdersTest].[CustomerID] like N'BO%') ORDERED FORWARD)

보내온 답 :

1번 문제는.. Key Lookup을 줄이는 방법입니다.

물론 Key Lookup을 하기 때문에.. 테이블내에 Clustered index가 있는 상태입니다.

만약, Clustered index가 없다면.. Key Lookup이 아닌.. RID Lookup을 하게 됩니다.

따라서 이를 해소하기 위해.. ncidx_tb_good_7이라는 인덱스에... key lookup을 하는 컬럼을 추가해서..

covered 또는 include index 형태로 구성하는 튜닝 방법이 정답이였습니다.

2번. 커버드 인덱스와 인클루드 인덱스의 차이점에 대해서 설명하시오.

2번 답 :

커버드 인덱스나 인클루드 인덱스는 테이블이나 클러스터형 인덱스에 액세스하지 않고 필요한 열 데이터를 인덱스 내에서 모두 찾을 수 있으므로 성능 향상에 도움이 됨.

단점은 포괄 열이 너무 많으면 인덱스 유지 관리 작업이 많아져 기본 테이블이나 인덱싱된 뷰에 대한 삽입, 업데이트 또는 삭제 작업에 필요한 시간이 늘어남

차이점은 작은 열에 대해 포함한 것은 일반적으로 커버드 인덱스라고 하며

INCLUDE INDEX는 키 열의 크기가 최대값인 900바이트를 초과하는 열을 인덱싱하는 것을 의미함.

참고문언 : http://technet.microsoft.com/ko-kr/library/ms189607%28SQL.100%29.aspx

보내온답 :

1번을 정확이 아시는 분은 2번도 쉽게 답하실 수 있습니다.

두개 모두.. 넌클러스터드 인덱스를 어떻게 활용했냐?에 따라서..두가지 형태로 갈리게 됩니다.

select a,b from 테이블 where a=10 일때..

a,b가 하나의 넌 클러스터드 인덱스에 걸려있는 상태라면... key 또는 rid lookup을 하지 않아도 되니..보통 커버링이 되었다고 표현을 합니다.(1번 튜닝 방법)

예제를 하나 봅시다.

a b

1 3

1 4

1 7

a=1,b=6 이 새로 insert 되려고 한다면... 커버링 같은 경우는... 중간에 반드시 낑겨 들어가야 합니다.

왜냐면.. 두개의 컬럼 모두 인덱스 컬럼이기 때문에 그렇죠~!

a b

1 3

1 4

1 6

1 7

이렇게 되겠죠... 이렇게 되면서... 페이지 스플릿이 날 수도 있고.. 그로 인해.. 그 넌 리프레벨에 있는 페이지도 UPDATE 가 될 문제가 발생 할 수 있습니다.

하지만.. 인클루드 인덱스를 살펴보면..

a만 인덱스가 선언되있고.. b가 include 컬럼이면.. b의 정렬따위 신경을 안쓰기 때문에.

a b

1 3

1 4

1 7

1 6 <-- 이런 형태로 insert 가 됩니다.

또한 중요한 점은 인덱스 제한 한계인 900바이트 이상인 컬럼도 지정이 가능합니다.

가량 예를 들면...게시판 내용검색을 한다고 가정합시다...

이건.. 무조건 %검색어% <-- 이런 형태기 때문에 인덱스를 이용할 수 없습니다.

무조건 Clustered index Scan이 떨어지게 되죠..

근데... 인클루드 인덱스는.. varchar(max) 도 include 컬럼에 포함될 수 있습니다...

이렇게 게시물 번호, 내용 두개의 컬럼을 넌클러스터드 인클루드 인덱스로 잡아둔다면..

index Scan을 하기 때문에.. Clustered index Scan을 하는 것보다 훨씬 효율적이겠죠 ^^

결국.. 인클루드 인덱스는........... 테이블에 반!드!시 한개만 존재하는 클러스터디 인덱스의 단점을 극복하기 위해..

테이블내에 두개이상의 클러스터드 인덱스을 만드는 방법! 으로 이해하시는게 빠릅니다.

사실 2번이 가장 어려운 문제였습니다 ~~

여기 글도 한번 참고해보시면 좋겠습니다 ^^

http://www.sqler.com/326192

3번.

주어진 결과 쿼리

결과 값(결과는 30일까지 출력이 되나.. 총 7월 31일까지 출력되야 합니다.)

주어진 쿼리

SELECT

CONVERT(SMALLDATETIME,'2011-07-01') AS CreateDate

, 3 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-05') AS CreateDate

, 10 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-11') AS CreateDate

, 3 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-02') AS CreateDate

, 20 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-18') AS CreateDate

, 11 AS TotalCount

3번 답 :

SELECT x.CreateDate, ISNULL(y.TotalCount, 0) as TotalCount
FROM (
    SELECT DATEADD(DAY, number, '2011-07-01') as CreateDate
    FROM [master].[dbo].[spt_values]
    WHERE type = 'p'
    AND number <= DATEDIFF(DAY, '2011-07-01', '2011-07-31')) x LEFT OUTER JOIN
    (
        SELECT
        CONVERT(SMALLDATETIME,'2011-07-01') AS CreateDate
        , 3 AS TotalCount
        UNION ALL
        SELECT
        CONVERT(SMALLDATETIME,'2011-07-05') AS CreateDate
        , 10 AS TotalCount
        UNION ALL
        SELECT
        CONVERT(SMALLDATETIME,'2011-07-11') AS CreateDate
        , 3 AS TotalCount
        UNION ALL
        SELECT
        CONVERT(SMALLDATETIME,'2011-07-02') AS CreateDate
        , 20 AS TotalCount
        UNION ALL
        SELECT
        CONVERT(SMALLDATETIME,'2011-07-18') AS CreateDate
        , 11 AS TotalCount
    ) y
    ON x.CreateDate = y.CreateDate

보내온답 :

3번은 주어진 쿼리를 통해... 결과처럼 화면에 나오는 화면인데.. 대부분 모두 맞추셨습니다.

문제의 핵심은.... 이럴때 LEFT JOIN으로 풀어야 하는지를 알고 있으십니까?

이거 였습니다.

--//아래 쿼리는 ROW_NUMBER 때문에.. SQL Server 2005 이상부터 수행됩니다.

SELECT

DEF.CreateDate

, ISNULL(DAT.TotalCount,0)

FROM (SELECT TOP 31 DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY OBJECT_ID),'2011-06-30') AS CreateDate from sys.objects) DEF

LEFT OUTER JOIN

(

SELECT

CONVERT(SMALLDATETIME,'2011-07-01') AS CreateDate

, 3 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-05') AS CreateDate

, 10 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-11') AS CreateDate

, 3 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-02') AS CreateDate

, 20 AS TotalCount

UNION ALL

SELECT

CONVERT(SMALLDATETIME,'2011-07-18') AS CreateDate

, 11 AS TotalCount

) DAT ON DEF.CreateDate = DAT.CreateDate

ORDER BY DEF.CreateDate

저작자표시 (새창열림)