반응형
반응형

checkpoint가 발생될 수 있는 조건이 갖추어 지면

자동으로 checkpoint가 발생하여 commit되지 않은 페이지가 disk에 모두 기록됩니다.

(checkpoint에 대한 자세한 내용은 BOL을 참조하세요.)

 

SQL Server 서비스의 shutdown시에도 checkpoint가 발생하게 되는대요.

비정상적인 종료로 인해 checkpoint가 발생하지 않은 경우는

다음 start up 시에 다음과 같은 복구과정을 거치게 됩니다.

    (1/3) - analyze

    (2/3) - rollforward

    (3/3) - rollback

 

2/3 또는 3/3 과정에 처리해야할 transaction이 많은 경우는

이 과정이 1시간 이상 소요되어 서비스에 지장을 초래할 수도 있으니

24*7 를 요하는 곳에서는 중요한 issue가 될 수도 있습니다.

 

따라서, checkpoint는 SQL Server의 중요한 activity중 하나입니다.

ERRORLOG를 통해서 이 checkpoint가 언제 발생하는지 확인이 가능합니다.

 

/*

    How to know when a checkpoint was occured

    2006-02-02

    Gi Whan Han

*/

 

--xp_readerrorlog undocumented extended procedure was used to read a errorlog

EXEC master..xp_readerrorlog

 

--make the checkpoint is logged to ERRORLOG

DBCC TRACEON (3502)

 

--execute checkpoint manually

checkpoint

-- 2006-02-02 14:06:40.64 spid53    DBCC TRACEON 3502, 서버 프로세스 ID(SPID) 53입니다.

-- 2006-02-02 14:07:48.27 spid53    Ckpt dbid 7 started (100000)

-- 2006-02-02 14:07:48.31 spid53    Ckpt dbid 7 phase 1 ended (100000)

-- 2006-02-02 14:07:48.42 spid53    Ckpt dbid 7 complete

 

--execute checkpoint manually

checkpoint

-- 2006-02-02 14:08:17.07 spid53    Ckpt dbid 7 started (100000)

-- 2006-02-02 14:08:17.10 spid53    Ckpt dbid 7 phase 1 ended (100000)

-- 2006-02-02 14:08:17.12 spid53    Ckpt dbid 7 complete

 

--make the checkpoint is not logged to ERRORLOG

DBCC TRACEOff (3502)

--2006-02-02 14:09:17.92 spid53    DBCC TRACEOFF 3502, 서버 프로세스 ID(SPID) 53입니다.

 

checkpoint

--No scripts were logged

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

SQL2005 저장프로시져 모니터하기  (0) 2011.07.20
COUNT Vs EXISTS  (0) 2011.07.18
Cross Apply를 이용한 그룹별 Top N 질의  (0) 2011.07.18
SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법  (0) 2011.07.18
SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
반응형
-- 1.저장프로시져별 실행수 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle)st
where DB_Name(st.dbid) is not null and cp.objtype = 'proc'
group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.execution_count) desc

--2. CPU소모량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time
from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where db_name(st.dbid) is not null and cp.objtype='proc'
group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_worker_time) desc

--3. IO량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum
(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc


--4. 처리시간이 긴 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc
반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

checkpoint 발생주기 찾기  (0) 2011.09.27
COUNT Vs EXISTS  (0) 2011.07.18
Cross Apply를 이용한 그룹별 Top N 질의  (0) 2011.07.18
SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법  (0) 2011.07.18
SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
반응형

본 포스트는 Microsoft MVP인 Andrew Kelly의 블로그에서 제기된 이슈를 참고하여 작성하였다.

그리고, 예제는 MS SQL Server 2005의 예제 데이터베이스인 Adventureworks의 sales.salesorderdetail 테이블을 사용하여 작성되었다.

 

여기서 소개할 내용은 MS SQL Server에 국한된 것만은 아니다. 모든 RDBMS에서 공통되며, 또 증명할 수 있는 내용이다. 이미 많은 책이나 블로그 등에서 다루어진 내용이긴 하지만 많은 개발자들이 실수하는 부분 중 하나인 COUNT(*) 잘못된 사용에 대해서 이야기하도록 하겠다.

 

 

COUNT Vs EXISTS

개발하다가 보면 아래와 같은 프로그램을 자주 접하게 될 것이다.

데이터 존재 여부를 체크하고, 그 체크에 의해 로직이 분기하는 내용이다.

만일 해당 상품번호에 대한 주문 이력이 있다면

     어떤 처리 ...

그렇지 않다면

     다른 처리 ...

 

이와 같은 프로그램을 구현할 때, 블로그 주인장은 아직도 많은 개발자들이 아래와 같은 실수를 범하는 것을 종종 목격할 수 있었다.

IF ( SELECT COUNT(*) FROM sales.salesorderdetail WHERE ProductID = 897 ) > 0

   Print 'Yes' ...

ELSE

   Print 'No' ...

 

단지, 해당 상품의 주문 이력이 있는지 없는지 체크하는 것 뿐임에도 불구하고, 상품주문이력 테이블에서 해당 상품의 주문이력을  전부 COUNT하고 있다. 만일 상품번호가 897의 주문이력이 수십만 혹은 수백만 이상이 된다면 어떤 결과를 초래할까?

생각만 해도 끔찍한 일이 아닐 수 없다.

 

이런 경우에는 COUNT()가 아닌 EXISTS를 사용해야 한다. 훨씬 좋은 성능을 얻을 수 있다.

 

그럼 COUNT()를 사용할 때와 EXISTS를 사용할 때와는 어떤 차이가 있는 것일까?

먼저 아래의 SQL을 살펴보자.

첫번째 IF문에서는 COUNT()를, 두번째 IF문에서는 EXISTS를 사용하여 해당 상품에 대한 주문 이력이 있는지를 체크하고 있다.

해당 테이블에는 상품번호 = 897의 데이터가 2건이 존재하고 있다. 

※ 본 예제 및 결과는 Andrew Kelly의 블로그에서 가져온 내용이다.

 

USE AdventureWorks
GO

 

SET STATISTICS IO ON

GO

 

IF (SELECT COUNT(*) FROM sales.salesorderdetail WHERE ProductID = 897 ) > 0

    Print 'Yes'

 

IF EXISTS (SELECT * FROM sales.salesorderdetail WHERE ProductID = 897)
    Print 'Yes'

 

SET STATISTICS IO OFF

GO

 

Result >>

Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

위의 결과에서 확인할 수 있듯이 체크부분에서 반환되는 행 수가 적을 경우는 IO의 차이는 크게 느낄 수가 없다.

하지만 체크 부분에서 반환되는 행 수가 많다면 이야기는 달라진다.

 

아래의 예를 살펴보자.

해당 테이블에는 상품번호 = 870의 데이터가 4688건 존재하고 있다. 

※ 본 예제 및 결과는 Andrew Kelly의 블로그에서 가져온 내용이다.

 

USE AdventureWorks
GO

 

SET STATISTICS IO ON

GO

 

IF (SELECT COUNT(*) FROM sales.salesorderdetail WHERE ProductID = 897 ) > 0

    Print 'Yes'

IF EXISTS (SELECT * FROM sales.salesorderdetail WHERE ProductID = 897)
    Print 'Yes'

 

SET STATISTICS IO OFF

GO

 

Result >>

Table 'SalesOrderDetail'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

결과에서 확인할 수 있듯이, Logical read IO가 5배의 차이가 발생하였다.

 

다음 예에서는 인덱스 검색을 하지 않았을 때의 성능 차이를 보여준다.

이 테이블의 전체 테이터 수는 121,317건이며 357건의 데이터가 검색된다.

이번에는 250배의 IO 성능차이를 보임을 확인 할 수 있다.

※ 본 예제 및 결과는 Andrew Kelly의 블로그에서 가져온 내용이다.

 

USE AdventureWorks
GO

 

SET STATISTICS IO ON

GO

 

IF (SELECT COUNT(*) FROM sales.salesorderdetail WHERE ModifiedDate = '20010701 00:00:00.000' ) > 0

    Print 'Yes'

IF EXISTS (SELECT * FROM sales.salesorderdetail WHERE ModifiedDate = '20010701 00:00:00.000')
    Print 'Yes'

 

SET STATISTICS IO OFF

GO

 

Result >>

Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, physical reads 0, read-ahead reads 331, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

BOL에 "하위 쿼리에 행이 있으면 TRUE를 반환한다." 라는 부분이 있다. 

이 말에서 짐작할 수 있듯이, EXISTS 문에서는 하위 쿼리의 결과 집합이 한 건이든 수 천만 건이든 간에 만족하는 결과가 발견되면 그 즉시 TRUE를 반환하고 스캔을 중지하게 된다. 따라서 전체를 검색하는 COUNT(*) 보다 성능 면에서 우수한 것은 당연할 것이다.

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

checkpoint 발생주기 찾기  (0) 2011.09.27
SQL2005 저장프로시져 모니터하기  (0) 2011.07.20
Cross Apply를 이용한 그룹별 Top N 질의  (0) 2011.07.18
SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법  (0) 2011.07.18
SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
반응형
주인장은 현재 일본에 거주하고 있습니다.

여러분도 아시다시피 동일본 대지진으로 인해 상황이 좋지 못한 관계로, 한동안 기사를 포스팅하지 못했네요.

그동안 관심을 가지고 기사를 읽어주신 동네 주민들께 너무 죄송합니다.

사태가 어떻게 지속될지 알 수는 없지만, 짬짬이 함께 공유했으면 하는 기사가 있으면 포스팅하도록 하겠습니다.

각설하고 본론으로 들어가도록 하겠습니다.

 

이해를 돕기 위해 먼저, Runners라는 테이블을 작성해 보자.

Runners 테이블은 각 주자(Runner) 별 기록(Time)과 주자의 연령(Age)을 저장하고 있다.

 

CREATE TABLE #Runners
(
  Runner integer NOT NULL
, Time integer NOT NULL
, Age integer NOT NULL
)
INSERT INTO #Runners
SELECT 1 , 10 , 20 UNION ALL
SELECT 2 , 15 , 20 UNION ALL
SELECT 3 , 11 , 20 UNION ALL
SELECT 4 , 12 , 30 UNION ALL
SELECT 5 , 18 , 30 UNION ALL
SELECT 6 , 9 , 40 UNION ALL
SELECT 7 , 16 , 40 UNION ALL
SELECT 8 , 13 , 30
 
자! 이제 이번 토픽의 주제인 각 연령별(그룹별)로 가장 빠른 주자 Top N을 질의해 보자.

여러분이라면 어떻게 T-SQL을 작성할 것인가?

가장 보편적으로 알려진 방법은 Ranking 함수(특히 row_number())와 CTE(Common table Expression) 사용하는 방법이다.

 

 

ROW_NUMBER() 함수와 CTE(Common table Expression) 를 이용한 방법

먼저, 코스를 완주한 주자를 기록 시간 순으로 정렬시켜 보자. 연령별로 그룹핑을 할 필요가 없다면 문제는 아주 간단하다.

 

SELECT * 
  FROM #Runners 
 ORDER BY Time
 
Runner        Time         Age
------ ----------- -----------
     6           9          40
     1          10          20
     3          11          20
     4          12          30
     8          13          30
     2          15          20
     7          16          40
     5          18          30
 
 
그리고, TOP 구문을 이용해서 상위 레코드 2개를 질의해 보자.
    
SELECT TOP(2) * 
  FROM #Runners 
 ORDER BY Time
 
Runner        Time         Age
------ ----------- -----------
     6           9          40
     1          10          20
 
 
하지만, 우리는 연령별로 TOP N 질의를 해야 한다. 이 때, ROW_NUMBER() 함수를 사용하면 유용하다.
 
SELECT *, ROW_NUMBER() OVER(ORDER BY Time) AS RowN 
  FROM #Runners
 
Runner        Time         Age                 RowN
------ ----------- ----------- --------------------
     6           9          40                    1
     1          10          20                    2
     3          11          20                    3
     4          12          30                    4
     8          13          30                    5
     2          15          20                    6
     7          16          40                    7
     5          18          30                    8
 
이 질의는 "해당 테이블(#Runners)의 모든 데이터(*)를 시간별로 레코드를 정렬하여(ORDER BY Time) 정렬 순번(ROW_NUMBER())을 구해서 보여주라"는 것을 의미한다.
만일 동일 순위에 대한 처리가 필요하다면 ROW_NUMBER()함수보다는 DENSE_DANK() 함수를 사용하는 것이 좋다.
 

이제 OVER 구에 “PARTITION_BY” 를 확장해 보자.

“PARTITION_BY” 구는 “PARTITION_BY” 구에 지정한 컬럼 별로 그룹 함수를 적용하라는 것을 의미한다.

따라서, 아래의 질의는 RowN 컬럼의 행 번호를 Age 별로 카운트한다.

 

SELECT *, ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) AS RowN 
  FROM #Runners 
 ORDER BY Age,RowN
 
Runner        Time         Age                 RowN
------ ----------- ----------- --------------------
     1          10          20                    1
     3          11          20                    2
     2          15          20                    3
     4          12          30                    1
     8          13          30                    2
     5          18          30                    3
     6           9          40                    1
     7          16          40                    2

 

이 질의로 연령별로 기록이 좋은 주자 순으로 정렬할 수 있게 되었다.

이제,TOP N을 필터링하는 일만 남았다.

여러분은 TOP N을 필터링하기 위해 아래의 SQL을 생각할 수 있을 것이다.

 

SELECT *, ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) AS RowN 
  FROM #Runners
 WHERE ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) <= 2
 ORDER BY Age,RowN

 

ㅜㅜ… 실행이 안된다...

당연히 구문 에러가 발생할 것이다.

어떻게 해야지?

이 문제를 해결하기 위해 일반적으로 CTE(Common Table Expresssion) 를 사용한다.

 

WITH cteRunners
AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) AS RowN 
        FROM #Runners )
SELECT *
  FROM cteRunners
 WHERE RowN <=2
 ORDER BY Age, Rown 
 
Runner        Time         Age                 RowN
------ ----------- ----------- --------------------
     1          10          20                    1
     3          11          20                    2
     4          12          30                    1
     8          13          30                    2
     6           9          40                    1
     7          16          40                    2

 

이제 우리가 원하는 소기의 목적을 달성하게 되었다.

하지만 이대로 만족하는가?!

그룹 함수는 그 특성상 그룹 함수의 대상이 되는 집합이 커지면 커질수록 CPU, Memory 등의 리소스 사용량이 늘어나고 응답 속도가 늦어진다.

그러면 어떻게 하면 좀 더 최적화된 질의를 할 수 있을까?

 

 

CROSS APPLY 연산자를 이용한 집합 축소

문제는 이러하다.

우리가 최종적으로 가지고 와야 할 집합은 그룹별 상위 N개 이지만, 상위 N개를 도출하기 위해서는 전체 집합을 읽어야 한다는 것이다.

그렇다! 최적화의 키는 읽어야 할 집합을 축소 하는 것이다. 모든 질의가 그러하듯이...

 

집합을 축소해 보자!!!

우리는 집합을 축소하기 위해 CROSS APPLY 연산자를 사용할 것이다.

 

우선 아래의 스크립트를 실행해서 예제 테이블 #RunnersBig을 만들자.

이 테이블에 100만 건의 데이터을 입력하였고, age + time 순서로 된 인덱스를 작성하였다.

 

IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL
    DROP TABLE #RunnersBig
GO 
 
CREATE TABLE #RunnersBig
(
    RunnerId INTEGER IDENTITY
  , Time INTEGER NOT NULL
  , Age INTEGER NOT NULL
)
GO
 
INSERT INTO #runnersbig ( Time , Age ) 
SELECT TOP 1000000 
       ABS (CHECKSUM(NEWID()))% 1000 
     , ABS (CHECKSUM(NEWID()))% 99 
  FROM sys.columns a 
 CROSS JOIN sys . columns b 
 CROSS JOIN sys . columns c
GO
 
CREATE INDEX idxrunnersbig
    ON #runnersbig ( age , time ) INCLUDE ( runnerid )

 

자! 이제 획기적인 성능 향상을 경험할 차례이다. 아래의 두 질의를 비교해서 실행해 보자.

위의 SQL은 ROW_NUMBER() 함수와 CTE를 이용하여 그룹별 상위 2개의 레코드를 필터링하고 있고, 아래의 SQL은 CROSS APPLY 연산자를 이용하여 읽어야 할 집합을 축소하였다.

 

1. ROW_NUMBER() 함수와 CTE를 이용하여 그룹별 상위 2개의 레코드를 필터링한 SQL

WITH cteRunners 
AS ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time ) AS RowN 
       FROM #RunnersBig ) 
SELECT * 
  FROM cteRunners 
 WHERE RowN <= 2 
 ORDER BY Age , Rown
GO

 

2. CROSS APPLY를 이용한 SQL

WITH cteN
AS ( SELECT number 
       FROM master .. spt_values 
      WHERE type = 'p' 
        AND number between 0 and 100 ) 
SELECT * 
  FROM cteN 
 CROSS APPLY ( SELECT TOP(2) * 
                 FROM #RunnersBig 
                WHERE #RunnersBig . Age = cteN . number 
                ORDER BY Time ) AS runners 
 ORDER by cteN.number , runners.Time

 

주인장의 머신에서 테스트한 결과  ROW_NUMBER() 함수와 CTE를 이용하여 그룹별 상위 2개의 레코드를 필터링한 SQL은 평균 463ms, CROSS APPLY를 이용한 SQL은 1ms 안팎을 기록하였다.

어떻게 이렇게 놀라운 성능 향상이 있을 수 있었는지는 실행 계획을 비교해 확인해 보기 바란다.

물론, 이 SQL을 실전에서 적절하게 사용하기 위해서는 CTE내의 master..spt_value에 해당하는 부모 자식 관계를 정확하게 데이터 모델링해야 하며, 적절한 인덱스 설계가 필요하다.

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

SQL2005 저장프로시져 모니터하기  (0) 2011.07.20
COUNT Vs EXISTS  (0) 2011.07.18
SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법  (0) 2011.07.18
SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
인덱스 정보를 조회하는 뷰 New  (0) 2011.07.18
반응형
  • SQL Server 2008 설치 에러, Visual studio tools for applications 2.0, vsta.exe, SSIS_ScriptTask, SSIS_ScriptComponent

SQL Server 2008을 설치할 때 Visual studio tools for applications 2.0 관련 에러가 발생하는 경우가 있다.

이 때 Detail.txt 로그 파일을 열어서 아래의 내용을 확인할 수 있다면 이 포스트의 내용을 주목해 보시길…

참고로 Detail.txt는 %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\설치년월일_설치일시 아래에 위치한다.

예) C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20100326_144522

2010-03-26 14:48:20 SSIS: Running: 
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe /hostid SSIS_ScriptTask /setup
2010-03-26 14:48:28 SSIS: 
Application failed with error -1: 
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe /hostid SSIS_ScriptTask /setup
2010-03-26 14:48:28 SSIS: 
Running: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe /hostid SSIS_ScriptComponent /setup
2010-03-26 14:48:29 SSIS: 
Application failed with error -1: 
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe /hostid SSIS_ScriptComponent /setup

 

이 에러는 이미 SQL Server 2008이 설치되어 있는 시스템에서 SQL Server 2008을 다시 설치할 경우 발생하곤 하는데, Visual Studio Tools for Applications 2.0 이 깔끔하게 삭제되지 않은 것이 원인이다.

 

SQL Server 2008을 다시 설치할 경우에는 Microsoft 고객지원 사이트(영문)에도 확인할 수 있듯이 제어판에서 아래의 프로그램을 반드시 삭제해 주어야 한다.

  • SQL Server 2008
  • Visual Studio 2008 Shell*integrated mode
  • Visual Studio Tools for Applications 2.0

그래도 같은 에러가 발생한다면 명령 프롬프트 창에서 아래의 명령어를 실행한 후 SQL Server 2008을 설치해 보자.

이 명령어는 로그에 있는 SSIS_ScriptTask와 SSIS_ScriptComponent 에 관련된 레지스트리 항목과 파일을 삭제한다.

REG DELETE HKEY_CURRENT_USER\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKEY_CURRENT_USER\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
REG DELETE HKEY_LOCAL_MACHINE\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKEY_LOCAL_MACHINE\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
 
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptTask"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\Microsoft\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\Microsoft\VSTAHost\SSIS_ScriptComponent"
rd /s /q "%ProgramFiles%\Microsoft SQL Server"
rd /s /q "%ProgramFiles%\Microsoft Visual Studio 9.0"
반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

COUNT Vs EXISTS  (0) 2011.07.18
Cross Apply를 이용한 그룹별 Top N 질의  (0) 2011.07.18
SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
인덱스 정보를 조회하는 뷰 New  (0) 2011.07.18
MS SQL Server 2005 설치 후 기본적으로 해야 할 일  (0) 2011.07.18
반응형

Microsoft SQL Server Management Studio 에서 [등록된 서버] 를 표시할 때 아래와 같은 에러가 발생하는 경우가 있다.

  • System.Security.Cryptography.CryptographicException: Key not valid for use in specified state.

 

이 때 아래의 파일명을 바꾸어 주면 해결할 수 있다.

예를 들어, RegSrvr.xml_ 등으로..

  • C:\Users\[USERNAME]\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

또는

  • C:\Users\[USERNAME]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

 

Microsoft SQL Server Management Studio 의 [등록된 서버] 정보는 xml 포맷으로 RegSrvr.xml 이라는 이름으로 저장된다.

이 에러는 (SSMS 2005 등에서) RegSrvr.xml가 이미 작성되어 있을 경우,

[등록된 서버] 보기를 호출할 때 이 파일을 읽어들이면서 암호화 키를 해석하는 과정에서 발생하는 것 같다.

 

참조 사이트: http://connect.microsoft.com/SQLServer/feedback/details/352529/ssms-registered-servers-view

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

Cross Apply를 이용한 그룹별 Top N 질의  (0) 2011.07.18
SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법  (0) 2011.07.18
인덱스 정보를 조회하는 뷰 New  (0) 2011.07.18
MS SQL Server 2005 설치 후 기본적으로 해야 할 일  (0) 2011.07.18
MySQL의 INET_ATON()함수와 ATON_INET()함수 구현하기  (0) 2011.07.18
반응형

이전 포스트에서 idxinfo라 명명한 인덱스 정보를 조회하는 뷰는 소개한 바 있다.

 

이번 포스트에서는 SQL Server 2005 이상에서 sys.objects 및 sys.indexes Object Catalog View를 이용하여 뷰를 재구성해 보았다.

IF EXISTS (SELECT [name]
             FROM sys.sysobjects
            WHERE [type] = 'V'
              AND [name] = 'idxinfo')
BEGIN
    DROP VIEW [dbo].[idxinfo]
END
GO
 
CREATE VIEW [dbo].[idxinfo]
AS
SELECT o.[schema_id]              AS [schema_id]
     , schema_name(o.[schema_id]) AS [schema_name]
     , o.[object_id]              AS [object_id]
     , o.[name]                   AS [table_name]
     , i.[name]                   AS [index_name]
     , i.[type_desc]              AS [index_type]
     , CASE i.[is_unique] WHEN 1 THEN 'Unique'
                          WHEN 0 THEN 'Non-unique'
                          ELSE '' END AS [uniqueness]
     , CASE WHEN i.[is_primary_key] = 1 THEN 'PK'
            WHEN i.[is_unique_constraint] = 1 THEN 'UK'
            ELSE '' END AS [constraint]
     , CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) IS NULL
            THEN ''
            ELSE index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16)
       END AS [index_columns]
     , i.[fill_factor] AS [fillfator]
     , CASE i.[is_disabled] WHEN 0 THEN 'Active' 
                            WHEN 1 THEN 'Inactive' END AS [is_disabled]
  FROM sys.objects o WITH(NOLOCK)
  LEFT OUTER JOIN
       sys.indexes i WITH(NOLOCK)
    ON o.[object_id] = i.[object_id]
 WHERE o.[type] = 'U'
   AND i.[index_id] < 255
GO

뷰의 조회방법은 동일하다.

 

실행 예1 전체 인덱스 정보 조회 

SELECT *
  FROM dbo.idxinfo
GO

 

실행 예2 Heap 구조의(Clustered Index가 없는) 테이블 조회 

SELECT *
  FROM dbo.idxinfo
 WHERE ClusterType = 'HEAP'
GO

 

실행 예3  특정 테이블의 인덱스 조회

SELECT * 
  FROM idxinfo
 WHERE [object_id] = object_id('schema_name.table_name')
GO
 
--or
SELECT *
  FROM idxinfo
 WHERE [schema_name] = 'schema_name' 
   AND [table_name] = 'table_name'
GO

 

아래의 뷰 는 sys.dm_db_index_physical_stats Dynamic Management Function을 사용하여 인덱스의 물리적 통계정보를 함께 조회한다.

IF EXISTS (SELECT [name]
             FROM sys.sysobjects
            WHERE [type] = 'V'
              AND [name] = 'idxinfo2')
BEGIN
    DROP VIEW [dbo].[idxinfo2]
END
GO
 
CREATE VIEW [dbo].[idxinfo2]
AS
SELECT o.[schema_id]              AS [schema_id]
     , schema_name(o.[schema_id]) AS [schema_name]
     , o.[object_id]              AS [object_id]
     , o.[name]                   AS [table_name]
     , i.[name]                   AS [index_name]
     , i.[type_desc]              AS [index_type]
     , CASE i.[is_unique] WHEN 1 THEN 'Unique'
                          WHEN 0 THEN 'Non-unique'
                          ELSE '' END AS [uniqueness]
     , CASE WHEN i.[is_primary_key] = 1 THEN 'PK'
            WHEN i.[is_unique_constraint] = 1 THEN 'UK'
            ELSE '' END AS [constraint]
     , CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) IS NULL
            THEN ''
            ELSE index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16)
       END AS [index_columns]
     , i.[fill_factor] AS [fillfator]
     , CASE i.[is_disabled] WHEN 0 THEN 'Active'
                            WHEN 1 THEN 'Inactive' END AS [is_disabled]
     , [st].[index_depth]  AS [index_depth]
     , [st].[index_level]  AS [index_level]
     , [st].[page_count]   AS [page_count]
     , ROUND([st].[avg_fragmentation_in_percent], 2) AS [avg_fragmentation_in_percent]
  FROM sys.objects o WITH(NOLOCK)
  LEFT OUTER JOIN
       sys.indexes i WITH(NOLOCK)
    ON o.[object_id] = i.[object_id]
  LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') st
    ON [i].[object_id] = [st].[object_id]
   AND [i].[index_id] = [st].[index_id]
 WHERE o.[type] = 'U'
   AND i.[index_id] < 255
GO

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법  (0) 2011.07.18
SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
MS SQL Server 2005 설치 후 기본적으로 해야 할 일  (0) 2011.07.18
MySQL의 INET_ATON()함수와 ATON_INET()함수 구현하기  (0) 2011.07.18
Oracle의 LPAD함수의 기능 구현하기  (0) 2011.07.18
반응형

본 포스트는 Sqdequl Hussain이 SQLSERVER.PERFPRMANCE.COM에 기재한 기사를 의역하여 재정리한 내용이다.

 

MS SQL Server 2005 설치 후 기본적으로 해야 할 일

 

SQL Server를 설치하는 일은 DBA로서의 가장 기본적인 작업일 것이다.

DBA의 경력이나 조직의 특성에 따라 다르겠지만, 어떤 DBA는 자주 설치 연습을 하기도 할 것이고,

또 어떤 DBA는 설치 경험이 없을 수도 있을 것이고, 조직 내에서 설치만 전문적으로 담당하는 역할을 수행할 수도 있을 것이다.

 

모든 DBMS가 마찬가지겠지만 설치 작업을 성공적으로 수행하기 위해서는 SQL Server의 기초 지식 및 OS에 대한 이해가 필요하다.

SQL Server의 설치가 완료되면, 일반적으로 데이터베이스를 생성하기 전에 SQL Server의 기본 구성을 변경할 필요가 있다.

 

본 포스트에서는 SQL Server를 설치한 후 일반적으로 해야 할 몇 가지 작업을 소개하고 한다.

이 포스트 내용을 SQL Server를 설치 후 해야할 체크 리스트로 활용해도 무방할 것이다.

 

 

STEP 1. 서비스 팩, 핫픽스, 업데이트 설치하기

이 단계는 SQL Server를 설치한 후 가장 먼저해야 할 일이다.

먼저 가장 최신의 서비스 팩(이하 SP)을 설치한다.

이 글을 작성한 시점의 가장 최신의 SP는 SQL Server 2005는 SP3, SQL Server 2008는 SP1이다.

SP는 마이크로 소프트 웹 사이트에서 다운로드 가능하다.

그리고 SP가 릴리즈 된 후 픽스나 최신 업데이트가 있는지 확인하고 있으면 반드시 패치하기 바란다.

 

주인장의 말, 말, 말

최신 소프트웨어를 유지하는 것은 DBA로서의 당연한 의무이다.

그리고 SP, 핫픽스, 업데이트 적용하기 전에는 반드시 그 내용을 숙지해야 한다.

 

이 단계의 모든 작업이 완료되었으면 시스템 데이터베이스(master, msdb...)를 백업을 하고, 서버를 재기동한다.

 

 

STEP 2. SQL 서비스 구성하기

설치 계획 단계에서 이미 어떤 SQL 서비스를 설치할지 계획하였다 하더라도 지금 당장 필요하지 않는 서비스가 분명히 존재할 것이다.

예를들어 현재는 필요하지 않지만 비즈니스적 요구를 예상하여 Reporting 서비스를 설치한 경우처럼 말이다.

만일 지금 당장 필요치 않은 서비스가 있다면 서비스를 정지하도록 한다.

 

아래의 예는 Browser 서비스를 정지한 예이다. 이 서비스는 기본적으로 설치되는 서비스이다. 만일 하나의 머신에 명명된 인스턴스(named instances)나 복수 개의 인스턴스를 설치한 것이 아니라면 이를 정지하도록 한다. 이 작업은 SQL Server Configuration Manager에서 설정 가능하다.

 


 

그리고, 또 하나 정지가 가능한 서비스로 VSS Writer가 있을 것이다. 이는 WVSCI(Windows Volume Shadow Copy Infrastructure)를 이용해 데이터베이스를 백업하는 애플리케이션을 사용하지 않는 한 필요하지 않는 서비스이므로 정지하는 것이 좋다. 이 서비스는 SQL Server Configuration Service에서 할 수 없고 아래의 그림처럼 제어판의 서비스 애플릿에서 설정이 가능하다.

 


 

이러한 서비스를 정지하더라도 정기적으로 실행되고 있는 다른 Agent에는 영향을 주지 않는다. 예를 들어, 백업 스케줄(Scheduled backup), 유지보수 계획(Mainternance Plan), 복제(Replication), 작업(Job)은 Agent 서비스에 의존하기 때문이다.

 

만일 서버에 Integration Service 패키지가 실행되고 있으면, 서버가 기동할 때 SSIS 서비스가 항상 자동으로 실행하도록 설정한다. 이 또한 SQL Server Configuration Manager에서 설정이 가능하다.

 

또한 어떤 원인으로 인해 예기치 않은 서비스 실패가 발생했을 경우의 서비스 복구 프로세스도 지정할 수 있다.

이는 제어판의 서비스 애플릿에서 지정할 수 있다. 해당 서비스 - 아래의 예에서는 SQL Server Service - 의 등록정보 > 복구 탭에서 설정한다. 첫 번째 실패했을 경우, 두 번째 실패했을 경우 등 각각에 대해서의 액션을 지정하면 된다.

 


 

특히, Agent 서비스의 경우 SSMS(Microsoft SQL Server Management Studio)의 SQL Server Agent 의 등록정보 창에 서도 위와 비슷한 내용에 대한 설정이 가능하다. 아래의 그림에서 보이는 것처럼 예상치 못한 서비스 정지시 SQL Server를 자동으로 재기동할지, 예상치 못한 서비스 정지시 SQL Server 에이전트를 자동으로 재기동할지, 에러 로그를 남길지 등에 대한 설정이 가능하다.

 


 

 

STEP 3. 기본 디렉토리 설정

SQL Server가 설치되는 동안 몇 가지 디렉토리가 생성된다. MS SQL Server가 설치된 디렉토리는 당연히 존재하는 것이겠고, 이 밖에 데이터 파일 및 로그 파일, 복제(replication), 전체 텍스트 인덱스(Full text index), 백업을 위해 별도의 디렉토리가 생성된다.

 

디렉토리 경로는 SQL Server 설치 완료 후에도 변경이 가능하다.

아래의 그림에서는 Server 등록정보 창에서 데이터 파일과 로그 파일의 저장경로를 각각 지정하는 모습을 보여주고 있다.

 


 

 

데이터 파일 및 로그 파일 뿐만 아니라 아래의 파일들에 대해서도 기본 경로를 지정하는 것이 가능하다. 

  • 백업 파일(Backup files)
  • 복제 파일(Replication files)
  • 전체 텍스트 인덱스 파일(Full text index files)
  • 작업 파일(Job outpu files) 

      ※ 기본적으로 위 파일들을 데이터 파일과 같은 위치에 저장된다.

 

하지만 애석하게도 위 파일들은 GUI툴에서 경로를 지정하는 것은 불가능하다. 위의 파일들에 대한 기본 경로를 지정하기 위해서는 레지스트리 편집기를 이용해서 레지스트리 정보를 직접 수정해야 한다. SQL Server의 버전에 의존적이긴 하지만 일반적으로 "WorkingDirectory" 로 키를 검색하면 각각의 경로 정보를 확인 할 수 있을 것이다. 아마 대부분의 경우는 아래의 경로에서 키값을 확인할 수 있을 것이다.

 

파일 종류 레지스트리 경로명

백업 파일(Backup files)

HKEY_LOCAL_MACHINE>Software>Microsoft>Microsoft SQL Server>MSSQL.1>MSSQLServer>BackupDirectory

복제 파일(Replication files)

HKEY_LOCAL_MACHINE>Software>Microsoft>Microsoft SQL Server>MSSQL.1>Replication>WorkingDirectory

전체 텍스트 인덱스 파일(Full text index files)

HKEY_LOCAL_MACHINE>Software>Microsoft>Microsoft SQL Server>MSSQL.1>MSSQLServer>FullTextDefaultPath

작업 파일(Job outpu files)

HKEY_LOCAL_MACHINE>Software>Microsoft>Microsoft SQL Server>MSSQL.1>SQLServerAgent>WorkingDirectory

 

또한, 아래의 예처럼 xpregwrite 저장 프로시저를 이용해서도 경로 정보 수정이 가능하다. 

-- Default Backup Directory
EXEC xp_regwrite
 N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
 N'BackupDirectory',
 REG_SZ,
 N'F:\SQL2005\BACKUP'
GO

 

-- Default Replication Directory
EXEC xp_regwrite
 N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\Replication',
 N'WorkingDirectory',
 REG_SZ,
 N'F:\SQL2005\REPLDATA'
GO

 

-- Default Full Text Directory
EXEC xp_regwrite
 N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
 N'FullTextDefaultPath',
 REG_SZ,
 N'F:\SQL2005\FTData'
GO

 

-- Default Backup Directory
EXEC xp_regwrite
 N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent',
 N'WorkingDirectory',
 REG_SZ,
 N'F:\SQL2005\JOBS'
GO

 

물론 지정된 경로에 레지스트리 키 값이 반드시 존재해야 한다. 이는 다르게 해석하면 사용자 임의로 폴더를 생성하여 지정할 수 있다는 의미이기도 하다.

키 값 지정이 완료되면 시스템이 변경 사항을 감지하도록 각각의 서비스를 재기동시키도록 한다.

 

 

STEP 4. 기본 데이터베이스 속성(Properity) 변경

시스템 데이터베이스인 model 데이터베이스는 새로운 데이터베이스를 생성시 사용되는 템플릿과 같은 데이터베이스라는 것은 잘 알고 있을 것이다. 만일 사용자 데이터베이스 전체적으로 공통적인 속성을 적용하고 싶다면 model 데이터베이스를 수정하면 된다. 

 

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

SSMS에서의 System.Security.Cryptography.CryptographicException 에러  (0) 2011.07.18
인덱스 정보를 조회하는 뷰 New  (0) 2011.07.18
MySQL의 INET_ATON()함수와 ATON_INET()함수 구현하기  (0) 2011.07.18
Oracle의 LPAD함수의 기능 구현하기  (0) 2011.07.18
SQL check - idera 사의 무료 모니터링 툴  (0) 2011.07.18
반응형
본 포스트에는 C나 MySQL에서 사용되고 있는 INET_ATON()함수와 ATON_INET()함수를 MS SQL Server에서 구현해 보도록 하겠다.

이에 앞서 먼저 INET_ATON()함수와 ATON_INET()함수에 대해 간략하게 설명하도록 하겠다. 
이 함수들은 원래 특정 IP Address에 매핑되는 지역정보를 출력하기 위해 유래되었다. 
예를 들어 '192.15.10.125는 미국이다' 혹은 '192.15.10.125는 미국의 Broomfield다' 라는 지역정보를 조회하기 위해서...

이를 구현하기 위해서는 먼저 아래와 같이 특정 IP 대역과 지역정보가 대응되는 Database가 필요하다.
대역의 시작 IP

대역의 마지막 IP

나라

192.168.0.1

192.168.10.23

KR

192.168.10.24

192.168.101.255

US

192.168.102.1

192.168.102.10

CN



하지만 아시다시피 IPV4는 3옥텟으로 구분되어 있기 때문에 이를 그대로 Database화 시키면 아래와 같이 엉뚱한 정보를 조회하는 
결과를 낳게 된다.

-- 테스트 테이블 작성

CREATE TABLE ipcountry (

    startip VARCHAR(15)

   ,endip   VARCHAR(15)

   ,country VARCHAR(2)

);

GO


-- 테스트 데이터 입력

INSERT INTO ipcountry VALUES ('192.168.0.1', '192.168.10.23', 'KR' );

INSERT INTO ipcountry VALUES ('192.168.10.24', '192.168.101.255', 'US' );

INSERT INTO ipcountry VALUES ('192.168.102.1', '192.168.102.10', 'CN' );

GO


SELECT *

  FROM ipcountry


startip         endip           country

--------------- --------------- -------

192.168.0.1     192.168.10.23   KR

192.168.10.24   192.168.101.255 US

192.168.102.1   192.168.102.10  CN


-- 데이터 조회

SELECT *

  FROM ipcountry

 WHERE '192.168.102.5' BETWEEN startip AND endip

GO


--> 출력 결과

startip         endip           country

--------------- --------------- -------



이를 해결하기 위해 어떤 똑똑한(?) 사람이 3옥텟으로 구분되어 있는 IP Adress를 다음과 같은 계산식으로 주소를 변환하여 
Database화를 시켜 범위 검색이 가능하게 하였다. 

192.15.10.125 를 변환할 경우
Segment(s)

Weight(w)

s * Power(256, w)

192

3

3221225472

15

2

983040

10

1

2560

125

0

125


IP Number

3222211197



이 공식을 이용하여 다시 테이블을 작성하면 아래와 같다.

-- 테스트 테이블 작성

CREATE TABLE ipcountry2 (

    startip VARCHAR(15)

   ,endip   VARCHAR(15)

   ,startipnum BIGINT

   ,endipnum   BIGINT

   ,country VARCHAR(2)

);

GO


-- 테스트 데이터 입력

INSERT INTO ipcountry2 VALUES ('192.168.0.1', '192.168.10.23', 3232235521, 3232238103, 'KR' );

INSERT INTO ipcountry2 VALUES ('192.168.10.24', '192.168.101.255', 3232238104, 3232261631, 'US' );

INSERT INTO ipcountry2 VALUES ('192.168.102.1', '192.168.102.10' , 3232261633, 3232261642, 'CN');

GO


SELECT *

  FROM ipcountry2


startip         endip           startipnum           endipnum             country

--------------- --------------- -------------------- -------------------- -------

192.168.0.1     192.168.10.23   3232235521           3232238103           KR

192.168.10.24   192.168.101.255 3232238104           3232261631           US

192.168.102.1   192.168.102.10  3232261633           3232261642           CN



-- 데이터 조회

SELECT *

  FROM ipcountry2

 WHERE 3232261637 BETWEEN startipnum AND endipnum -- 3232261637 = '192.168.102.5'


--> 출력 결과

startip         endip           startipnum           endipnum             country

--------------- --------------- -------------------- -------------------- -------

192.168.102.1   192.168.102.10  3232261633           3232261642           CN

하지만, 이렇게 사용하기 위해서는 매번 특정 IP 주소에 대응하는 숫자형 주소로 바꾸어 계산해야 하는 불편함이 있다.
그래서 사용되는 것이 NET_ATON()함수와 ATON_INET()함수이며, 이를 MS SQL Server에서 구현하기 위해 
다음과 같은 함수를 만들어 보았다.

1. 숫자형 IP주소를 IP Address로 변환하는 함수 작성하기

CREATE FUNCTION dbo.Func_ConvertNum2Ip(@ip BIGINT)

RETURNS VARCHAR(15)

AS

BEGIN

    DECLARE @delimiter VARCHAR(1)

          , @SUBNET_MASK INT

          , @idx INT

          , @ipSeg BIGINT

          , @segments VARCHAR(15)


    SET @delimiter = '.'

    SET @SUBNET_MASK = 256

    SET @idx = 1

    SET @ipSeg = 0

    SET @segments = ''


    WHILE (@idx <= 4)

    BEGIN

        SET @ipSeg = CAST(@ip / POWER(@SUBNET_MASK,4-@idx) AS BIGINT)

        SET @ip=@ip - CAST(@ipSeg * POWER(@SUBNET_MASK,4-@idx) AS BIGINT)

        IF (@idx = 1)

            SET @segments = CAST(@ipSeg AS VARCHAR)

        ELSE

            SET @segments = @segments + @delimiter + CAST(@ipSeg AS VARCHAR)

            SET @idx = @idx +1

    END


    RETURN  @segments

END

GO


2. IP Address를 숫자형 IP주소로 변환하는 함수 작성하기

CREATE FUNCTION dbo.Func_ConvertIp2Num(@ip NVARCHAR(15))

RETURNS BIGINT

AS

BEGIN

    DECLARE @delimiter NVARCHAR(1)

          , @SUBNET_MASK INT

          , @idx INT

          , @ipNum FLOAT

          , @textXML XML

    DECLARE @segments TABLE(id INT ,col INT)


    SET @delimiter = '.'

    SET @SUBNET_MASK = 256

    SET @idx = 4

    SET @ipNum = 0

    SELECT    @textXML = CAST('<col>' + REPLACE(@ip, @delimiter, '</col><col>') + '</col>' AS XML);


    INSERT INTO @segments(id, col)

        SELECT ROW_NUMBER () OVER (ORDER BY col) AS id

             , T.col.value('.', 'int') AS col

          FROM @textXML.nodes('/col') T(col)

         ORDER BY id DESC


    SELECT @ipNum = @ipNum + (CAST((col % @SUBNET_MASK) AS FLOAT) * POWER(@SUBNET_MASK,@idx-id))

      FROM @segments


    RETURN CAST(@ipNum AS BIGINT)

END

GO


자 이제, 먼저 테스트에 사용한 질의문을 아래와 같이 수정한 다음 조회을 하면 성공적으로 결과를 얻을 수 있음을 확인할 수 있다.

단, 당연한 이야기이겠지만 대량의 데이터를 조회하기 위해서는 최적의 인덱스 설계가 필요하다.

-- 데이터 조회

SELECT *

  FROM ipcountry2

 WHERE dbo.Func_ConvertIp2Num('192.168.102.5') BETWEEN startipnum AND endipnum


--> 출력 결과

startip         endip           startipnum           endipnum             country

--------------- --------------- -------------------- -------------------- -------

192.168.102.1   192.168.102.10  3232261633           3232261642           CN




반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

인덱스 정보를 조회하는 뷰 New  (0) 2011.07.18
MS SQL Server 2005 설치 후 기본적으로 해야 할 일  (0) 2011.07.18
Oracle의 LPAD함수의 기능 구현하기  (0) 2011.07.18
SQL check - idera 사의 무료 모니터링 툴  (0) 2011.07.18
인덱스 조각화(단편화) 정보를 조회하는 뷰 2  (0) 2011.07.18
반응형
오라클의 LPAD함수는 MS SQL Server에는 구현되어 있지 않는 오라클의 함수의 대표적인 예이다.
본 포스트에서는 LPAD함수 기능을 구현하는 함수의 작성 스크립트를 소개하고자 한다.

1. 함수 작성하기

CREATE FUNCTION dbo.Func_lpad ( @str AS VARCHAR(8000), @nofchars AS INT, @fillchar AS VARCHAR(8000) = '')

    RETURNS VARCHAR(2000)

AS

BEGIN

    RETURN

        CASE WHEN LEN(@str) >= @nofchars THEN SUBSTRING(@str, 1, @nofchars)

             ELSE SUBSTRING(REPLICATE(@fillchar, @nofchars), 1, @nofchars - LEN(@str)) + @str

        END

END

GO


2. 파라미터 설명
파리미터 명설명

@str

입력 문자열

@nofchars

반환할 문자열의 전체길이

@fillchar

덧불일 문자

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

MS SQL Server 2005 설치 후 기본적으로 해야 할 일  (0) 2011.07.18
MySQL의 INET_ATON()함수와 ATON_INET()함수 구현하기  (0) 2011.07.18
SQL check - idera 사의 무료 모니터링 툴  (0) 2011.07.18
인덱스 조각화(단편화) 정보를 조회하는 뷰 2  (0) 2011.07.18
Blocking 정보를 조회하는 스토어드 프로시저 - sp_who3  (0) 2011.07.18
반응형

SQL check 은 MS SQL Server를 모니터링하는 무료 모니터링 툴이다.

 

프로그램은 idera사의 홈페이지에서 회원등록 과정을 거친 후 무료로 내려받을 수 있다.

 

아래의 스크린샷에서 확인할 수 있듯이 각종 성능 카운터를 실시간으로 모니터링할 수 있으며 sql process, sql error log, job, 서버 구성 정보를 확인할 수 있다. 또한 스크린 세이버로 이 툴을 사용할 수도 있다.

 


 

기타 제품에 대한 더 자세한 내용은 아래의 링크를 참조하기 바란다.

 

    https://www.idera.com/Products/Free-Tools/SQL-check/

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

MySQL의 INET_ATON()함수와 ATON_INET()함수 구현하기  (0) 2011.07.18
Oracle의 LPAD함수의 기능 구현하기  (0) 2011.07.18
인덱스 조각화(단편화) 정보를 조회하는 뷰 2  (0) 2011.07.18
Blocking 정보를 조회하는 스토어드 프로시저 - sp_who3  (0) 2011.07.18
스키마간에 개체 이동하기  (0) 2011.07.18
반응형

앞서 dbo.idxinfo_v 라는 이름으로 인덱스 정보를 조회하는 뷰를 소개한 바 있다.
본 포스트에서는 dbo.idxinfo_v2 라는 이름의 인덱스 조각화 정보도 함께 조회해 주는 뷰를 소개하겠다.

dbo.idxinfo_v2 는 dbo.idxinfo_v 에 sys.dm_db_index_physical_stats 동적 관리 함수에서 리턴된 일부 정보를 포함한 것이라고 생각하면 되겠다. 그리고 인덱스 조각화에 대한 추가적인 정보가 필요하면 이 뷰를 수정하여 사용하기 바란다.

 

Index 조각화정보를 조회하는 뷰를 작성하는 스크립트

DROP VIEW [dbo].[idxinfo_v2]
GO

CREATE VIEW [dbo].[idxinfo_v2]
AS
SELECT
       CAST(SERVERPROPERTY('MachineName') AS VARCHAR(20)) AS [Hostname]
     , DB_NAME(DB_ID()) AS [DBName]
     , [u].[name]       AS [Owner]
     , [o].[name]       AS [TableName]
     , [i].[indid]      AS [IndexID]
     , CASE [i].[name]  WHEN [o].[name] THEN '** NONE **'
                        ELSE [i].[name] END AS [IndexName]
     , CASE [i].[indid] WHEN 1 THEN 'CLUSTERED'
                        WHEN 0 THEN 'HEAP'
                        ELSE 'NONCLUSTERED' END AS [ClusterType]
     , CASE WHEN ([i].[status] & 2048) > 0 THEN 'PRIMARY KEY'
            WHEN ([i].[status] & (2|4096)) > 0 THEN 'UNIQUE'
            ELSE '' END AS [UniqueType]
     , CASE WHEN ([i].[status] & (2048)) > 0 OR (([i].[status] & (4096)) > 0 ) THEN 'CONSTRAINT'
            WHEN [i].[indid] = 0 THEN ' '
            ELSE 'INDEX' END AS [IndexType]
     , CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 1) IS NULL THEN ''
            ELSE INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 1) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 2) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],2) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 3) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],3) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 4) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],4) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 5) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],5) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 6) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],6) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 7) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 7) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid], 8) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],8) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 9) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],9) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 10) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],10) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 11) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],11) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 12) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],12) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 13) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],13) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 14) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],14) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 15) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],15) END +
       CASE WHEN INDEX_COL([u].[name]+'.'+ QUOTENAME([o].[name]), [i].[indid], 16) IS NULL THEN ''
            ELSE ', '+INDEX_COL([u].[name]+'.'+QUOTENAME([o].[name]), [i].[indid],16) END AS [AllColName]
     , [i].[OrigFillFactor]AS [FillFactor]
     , [st].[index_depth]  AS [IndexDepth]
     , [st].[index_level]  AS [IndexLevel]
     , [st].[page_count]   AS [TotalPageCount]
     , ROUND([st].[avg_fragmentation_in_percent], 2) AS [PctFrag]
 FROM [dbo].[sysobjects] o (NOLOCK)
 LEFT OUTER JOIN [dbo].[sysindexes] i (NOLOCK)
   ON [o].[id] = [i].[id]
 LEFT OUTER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'LIMITED') st
   ON [i].[id] = [st].[object_id]
  AND [i].[indid] = [st].[index_id]    
 JOIN [dbo].[sysusers] u (NOLOCK)
   ON [o].[uid] = [u].[uid]
 WHERE [o].[type] = 'U'
 AND [i].[indid] < 255
 AND [o].[name] NOT IN ('dtproperties')
 AND [i].[name] NOT LIKE '_WA_Sys_%'
GO

 

GRANT SELECT ON [dbo].[idxinfo_v2] TO public
GO

 

다수의 데이터베이스 서버와 데이터베이스를 관리하는 경우 호스트명과 데이터베이스 명을 함께 표시할 수 있도록 하였으며, dbo.idxinfo_v에서 추가적으로 조회할 수 있는 인덱스 정보는 다음과 같다.

 

결과 집합

컬럼 명 설명

FillAactor

해당 인덱스의 FillFactor(채우기 비율)

Index_depth

인덱스 수준의 수, 즉 인덱스의 깊이

Page_count

전체 인덱스 혹은 데이터 페이지 수

PctFrag

단편화 정도(%)

 

작성된 뷰의 사용방법은 일반적인 뷰를 조회하는 것과 같으며, 이하  sys.dm_db_index_physical_stats  에 대한  더 자세한 내용은

링크된 (BOL)을 참조하기 바란다.

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

Oracle의 LPAD함수의 기능 구현하기  (0) 2011.07.18
SQL check - idera 사의 무료 모니터링 툴  (0) 2011.07.18
Blocking 정보를 조회하는 스토어드 프로시저 - sp_who3  (0) 2011.07.18
스키마간에 개체 이동하기  (0) 2011.07.18
오브젝트의 소유자 변경하기  (0) 2011.07.18
반응형

Blocking 정보를 조회하는 스토어드 프로시저 - sp_who3



sp_who3-wing3a07.sql

 

MS SQL Server에서 Blocking 혹은 Lock정보를 조회하기 위해서는 일반적으로 sp_who, sp_who2, sp_lock, sp_lockinfo 시스템 저장 프로시저를 이용해서 Blocking 정보를, DBCC INPUTBUFFER를 실행해서 Blocking 관련 SQL을 조회해야 하는 번거로움이 있다.

이번 포스트에서 소개하고자 하는 것은 바로 이런 번거로운 작업을 간단하게 해소하고자 필자가 작성한 스토어드 프로시저로써, sp_who3라고 명명하였다.

 

사용방법은 간단하다. 첨부된 스크립트를 master 데이터베이스에서 실행하여 프로시저를 등록한 후 실행만 하면 된다.

실행 결과는 다음과 같이 2단 테이블로 표시되며 조회되는 내용은 아래의 표를 참고하기 바란다.  

 


[그림 1 sp_who3의 실행 예]

 

구문

EXEC sp_who3

   

결과 집합

컬럼 명 설명

Blocked Session ID

현재 해당 요청을 소유한 세션 ID - Blocking된 세션 ID

Blocking Session ID

현재 해당 요청을 Blocking하고 있는 세션 ID

Database Name

해당 요청의 데이터베이스 명

Resource Type

해당 요청의 리소스 유형

 

가능한 값은 아래와 같다.

DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT

Request Owner Type

해당 요청을 소유하는 엔티티 유형

 

가능한 값은 아래와 같다.

TRANSACTION = 트랜잭션이 요청을 소유한다.

CURSOR = 커서가 요청을 소유한다.

SESSION = 사용자 세션이 요청을 소유한다.

SHARED_TRANSACTION_WORKSPACE = 트랜잭션 작업 영역 중 공유 부분이 요청을 소유한다.

EXCLUSIVE_TRANSACTION_WORKSPACE = 트랜잭션 작업 영역 중 배타 부분이 요청을 소유한다.

Resource Description

리소스 설명, 다른 리소스 열에서 사용할 수 없는 정보만 포함한다.

Request Mode

해당 요청의 모드

요청의 경우 허용 모드이고 대기 중인 요청의 경우에는 요청 중인 모드이다.

Request Status

해당 요청의 현재 상태

 

가능한 값은 아래와 같다.

GRANT, CONVERT, WAIT

Session ID

1단 테이블의 Blocked 또는 Blocking 세션의 세션 ID

Session Type

Session ID의 Blocking 유형

 

가능한 값은 아래와 같다.

Blocked Session, Blocking Session

SQL

Buffer에서 읽어온 해당 세션에서 실행된 SQL

 

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

SQL check - idera 사의 무료 모니터링 툴  (0) 2011.07.18
인덱스 조각화(단편화) 정보를 조회하는 뷰 2  (0) 2011.07.18
스키마간에 개체 이동하기  (0) 2011.07.18
오브젝트의 소유자 변경하기  (0) 2011.07.18
데이터베이스 내의 모든 외래키 비활성화 및 활성화하기  (0) 2011.07.18
반응형

스키마 간에 개체 이동하기

 

ALTER SCHEMA... TRANSFER... 구문을 사용하여 스키마 간에 보안 개체를 이동할 수 있다.

 

구문

ALTER SCHEMA schema_name TRANSFER securable_name

  

인수

인수 설명

schema_name

현재 데이터베이스에서 보안 개체가 이동될 스키마의 이름이다.

SYS 또는 INFORMATION_SCHEMA는 지정할 수 없다.

securable_name

스키마에 포함된 보안 개체 중에서 스키마로 이동될 보안 개체의 한 부분(object_name) 또는 두 부분(current_schema_name.object_name)으로 구성된 이름이다.

 

실행 예

다음 예는 Person 스키마에서 HumanResources 스키마로 Address 테이블을 이동시킨다.

USE AdventureWorks;
GO


ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

 

※ ALTER SCHEMA는 같은 데이터베이스에서 스키마 간에 보안 개체를 이동할 때만 사용할 수 있다.

 

사용권한

  • 한 스키마에서 다른 스키마로 보안 개체를 이동하려면 현재 사용자에게 보안 개체(스키마가 아님)에 대한 CONTROL 권한과 대상 스키마에 대한 ALTER 권한이 있어야 한다.
  • 보안 개체에 EXECUTE AS OWNER 사양이 있고 소유자가 SCHEMA OWNER로 설정된 경우에는 사용자가 대상 스키마의 소유자에 대한 IMPERSONATION 권한도 가져야 한다.
  • 이동되는 보안 개체와 연결된 사용 권한은 이동 시 모두 삭제된다.
    • 반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    인덱스 조각화(단편화) 정보를 조회하는 뷰 2  (0) 2011.07.18
    Blocking 정보를 조회하는 스토어드 프로시저 - sp_who3  (0) 2011.07.18
    오브젝트의 소유자 변경하기  (0) 2011.07.18
    데이터베이스 내의 모든 외래키 비활성화 및 활성화하기  (0) 2011.07.18
    인덱스의 크기 및 인덱스 조각화 정보 조회하기  (0) 2011.07.18
    반응형

    오브젝트의 소유자 변경하기

     

    SQL Server 2000 이전 버전에서는 오브젝트의 소유자를 변경하기 위해서 sp_changeobjectowner 시스템 저장 프로시저를 사용한다. 하지만, SQL Server 2005에 이르러서는 sp_changeobjectowner 시스템 저장 프로시저를 대신하여 ALTER AUTHORIZATION... 구문을 통해 오브젝트의 소유자를 변경할 수 있게 되었다.

     

     

    1. SQL Server 2000 이전 버전에서

     

    구문

    sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

      

    인수

    인수

    데이터

    형식

    		설명

    [ @objname = ] 'object'

    VARCHAR(776)

    현재 데이터베이스의 기존 오브젝트 명이다.

    오브젝트에서 테이블, 뷰, 사용자 정의 함수 또는 저장 프로시저가 될 수 있다.

    기본값은 없다.

    스키마와 스키마 소유자가 동일한 이름을 갖고 있는 경우 object는 existing_owner.object 의 형태로 기존 개체의 소유자를 명시해야 한다.

    [ @newowner=] 'owner '

    SYSNAME

    개체의 새 소유자가 될 보안 계정의 이름이다.

    기본값은 없다.

    owner는 유효한 데이터베이스 사용자, 서버 역할, Microsoft Windows 로그인 또는 현재 데이터베이스에 액세스할 수 있는 Windows 그룹이어야 한다.

    새 소유자가 해당하는 데이터베이스 수준의 보안 주체가 없는 Windows 사용자 또는 Windows 그룹이면 데이터베이스 사용자가 생성된다.

     

    실행 예

    다음 예는 author 테이블의 소유자 를 testuser1에서 testuser2로 변경한다.

    USE PUBS

    GO

     

    EXEC sp_changeobjectowner 'testuser1.author','testuser2'

    GO

     

     

    2. SQL Server 2005 이후 버전에서

     

    구문

    ALTER AUTHORIZATION
       ON [ <entity_type> :: ] entity_name
       TO { SCHEMA OWNER | principal_name }

    <entity_type> ::=
        {
            Object | Type | XML Schema Collection
            | Fulltext Catalog | Fulltext Stoplist | Schema
        | Assembly | Role | Message Type | Contract | Service
        | Remote Service Binding | Route | Symmetric Key | Endpoint
        | Certificate | Database
        }

      

    인수

    인수 설명

     <entity_type> ::

    소유자가 변경될 엔터티의 클래스

    Object가 기본값이다.

     entity_name

    		엔터티의 이름

     principal_name

    엔터티를 소유하게 될 보안 주체의 이름

     

    주의사항

  • 데이터베이스 수준 엔터티의 소유권은 데이터베이스 수준의 모든 보안 주체에게 이전할 수 있다.
  • 서버 수준 엔터티의 소유권은 서버 수준 보안 주체에게만 이전할 수 있다.
  • SQL Server 2005부터 사용자는 다른 데이터베이스 사용자가 소유한 스키마에 포함된 OBJECT 또는 TYPE을 소유할 수 있다.
  • SCHEMA OWNER 옵션은 스키마 수준 엔터티의 소유권을 이전하는 경우에만 유효하다.
  • SCHEMA OWNER는 엔터티의 소유권을 엔터티가 속한 스키마의 소유자에게 이전한다.
  • 스키마 수준 엔터티는 OBJECT, TYPE 또는 XML SCHEMA COLLECTION 클래스뿐이다.

    •  

    이전 가능한 엔티티

  • "object" 형식의 스키마 수준 엔터티인 테이블, 뷰, 함수, 프로시저, 큐 및 동의어 엔터티

    •  

    이전 불가능한 엔티티

  • 연결된 서버, 통계, 제약 조건, 규칙, 기본값, 트리거, Service Broker 큐, 자격 증명,
  • 파티션 함수, 파티션 구성표, 데이터베이스 마스터 키, 서비스 마스터 키 및 이벤트 알림 엔터티
  • 서버, 로그인, 사용자, 응용 프로그램 역할 및 열 보안 개체 클래스의 멤버 소유권

    •  

    실행 예 1: 테이블의 소유권 이전

    다음 예는 Person 스키마의 TestTable 테이블의 소유권을  testuser2 사용자에게 이전한다.

     

    이에 앞서 현재 Person.TestTable 테이블의 소유권이 누구에게 있는지 확인해 보자

    SELECT
           t2.[name] AS UserName
         , SCHEMA_NAME(t1.schema_id) AS SchemaName
         , t1.[name] AS ObjectName
      FROM sys.objects AS t1
      INNER JOIN sys.database_principals AS t2
        ON t1.principal_id = t2.principal_id
     WHERE [object_id] = object_id('Person.TestTable')
    GO

     

    Result >>

    UserName   SchemaName  ObjectName
    ------------ ---------------- ---------------
    testuser1    Person            TestTable

    조회를 통해 Person.TestTable 테이블의 소유권은 testuser1에 있음을 확인할 수 있다.

     

    이제 이 소유권을 testuser1 사용자에서 testuser2 사용자에게 이전하도록 하자.

    ALTER AUTHORIZATION ON OBJECT::Person.TestTable TO testuser2;
    GO

    또는

    ALTER AUTHORIZATION ON Person.TestTable TO testuser2;
    GO

     

    이제 제대로 소유권이 변경되었는지 확인해 보도록 하자.

    SELECT
           t2.[name] AS UserName
         , SCHEMA_NAME(t1.schema_id) AS SchemaName
         , t1.[name] AS ObjectName
      FROM sys.objects AS t1
      INNER JOIN sys.database_principals AS t2
        ON t1.principal_id = t2.principal_id
     WHERE t1.[object_id] = object_id('Person.TestTable')
    GO

     

    Result >>

    UserName   SchemaName  ObjectName
    ------------ ---------------- ---------------
    testuser2    Person            TestTable

    Person.TestTable 테이블의 소유권이 testuser1 사용자에서 testuser2 사용자로 이전되었음을 확인할 수 있다.

     

     

    실행 예 2: 스키마의 사용권을 다른 사용자에게 이전

    다음 예는 TestUser1Schema 스키마의 소유권을 testuser1 사용자에게 소유권을 이전한다.

     

    이에 앞서 현재 TestUser1Schema 스키마의 소유권이 누구에게 있는지 확인해 보도록 하자.

    SELECT t1.[name] AS SchemaName
         , t2.[name] AS UserName
      FROM sys.schemas AS t1
     INNER JOIN sys.database_principals AS t2
        ON t1.principal_id = t2.principal_id
     WHERE t1.[schema_id] = SCHEMA_ID('TestUser1Schema')
    GO
     

    Result >>

    SchemaName        UserName
    -------------------- ----------------
    TestUser1Schema   testuser2

    조회를 통해 TestUser1Schema 스키마의 소유권이 testuser2에 있음을 확인할 수 있다.

     

    이제 TestUser1Schema 스키마의 소유권을 testuser1 사용자에게 넘기도록 하겠다.

    ALTER AUTHORIZATION ON SCHEMA::TestUser1Schema TO testuser1;
    GO

     

    이제 제대로 소유권이 변경되었는지 확인해 보도록 하자.

    SELECT t1.[name] AS SchemaName
         , t2.[name] AS UserName
      FROM sys.schemas AS t1
     INNER JOIN sys.database_principals AS t2
        ON t1.principal_id = t2.principal_id
     WHERE t1.[schema_id] = SCHEMA_ID('TestUser1Schema')
    GO
     

    Result >>

    SchemaName        UserName
    -------------------- ----------------
    TestUser1Schema   testuser1

    TestUser1Schema 스키마의 소유권이 testuser2 사용자에서 testuser1 사용자로 이전되었음을 확인할 수 있다.

     

    기타 예는 ALTER AUTHORIZATION의 BOL을 참조하기 바란다.

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    Blocking 정보를 조회하는 스토어드 프로시저 - sp_who3  (0) 2011.07.18
    스키마간에 개체 이동하기  (0) 2011.07.18
    데이터베이스 내의 모든 외래키 비활성화 및 활성화하기  (0) 2011.07.18
    인덱스의 크기 및 인덱스 조각화 정보 조회하기  (0) 2011.07.18
    IDENTITY가 설정된 컬럼에 데이터 입력하기-SET IDENTITY_INSERT  (0) 2011.07.18
    반응형

    1. 먼저 아래의 저장 프로시저를 작성하여 컴파일한다.

    CREATE PROCEDURE dbo.usr_Disable_Foreign_Keys
        @disable BIT = 1
    AS
        DECLARE
            @sql VARCHAR(500),
            @schemaName VARCHAR(128),
            @tableName VARCHAR(128),
            @foreignKeyName VARCHAR(128)

        DECLARE foreignKeyCursor CURSOR
        FOR
        SELECT
            ref.constraint_name AS FK_Name,
            fk.table_schema AS FK_Schema,
            fk.table_name AS FK_Table
        FROM
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
        ON ref.constraint_name = fk.constraint_name
        ORDER BY
            fk.table_schema,
            fk.table_name,
            ref.constraint_name

        OPEN foreignKeyCursor

        FETCH NEXT FROM foreignKeyCursor

        INTO @foreignKeyName, @schemaName, @tableName
        WHILE ( @@FETCH_STATUS = 0 )
            BEGIN
                IF @disable = 1
                    SET @sql = 'ALTER TABLE ['
                        + @schemaName + '].['
                        + @tableName + '] NOCHECK CONSTRAINT ['
                        + @foreignKeyName + ']'
                ELSE
                    SET @sql = 'ALTER TABLE ['
                        + @schemaName + '].['
                        + @tableName + '] CHECK CONSTRAINT ['
                        + @foreignKeyName + ']'
            PRINT 'Executing Statement - ' + @sql
            EXECUTE(@sql)
            FETCH NEXT FROM foreignKeyCursor
            INTO @foreignKeyName, @schemaName, @tableName
        END
        CLOSE foreignKeyCursor
        DEALLOCATE foreignKeyCursor
    GO

      

    2. 모든 외래키 활성화 및 비활성화하기

    Exec dbo.usr_Disable_Foreign_Keys 0 -- 0: 활성화

    Exec dbo.usr_Disable_Foreign_Keys 1 -- 1: 비활성화

      

    3. 실행 예

    다음 예는 AdventureWoks 예제 데이터 베이스에서 외래키를 비활성화 시키는 예이다.

    USE AdventureWorks

    GO

     

    Exec dbo.usr_Disable_Foreign_Keys 1

    GO

     

    Result>>

    Executing Statement - ALTER TABLE [HumanResources].[Employee] NOCHECK CONSTRAINT [FK_Employee_Contact_ContactID]
    Executing Statement - ALTER TABLE [HumanResources].[Employee] NOCHECK CONSTRAINT [FK_Employee_Employee_ManagerID]
    Executing Statement - ALTER TABLE [HumanResources].[EmployeeAddress] NOCHECK CONSTRAINT [FK_EmployeeAddress_Address_AddressID]
    Executing Statement - ALTER TABLE [HumanResources].[EmployeeAddress] NOCHECK CONSTRAINT [FK_EmployeeAddress_Employee_EmployeeID]
    Executing Statement - ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] NOCHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
    Executing Statement - ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] NOCHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID]
    Executing Statement - ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] NOCHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
    Executing Statement - ALTER TABLE [HumanResources].[EmployeePayHistory] NOCHECK CONSTRAINT [FK_EmployeePayHistory_Employee_EmployeeID]

    ...

    ...

    ...

      

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    스키마간에 개체 이동하기  (0) 2011.07.18
    오브젝트의 소유자 변경하기  (0) 2011.07.18
    인덱스의 크기 및 인덱스 조각화 정보 조회하기  (0) 2011.07.18
    IDENTITY가 설정된 컬럼에 데이터 입력하기-SET IDENTITY_INSERT  (0) 2011.07.18
    SQL Server 서버 인스턴스 속성 정보 조회하기(버전 정보 등)  (0) 2011.07.18
    반응형

    MS SQL Server 2005 부터는 이전 버전보다 보다 다양하고 막강한 동적관리 뷰 및 함수를 제공하고 있다.

    그 중에서 관리적 측면에서 이슈가 되는 인덱스의 크기 및 인덱스의 조각화 정도를 조회할 수 있는 sys.dm_db_index_physical_stats 함수를 소개하고 한다.

    참고로 DBCC SHOWCONTIG를 통해서도 조회가 가능하지만 MS SQL Server 2008 이후의 차기 버전에서는 제거될 예정이다.

     

    1. 구문

    sys.dm_db_index_physical_stats (
        { database_id | NULL | 0 | DEFAULT }
        , { object_id | NULL | 0 | DEFAULT }
        , { index_id | NULL | 0 | -1 | DEFAULT }
        , { partition_number | NULL | 0 | DEFAULT }
        , { mode | NULL | DEFAULT }
    )

      

    2. 인수

    인수

    데이터

    형식

    		설명

    database_id | NULL | 0 | DEFAULT

    smallint

    데이터베이스 ID.

    입력 가능한 인수는 데이터베이스의 ID 번호, NULL, 0 또는 DEFAULT이다.

    기본값은 0이며 이 경우 NULL, 0 및 DEFAULT는 동등한 값이다.

    SQL Server 인스턴스의 모든 데이터베이스에 대한 정보를 반환하려면 NULL을 지정한다.

    database_id에 NULL을 지정하는 경우 object_id, index_id 및 partition_number에도 NULL을 지정해야 한다.

    :Track(?ctl00_rs1_mainContentContainer_cpe31375_c|ctl00_rs1_mainContentContainer_ctl11?,this);?>DB_ID 기본 제공 함수를 지정할 수 있으며, 이 경우 현재 데이터베이스의 호환성 수준은 90이어야 한다. 

    반드시 아래의 시스템 함수 사용하여 매개변수 값 지정 시 주의 사항을 읽어보자.

    object_id | NULL | 0 | DEFAULT

    int

    인덱스가 있는 테이블 또는 뷰의 개체 ID.

    입력 가능한 인수는 은 테이블 및 뷰의 ID 번호, NULL, 0 또는 DEFAULT이다.

    기본값은 0이며. 이 경우 NULL, 0 및 DEFAULT는 동등한 값이다.

    지정된 데이터베이스에 있는 모든 테이블 및 뷰에 대한 정보를 반환하려면 NULL을 지정한다.

    object_id에 NULL을 지정하는 경우 index_id 및 partition_number에도 NULL을 지정해야 한다.

    OBJECT_ID 기본 제공 함수를 지정할 수 있으며, 이 경우 현재 데이터베이스의 호환성 수준은 90이어야 한다.

    반드시 아래의 시스템 함수 사용하여 매개변수 값 지정 시 주의 사항을 읽어보자.

    index_id | 0 | NULL | -1 | DEFAULT

    int

    인덱스 ID.

    입력 가능한 인수는 인덱스의 ID 번호, object_id가 힙인 경우 0, NULL, -1 또는 DEFAULT이다.

    기본값은 -1이며, 이 컨텍스트에서 NULL, -1 및 DEFAULT는 동등한 값이다.

    기본 테이블 또는 뷰에 대한 모든 인덱스 정보를 반환하려면 NULL을 지정한다.

    index_id에 NULL을 지정하는 경우 partition_number에도 NULL을 지정해야 한다.

    partition_number | NULL | 0 | DEFAULT

    int

    개체의 파티션 번호.

    입력 가능한 인수는 인덱스 또는 힙의 partion_number, NULL, 0 또는 DEFAULT이다.

    기본값은 0이며, 이 경우 NULL, 0 및 DEFAULT는 동등한 값이다.

    소유하는 개체의 모든 파티션에 대한 정보를 반환하려면 NULL을 지정한다.

    partition_number는 1부터 시작되며, 분할되지 않은 인덱스 또는 힙의 partition_number는 1로 설정되어 있다.

    mode | NULL | DEFAULT

    sysname 

    모드 이름.

    mode는 통계를 얻는 데 사용되는 검색 수준을 지정하는 것으로 입력하능한 인수는 DEFAULT, NULL, LIMITED, SAMPLED 또는 DETAILED이다.

    기본값(NULL)은 LIMITED이다.

    모드에 대한 더 자세한 내용은 아래의 검색 모드(mode)에 대한 부연 설명 참조

     

    ※검색 모드(mode)에 대한 부연 설명

    검색 모드는 통계 데이터를 가져오기 위한 샘플링의 수준을 결정하는 인수이다. 

    위의 표에서 살펴보았듯이 mode는 LIMITED, SAMPLED 또는 DETAILED로 지정할 수 있다.

    sys.dm_db_index_physical_stats는 테이블이나 인덱스의 지정한 파티션을 구성하는 할당 단위에 대해 페이지 체인을 검색하게 되는데, 이때 실행되는 모드에 관계없이 내재된 공유(IS) 테이블 잠금을 사용한다.

    • LIMITED 모드: 가장 빠른 모드이며 가장 적은 수의 페이지를 검색한다. 인덱스의 경우 부모 수준 페이지만(즉, 리프 수준 이상의 페이지) 검색한다. 힙의 경우 연결된 PFS 및 IAM 페이지만 조사되고 힙의 데이터 페이지는 검색하지 않는다. SQL Server 2005에서 힙의 모든 페이지는 LIMITED 모드로 검색한다.
    • SAMPLED 모드: 인덱스나 힙의 모든 페이지에 대한 1% 샘플을 기준으로 통계를 반환한다. 인덱스나 힙의 페이지 수가 10,000개 미만이면 SAMPLED 대신 DETAILED 모드가 사용한다.
    • DETAILED 모드: 모든 페이지를 검색하여 전체 통계를 반환한다.

    따라서 수행속도는 LIMITED > SAMPLED> DETAILED 모드의 순이 된다.

     

    ※시스템 함수를 사용하여 매개변수 값 지정 시 주의 사항

    DB_ID 및 OBJECT_ID 함수를 사용하여 database_id 및 object_id 매개 변수 값을 지정할 수 있으나 이러한 함수에 유효하지 않은 값을 전달하면 의도하지 않은 결과가 발생할 수 있다. 존재하지 않거나 철자가 틀린 경우와 같이 데이터베이스 또는 개체 이름을 찾을 수 없는 경우에는 두 함수 모두 NULL을 반환된다. 따라서 sys.dm_db_index_physical_stats 함수에서는 NULL을 모든 데이터베이스나 모든 개체를 지정하는 와일드카드 값으로 해석하므로 주의해야 한다.

    또한 OBJECT_ID 함수는 sys.dm_db_index_physical_stats 함수가 호출되기 전에 처리되므로 database_id에 지정된 데이터베이스가 아니라 현재 데이터베이스의 컨텍스트에서 계산된다. 이 동작으로 인해 OBJECT_ID 함수에서 NULL 값이 반환될 수 있고, 또한 개체 이름이 현재 데이터베이스 컨텍스트와 지정된 데이터베이스에 둘 다 있는 경우에는 오류 메시지가 반환될 수도 있다.

    이러한 오작동을 방지하기 위해서는 OBJECT_ID(N'AdventureWorks.Person.Address') 처럼 database_name.owner.object_name 의 세 부분으로 된 구성 이름을 사용하거나 sys.dm_db_index_physical_stats 함수를 실행하기 전에 사전 검사를 하는 것이 좋다.

     

    3. 반환되는 테이블

    열 이름

    데이터

    형식

    		설명

    database_id

    smallint

    테이블 또는 뷰의 데이터베이스 ID

    object_id

    int

    		인덱스가 있는 테이블 또는 뷰의 개체 ID

    index_id

    int

    인덱스의 인덱스 ID

     

    0 = 힙

    partition_number

    int

    테이블, 뷰 또는 인덱스 등의 소유하는 개체 내의 파티션 번호

     

    1부터 시작 

    1 = 분할되지 않은 인덱스 또는 힙

    index_type_desc

    nvarchar(60)

    인덱스 유형에 대한 설명

     

    HEAP

    CLUSTERED INDEX

    NONCLUSTERED INDEX

    PRIMARY XML INDEX

    SPATIAL INDEX

    XML INDEX

    alloc_unit_type_desc

    		 nvarchar(60)

    할당 단위 유형에 대한 설명

     

    IN_ROW_DATA

    LOB_DATA

    ROW_OVERFLOW_DATA

     

    LOB_DATA: text, ntext, image, varchar(max), nvarchar(max), varbinary(max) 및 xml 형식의 열에 저장되는 데이터

    ROW_OVERFLOW_DATA: varchar(n), nvarchar(n), varbinary(n) 및 sql_variant 형식의 열에 저장되는 행 외부로 밀어넣은 데이터

    index_depth

    tinyint

    인덱스 수준의 수

     

    1 = 힙 또는 LOB_DATA나 ROW_OVERFLOW_DATA 할당 단위

    index_level

    		 tinyint

    인덱스의 현재 수준

     

    인덱스 리프 수준, 힙 및 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에 대해서는 0이다.

    리프가 아닌 인덱스 수준의 경우 0보다 크다. index_level은 인덱스의 루트 수준에서 가장 높다.

    리프가 아닌 인덱스 수준은 mode = DETAILED인 경우에만 처리된다.

    avg_fragmentation_in_percent

    		 float

    인덱스의 논리적 조각화 또는 IN_ROW_DATA 할당 단위에서 힙의 익스텐트 조각화의 백분율 

     

    LOB_DATA 및 ROW_OVERFLOW_DATA 할당 단위에 대해서는 0이다.

    mode = SAMPLED인 경우 힙에 대해 NULL이다.

     

    논리적 조각화 및 익스텐트 조각화에 대해서는 아래의 조각화 참조

    fragment_count

    bigint

    IN_ROW_DATA 할당 단위의 리프 수준에 있는 조각 수

     

    리프가 아닌 인덱스 수준과 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에 대해서는 NULL이다.

    mode = SAMPLED인 경우 힙에 대해 NULL이다.

    avg_fragment_size_in_pages

    float

    IN_ROW_DATA 할당 단위의 리프 수준에 있는 조각 하나의 평균 페이지 수

     

    리프가 아닌 인덱스 수준과 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에 대해 NULL이다.

    mode = SAMPLED인 경우 힙에 대해 NULL이다.

    page_count

    bigint

    전체 인덱스 또는 데이터 페이지 수

     

    인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 있는 총 인덱스 페이지 수, 힙의 경우 IN_ROW_DATA 할당 단위에서 총 데이터 페이지 수이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 할당 단위에서 총 페이지 수이다.

    avg_page_space_used_in_percent

    float

    모든 페이지에서 사용되는 사용 가능한 데이터 저장 공간의 평균 백분율

     

    인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 평균이, 힙의 경우 IN_ROW_DATA 할당 단위에서 모든 데이터 페이지의 평균이다. LOB_DATA 또는 ROW_OVERFLOW DATA 할당 단위의 경우 할당 단위에서 모든 페이지의 평균이다.

    mode = LIMITED인 경우 NULL입니다.

    record_count

    bigint

    총 레코드 수

     

    인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 총 레코드 수가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 총 레코드 수이다.

     

    힙의 경우 이 함수에서 반환된 레코드 수는 힙에 대해 SELECT COUNT(*)를 실행하여 반환된 행 수와 다르다. 이는 한 행에 여러 레코드가 존재하기 때문이다.

     

    LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 총 레코드 수이다.

    mode = LIMITED인 경우 NULL이다.

    ghost_record_count

    bigint

    할당 단위에서 삭제할 레코드 정리 태스크에 의해 제거될 삭제할 레코드 수

     

    IN_ROW_DATA 할당 단위에서 리프가 아닌 인덱스 수준에 대해 0이다.

    mode = LIMITED인 경우 NULL이다.

    version_ghost_record_count

    bigint

    할당 단위에서 처리 중인 스냅숏 격리 트랜잭션이 보유하고 있는 삭제할 레코드 수

     

    IN_ROW_DATA 할당 단위에서 리프가 아닌 인덱스 수준에 대해 0이다.

    mode = LIMITED인 경우 NULL이다.

    min_record_size_in_bytes

    		 int

    최소 레코드 크기(바이트)

     

    인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 최소 레코드 크기가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 최소 레코드 크기이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 최소 레코드 크기이다.

    mode = LIMITED인 경우 NULL이다.

    max_record_size_in_bytes

    		 int

    최대 레코드 크기(바이트)

     

    인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 최대 레코드 크기가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 최대 레코드 크기이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 최대 레코드 크기이다.

    mode = LIMITED인 경우 NULL이다.

    avg_record_size_in_bytes

    float

    평균 레코드 크기(바이트)

     

    인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 평균 레코드 크기가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 평균 레코드 크기이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 평균 레코드 크기이다.

    mode = LIMITED인 경우 NULL이다.

    forwarded_record_count

    bigint

    다른 데이터 위치로의 전달 포인터가 있는 힙의 레코드 수

     

    이 상태는 업데이트하는 동안 원본 위치에 새 행을 저장할 공간이 충분하지 않은 경우에 발생한다(Split).

     

    힙의 IN_ROW_DATA 할당 단위 이외의 모든 할당 단위에 대해 NULL이다.

    mode = LIMITED인 경우 힙에 대해 NULL이다.

    compressed_page_count

    bigint

    압축된 페이지 수

     

    힙의 경우 새로 할당된 페이지는 압축된 페이지가 아니다.

    힙은 데이터를 대량으로 가져오거나 힙을 다시 작성하는 경우의 두 가지 특별한 조건에서 압축된 페이지이다. 일반적으로 페이지 할당을 발생시키는 DML 작업은 압축된 페이지를 생성하지 않으며, 이 값이 원하는 임계값보다 커지면 힙을 다시 작성하는 것이 좋다.

     

    인덱스 조각화 평가

    avg_fragment_size_in_pages: 이 값이 클수록 범위 검색 성능이 좋아진다.

    avg_fragment_size_in_pages: 이 값이 작을수록 범위 검색 성능이 좋아진다.

    avg_fragmentation_in_percent: 이 값은 작을수록 범위 검색 성능이 좋아진다.

     

    ※조각화

    조각화는 테이블에 정의된 인덱스에 대한 데이터 수정 작업(INSERT, UPDATE, DELETE)을 처리할 때 발생된다. 이러한 수정 작업은 B-Tree 인덱스의 밸런스를 무너뜨려 각 페이지의 사용률을 저하시킨다. 따라서 검색 시 읽어야 하는 페이지 수가 늘어나 성능에 영향을 미칠 수 있다. 이 경우 인덱스를 다시 구성하거나 작성할 필요가 있다. 이는 본 포스트에 언급하기에는 많은 내용이므로 다른 포스트에서 소개하도록 하겠다. 인덱스 재편성에 대한 내용은 일단 BOL의 인덱스 다시 구성 및 다시 작성 을 참조하기 바란다.

     

    논리적 조각화

    인덱스의 리프 페이지에서 순서가 잘못된 페이지의 비율을 의미한다.

    인덱스의 리프 페이지는 Double-linked list 구조로 되어 있다. 예를 들어 101 페이지에는 "101 페이지의 다음 페이지는 102 페이지 입니다" 라는 포인터라는 포인터를, 102 페이지에는 "102 페이지의 이전 페이지에는 101페이지 입니다" 라는 포인터를 가지고 있어 서로를 가리킨다.


     

     

    여기서 순서가 잘못된 페이지의 비율이란 물리적 페이지의 순서가 포인터가 가리키는 페이지의 순서가 다른 경우를 의미한다. 예를 들어, 인덱스의 리프 페이지가 101, 102, ... 110 순서로 10개가 있다고 가정할 경우, 101 페이지의 다음은 물리적으로 102 이지만 Split에 의해 논리적 순서가 101, 104, 102 ..  등과 같이 달라지기도 한다. 

    익스텐트 조각화

    힙의 리프 페이지에서 순서가 잘못된 익스텐트의 비율이다. 순서가 잘못된 익스텐트란 위에서 설명한 순서가 잘못된 인덱스의 경우를 보면 충분히 설명될 것이다.

     

    실행 예

    DECLARE @db_id SMALLINT;
    DECLARE @object_id INT;

    SET @db_id = DB_ID(N'AdventureWorks');
    SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');

    IF @db_id IS NULL
    BEGIN;
        PRINT N'Invalid database';
    END;
    ELSE IF @object_id IS NULL
    BEGIN;
        PRINT N'Invalid object';
    END;
    ELSE
    BEGIN;
        SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
    END;
    GO

      

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    오브젝트의 소유자 변경하기  (0) 2011.07.18
    데이터베이스 내의 모든 외래키 비활성화 및 활성화하기  (0) 2011.07.18
    IDENTITY가 설정된 컬럼에 데이터 입력하기-SET IDENTITY_INSERT  (0) 2011.07.18
    SQL Server 서버 인스턴스 속성 정보 조회하기(버전 정보 등)  (0) 2011.07.18
    SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기  (0) 2011.07.18
    반응형

    MS SQL Server에서 일련번호처럼 일정한 증가 값을 가진 데이터를 입력하기 하기 위해서는 어떻게 해야 할까?

    몇 가지 방법이 있겠지만, 일반적으로 테이블의 열에 IDENTITY 속성을 지정하는 방법을 많이 사용한다.

    속성이라는 말에서도 알 수 있듯이 Oracle의 Sequence 같은 객체는 아니다. 

    오라클에서는 미리 작성해 둔 Sequence 객체에서 NEXTVAL을 이용해서 다음 값을 취득하여 사용하지만, IDENTITY 속성은 CREATE TABLE 및 ALTER TABLE Transact-SQL 문을 이용하여 컬럼에 지정하게 된다 (이하 IDENTITY에 대한 더 자세한 내용은 여기(BOL)를 참고하길 바란다).

     

    이 때, IDENTITY 속성이 지정된 열에 테이터를 입력하고자 할 때 다음과 같은 오류 메시지를 접하게 될 것이다.

     

    * IDENTITY_INSERT가 OFF로 설정되면 'XXX' 테이블의 ID 열에 명시적 값을 삽입할 수 없습니다.

     

    그렇다면 IDENTITY 속성이 지정된 열에 임시적으로 데이터를 입력하려면 어떻게 해야 할까?

    ☞ SET IDENTITY_INSERT 문을 사용해야 한다.

     

    예를 통해 그 사용법을 간단하게 살펴보도록 하자.

     

    먼저 dbo.identity_insert_test 이라는 이름으로 예제 테이블을 작성하도록 하겠다. 이 테이블의 seq 열에 1부터 1씩 증가하도록 IDENTITY 속성을 지정하고, 데이터는 10건을 입력하겠다.

    IF EXISTS (SELECT *
                 FROM sys.objects
                WHERE object_id = OBJECT_ID(N'[dbo].[identity_insert_test]')
                  AND type in (N'U'))
    BEGIN
          DROP TABLE dbo.identity_insert_test
    END

     

    CREATE TABLE dbo.identity_insert_test
    (seq INT IDENTITY(1,1),
     cname VARCHAR(10),
     CONSTRAINT identity_insert_test_pk PRIMARY KEY(seq)
    )
    GO

     

    DECLARE @loop_cnt INT;
    SET @loop_cnt = 1;

    WHILE @loop_cnt <= 10
    BEGIN
        INSERT INTO dbo.identity_insert_test(cname) VALUES ( 'test');  
        SET @loop_cnt = @loop_cnt + 1;
    END

    GO

     

    SELECT * FROM dbo.identity_insert_test
    GO

     

    Result >>

    seq         cname
    ----------- ----------
    1           test
    2           test
    3           test
    4           test
    5           test
    6           test
    7           test
    8           test
    9           test
    10          test

     

    이제 IDENTITY속성이 지정된 열에 데이터를 입력해 보도록 해보자.

    INSERT INTO dbo.identity_insert_test (seq, cname) VALUES (12, 'new')
    GO

     

    그 결과 다음과 같은 오류 메시지와 함께 에러가 발생할 것이다.

    메시지 544, 레벨 16, 상태 1, 행 1

    IDENTITY_INSERT가 OFF로 설정되면 'identity_insert_test' 테이블의 ID 열에 명시적 값을 삽입할 수 없습니다.

     

    여기서 유심히 살펴보면, 친절(?)하게도 오류 메시지가 해당 열에 데이터를 입력할 수 있는 방법을 보여주고 있다.

    IDENTITY_INSERT가 OFF로 설정되면 이라고... 즉 IDENTITY_INSERT를 ON으로 설정하면 가능하다는 얘기?

     

    속는 셈 치고 IDENTITY_INSERT를 ON으로 설정한 후, 다시 한 번 SQL을 실행해 보도록 하자.

    SET IDENTITY_INSERT identity_insert_test

    ON

    INSERT INTO dbo.identity_insert_test (seq, cname) VALUES (12, 'new')
    GO

     

    다음과 같이 오류없이 잘 입력되었음을 확인할 수 있을 것이다.

    SELECT * FROM identity_insert_test
    GO

     

    Result >>

    seq         cname
    ----------- ----------
    1           test
    2           test
    3           test
    4           test
    5           test
    6           test
    7           test
    8           test
    9           test
    10          test
    12          new

     

    결과 확인이 끝났으면 IDENTITY_INSERT을 다시 OFF으로 설정하고 IDENTITY 속성이 잘 동작하는지 확인해 보자

    SET IDENTITY_INSERT identity_insert_test OFF
    INSERT INTO dbo.identity_insert_test (cname) VALUES ('new')
    GO

     

    실행 결과는 다음과 같이 나타날 것이다. 다음 행에 seq컬럼의 최대값 + 1 이 입력되었다.

    SELECT * FROM dbo.identity_insert_test
    GO

     

    Result >>

    seq         cname
    ----------- ----------
    1           test
    2           test
    3           test
    4           test
    5           test
    6           test
    7           test
    8           test
    9           test
    10          test
    12          new
    13          new

     

    SET IDENTITY_INSERT 구문에 대한 더 자세한 내용은 여기(BOL)을 참고하길 바란다.

     

     

    ☞ 이 밖의 IDENTITY 관련 팁

    1. 현재 IDENTITY값이 열에 저장되어 있는 최대 값보다 작을 경우, 열의 최대값을 사용하여 다시 설정하고자 할 때

      DBCC CHECKIDENT ( table_name )

      또는

      DBCC CHECKIDENT ( table_name, RESEED )

       
    2. 현재 IDENTITY값을 특정 값으로 초기화 할 경우

      DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

       
      테이블이 생성된 후 삽입된 행이 없거나, TRUNCATE TABLE문을 사용하여 모든 행을 제거한 경우에는 DBCC CHECKIDENT를 실행한 후에 처음 입력된 행이 new_reseed_value를 값으로 사용하게 된다. 따라서 그 다음에 입력된 행은 new_reseed_value + 증분 값을 사용한다.
      열에 PRIMARY KEY, UNIQUE 제약 조건이 있는 경우 충돌이 발생하지 않도록 주의해야 할 것이다.
       
      ※ DBCC CHECKIDENT 의 BOL
    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    데이터베이스 내의 모든 외래키 비활성화 및 활성화하기  (0) 2011.07.18
    인덱스의 크기 및 인덱스 조각화 정보 조회하기  (0) 2011.07.18
    SQL Server 서버 인스턴스 속성 정보 조회하기(버전 정보 등)  (0) 2011.07.18
    SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기  (0) 2011.07.18
    DDL 스트립트를 작성하는 프로그램 (오픈 소스)  (0) 2011.07.18
    반응형

    해당 서버에 설치된 SQL Server의 에디션이 무엇인지, 서비스 팩은 어디까지 적용이 되어 있는지, 혹은 32bit인지 64비트인지 등과 같은 서버 인스턴스에 대한 속성 정보가 필요할 경우가 종종 있다. 특히 수십 대 혹은 수백 대의 MS SQL Server를 관리하는 곳에서 관리 대장을 작성할 경우 더욱 그러할텐데, 본 포스트에서는 이 때 요긴하게 쓰일 SQL을 소개하고자 한다.

     

    물론, @@version의 글로벌 변수를 이용해 간단하게 조회가 가능하나, 서비스 팩 설치 정보 등과 같이 조회되지 않는 항목도 있다. 이 때 SERVERPROPERTY 시스템 저장 프로시저를 이용하면 간단하게 해결할 수 있다.

     

    SELECT SERVERPROPERTY ('productversion') AS ProductVersion
         , SERVERPROPERTY ('productlevel')   AS ProductLevel
         , SERVERPROPERTY ('edition')        AS Edition

    GO

     

    Result>>

    ProductVersion       ProductLevel  Edition
    -------------------- ---------------- ---------------------------
    9.00.4035.00         SP3               Standard Edition (64-bit)

     

     

    SERVERPROPERTY (BOL 인용)
  • 구문
    •     SERVERPROPERTY ( propertyname )

     

  • 인수
    • 속성 반환된 값

    BuildClrVersion

    SQL Server 인스턴스를 작성하는 동안 사용된 Microsoft .NET Framework CLR(공용 언어 런타임)의 버전

     

    기본 데이터 형식: nvarchar(128)

    Collation

    서버의 기본 데이터 정렬 이름

     

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: nvarchar(128)

    CollationID

    SQL Server 데이터 정렬의 ID

     

    기본 데이터 형식: int

    ComparisonStyle

    데이터 정렬의 Windows 비교 스타일

     

    기본 데이터 형식: int

    ComputerNamePhysicalNetBIOS

    SQL Server 인스턴스가 현재 실행되고 있는 로컬 컴퓨터의 NetBIOS 이름

     

    장애 조치(Failover) 클러스터의 SQL Server 클러스터형 인스턴스에서 SQL Server 인스턴스가 장애 조치 클러스터의 다른 노드로 장애 조치되면 이 값이 변경된다.

    독립 실행형 SQL Server 인스턴스에서 이 값은 일정하게 유지되며 MachineName 속성과 같은 값을 반환된다.

     

    참고:
    SQL Server 인스턴스가 장애 조치 클러스터에 있는 상태에서 장애 조치 클러스터형 인스턴스 이름을 가져오려는 경우 MachineName 속성을 사용할 것

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: nvarchar(128)

    Edition

    SQL Server 인스턴스의 설치된 제품 버전

     

    이 속성 값을 사용하여 설치된 제품에서 지원하는 기능 및 최대 CPU 수와 같은 제한을 확인한다. 64비트 버전의 데이터베이스 엔진에는 (64비트)가 추가된다..

     

    'Desktop Engine'(SQL Server에는 사용할 수 없음)

    'Developer Edition'

    'Enterprise Edition'

    'Enterprise Evaluation Edition'

    'Personal Edition'(SQL Server에는 사용할 수 없음)

    'Standard Edition'

    'Express Edition'

    'Express Edition with Advanced Services'

    'Workgroup Edition'

    'Windows Embedded SQL'

     

    기본 데이터 형식: nvarchar(128)

    EditionID

    SQL Server 인스턴스의 설치된 제품 버전을 나타내는 ID 번호

     

    이 속성 값을 사용하여 설치된 제품에서 지원하는 기능 및 최대 CPU 수와 같은 제한을 확인한다.

    -1253826760 = Desktop

    -1592396055 = Express

    -1534726760 = Standard

    1333529388 = Workgroup

    1804890536 = Enterprise

    -323382091 = Personal

    -2117995310 = Developer

    610778273 = Enterprise Evaluation

    1044790755 = Windows Embedded SQL

    4161255391 = Express with Advanced Services

     

    기본 데이터 형식: int

    EngineEdition

    서버에 설치된 SQL Server 인스턴스의 데이터베이스 엔진 버전

     

    1 = 개인용 또는 데스크톱 엔진(SQL Server에는 사용할 수 없음)

    2 = 스탠더드(스탠더드 및 워크그룹 버전인 경우 이 값이 반환됨)

    3 = 엔터프라이즈(엔터프라이즈, 엔터프라이즈 평가 및 디벨로퍼 버전인 경우 이 값이 반환됨)

    4 = 익스프레스(익스프레스, Express with Advanced Services 및 Windows Embedded SQL 버전인 경우 이 값이 반환됨)

     

    기본 데이터 형식: int

    InstanceName

    사용자가 연결된 인스턴스의 이름

     

    인스턴스 이름이 기본 인스턴스이거나 입력이 유효하지 않거나 오류일 경우에는 NULL을 반환한다.

     

    기본 데이터 형식: nvarchar(128)

    IsClustered

    서버 인스턴스가 장애 조치 클러스터에 구성되어 있어 있다.

     

    1 = 클러스터형

    0 = 비클러스터형

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: int

    IsFullTextInstalled

    전체 텍스트 구성 요소가 SQL Server의 현재 인스턴스에 설치되어 있는지 확인

     

    1 = 전체 텍스트가 설치되었다.

    0 = 전체 텍스트가 설치되지 않다.

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: int

    IsIntegratedSecurityOnly

    통합 보안 모드

     

    1 = 통합 보안 모드이다.

    0 = 통합 보안 모드가 아니다.

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: int

    IsSingleUser

    단일 사용자 모드

     

    1 = 단일 사용자 모드이다.

    0 = 단일 사용자 모드가 아니다.

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: int

    LCID

    데이터 정렬의 Windows LCID(로케일 ID)

    기본 데이터 형식: int

    LicenseType

    SQL Server 인스턴스의 모드

     

    PER_SEAT = 사용자 단위 모드

    PER_PROCESSOR = 프로세서 단위 모드

    DISABLED = 라이선스가 해제되었다.

     

    기본 데이터 형식: nvarchar(128)

    MachineName

    서버 인스턴스가 실행 중인 Windows 컴퓨터 이름

     

    Microsoft Cluster Service의 가상 서버에서 실행되는 SQL Server 클러스터형 인스턴스인 경우에는 가상 서버의 이름을 반환된다.

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: nvarchar(128)

    NumLicenses

    사용자 단위 모드일 경우 SQL Server 인스턴스에 대해 등록된 클라이언트 라이선스의 수

     

    프로세서 단위 모드일 경우 SQL Server 인스턴스에 대해 허가된 프로세서의 수이다.

    서버가 이 중 어느 것에도 해당하지 않으면 NULL을 반환한다.

     

    기본 데이터 형식: int

    ProcessID

    SQL Server 서비스의 프로세스 ID

     

    ProcessID는 인스턴스에 속하는 Sqlservr.exe를 식별하는 데 유용하다.

    NULL = 입력이 유효하지 않거나 오류입니다.

     

    기본 데이터 형식: int

    ProductVersion

    SQL Server 인스턴스의 버전으로 'major.minor.build' 형식이다.

    기본 데이터 형식: nvarchar(128)

    ProductLevel

    SQL Server 인스턴스의 버전 수준

     

    'RTM' = 초기 릴리스 버전

    'SPn' = 서비스 팩 버전

    'CTP', = Community Technology Preview 버전

     

    기본 데이터 형식: nvarchar(128)

    ResourceLastUpdateDateTime

    리소스 데이터베이스를 마지막으로 업데이트한 날짜와 시간을 반환한다.

     

    기본 데이터 형식: datetime

    ResourceVersion

    리소스 데이터베이스 버전을 반환한다.

     

    기본 데이터 형식: nvarchar(128)

    ServerName

    Windows 서버 및 지정된 SQL Server 인스턴스에 대한 인스턴스 정보

     

    NULL = 입력이 유효하지 않거나 오류

     

    기본 데이터 형식: nvarchar(128)

    SqlCharSet

    데이터 정렬 ID의 SQL 문자 집합 ID

     

    기본 데이터 형식: tinyint

    SqlCharSetName

    데이터 정렬의 SQL 문자 집합 이름

     

    기본 데이터 형식: nvarchar(128)

    SqlSortOrder

    데이터 정렬의 SQL 정렬 순서 ID

     

    기본 데이터 형식: tinyint

    SqlSortOrderName

    데이터 정렬의 SQL 정렬 순서 이름

     

    기본 데이터 형식: nvarchar(128)

    FilestreamShareName

    FILESTREAM이 사용하는 공유의 이름

    FilestreamConfiguredLevel

    구성된 FILESTREAM 액세스 수준

    FilestreamEffectiveLevel

    효과적인 FILESTREAM 액세스 수준

     

    수준이 변경되었고 인스턴스 다시 시작이나 컴퓨터 다시 시작이 보류 중인 경우 이 값은 FilestreamConfiguredLevel과 다를 수 있다.

     

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    인덱스의 크기 및 인덱스 조각화 정보 조회하기  (0) 2011.07.18
    IDENTITY가 설정된 컬럼에 데이터 입력하기-SET IDENTITY_INSERT  (0) 2011.07.18
    SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기  (0) 2011.07.18
    DDL 스트립트를 작성하는 프로그램 (오픈 소스)  (0) 2011.07.18
    인덱스 정보를 조회하는 뷰  (0) 2011.07.18
    반응형

    이미 많은 분들이 알고 계시는 내용이긴 하지만, SQL Server 2005(2008)를 처음으로 접해 보신 분들이나 모르시는 분들을 위해 포스트해 드립니다.

     

    SQL Server 2005(2008)을 처음 설치하고 SQL Server Management Studio 나 Business Intelligence Development Studio를 띄우실 때 그 속도에 당황하셨을 겁니다.

    SQLLeader.com에서 한대성 님이 기술하신 해결방안을 공유해 드립니다.

     

    SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기

     

    눈에 띄게 빨라짐을 확인할 수 있을 겁니다.

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    IDENTITY가 설정된 컬럼에 데이터 입력하기-SET IDENTITY_INSERT  (0) 2011.07.18
    SQL Server 서버 인스턴스 속성 정보 조회하기(버전 정보 등)  (0) 2011.07.18
    DDL 스트립트를 작성하는 프로그램 (오픈 소스)  (0) 2011.07.18
    인덱스 정보를 조회하는 뷰  (0) 2011.07.18
    [MS SQL Server New Feature 1] 최소 로깅 Insert  (0) 2011.07.18
    반응형

    본 프로그램은 전체 데이터베이스, 특정 데이터베이스 혹은 특정 테이블에 대해 DDL 스크립트를 생성해주는 프로그램이다. 물론 Enterprise Manager(2000 버전 이전), SQL Server Managment Studio(2005 버전 이후)의 스크립트 생성을 이용해서 생성 가능하지만 정기적으로 스크립트를 백업하고 배포하는 상황이라면 이 프로그램이 상당히 유용하게 사용될 수 있을 것 같다. 원래는 총 3개의 프로그램으로 이루어져 있으나, 블로그 주인장이 개인적인 용도로 사용하기 위해 만든 Windows 배치 프로그램(5. 배치프로그램 설명 참조)을 작성하여 추가해 두었다. 그리고 필요하다면 SQL Agent Job에 등록하여 정기적으로 백업해도 유용할 것이다. 또한 이 프로그램은 오픈 소스(1. 프로그램 정보 참조)로써 자신의 구미에 맞게 수정해서 사용할 수 있다.

     

     

    1. 프로그램 정보

      

    2. 프로그램 구성

    • ScriptAllDBs.exe
    • ScriptDB.exe
    • ScriptTable.exe
    • ScriptAllDBs.bat
    • ScriptDB.bat
    • ScriptTable.bat

      

    3. 생성되는 스크립트 종류

    • Database
    • Database Triggers
    • Functions
    • Security
    • Stored Procedures
    • Synonyms
    • Tables
    • Types
    • Views  

     

    4. 프로그램 설명

    • ScriptAllDBs.exe
      Comment 서버 인스턴스 내에 있는 전 데이터 베이스에 대한 DDL 스크립트를 작성한다.
      Syntax ScriptAllDBs <Directory_name> <Server_name> [Login <Password>]
      Example DBServer1라는 서버의 전 데이터베이스에 대해 DDL스크립트를 D:Temp에 작성하고자 하는 경우

      D:>ScriptAllDBs D:Temp DBServer1 user password
      or
      D:>ScriptAllDBs D:Temp 192.168.xxx.xxx user password
    • ScriptDB.exe
      Comment 서버 인스턴스 내에 있는 특정 데이터 베이스에 대한 DDL 스크립트를 작성한다.
      Syntax ScriptDB <Directory_name> <Server_name> <Database_name> [Login <Password>]
      Example DBServer1라는 서버의 AdventureWorks 데이터베이스에 대해 DDL스크립트를 D:Temp에 작성하고자 하는 경우

      D:>ScriptDB D:Temp DBServer1 AdventureWorks user password
      or
      D:>ScriptDB D:Temp 192.168.xxx.xxx AdventureWorks user password
    • ScriptTable.exe
      Comment 서버 인스턴스 내에 잇는 특정 데이터 베이스의 특정 테이블에 대한 DDL 스크립트를 작성한다.
      Syntax ScriptTable <Directory_name> <Server_name> <Database_name> <Owner_name(Schema).Table_name> [Login <Password>]
      Example DBServer1라는 서버의 AdventureWorks 데이터베이스의 Person.Address 테이블에 대해 DDL스크립트를 D:Temp에 작성하고자 하는 경우

      D:>ScriptAllDBs D:Temp DBServer1 AdventureWorks Person.Address user password
      or
      D:>ScriptAllDBs D:Temp 192.168.xxx.xxx AdventureWorks Person.Address user password
       
    5. 배치 프로그램 설명
    • ScriptAllDBs.bat
      ScriptAllDBs.exe을 실행한다. 작성된 스크립트는 기본적으로 D:MSSQLServerScriptBackupAllDBScript 아래에 작성된다. 디렉토리를 변경하고 싶은 경우 배치 파일의 SET DirName=D:MSSQLServerScriptBackupAllDBScript%DateStr% 부분을 수정한다.
       
    • ScriptDB.bat
      ScriptDB.exe을 실행한다. 작성된 스크립트는 기본적으로 D:MSSQLServerScriptBackupDBScript 아래에 작성된다. 디렉토리를 변경하고 싶은 경우 배치 파일의 SET DirName=D:MSSQLServerScriptBackupDBScript%DateStr% 부분을 수정한다.
       
    • ScriptTable.bat
      ScriptTable.exe을 실행한다. 작성된 스크립트는 기본적으로 D:MSSQLServerScriptBackupTableScript 아래에 작성된다. 디렉토리를 변경하고 싶은 경우 배치 파일의 SET DirName=D:MSSQLServerScriptBackupTableScript%DateStr% 부분을 수정한다.
       

    6. 실행 예

    ①ScriptAllDBs.bat을 클릭하여 배치 프로그램을 실행한다.

     

    ②입력 변수에 값을 입력한다.

     

    ③Enter키를 눌러 스크립트 작성 프로그램을 실행한다.   

    프로그램 실행 화면1

     

    프로그램 실행 화면 2 

     

    작성이 완료되면 아래와 같이 배치 프로그램에서 설정된 디렉토리에 오브젝트별로 각각의 스크립트가 작성된다.

     

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    SQL Server 서버 인스턴스 속성 정보 조회하기(버전 정보 등)  (0) 2011.07.18
    SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기  (0) 2011.07.18
    인덱스 정보를 조회하는 뷰  (0) 2011.07.18
    [MS SQL Server New Feature 1] 최소 로깅 Insert  (0) 2011.07.18
    [MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정  (0) 2011.07.18
    반응형

    MS SQL Server에서 인덱스 정보를 조회하기 위해서는 Enterprise Manager(2000 버전 이전), SQL Server Managment Studio(2005 버전 이후)와 같은 GUI 관리 프로그램이나 sp_help 혹은 sp_helpindex와 같은 시스템 저장 프로시저를 사용해서 조회해야 한다. 하지만 이러한 툴들은 출력 형식이 정해져 있고 테이블 단위로 정보를 보여주기 때문에 보고서 용도로 사용하기에는 다소 불편함을 느낄수 있을 것이다. 내가 원하는 형식으로 인덱스 정보를 조회할 수 있는 방법이 없을까 고민하던 중 MS SQL Server에서 인덱스 정보를 조회하는 뷰를 작성하는 스크립트를 발견하였다. 블로그 주인장은 아래와 같이 원래의 스크립트를 수정하여 유용하게 사용하고 있다.  관심이 있는 분들은 각자의 구미에 맞게 스크립트를 수정하여 사용해 보길 바란다.

     

    본 스크립트는 SQLLeader.com의 스크립트 자료실에서 얻은 스크립트를 수정한 것으로 작성자는 avigneau이다. 스크립트 테스트는 SQL Server 6.X, 7.0, 2000버전까지 이루어진 것  같다(참고로 주인장은 2005버전에 적용해 아무 이상 없이 사용하고 있다).

    IF EXISTS (SELECT [name]
                 FROM sys.sysobjects
                WHERE [type] = 'V'
                  AND [name] = 'idxinfo')
    BEGIN
        DROP VIEW [dbo].[idxinfo]
    END
    GO
     
    CREATE VIEW dbo.idxinfo
    AS
    SELECT o.id AS [TableId]
         , u.name AS [Owner]
         , o.name AS [TableName]
         , i.indid AS [IndexID]
         , CASE i.name WHEN o.name THEN '** NONE **'
                       ELSE i.name END AS [IndexName]
         , CASE i.indid WHEN 1 THEN 'CLUSTERED'
                        WHEN 0 THEN 'HEAP'
                        ELSE 'NONCLUSTERED' END AS [ClusterType]
         , CASE WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'
                WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'
                ELSE '' END AS [UniqueType]
         , CASE WHEN (i.status & (2048)) > 0 OR ((i.status & (4096)) > 0 ) THEN 'CONSTRAINT'
                WHEN i.indid = 0 THEN ' '
                ELSE 'INDEX' END AS [IndexType]
         , CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN ''
                ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 1) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END +
           CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END +
           CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 4) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END +
           CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 6) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END +
           CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) END +
           CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 8) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,15) END +
           CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN ''
                ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS [AllColName]
         , OrigFillFactor AS [FillFactor]
    FROM sysobjects o (NOLOCK)
        LEFT OUTER JOIN sysindexes i (NOLOCK)
            ON o.id = i.id
       JOIN sysusers u (NOLOCK)
            ON o.uid = u.uid
    WHERE o.type = 'U' AND i.indid < 255
    AND o.name NOT IN ('dtproperties')
    AND i.name NOT LIKE '_WA_Sys_%'  -- because of SQL Server 7.0
    GO

     

    실행 예1 전체 인덱스 정보 조회

    SELECT *
      FROM dbo.idxinfo
    GO

     

    실행 예2 Heap 구조의(Clustered Index가 없는) 테이블 조회

    SELECT *
      FROM dbo.idxinfo
     WHERE ClusterType = 'HEAP'
    GO

     

    실행 예3  dbo.Employee 테이블의 인덱스 조회

    SELECT *
      FROM dbo.idxinfo
     WHERE Owner = 'dbo'
       AND TableName = 'Employee'
    GO
    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기  (0) 2011.07.18
    DDL 스트립트를 작성하는 프로그램 (오픈 소스)  (0) 2011.07.18
    [MS SQL Server New Feature 1] 최소 로깅 Insert  (0) 2011.07.18
    [MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정  (0) 2011.07.18
    프로시져 연습  (0) 2010.06.18
    반응형

    본  포스트에서는 MSDN Blog에 MS Server 2008 부터 도입된 신기능에 대한 유용한 정보가 있어 소개하고자 한다.

     

    원문 블로그:

     http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx

    작성자: Sunil Agarwal

     

    RDMBS에서는 장애 발생시 혹은 특점 시점으로 롤백 또는 롤포워드를 하기 위해 데이터 변경 작업 (일반적으로 DML)에 대해 로그를 기록한다. 트랜잭션 작업 성능에 영향을 미치는 것이 바로 이러한 로깅인데, 특히 배치 및 ETL 등과 같은 대량 데이터의 변경작업시 로그파일에 많은 IO를 발생시켜 성능상의 이슈가 되고 있다.

     

    이에 MS SQL Server에서는 로깅을 최소화하는 방법으로 데이터베이스 복구 모델의 형태로 Simple 및 Bluk Logged 복구 모델을, 그리고 SSIS(DTS), BCP, SELECT INTO.., BULK INSERT와 같은 툴과 구문을 제공하고 있다.

     

    MS SQL Server 2008부터는 위에서 언급한 방법이외에 INSERT 구문에서도 로그를 최소화할 수 있는 기능이 추가되었는데 제법 유용하게 사용될 것이라고 생각된다.

     

    아래는 이 기능에 대해 원문 블로그에 있는 내용을 발췌하여 재정리한 것으로 참고하길 바란다.

     

    1. 테스트를 위해 테스트하고자 하는 DB의 복구 모델을 "전체(Full)"로 바꾸도록 하자.

     

    2. 소스 테이블 생성

    -- create the source table

    create table t_source (c1 int, c2 int, c3 char (100), c4 char(1000))

    go

     

    declare @i int

    select @i = 1

    while (@i < 1000)

    begin

                insert into t_source values (@i, @i+10000, 'indexkey', 'hello')

                select @i= @i + 1

    end

       

    3. 타겟 힙 테이블 생성

    -- create the target heap

    create table t_heap (c1 int, c2 int, c3 char(100), c4 char(1000))

    go

       

    4. INSERT 시 TABLOCK 힌트를 줘서 최소 로깅한 경우

    -- this is minimally logged.

    -- LOCK: X lock on the table

    -- this behavior is same even when ‘t_heap’ is not empty

    begin tran

    insert into t_heap with (TABLOCK) select * from t_source

     

    4의 경우 로그 정보 확인

    select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName

    from fn_dblog(null, null)

    where allocunitname='dbo.t_heap'

    order by [Log Record Length] Desc

     

    -- here are the top-10 log records. You can see that it is minimally logged


     

    5. INSERT 시 TABLOCK 힌트를 주지 않고 전체 로깅한 경우

    -- this is fully logged

    insert into t_heap select * from t_source

     

    5의 경우 로그 정보 확인

    select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName

    from fn_dblog(null, null)

    where allocunitname='dbo.t_heap'

    order by [Log Record Length] Desc

     

    -- here are the top 10 log records. You can see that it is fully logged

     


    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    DDL 스트립트를 작성하는 프로그램 (오픈 소스)  (0) 2011.07.18
    인덱스 정보를 조회하는 뷰  (0) 2011.07.18
    [MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정  (0) 2011.07.18
    프로시져 연습  (0) 2010.06.18
    Partition Table  (0) 2010.06.18
    반응형
    본 포스트는 아래의 BOL의  참조하여 작성한 내용이다.

    SQL Server Profiler를 사용하여 Windows 성능 로그를 열고 추적과의 상관관계를 지정할 카운터를 선택한 다음 선택한 카운터를 추적과 함께 SQL Server Profiler에 표시할 수 있다.
    추적 창에서 이벤트를 선택하면 선택한 추적 이벤트와 상관 관계가 있는 성능 로그 데이터가 SQL Server Profiler의 시스템 모니터 데이터 창에서 빨간 세로 막대로 표시된다.

    추적과 성능 카운터의 상관관계를 지정하려면 StartTime 및 EndTime 데이터 열을 포함하는 추적파일과 테이블을 연 다음 SQL Server Profiler 파일 메뉴에서 성능데이터 가져오기를 선택한다.
    그러면 성능 로그를 열고 추적과의 상관 관계를 지정할 시스템 모니터 개체와 카운터를 선택할 수 있다.


    Step 1 : 추적 및 Windows 성능 로그 데이터 수집

    1. SQL Server Profiler에서 추적을 수집하여 파일 또는 테이블에 저장한다.

      · 이 때 StartTime 과  EndTime 컬럼을 반드시 포함해야 한다.

      · 추적을 만드는 방법은 아래의 BOL을 참조하기 바란다.

        추적 만들기(SQL Server 프로파일러)

      · 추적 템플릿을 만들어 두면 매번 추적을 만들 필요가 없으므로 용이하다. 관련 내용은 다음 BOL을 참조하기 바란다.

        추적 템플릿 만들기(SQL Server 프로파일러)


    2. 추적을 수집함과 동시에 Windows 성능 로그 데이터를 수집한다.

      성능 로그를 수집하는 방법은 방법 : 추적과 Windows 성능 로그 데이터의 상관 관계 지정(SQL Server 프로파일러) 의 다른 버전의 Windows와 공유라 수 있는 성능 로그를 만들려면 을 참조하기 바란다.


    STEP 2 : 추적과 성능 로그 데이터의 상관 관계 지정
    1. SQL Server Profiler에서 앞에서 저장한 추적 파일 또는 추적 테이블을 연다. 실행인 추적의 경우에는 상관 관계를 지정할 수 없다.

    2. SQL Server Profiler의 파일 메뉴에서 성능 데이터 가져오기를 클릭한다.


    3. 열기 대화 상자에서 앞에서 수집한 성능 로그가 들어 있는 파일을 선택한다.


    4. 성능 카운터 제한 대화 상자에서 추적과 함께 표시하고자 하는 시스템 모니터 개체와 카운터를 선택한 다음 확인 버튼을 클릭한다.


    5. 추적 이벤트 창에서 이벤트를 선택하거나 화살표 키를 사용하여 추적 이벤트 창의 인접 형으로 이동한다. 시스템 모니터 데이터 창의 빨강 세로 막대는 선택한 추적 이벤트와 상관 관계가 있는 성능 로그 데이터를 나타낸다.


    6. 시스템 모니터 그래프에서 자세히 보고 싶은 시점을 클릭하면 선택한 시간에 가장 가까운 해당 추적행이 표시되며, 시간 범위를 확대하려면 시스템 모니터 그래프에서 마우스 포인터를 누른 다음 끌어서 범위를 선택하면 된다.

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    인덱스 정보를 조회하는 뷰  (0) 2011.07.18
    [MS SQL Server New Feature 1] 최소 로깅 Insert  (0) 2011.07.18
    프로시져 연습  (0) 2010.06.18
    Partition Table  (0) 2010.06.18
    Open Query  (0) 2010.06.18
    반응형

    /*매출정보 procedure 1 날짜별 (0,0)
          2.상품별 (A,01) (b,03)
          3.연령별 (인자값,null) */
    --1. 날짜별
    create proc soldinfo_date @date_from nchar(8),@date_to nchar(8)  
    as
     select substring(o.outcode,4,10) as '날짜',
       sum (od.qty*od.price) as '상품별 매출액' from orderdetail od inner join [output] o  --결제가 되어야 매출이 이뤄지므로
       on od.ocode=o.ocode                 --결제시 생성되는 outcode로 일정기간동안 매출을
       where substring(o.outcode,4,10) > convert(datetime,@date_from)                      --처리
             or substring(o.outcode,4,10) < convert(datetime,@date_to) --from에서 to까지 날동안의 매출액
     group by substring(o.outcode,4,10)

    exec soldinfo_date '20080205','20080606'


    --2. 상품별
    create proc soldinfo_pt @pt_category nchar(1),@pt_num nchar(2)  --product 카테고리+넘버
    as
    select 
           p.pcode as '매출상품코드',
           sum(od.qty*od.price) as '상품별 총 매출액' 
     from [order] o inner join orderdetail od  -- order와ordertail을 조인하여 상품별 총 매출액을 구할 수 있다
      inner join product p                  -- product를 멀티 조인하여 상품 값을 구할수 있다.
      on p.pcode=p.pcode
    where o.payment=1 and p.pcode=(@pt_category + @pt_num)
    group by p.pcode

    exec soldinfo_pt 'd','01'

    --3. 연령별
    create proc soldinfo_age  @age nvarchar(3)
    as
    select m.ages as '연령대', 
           sum(od.[sum]) as '매출액'
     from [order] o inner join 
        (select qty*price as 'sum',
          ocode from orderdetail) od --판매된 아이템 총가격과 ocode,를 orderdetail에서 
      on o.ocode=od.ocode                               
      inner join (select (age/10)*10 as 'ages',
          id from member) m --연령대와 id를 멤버테이블에서
      on m.id=o.id
    where m.ages=@age
    group by m.ages

    exec soldinfo_age '20'
    -------------------------------------------------------------------
    --4.재고정보-재고량(a,01) <현재재고, 안전재고, 여유재고(현재-안전재고)>

    create proc stock @pt_category nchar(1), @pt_num nchar(2)
    AS
    select pcode as '상품코드', 
           pqty as '현재재고',
        safeqty as '안전재고',
        (pqty-safeqty) as '여유재고' from product
    where pcode=@pt_category+@pt_num

    exec stock 'd','01'
    -----------------------------------------------------------------------
    /*고객정보 1. 주문정보 (고객id,[오늘,1주일,1개월]) ; 결제 x
        2. 상품구매현황 ; 결제o */

    -- 5.주문정보 : 결제 x
    create view order_detail
    as  
    select o.*, od.pcode, od.qty, od.price, od.pay                        --order와 orderdetail의 join 부분을 뷰로 만들었다.
      from [order] o inner join 
         (select *,(qty*price) as 'pay' from orderdetail) od   
         on o.ocode=od.ocode


    create proc cs_data 
    @id nvarchar(10),
    @date nvarchar(6)
    as

    if @date='오늘'
     begin
      select * from order_detail where id=@id 
       and substring(ocode,2,10)=(convert(nchar(10),Getdate(),120))   -- 오늘
     end
    else if @date='1주일'
     begin
      select * from order_detail where id=@id  
       and substring(ocode,2,10)<=(convert(nchar(10),Getdate()+7,120)) -- 1주일
     end
    else if @date='1개월' 
     begin
      select * from order_detail where id=@id 
       and substring(ocode,2,10)<=(convert(nchar(10),dateadd(mm,1,Getdate()),120)) -- 1개월
     end
    else
     begin
      select '잘못된 날짜 입니다'
     end

    exec cs_data 'khdba37-13','1개월'
    -- 7.상품구매현황 : 결제o

    alter proc cs_data 
    @id nvarchar(10),
    @date nvarchar(6)
    as
    if @date='오늘'
     begin
      select od.* from order_detail od inner join output op
                     on od.ocode=op.ocode where od.id=@id                    -- 6번사항에서 만든 view에 output을 조인 시켰다.
       and substring(op.outcode,2,10)=(convert(nchar(10),Getdate(),120))   -- 상품구매현황이기 때문에 payment시 생성되는 outcode를 이용했다.
     end
    else if @date='1주일'
     begin
      select od.* from order_detail od inner join output op
                     on od.ocode=op.ocode where od.id=@id  
       and substring(op.outcode,2,10)<=(convert(nchar(10),Getdate()+7,120)) -- 1주일
     end
    else if @date='1개월' 
     begin
      select od.* from order_detail od inner join output op
                     on od.ocode=op.ocode where od.id=@id 
       and substring(op.outcode,2,10)<=(convert(nchar(10),dateadd(mm,1,Getdate()),120)) -- 1개월
     end
    else
     begin
      select '잘못된 날짜 입니다'
     end

    exec cs_data 'khdba37-13','1개월'

    -- 8.출고현황 (과거or미래,날짜,날짜)
    alter proc shipping
    @date bit, 
    @from nchar(10),
    @to  nchar(10)
    as
      if @date=0
        begin
         select * from output where  
           (convert(datetime,@from)<=edate and convert(datetime,@to)>=edate)
        end
      else if @date=1
       begin
        select * from output where edate='미출고' and 
          (convert(datetime,@from)<=reserve and convert(datetime,@to)>=reserve)
       end
     
    exec shipping '1','20040204','20080507'

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    [MS SQL Server New Feature 1] 최소 로깅 Insert  (0) 2011.07.18
    [MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정  (0) 2011.07.18
    Partition Table  (0) 2010.06.18
    Open Query  (0) 2010.06.18
    Linked Server  (0) 2010.06.18
    반응형

    특징

    1) 어떤 하나의 테이블이 특정 컬럼의 value 값에 따라 서른 다른 물리적인 파일로 저장.

    2) 대용량 데이터베이스화 하기 위한 필수 기술
    3) MS-SQL2005 부터 지원
    4) 읽고 쓸 때 TRAFFIC의 OVERHEAD를 막을 수 있다.
    5) PARTITON TABLE은 생성시에만 별도의 작업이 필요할 뿐,   실제 데이터의 추가, 삭제, 조회는 기

       존의 방법과 동일하게 이루어진다
     

    실행계획

     STEP 1 

    HARDWARE적인 설계 및 기획

    STEP 2

    테이블을 분리하는데 기준이 되는 PARTITION FUNCTION 생성
    범위 설정 시 LEFT, RIGHT 지시어에 대한 사용법 숙지

    STEP 3

    PARTITION SCHEMA 생성 
    실제 저장되어질 파일 그룹 설정
    그룹 지정시 PRIMARY는 반드시 []로 묶어준다

    STEP 4

    논리적으로 접근할 TABLE 생성
    PARTITION SCHEMA    를 사용


    1. STEP 1


     1)로컬 디렉터리 생성
    xp_cmdshell 'mkdir E:\MSSQL\PART\DATA1'
    go
    xp_cmdshell 'mkdir E:\MSSQL\PART\DATA2'
    go
    xp_cmdshell 'mkdir E:\MSSQL\PART\DATA3'
    go
    xp_cmdshell 'mkdir E:\MSSQL\PART\INDEX'
    go
    xp_cmdshell 'mkdir E:\MSSQL\PART\LOG'
    go


     2) DATABASE CREATION
    CREATE DATABASE PART
    ON PRIMARY
    (
     NAME   = 'PART_DATA1959',
     FILENAME = 'E:\MSSQL\PART\DATA1\PART_DATA1959.MDF',
     SIZE   = 5MB,
     MAXSIZE  = 10MB,
     FILEGROWTH= 10%
    )
    ,
    FILEGROUP PART2
    (
     NAME   = 'PART_DATA1960_69',
     FILENAME = 'E:\MSSQL\PART\DATA2\PART2_DATA1960_69.NDF',
     SIZE   = 5MB,
     MAXSIZE  = 10MB,
     FILEGROWTH= 10%
    )
    ,
    FILEGROUP PART3
    (
     NAME   = 'PART_DATA1970',
     FILENAME = 'E:\MSSQL\PART\DATA3\PART3_DATA1970.NDF',
     SIZE   = 5MB,
     MAXSIZE  = 10MB,
     FILEGROWTH= 10%
    )
    ,
    FILEGROUP INDEXGROUP
    (
     NAME   = 'PART_IDX',
     FILENAME = 'E:\MSSQL\PART\INDEX\PART_IDX.NDF',
     SIZE   = 4MB,
     MAXSIZE  = 8MB,
     FILEGROWTH= 10%
    )
    LOG ON 
    (
     NAME   = 'PART_LOG',
     FILENAME = 'E:\MSSQL\PART\LOG\PART_LOG.LDF',
     SIZE   = 2MB,
     MAXSIZE  = 4MB,
     FILEGROWTH= 10%
    )
    GO

     

    2. STEP 2

      1)파티션 함수생성


    CREATE PARTITION FUNCTION PF_AGE(INT) 
    AS
    RANGE     [LEFT|RIGHT] FOR VALUES(1959, 1970)  분기점  left면 1.~1959 2.1960~1970 3.1971~
    GO                                                     분기점  right면 1.~1960 2.1961~1969 3.1970

     

      --$Patition . 파티션 함수(값)으로 분할 번호 확인
    select $partition.pf_age(1950);
    select $partition.pf_age(1959);
    select $partition.pf_age(1960);
    select $partition.pf_age(1965);
    select $partition.pf_age(1966);
    select $partition.pf_age(1969);
    select $partition.pf_age(1970);
    select $partition.pf_age(1971);
    select $partition.pf_age(1979);

    --2. 파티션 스키마 생성
    CREATE PARTITION SCHEME PS_AGE
    AS
     PARTITION PF_AGE     --PARTITION FUNCTION PF_AGE(INT)기준으로 의해 
      TO([PRIMARY], [PART2], [PART3]) -- 파일그룹이름
    GO

    --3. 논리 테이블 생성
    CREATE TABLE FAMILY
    (
     ID    VARCHAR(12) NOT NULL,
     NAME   VARCHAR(10) NOT NULL,
     BIRTHYEAR INT,               -- 위에도 INT
     ADDR   NVARCHAR(50)
    )ON PS_AGE(BIRTHYEAR) --birthyear는 Partition scheme의해 저장 된다
    GO
    --primary key 지정
    ALTER TABLE FAMILY
    ADD CONSTRAINT F_ID_PK PRIMARY KEY(ID)
    GO
    -- 실행이 불가능
    -- PRIMARY KEY는 기본적으로 CLUSTERED INDEX를 이용하므로(정렬되서 보관되는 저장공간이 한곳이다)
    -- 저장 공간이 분산되어 있는 PARTITION TABLE에서는 사용할 수 없다.

    create unique clustered index famiy_birth_id_pk -- not조건은 적용이 안되지만 PK와 같다.
    on family(birthyear,id)
    go

    INSERT INTO FAMILY 
     VALUES('FATHER','아버지',1938,'경북포항시용흥동');
    INSERT INTO FAMILY 
     VALUES('MOTHER','어머니',1940,'경북포항시용흥동');
    INSERT INTO FAMILY 
     VALUES('OLDSISTER','큰누나',1965,'서울강남구');
    INSERT INTO FAMILY
     VALUES('YOUNGSISTER','작은누나',1967,'서울동작구');
    INSERT INTO FAMILY 
     VALUES('BROTHER','동생',1974,'서울서초구');
    INSERT INTO FAMILY 
     VALUES('WIFE','마누라',1974,'서울송파구');
    INSERT INTO FAMILY 
     VALUES('CHILD','딸',2002,'서울송파구');

     


    SELECT * FROM FAMILY

     

     ADDITIONAL FEATURE
      - 분산 저장된 데이터별로 검색
       : $PARTITION.[PARTITON_FUNCTION()] = [PARTITION_NUMBER]


    SELECT * 
     FROM FAMILY
     WHERE $PARTITION.PF_AGE(BIRTHYEAR)=1;
     
    SELECT * 
     FROM FAMILY
     WHERE $PARTITION.PF_AGE(BIRTHYEAR)=2;
     
    SELECT * 
     FROM FAMILY
     WHERE $PARTITION.PF_AGE(BIRTHYEAR)=3;
     

    --partition function information

    select * from sys.partition_functions;
    select * from sys.partition_range_values;

    --new partition create
    --partition scheme 수정
     alter partition scheme pf_age
     next used indexgroup                   --새로운 분기점이 생기면 index파일그룹에 저장한다.
     go

    --partition function split (분할)  ,서버 다운된다
     alter partition function pf_age() --정의 되어있는 function이라 데이타 타입을 안쓴다.
     split range(1965)        --left,right는 이미 결정 되어져 있다.
     go
    select * from sys.partition_functions;
    select * from sys.partition_range_values;
     
    --partition function merge 결합
     alter partition function pf_age()
     merge range(1965)  -- 위에서 나눈 1965 범위를 합친다.

    select * from sys.partition_functions;
    select * from sys.partition_range_values;


    --일반 테이블로 이동 
     -- 1.파티션 테이블에 저장된 데이터의 구조와 일반 테이블의 구조
     --   는 일치해야 한다.
     -- 2.일반 테이블의 index구조는 파티션 테이블의 index구조와 일치
     --   해야 한다. index에 의해 참조해서 집어 넣기때문에
     -- 3.일반 테이블의 index file의 위치는 이동할 partition과 같은 
     --   파일 그룹에 존재해야 한다.

    sp_tables 'sister';


    ALTER TABLE SISTER
    ALTER COLUMN ADDR NVARCHAR(50);

     

    특정 파티션에 저장된 데이터를 다른 테이블로 이동
       : ALTER TABLE [TABLE_NAME]
        SWITCH PARTITION [PARTITION_NUMBER] 
                 TO [DESTINATION_TABLE]

     

    -- 특정 파티션의 데이터를 다른 테이블로 이동 2,3번 조건 충족되지 않음
    ALTER TABLE FAMILY 
    SWITCH PARTITION 2 TO SISTER;

    create table sister
    (
     id   varchar(12) not null,
     name  varchar(10) not null, 
     birthyear  int,
     addr  nvarchar(50)
    )
    go

    -- partition table과 돌일한 인덱스 구조의 인덱스를 이동할 파티션의
    -- 파일 그룹에 생성

     

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    [MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정  (0) 2011.07.18
    프로시져 연습  (0) 2010.06.18
    Open Query  (0) 2010.06.18
    Linked Server  (0) 2010.06.18
    INDEX 2  (0) 2010.06.18
    반응형

    select *

    from openquery(연결된서버이름,'select~')

     

    select *

    from openquery(ybj,'select *  from dba37.dbo.sawon)

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    프로시져 연습  (0) 2010.06.18
    Partition Table  (0) 2010.06.18
    Linked Server  (0) 2010.06.18
    INDEX 2  (0) 2010.06.18
    INDEX  (0) 2010.06.18
    반응형

    1. 실행전 사전 실행사항

    1) 서비스 - Remote Procedure Call 활성화

                              SQL Server Browser 활성화

    2) SSCM - SQL Server 네트워크 구성 -> 1. TCP/IP 사용 -> IP1 사용, IP주소 자신주소,

                                                                          TCP 포트 1433

                                                              2. 명명된 파이프 사용

    3) 클라이언트 프로토콜 TCP/IP사용, 명명된 파이트 사용

     

    4) Link 할 서버 별칭 생성

     

    2. 별칭 서버를 등록

     

     

     

    3. Linked Server

    스크립트로 연결시 명령어

     

    use master

    go

    exec sp_addlinkedserver '별칭서버네임',N'sql server'

    go

    exec sp_addlinkedserversrvlogin '별칭서버네임','false','sa','sa','별칭서버 sa의 암호'

    go

     

    GUI로 연결시

     

     

     

    연결이 되었으며,

    개체 탐색기에서 별칭서버를 연결하지 않고 직접 서버에 접근하여 다양한 작업을 할수 있다.

    ex) select * from 별칭서버네임.해당DB.dbo.테이블;

     

    4. Excel 연결하기

     

    스크립트문

    exec sp_addlinkedserver

     @server='알아보기 쉬운이름',

     @provider='Microsoft.Jet.OLEDB.4.0',

     @srvproduct=''

     @datasrc='원본위치'

     @provstr='excel 8.0'

    go

     

    GUI

     

    select * from 연결된서버이름...만든이름$

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    Partition Table  (0) 2010.06.18
    Open Query  (0) 2010.06.18
    INDEX 2  (0) 2010.06.18
    INDEX  (0) 2010.06.18
    INDEXING VIEW  (0) 2010.06.18
    반응형

    1. Clustered Index / Nonclustered Index

    ACTION

    Clustered Index

    Nonclustered Index

    Index 생성 후 크기 증가

    테이블의 1~5%

    테이블의 10~20%

    Point Query 실행시

    상당히 빠르다.

    Clustered index보다 약간느리다.

    Range Search 실행시

    빠르다.

    일반적으로 Table Scan보다 느리다.

    DML문 실행시

    과부하 걸린다.

    Clustered Index보다 부하가 적다.

    Covered Index의 경우

    도움이 안된다.

    매우 도움이 된다.

    대단히 빠르다.

    테이블당 생성 개수

    오직 1개

    249개까지 가능

     

     

    Clustered Index

    Nonclustered Index

    1.     인덱스 Leaf 페이자가 바로 데이터 페이지

    2.     데이터 페이지는 인덱스가 생성된 컬럼을 기준으로 항상 정렬

    3.     Range Seach 빠른 성능 

    1.     인덱스의 Leaf 페이지는 데이터페이즈를 가리키는 Data Page번호+RowID

    2.     인덱스 페이지 항상정렬이지만

    데이터 페이지는 그렇지 않다

    3.     특정한 값을 찾는데 빠르게 동작

     

    4.     DML문의 작업은 Clustered 인덱스 보다 빠르게 동작

     

    인덱스 검색 방법

     

    ▶ Table Scan : 데이터 페이지를 처음부터 끝까지 검색하는 것

    ▶ Index Seek : Nonclustered Index에서 데이터를 검색하는 것

    ▶ Clustered Index Seek : Clustered Index에서 데이터를 검색하는 것

    ▶ Clustered Index Scan : Table Scan과 동일 , Clustered Index의 리프레벨은 결국 데이터페이지

     

    쿼리 성능 검사 명령어

    명령어

    설명

    SET STATISTICS IO ON/OFF

    페이지 I/O가 얼마나 발생했는지 보여준다.

    주어지 Query로 인해서 얼마나 많은 Page I/O가 발생하는가를 보여준다.

    Page I/0가 많다는 것은 속도가 느리다는 것이다.

    SET STATISTICS TIME ON/OFF

    실행하는데 걸린 시간을 보여준다.

    실제 실행 계획 포함 (Ctrl +M)

    Query문의 실행계획을 그래픽으로 보여준다

    DBCC DROPCLEANBUFFERS

    DBCC DROPCLEANBUFFERS를 사용하면 서버를 다시 시작 하지 않아도 완전히 빈 버퍼캐시를 사용하여 Query를 테스트 할 수 있다.

     

    TIP

    ▶ Range Search(범위안에 모든 값을 가져오는 것)에서는 Nonclustered Index보다 Clustered Index

       가 효과적이다. Nonclustered Index의 경우 리프레벨에서 검색 후 존재하지 않으면 데이타 페이지

       를 검색해야 하기 떄문이다

         큰 범위 검색에서는  Nonclustered Index보다는 Index를 사용하지 않고 Full Scan하는 방법

       이 더 낫다. 따라서 빈번히 영역찾기를 하는 컬럼이 있다면 Clustered Index를 지정해주는 것이 좋

       다.

    ▶ Covered Query

        질의에 나타난 조건 컬럼과 질의 대상 컬럼이 모두 Nonclustered Index에 복합키(Composite Key)

      에 포함되어 있는 경우, Covered Index라고도 한다.

        이런 경우에는 Clustered Index보다 월등히 빠른 검색 속도를 나타낸다. 또한 Point Query(한 행만

      검색하는 경우)뿐만 아니라 Ranger Search에도 매우 빠른 성늘을 나타낸다.

     

    2. Composite Index

        하나의 컬럼에만  Index를 만든 것은  Single Column Index이라고 하고, 둘 이상의 컬럼에 Index를

      만드는 것을 Composite Index라고 한다.

     1) where 절에서 Index의 순서, 또는 그 부분으로만 찾는 경우.

        ex) ('부서명'컬럼+'이름'컬럼)의 Index 생성시

              --> 반드시 where절에 Index의 첫 번재 위치한 컬럼(선행컬럼)인 '부서명' 컬럼이 있어야

                    성능 향상에 도움이 된다.

     2) 가급적 컬럼의 총 크기가 20 Byte가 넘지 않아야

     3) Unique한 컬럼을 선행 컬럼으로.

     

    3. Index 생성시 고려 상황

    ▶ 인덱스 생성해야 하는 경우

     1) where 조건절에 자주 사용되는 컬럼

     2) select 절에서 자주 검색되는 컬럼

     3) 선택도가 좋은 컬럼

     4) Foreign Key 컬럼

     5) Join 조건절에 자주 사용되어지는 컬럼

     6) Ordey by절에 자주 사용되는 컬럼은 Clustered Index를 생성해야 좋다.

     7) Range Search 잦은 컬럼도 Clustered Index를 생성해야 좋다.

     8) 특정한 하나의 값은 Nonclustered Index를 생성해야 좋다.

     

    ▶ 인덱스를 생성하지 말아야 하는 경우 

     1) 자주 검색되지 않는 컬럼

     2) where 조건절에 자주 사용되더라도 선택도가 좋지 않은 컬럼

     3) DML문의 실행이 빈번하면서 select보다 DML문의 속도가 중요할 때

     

    ▶ Clustered Index가 테이블에 아예 없어야 좋은 경우

      Web 상에서 회원테이블 처럼 Insert가 빈번한 컬럼은 Nonclustered Index로만 주는 것이 오

        히려 성능이 좋아진다.

     

     

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    Open Query  (0) 2010.06.18
    Linked Server  (0) 2010.06.18
    INDEX  (0) 2010.06.18
    INDEXING VIEW  (0) 2010.06.18
    UNION ,UNION ALL ,INTERSECT and EXCEPT  (0) 2010.06.18
    반응형

    1.INDEX의 정의

    1)정의

    데이터를 찾을 때 시간을 단축하기 위해서 사용되어진다.

    특정한 값을 찾기 위해 INDEX가 없으면 TABLE SCAN을 이용 함으로

    서버 퍼포먼스가 저하된다.

     

    2)특징

    신속한 자료 검색을 위해 이용

    단점 : DML문을 이용한 데이터의 추가/삭제 시 과부하로 인한 서버 속도 저하

            (이유:DB변경시 INDEX도 변경해줘야 한다.)

     

    3)TABLE에 INDEX를 사용하는 이유

    TABLE 내에 존재하는 각가의 레코들의 유일함(UNIQUE)를 증명

    TABLE 상호 간의 JOIN속도 증가

    자료의 조회가 빠름

    ORDER BY와 GROUP BY의 빠른 수행

     

    4)모든 COLUMN에 사용 하지 않는 이유

    INDEX작성 시 많은 시간 소요

    INDEX작성 시 과도한 디스크 영역의 사용

     CLUSTERED의 경우 TABLE SIZE의 5%증가

    NONCLUSTERED의 경우 TABLE SIZE의 10~20%증가

    DML(INSERT,DELETE,UPDATE)문 사용시 많은 시간 소요

     

    2.INDEX의 생성 구문

     

    인덱스 종류

    Clustered

    NonClustered

    테이블 당 생생 갯수

    1개

    249개

     

    CREATE [UNIQUE] [CLUSTERED | NONCLUSTERD] INDEX --  지정해주지 않을경우 UNIQUE하지

    index_name                                                                     않고  NONCLUSTERED한 INDEX생성

    ON <TB_NAME>(column [ASC|DESC])

    WITH <elational_index_option>

    ON  [partition_scheme_name

          | filegroup_name  -- TABLE를 0번에 생성하고 INDEX를 1번에 생성하면 디스크 분산 사용으로

          | default                  인한 성능 향상 효과를 가질 수 있다.

     

    <relational_index_option>

    PAD_INDEX = [ON|OFF]  -- INDEX 생성시 여유값을 가지냐 마냐를 설정

    FILLFACTOR =               -- PAD_INDEX ON값 일 때 여유값을 여기서 설정해준다.

                                            초기 INDEX값은 2개의 행만 입력할 공간을 남기고 INDEX페이지를

                                           채운다. DML문의 의해  INDEX갱신시 페이지 분할로 인한서버성능이 저

                                           하 되기떄문에 초기 값을 높여주면 INDEX 페이지 분할 확률이 감소된다.

                                           하지만 초기값이 높기 떄문에 초기에 낭비되는 공간으로서 물리적 공간

                                          을 많이 잡아줘야 하는 단점이 있다.

                                          ex)fillfactor=50은 Index 페이지 50%+나중 Index 페이지 공간

    SORT_IN_TEMPDB=ON|OFF -- INDEX생성시 정렬작업을 TEMPDB에서 한다는 것으로  해당 DB의

                                              로딩이 상당히 줄어드는 효과가 있다. TEMPDB는 물리적 독립된 저

                                              장 공간을 가져야 하며 공간도 커야 하다.

    IGNORE_DUP_KEY =ON|OFF

    STATISTICS_NORECOMPUTE= ON|OFF

    ONLINE=ON|OFF --초기값은 OFF 이며 ON시키면 Index생성중에도 TABLE의 쿼리가 가능하다

    DROP_EXISTING=ON|OFF --사용시 INDEX 만드는 시간을 단축

    ALLOW_ROW_LOCKS= ON|OFF

    ALLOW_PAGE_LOCKS=ON|OFF

    MAXDOP = max_degree_of_parallelism --INDEX생성시 사용되는 CPU의 갯수 기본값은 0(자동)

      밑줄 기본값

     

    ex)인덱스 생성

    create view a_money with schemabinding
    as
    select count_big(*) '부서인원'
        ,d.deptno
        ,sum(isnull(s.sal,0)*12+isnull(s.comm,0)) 'yearpay'
    from dbo.sawon s inner join dbo.dept d
    on s.deptno = d.deptno
    group by d.deptno

     

    create index a_u_clidx_a_money_deptno  -- UNIQUE하지 않고 Nonclustered한 Index생성
    on a_money(deptno);

     

     인덱스 제거

    drop index TABLE_NAME.INDEX_NAME

     

    비고> 1. text,ntext,image,bit 자료형의 컬럼에는 INDEX생성 불가

             2. 복합인덱스(Composite INDEX)생성시 최고 900 byte를 넒지 못한다.

                즉, 중복  PK의 총 사이즈는 900 Byte.

                Varchar가 8000Byte까지 가능하지만 900Byte이상으로 잡은 컬럼은 PK생성 불가.

             3. 최대 16개의 컬럼을 조합하여 하나의 INDEX생성 가능

             4. 계산된 열에도 INDEX의 생성 가능. persisted키워드를 사용

     

    create table sungjuk2

    (hakbun varchar(10)

    ,kor smallint

    ,eng smallint

    ,math smallint

    ,total as (kor+eng+math) persisted

    ,average as (kor+eng+math)/3.0

    );

    go

     

    create index ncl_sungjuk2_total

    on sungjuk2(total desc);

     

    sp_helpindex sungjuk2;

     

            5. INDEX 생성의 Create index 로 만드는 것과 PK와 UK 제약으로 만드는 2가지다.

               PK는 자동적으로 unique와 clustered index생성

               UK는 자동적으로 unique와 nonclustered index생성

     

    ex)  alter table sungjuk2
    add constraint a_kor_pk primary key nonclustered(kor);     -- nonclustered값을 지정해주면 강제로

                                                                                      clustered가 아닌 non의제약을준다

    sp_helpindex sungjuk2

    반대로 UK를 clustered화 시킬 수도 있다.                        

     

    3. INDEX 의 구조

    1)데이터 페이지 -> 실제 데이터를 담고 있는 부분

     

    2)인덱스 페이지 -> 인덱스를 담고 있는 부분   인덱스=인덱스페이지 데이타=데이타페이지

                                                                  인덱스와 데이타는 공존하지 않는다.

    3)루트 레벨(Root level)

    ->B-TREE(Balaced TREE) 구조에서 가장 최상위 즉 인덱스의 최상위 단계

     

    4)넌리프 레벨(Non-leaf level)

    -> 최상위와 제일 하위단계가 아닌 나머지 모든 단계로 Key 와 자식페이지의 대한 주소를 가짐

     

    5)리프 레벨(Leat Level)

    -> B-TREE 제일 하위단계로 KEy와 실제 데이타 위치(rowID)값을 가진다.

     

    4. INDEX의 종류

    1)Clustered Index

     ▶ Clustered Index가 있는 테이블은 테이터의 순서가 입력한 순서가 아닌 Clustered Index가 걸려진

     컬럼의 오름차순 정렬되어 데이터페이지에 입력되어 보여진다.

     

    ex)

    sp_helpindex sawon

    set rowcount 10

    select * from sawon

    clustered된 sano순서되로 값이 출력된다.

     

    ▶  Clustered Index를 구성하면 데이타페이지가 리프 페이지가 되므로 데이타 페이지의

         데이타는 항상 오름차순으로 정렬되어진다.

     

    ▶  Clsutered Inder의 실행 계획은 Clustered Index Seek이다.

     

    2)NonClustered Index


    create table NonClustertbl

    (id int

    ,name nvarchar(10));

     

    create unique nonclustered index uni_nclidx_nonclustertbl_id

    on nonclustertbl(id);


    insert into nonclustertbl values(1003,'삼용이')
    insert into nonclustertbl values(1007,'칠용이')
    insert into nonclustertbl values(1009,'구용이')
    insert into nonclustertbl values(1001,'일용이')
    insert into nonclustertbl values(1004,'사용이')
    insert into nonclustertbl values(1002,'이용이')
    insert into nonclustertbl values(1005,'오용이')
    insert into nonclustertbl values(1008,'팔용이')
    insert into nonclustertbl values(1006,'육용이')
    insert into nonclustertbl values(1010,'십용이')

     

    ▶ NonClustered Index의  테이블은 테이터의 순서가 입력한 순서대로 저장된다.

     

    sp_helpindex nonclustertbl

     

    select * from nonclustertbl

     

    ▶ NonClustered Index의 경우 인덱스가 저장되는 물리적 파일병은 해당 테이블이 저장되는 물리적인 파일명과 달라야 좋은 성능의 효과를 기대 할 수 있다.

     

     Create unique nonclustered index uni_nclidx_nonclustertbl_id

    on nonclustertbl(id)

     on indexgroup

     

    ▶ NonClsutered Inder의 실행 계획은 Clustered Index Seek이다.

     

    select * 
    from nonclustertbl
    with(index(uni_nclidx_nonclustertbl_id)) --     with(index(index_name)) 

     

    select * 
    from nonclustertbl
    with(index(0))       -- 해당 인덱스를 사용하지 않겠다는 말

     NonClustered Index만 존재하는 테이블에 데이타가 insert되면은 데이타페이지에는 분할이 발생되지 않고 인덱스 페이지에만 Sort로 인한 분할이 발생된다.

     

    3)Clustered Index 와 NonClustered Index와의 공존시 

      

    create table clusnonclustbl

    (id int
    ,name nvarchar(10)
    ,addr nvarchar(10));

     

    create unique clustered index uniclidx_clusnonclustbl_id
    on clusnonclustbl(id);

     

    create index nclidx_clusnonclustbl_name
    on clusnonclustbl(name);

     

    insert into clusnonclustbl values(1003,'삼용이','서울')
    insert into clusnonclustbl values(1007,'칠용이','인천')
    insert into clusnonclustbl values(1009,'구용이','수원')
    insert into clusnonclustbl values(1001,'일용이','서울')
    insert into clusnonclustbl values(1004,'사용이','수원')
    insert into clusnonclustbl values(1002,'이용이','인천')
    insert into clusnonclustbl values(1005,'오용이','서울')
    insert into clusnonclustbl values(1008,'팔용이','부천')
    insert into clusnonclustbl values(1006,'육용이','서울')
    insert into clusnonclustbl values(1010,'십용이','인천')


      

    ▶ Clustered Index와 NonClustered Index 있는 Clustered Index가 걸려진 컬럼의 오름차순 정렬되어 데이터페이지에 입력되어 보여진다

     

    sp_helpindex clusnonclustbl


    select * from clusnonclustbl

     

    ▶ 동일한 테이블에 Clustered Index 와 NonClustered Index가 모두 존재하는 경우 NonClustered Index의 리프레벨이 가리키는 값이 데이터의 주소값이 아니라 Clustered Index를 카리키는 이유는 시스템 부하를 줄이기 위해서이다.  NonClustered Index값이 데이타 값을 지정하고 있다면 , 새로 insert되는 데이타 값은 NonClustered Index에 저장 되어지고 동일 테이블 안에 있는 Clustered index에 의해 재배열 된다면 또 데이타 값은 다시 NonClustered Index에 저장되어지는 시스템 적인 과부하가 걸린다.

     ---> 따라서 인덱스 생성순서는 Clustered Index 에서 Nonclustered Index순으로

            인덱스의 삭제는 역순으로 해줘야 시스템 과부하 덜 걸린다.

     ---> Nonclustered Index의 리프 레벨에는 Clustered Index의 값이 들어오므로, 될수록 Clustered

            Index 가 되는 컬럼의 크기를 작게 잡아야 Nonclustered Index에 저장되는 크기도 작아지며 성

            능이 향상된다.

     

     

    반응형
    저작자표시

    '연구개발 > SQL2005' 카테고리의 다른 글

    Linked Server  (0) 2010.06.18
    INDEX 2  (0) 2010.06.18
    INDEXING VIEW  (0) 2010.06.18
    UNION ,UNION ALL ,INTERSECT and EXCEPT  (0) 2010.06.18
    join & subquery 연습  (0) 2010.06.18

    + Recent posts

    반응형

    티스토리툴바