'2012/11'에 해당되는 글 4건
- 2012.11.19 파티션 테이블 - 문제점, 주의 사항
- 2012.11.02 T-SQL::동적 PIVOT 생성 3
- 2012.11.01 tablediff 유틸리티
- 2012.11.01 Function to return a range of dats - 날짜 범위 펑션
파티션 테이블 - 문제점, 주의 사항
Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale23 October 2012
SQL Server 2005 에서 Partitioned Tables 기능이 나왔을때 우리는 기대를 했다.
많은 데이터를 최소한의 locking 으로 Switch 할 수 있을 것이라고, 그리고 쿼리 성능에도 좋은 영향을 미칠 것이라고..
그러나 무료 제공하는 점심 같은것은 없었다. 파티션 되면서 더 많은 저장 공간이 요구되었다.
Storage space increase
파티션을에 대한 계획을 할 때 꼭, 클러스터된 인덱스 key가 포함되어야 합니다. 불행이도 파티션되어야 하는 key가 고유한 인덱스가 아닐 수도 있습니다. 이 해결책은 클러스터된 인덱스에 포함시키는 방법입니다.
non-clustered index는 clustered index 의 key point를 가지고 있습니다. 파티션 테이블은 일반적으로 대형이며, 클러스터 된 인덱스 키에 새 열을 추가하면, non-clustered index의 모든 레코드가 증가됩니다.
예를 들어 500,000,000 테이블에 clustered index 에 8 byte 날짜 열이 추가된다고 가정해 보면
500000000 * 8/1024/1024/1024 = 3.75GB 인덱스당 저장공간이 증가됩니다.
이 수치는 스토리지 오버헤드의 저장공간은 포함되지 않습니다.
이 증가된 용량은 백업 시간, 복구 시간도 증가하게되며, 색인 유지 보수 비용도 증가합니다.
불행하게도 이 작업에 대한 해결책은 존재하지 않습니다.
데이터가 13개월을 가지고 있고 12개월 데이터를 select 를 매년 초에 해야 한다고 한다면 당연히 날짜 Data가 파티션 key에 존재해야 하며, 파티션이 슬라이등 되어야 합니다. 그러나, 오히려 작은 단위로 데이터를 삭제 하는것이 더 효율 적입니다.
기존 테이블을 파티션 하기 위해 clustered index에 열을 추가하는 것도 쉽지 않습니다.
그나마 대안으로는 indentity 컬럼이 있을 경우 해당 월이 시작되는 값을 알아내어, 그 key로 파티션 하는 것입니다.
저장 공간 뿐 아니라, 실행계획에 대해서도 살펴봐야 합니다.
Suboptimal execution plans
파티셔닝을 구현할 때, 물리적 데이터 레이아웃이 변경됨으로 퀴러의 실행 계획이 예기치 못한 결과를 나타낼 수도 있습니다.
그림 참고)
Listing 1 shows the structure of the Orders table, with clustered index.
GO
CREATE TABLE dbo.Orders
(
Id INT NOT NULL ,
OrderDate DATETIME NOT NULL ,
DateModified DATETIME NOT NULL ,
Placeholder CHAR(500)
NOT NULL
CONSTRAINT Def_Data_Placeholder DEFAULT 'Placeholder',
);
GO
CREATE UNIQUE CLUSTERED INDEX IDX_Orders_Id
ON dbo.Orders(ID);
GO
Listing 2 inserts 524,288 records into the Orders table and creates a non-clustered index.
WITH N1 ( C )
AS ( SELECT 0
UNION ALL
SELECT 0
)-- 2 rows
, N2 ( C )
AS ( SELECT 0
FROM N1 AS T1
CROSS JOIN N1 AS T2
)-- 4 rows
, N3 ( C )
AS ( SELECT 0
FROM N2 AS T1
CROSS JOIN N2 AS T2
)-- 16 rows
, N4 ( C )
AS ( SELECT 0
FROM N3 AS T1
CROSS JOIN N3 AS T2
)-- 256 rows
, N5 ( C )
AS ( SELECT 0
FROM N4 AS T1
CROSS JOIN N4 AS T2
)-- 65,536 rows
, N6 ( C )
AS ( SELECT 0
FROM N5 AS T1
CROSS JOIN N2 AS T2
CROSS JOIN N1 AS T3
)-- 524,288 rows
, IDs ( ID )
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL
) )
FROM N6
)
INSERT INTO dbo.Orders
( ID ,
OrderDate ,
DateModified
)
SELECT ID ,
DATEADD(second, 35 * ID, @StartDate) ,
CASE WHEN ID % 10 = 0
THEN DATEADD(second,
24 * 60 * 60 * ( ID % 31 ) + 11200 + ID
% 59 + 35 * ID, @StartDate)
ELSE DATEADD(second, 35 * ID, @StartDate)
END
FROM IDs;
GO
CREATE UNIQUE NONCLUSTERED INDEX IDX_Orders_DateModified_Id
ON dbo.Orders(DateModified, Id);
SELECT TOP 100
ID ,
OrderDate ,
DateModified ,
PlaceHolder
FROM dbo.Orders
WHERE DateModified > @LastDateModified
ORDER BY DateModified ,
Id;
DROP INDEX IDX_Orders_Id ON dbo.Orders;
GO
CREATE PARTITION FUNCTION pfOrders(DATETIME)
AS RANGE RIGHT FOR VALUES
('2012-02-01', '2012-03-01',
'2012-04-01','2012-05-01','2012-06-01',
'2012-07-01','2012-08-01');
GO
CREATE PARTITION SCHEME psOrders
AS PARTITION pfOrders
ALL TO ([primary]);
GO
CREATE UNIQUE CLUSTERED INDEX IDX_Orders_OrderDate_Id
ON dbo.Orders(OrderDate,ID)
ON psOrders(OrderDate);
GO
CREATE UNIQUE INDEX IDX_Data_DateModified_Id_OrderDate
ON dbo.Orders(DateModified, ID, OrderDate)
ON psOrders(OrderDate);
GO
Figure 6 shows how SQL Server actually processes the query.
파티션되지 않은 테이블은 top 100 조회하면 처음 나오는 값 기준으로 정렬없이 100개를 가져오니까 비교적 효율적.
만약 파티셔닝을 구현 한다며 key가 변경되고 정렬되어 있는 것 중에 모두 데이터를 찾아야 한다. 즉, 파티션이 8개 되었다고 한다면 100의 데이터가 있는지 보다 파티션 별로 데이터를 찾아서 정렬한다.
물론, 힌트로 고정할 수도 있지만 예상치 않은 결과 이다.
Fixing the problem
그림 참고)
SELECT TOP 100
ID ,
OrderDate ,
DateModified ,
PlaceHolder
FROM dbo.Orders
WHERE DateModified > @LastDateModified
AND $partition.pfOrders(OrderDate) = 5
ORDER BY DateModified ,
ID;
간혹 이상할 경우 많이 사용하는 방법인데 파티션 범위를 확인하고 지정 파티션을 정해 주는 것이다. (파티션 된 테이블을 조건에 해당하는 데이터를 삭제 할 경우 top 10개로.. 등. 이관시 꼭 실행계획을 확인하고 해당 파티션만 찾게 범위를 지정해 줘야 하는 것이 이 이유이다. )
그러려면 파티션의 총 수를 알아야 한다.. ( ys.partition_range_values)
@BoundaryCount INT;
SELECT @BoundaryCount = MAX(boundary_id) + 1
FROM sys.partition_functions pf
JOIN sys.partition_range_values prf
ON pf.function_id = prf.function_id
WHERE pf.name = 'pfOrders';
WITH Boundaries ( boundary_id )
AS ( SELECT 1
UNION ALL
SELECT boundary_id + 1
FROM Boundaries
WHERE boundary_id < @BoundaryCount
)
SELECT part.ID ,
part.OrderDate ,
part.DateModified ,
$partition.pfOrders(part.OrderDate) AS [Partition Number]
FROM Boundaries b
CROSS APPLY ( SELECT TOP 100
ID ,
OrderDate ,
DateModified
FROM dbo.Orders
WHERE DateModified > @LastDateModified
AND $Partition.pfOrders(OrderDate) =
b.boundary_id
ORDER BY DateModified ,
ID
) part;
Dealing with Cardinality Estimation Errors
윗 부분 처럼하면 경계값 에러가 발생할 수 있다. 개선책.
tempdb를 사용한다.
(
ID INT NOT NULL
PRIMARY KEY
);
DECLARE @LastDateModified DATETIME = '2012-06-25' ,
@BoundaryCount INT;
SELECT @BoundaryCount = MAX(boundary_id) + 1
FROM sys.partition_functions pf
JOIN sys.partition_range_values prf
ON pf.function_id = prf.function_id
WHERE pf.name = 'pfOrders';
WITH Boundaries ( boundary_id )
AS ( SELECT 1
UNION ALL
SELECT boundary_id + 1
FROM Boundaries
WHERE boundary_id < @BoundaryCount
)
INSERT INTO #T
( ID
)
SELECT boundary_id
FROM Boundaries;
WITH Top100 ( ID, OrderDate, DateModified )
AS ( SELECT TOP 100
part.ID ,
part.OrderDate ,
part.DateModified
FROM #T b
CROSS APPLY ( SELECT TOP 100
ID ,
OrderDate ,
DateModified
FROM dbo.Orders
WHERE DateModified >
@LastDateModified
AND $Partition.pfOrders(OrderDate) =
b.id
ORDER BY DateModified ,
ID
) part
ORDER BY part.DateModified ,
part.ID
)
SELECT d.Id ,
d.OrderDate ,
d.DateModified ,
d.Placeholder
FROM dbo.Orders d
JOIN Top100 t ON d.Id = t.Id
AND d.OrderDate = t.OrderDate
ORDER BY d.DateModified ,
d.ID;
DROP TABLE #T;
Hardcoding the number of Partitions
그림 참고)
WITH Boundaries ( boundary_id )
AS ( SELECT V.v
FROM ( VALUES ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7),
( 8) ) AS V ( v )
),
Top100 ( ID, OrderDate, DateModified )
AS ( SELECT TOP 100
part.ID ,
part.OrderDate ,
part.DateModified
FROM Boundaries b
CROSS APPLY ( SELECT TOP 100
ID ,
OrderDate ,
DateModified
FROM dbo.Orders
WHERE DateModified >
@LastDateModified
AND $Partition.pfOrders(OrderDate) =
b.boundary_id
ORDER BY DateModified ,
ID
) part
ORDER BY part.DateModified ,
part.ID
)
SELECT d.Id ,
d.OrderDate ,
d.DateModified ,
d.Placeholder
FROM dbo.Orders d
JOIN Top100 t ON d.Id = t.Id
AND d.OrderDate = t.OrderDate
ORDER BY d.DateModified ,
d.ID;
윗 부분 처럼 하면 사용하고자 하는 메모리보다 더 많은 메모리를 사용하게 된다. ( 일부 파티션에 데이터가 없는 경우)
파티션 수를 하드 코딩 한다. -> 파티션을 추가 되었을 경우 수정해야 한다. 이론.. ㅜㅜ;;
Summary
테이블 파티셔닝은 다양한 디자인과 성능 문제에 도움을 줄 수 있는 훌륭한 기능이지만, 또한 문제 집합을 만 들 수도 있습니다.
clustered index에 파티션 열을 추가하면 저장공간 및 인덱스 유지 보수 비용이 증가합니다.
다른 물리적 데이터 레이아웃 변경이 될 경우 실행 계획이 변화 될 수 있습니다. 이런 부작용이 있다는것을 알고 적용하기 전에 꼭 Test 해봐야 합니다.
==> 그래서 처음 설계 부터 파티션 하게 하거나, 자주 조회되지 않은 과거 테이블이나 이력 테이블을 적용하고 있고
또는 키를 변경하기 보다는 기존 key의 범위로 파티션 key를 사용하고 있다, 물론 조회되는 중요 sp들의 조건 절을 확인한다.
기존 테이블을 파티셔닝 하는것은 항상 조심해야 한다.
'Peformance Tuning' 카테고리의 다른 글
Index IGNORE_DUP_KEY 옵션 TEST (1) | 2015.09.14 |
---|---|
query plan의 실행 옵션 보기 (0) | 2012.01.29 |
Dynamic Management Views (0) | 2012.01.22 |
Hash Join 제약. (0) | 2011.10.10 |
T-SQL::동적 PIVOT 생성
원문 : http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
동적으로 PIVOT을 만들 수 있는 방법입니다.
아직도 case 문이 익숙하긴 하지만, 가끔은 오히려 더 복잡해지는 case 문 때문에 PIVOT을 사용해야 한다고 느껴질 때가 있습니다.
아직 PIVOT 익숙하지는 않은데 하다보니 컬럼 고정이 되서 불편한 점이 있는데 이 방법으로 사용하면 좋을 것 같네요.
PIVOT 은 행 형태의 반환을 열로 만들어 주는 것입니다.
아래로 테스트할 데이터를 생성 합니다.
CREATE TABLE dbo.Products
(
ProductID INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL UNIQUE
/* other columns */
);
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE dbo.OrderDetails
(
OrderID INT,
ProductID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT
/* other columns */
);
INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);
SELECT p.Name, Quantity = SUM(o.Quantity)
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
GROUP BY p.Name;
결과는 아래 처럼 행으로 나 옵니다. 그러나 열로 표시하고 싶어요. 하면 PIVOT 처리 합니다.
SELECT p.[foo], p.[bar], p.[kin]
FROM
(
SELECT p.Name, o.Quantity
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
) AS j
PIVOT
(
SUM(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;
원하는 결과가 나왔습니다. ~
다른 데이터를 추가 해 보죠.
INSERT dbo.Products SELECT 4, N'blat';
INSERT dbo.OrderDetails SELECT 4,4,5;
그리고 나서 다시 윗 쿼리를 실행 해 보면 결과는 동일하게 3개의 데이터의 집계만 나타납니다.
당연한 결과 입니다. 지금 추가된 blat는 보이지 않습니다. PIVOT는 집계해서 보여줄 컬럼을 명시해야 하기 때문입니다. 쿼리를 그럼 고쳐야 하는데 좀 더 쉽게 동적으로 할 수 있게 아래는 도와 줍니다.
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name) -- += 는 컬럼을 합쳐서 한 문자로 만들어 주죠.
FROM (SELECT p.Name FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
GROUP BY p.Name) AS x;
print @columns
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.Name, o.Quantity
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
) AS j
PIVOT
(
SUM(Quantity) FOR Name IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
-- @sql의 문은 컬럼을 만들어서 PIVOT 를 생성해 줍니다.
/*결과)
SELECT p.[foo], p.[bar], p.[kin], p.[blat]
FROM
(
SELECT p.Name, o.Quantity
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
) AS j
PIVOT
(
SUM(Quantity) FOR Name IN ([foo],[bar],[kin],[blat])
) AS p;
*/
원하는 결과가 도출.
'T-SQL' 카테고리의 다른 글
master SP 생성 -> DB별 view 인식 하게 하기 (0) | 2015.04.01 |
---|---|
월에 두번째/세번째 요일 구하기. (0) | 2013.03.13 |
Function to return a range of dats - 날짜 범위 펑션 (0) | 2012.11.01 |
T-SQL:: 인덱스 압축 예상 Size (0) | 2012.07.26 |
tablediff 유틸리티
SQL SERVER 2005 이상
와 어찌 하다가 이제서야 알게됨.
TABLEDIFF 유틸.. 이름에서도 알 수 있 듯이 테이블 차이점 찾기 유틸입니다.
복제되어 있는 곳에서 데이터 불일치 문제를 해결하는데 유용하며,
batch file일 이용해서 사용 할 수 있습니다.
-
복제 게시자 역할을 하는 MicrosoftSQL Server 인스턴스에 있는 원본 테이블과 복제 구독자 역할을 하는 하나 이상의 SQL Server 인스턴스에 있는 대상 테이블을 행 단위로 비교할 수 있습니다.
-
행 개수와 스키마만 비교하여 비교 작업을 빨리 수행할 수 있습니다.
-
열 수준에서 비교할 수 있습니다.
-
대상 서버의 불일치를 해결하는 Transact-SQL 스크립트를 생성하여 원본 테이블과 대상 테이블을 일치시킬 수 있습니다.
-
결과를 출력 파일이나 대상 데이터베이스의 테이블에 기록할 수 있습니다.
데이터 형식이 sql_variant 인 열은 지원하지 않는다.
도움말 :http://msdn.microsoft.com/ko-kr/library/ms162843(v=SQL.100).aspx
- 사용권한
-o 또는 -f 옵션을 사용하려면 지정된 파일 디렉터리 위치에 대한 쓰기 권한이 있어야 함.
-et 옵션을 사용하려면 db_owner 고정 데이터베이스 역할의 멤버이거나 적어도 구독 데이터베이스에 대한 CREATE TABLE 권한과 대상 서버의 대상 소유자 스키마에 대한 ALTER 권한이 있어야 함
-dt 옵션을 사용하려면 db_owner 고정 데이터베이스 역할의 멤버이거나 적어도 대상 서버의 대상 소유자 스키마에 대한 ALTER 권한이 있어야 함.
- 사용 법
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1 -sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test -destinationtable table2
- 결과
Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:
-sourceserver server1
-sourcedatabase test
-sourcetable table1
-destinationserver server2
-destinationdatabase test
-destinationtable table2
Table [test].[dbo].[table1] on server1 and Table [test].[dbo].[table2] on server1 have 3 differences.
Err PersonID
Mismatch 1
Dest. Only 2
Src. Only 3
The requested operation took 0.4375 seconds.
'Common Admin' 카테고리의 다른 글
Database Landscape map – February 2013 (0) | 2013.03.13 |
---|---|
SQL Server tempdb 복구 (0) | 2013.01.08 |
Admin::2000용 TEST DB 생성. (0) | 2011.10.05 |
Admin:: SQL Server 에러로 다른 서버에서 Rebuilding 처리 (0) | 2011.08.14 |
Function to return a range of dats - 날짜 범위 펑션
http://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/By: Albert Hetzel 정리
SQL SERVER 2005 이상
일자별 sum, max 등의 집계 쿼리를 할 때 일자의 범위가 모두 포함되기어 표현되어지기를 원할 때가 있다.
그러나 실제 데이터는 일자 범위에 데이터가 없을 수도 있고 있을 수도 있다. 이럴때 어떻게 해야 할까??
물론, row_number 함수를 사용하여 corss join을 사용하면 가능하다. 그러나 이런 쿼리는 복잡하고 원하는 결과를 나타내지 않을 수도 있다.
여기서는 테이블 함수를 사용하는 방법으로 해결 합니다.
이 함수는 CTE를 사용하여 반복적으로 날짜를 반환 해 준다. (@Increment 옵션에 따라서)
DROP FUNCTION [DateRange] GO CREATE FUNCTION [dbo].[DateRange] ( @Increment CHAR(1), -- 일/주/달 여부 @StartDate DATETIME, -- 시작 날짜 @EndDate DATETIME -- 종료 날짜 ) RETURNS @SelectedRange TABLE (IndividualDate DATETIME) AS BEGIN ;WITH cteRange (DateRange) AS ( SELECT @StartDate UNION ALL SELECT CASE WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange) WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange) WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange) END FROM cteRange WHERE DateRange <= CASE WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate) WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate) WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate) END) INSERT INTO @SelectedRange (IndividualDate) SELECT DateRange FROM cteRange OPTION (MAXRECURSION 3660); RETURN END GO
실행을 해 보면 아래와 같다.
SELECT IndividualDate FROM DateRange('d', '2012-11-01', '2012-11-10')
SELECT IndividualDate FROM DateRange('w', '2012-11-01', '2012-11-10')
SELECT IndividualDate FROM DateRange('m', '2012-11-01', '2013-01-10')
자 그럼 이제 집계하고자 하는 데이터와 조인해서 사용해 보자
임시 테이블을 사용해서 해당 날짜의 제품이 판매된 정보를 입력 하였다. 아래 데이터에서 봤을 경우 11/1 ~ 11/10 사이에
매출은 이틀만 발생 하였다.
CREATE TABLE #temp (orderDate DATETIME, orderInfo VARCHAR(50))
INSERT INTO #temp VALUES ('2012-11-01','2 copies of SQL Server 2008')
INSERT INTO #temp VALUES ('2012-11-05','6 copies of SQL Server 2008 R2')
INSERT INTO #temp VALUES ('2012-11-05','10 copies of SQL Server 2012')
SELECT a.IndividualDate , b.orderDate, b.orderInfo
FROM DateRange('d', '2012-11-01', '2012-11-10') as a
LEFT JOIN #temp as b on a.IndividualDate = b.orderDate
결과에서 알 수 있듯이, 매출이 발생하지 않은 일자도 나열 되어 표시된다. 이럴 경우 매출이 없는 일자를 찾기도 쉽다.
'T-SQL' 카테고리의 다른 글
월에 두번째/세번째 요일 구하기. (0) | 2013.03.13 |
---|---|
T-SQL::동적 PIVOT 생성 (3) | 2012.11.02 |
T-SQL:: 인덱스 압축 예상 Size (0) | 2012.07.26 |
TSQL::월별 누적 건수를 구하기. (0) | 2012.01.29 |