Study/Mssql2012. 6. 27. 08:49

SQ2K8 Index 생성 관련 lock 관련 Guide 요청

[제목]

Index 생성 관련 lock 관련 Guide 요청

[요약]

--문제

Index 생성 관련 lock 관련 Guide 요청

[진행 사항]

Step 1 ) A(Source) Table 에서 B Table로 Data 복사 (SELECT INTO or INSERT SELECT )

Step 2 ) B Table에 PK를 포함한 Index 생성

Step 3 ) A Table에 대해서 아래 Query를 이용해서 Access 여부 확인

Step 4 ) Step 3에서 A Table에 Access가 없는 경우 A Table을 A_Org로 Rename

Step 5 ) B Table을 A Table으로 Rename

SELECT * FROM

(

SELECT R.session_id, SUBSTRING(S.text, (R.statement_start_offset/2)+1,

((CASE R.statement_end_offset

WHEN -1 THEN DATALENGTH(S.text)

ELSE R.statement_end_offset

END - R.statement_start_offset)/2) + 1)

AS SQL

FROM sys.dm_exec_requests AS R

cross apply sys.dm_exec_sql_text(r.sql_handle) AS S

WHERE R.session_id > 50 AND R.session_id <> @@SPID

) AS S

WHERE S.SQL like '%table_name%'


Posted by 영혼도둑
Study/Mssql2008. 9. 26. 11:16

max server memory 사이즈 수정


sp_configure

sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
sp_configure 'show advanced options',0
RECONFIGURE
GO


Posted by 영혼도둑
Study/Mssql2008. 9. 26. 07:19

SQL Server 2005 Cluster Agent Service 실행실패

http://laigo.tistory.com/62

오늘 hotfix 적용하면서 발생한 문제 해결법
Posted by 영혼도둑
Study/Mssql2008. 9. 22. 17:16

IO 높은 쿼리

use master
SELECT TOP 50
        (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
            SUBSTRING(qt.text,qs.statement_start_offset/2,
            (case when qs.statement_end_offset = -1
            then len(convert(nvarchar(max), qt.text)) * 2
            else qs.statement_end_offset end -qs.statement_start_offset)/2)
        as query_text,
        qt.dbid, dbname=db_name(qt.dbid),
        qt.objectid,
        qs.sql_handle,
        qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
       [Avg IO] DESC
Posted by 영혼도둑
Study/Mssql2008. 9. 22. 16:58

인덱스 안타는 것들....



declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
    , indexname=i.name, i.index_id
    , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
    sys.indexes i
where database_id = @dbid
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc


declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
    , indexname=i.name, i.index_id
    , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
    sys.indexes i
where database_id = @dbid 
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
and object_name(s.object_id)= 'mt_member'
order by (user_seeks + user_scans + user_lookups + user_updates) asc


Posted by 영혼도둑
Study/Mssql2008. 9. 12. 10:56

isolation Level 보기 및 변경하기

dbcc useroptions

ALTER DATABASE testdb
    SET READ_COMMITTED_SNAPSHOT ON;

SELECT NAME, SNAPSHOT_ISOLATION_STATE,
SNAPSHOT_ISOLATION_STATE_DESC, IS_READ_COMMITTED_SNAPSHOT_ON
FROM SYS.DATABASES
Posted by 영혼도둑
Study/Mssql2008. 9. 11. 16:18

select count(*) from Table 을 빠르게 하는 법


 

단순한 Table Count를 하기 위해서 보통
select count(*) from TableName
을 사용한다..
이럴경우 전체 테이블을 인덱스 스캔을 하게 되고 Stream Aggregate을 시행한다..
데이타가 많지 않을 경우는 괜찮겠지만..
몇백만 몇천만의 데이타일 경우는 문제가 쪼까 되겄지..

SQL Server 2005에서 제공하는 시스템 카테고리 뷰를 가지고 UDF를 만들어서 빠르게 할수가 있다..

CREATE FUNCTION dbo.row_count (@table_name sysname)
-- @table_name we want to get count

RETURNS bigint
AS
BEGIN
DECLARE @nn bigint -- number of rows

IF @table_name IS NOT NULL 
BEGIN
 SELECT  @nn = sum(  p.rows )  
 FROM sys.partitions p
 LEFT JOIN  sys.allocation_units a ON  p.partition_id = a.container_id
 WHERE
  p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
       and p.rows is not null
  and a.type = 1  -- row-data only , not LOB
  and p.object_id = object_id(@table_name)    
 END 
RETURN (@nn)
END
GO


select dbo.row_count ('Sales.SalesOrderDetail')
go
select count (*) from Sales.SalesOrderDetail
go


 

 

Cost가 거의 10분의 1로 줄어든다.. ^^

내가 테스트 하기론 한 100배는 빨라지는거 같다..





Posted by 영혼도둑
Study/Mssql2008. 8. 26. 17:33

CPU 쳐 잡아 먹는 쿼리 찾기


select top 150 last_execution_time,total_worker_time as [total cpu time],
 total_worker_time/execution_count as [Avg cpu time],execution_count,text,  qp.query_plan  
from sys.dm_exec_query_stats as qs  cross apply sys.dm_exec_sql_text(qs.sql_handle)
as st  cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp  
where datediff(hour,last_execution_time,getdate())<1  
order by total_worker_time desc;
Posted by 영혼도둑
Study/Mssql2008. 8. 25. 10:54

MS SQL Server 7.0 쿼리 프로세서

MS SQL Server 7.0 쿼리 프로세서

저자: Goetz Graefe, Jim Ewel, Cesar Galindo-Legaria

소개

Microsoft® SQL Server™ 7.0은 Microsoft SQL Server 6.5를 기반으로 구축된 제품으로서, Microsoft의 데이터베이스 제품을 정의하는 릴리스입니다. 고객의 필요와 요구 사항은 SQL Server 7.0에 중대한 제품 혁신을 가져왔습니다. SQL Server는 Microsoft Windows® 운영 체제 제품군을 위한 최상의 관계형 데이터베이스 관리 시스템(RDBMS)으로서, 기업 고객 및 독립 소프트웨어 공급업체(ISV)에 알맞은 제품입니다.

이 설명서는 SQL 구문을 수용하고 그 구문을 실행하는 방법을 결정하고 선택된 계획을 실행하는 데이터베이스 서버의 일부분인 SQL Server 쿼리 프로세서에서 혁신적으로 향상된 부분에 대해 설명합니다. SQL Server 7.0은 사용 가능한 다른 상용 RDBMS 제품과 비교할 때 중대한 혁신을 가능하게 해주는 토대가 됩니다.

쿼리 프로세서의 역할과 종류 및 구성 요소를 간단히 소개한 다음 아래 항목에 대해 설명합니다.

  • Microsoft SQL Server 7.0의 목표
  • 쿼리 실행
  • 쿼리 최적화
  • 분산 쿼리
  • 쿼리 작업

자세한 내용은 Storage Engine 백서(부품 번호 098-80769)를 참조하십시오.

관계형 데이터베이스는 여러 부분으로 되어 있지만, 핵심 구성 요소는 저장소 엔진과 쿼리 프로세서입니다. 저장소 엔진은 디스크에 데이터를 기록하고 디스크에 기록된 데이터를 읽습니다. 또한 레코드를 관리하고, 병행성을 제어하고, 로그 파일을 유지합니다.

쿼 리 프로세서는 SQL 구문을 수용하고, 그 구문의 실행 방법을 결정하며, 선택된 계획을 수행합니다. 사용자 또는 프로그램은 쿼리 프로세서와 상호 작용하며, 그런 다음 쿼리 프로세서는 저장소 엔진과 상호 작용합니다. 세부 실행 과정은 쿼리 프로세서가 자체적으로 수행합니다. 즉, 사용자가 결과를 지정하면 쿼리 프로세서는 그러한 결과를 획득하는 방식을 결정합니다.

쿼리 프로세스에는 쿼리 최적화와 쿼리 실행 등 두 가지 주요 단계가 있습니다.

쿼리 최적화는 가장 빠른 실행 계획을 선택하는 프로세스입니다. 최적화 단계에서 쿼리 프로세서는 다음을 선택합니다.

  • 사용할 인덱스(존재하는 경우)
  • 조인 실행 순서
  • WHERE 절 같은 제약 조건을 적용할 순서
  • 통계를 통해서 알 수 있는 비용 정보에 따라 최적의 성능을 이끌어내기 위한 알고리즘

쿼 리 실행은 쿼리 최적화 과정에서 선택한 계획을 수행하는 프로세스입니다. 또한 쿼리 실행의 구성 요소는 쿼리 최적화 프로그램에서 사용할 수 있는 기술을 결정합니다. 예를 들어, SQL Server는 쿼리 최적화 프로그램에서 모두 사용 가능한 해시 조인 알고리즘 및 병합 조인 알고리즘을 구현합니다.

쿼리 최적화 프로그램은 관계형 데이터베이스 시스템의 두뇌에 해당합니다. 거의 모든 작업을 수동으로 실행할 수도 있지만, 쿼리 프로세서는 관계형 데이터베이스가 신속하고 효율적으로 작업할 수 있도록 합니다.

두 건설 회사가 있다고 가정해 봅니다. 두 회사 모두 집을 한 채 짓는 작업을 할당 받았습니다. 집의 크기, 창문 개수, 침실과 화장실 개수는 정해져 있고 부엌은 하나입니다. 첫째 회사는 계획부터 시작합니다. 그리고 필요한 작업 순서, 동시에 진행할 수 있는 단계들, 이전 작업이 있어야 진행이 가능한 단계들, 다양한 작업을 수행할 능력이 있는 작업자 등을 결정합니다. 다른 건설 회사는 아무런 계획도 세우지 않습니다. 작업자들은 아무 연장이나 들고 건축에 착수합니다.

간단한 작업이 아니라면(집을 짓는 일은 일반적으로 간단하지 않습니다), 사전에 계획을 세운 회사가 먼저 작업을 끝낼 가능성이 큽니다. 같은 이유로, 정교한 쿼리 최적화 프로그램을 사용하는 관계형 데이터베이스가 단순한 쿼리 최적화 프로그램을 사용하는 관계형 데이터베이스보다 쿼리, 특히 복잡한 쿼리를 빠르게 실행할 수 있을 것입니다.

또한 사용 가능한 도구 종류가 작업 실행 속도를 결정하며 어떤 종류의 작업을 처리할 수 있는지도 어느 정도는 결정합니다. 예를 들어, 덤프 트럭은 집을 짓는 데는 적당한 도구가 될 수 있지만, 정원을 청소하는 데는 부적합합니다. 마찬가지로, 관계형 데이터베이스는 정교한 쿼리 최적화 프로그램을 사용해야 할 뿐만 아니라 쿼리 실행에 적합한 도구를 포함하고 있어야 하며, 작업에 적당한 도구를 선택해야 합니다.

관계형 데이터베이스에는 구문 기준 쿼리 최적화 프로그램과 비용 기준 쿼리 최적화 프로그램 등 중요한 두 가지 쿼리 최적화 프로그램이 있습니다.

구문 기준 쿼리 최적화 프로그램

구 문 기준 쿼리 최적화 프로그램은 SQL 쿼리에 대한 응답을 얻기 위해 체계적인 계획을 세웁니다. 그러나 쿼리 최적화 프로그램이 선택하는 특정 계획은 정확한 쿼리 구문 및 쿼리 내의 절 순서에 따라 달라집니다. 구문 기준 쿼리 최적화 프로그램은 시간이 초과하여 발생하는 데이터베이스 변경 사항에 포함되는 레코드 수나 구성과는 상관 없이 매번 같은 계획을 수행합니다. 비용 기준 쿼리 최적화 프로그램과 달리, 구문 기준 쿼리 최적화 프로그램은 데이터베이스에 대한 통계를 사용하거나 유지 관리하지 않습니다.

비용 기준 쿼리 최적화 프로그램

비 용 기준 쿼리 최적화 프로그램은 SQL 쿼리에 대신 응답할 수 있는 계획을 선택합니다. 계획 선택은 특정 계획을 수행하기 위한 I/O 작업 수, CPU 작업 시간(초) 등을 기반으로 산출되는 예상 비용을 토대로 이루어집니다. 비용 기준 쿼리 최적화 프로그램은 테이블이나 인덱스의 레코드 수 및 구성에 대한 통계를 사용하여 이러한 비용을 추정합니다. 구문 기준 쿼리 최적화 프로그램과 달리, 비용 기준 쿼리 최적화 프로그램은 정확한 쿼리 구문이나 쿼리 내의 절 순서에는 영향을 받지 않습니다.

SQL Server 6.5와 7.0은 모두 비용 기준 쿼리 최적화 프로그램을 구현합니다. SQL Server 7.0의 쿼리 실행 엔진과 쿼리 최적화 프로그램은 정교함이 크게 향상되었습니다.

Microsoft SQL Server 7.0의 목표

여기서는 Microsoft SQL Server 7.0의 전체적인 목표와 쿼리 프로세서의 구체적인 목표에 대해 설명합니다.

제품 목표는 사용의 용이성, 확장성 및 신뢰성, 포괄적인 데이터 웨어하우징 지원 등입니다.

사용의 용이성

고 객들은 업무상 경험하는 문제점들을 해결할 수 있는 솔루션을 찾고 있습니다. 대부분의 데이터베이스 솔루션은 더 복잡할 뿐만 아니라 추가 비용까지 발생합니다. Microsoft의 전략은 SQL Server를 비즈니스 응용 프로그램 작성, 관리, 배포를 위한 최선의 데이터베이스로 만들려는 것입니다. 이것은 개발자에게 빠르고 간단한 프로그래밍 모델을 제공하고, 기본적인 작업을 위한 데이터베이스 관리 과정을 없애고, 보다 복잡한 작업에 맞는 정교한 도구를 제공하겠다는 의미입니다.

확장성 및 신뢰성

고 객은 데이터베이스에 응용 프로그램을 작성하고 배포 및 관리에 필요한 교육을 실시하는 형식으로 데이터베이스 관리 시스템에 투자하고 있습니다. 그러한 투자에 합당한 대가가 있어야 할 것입니다. 사업 규모가 확대됨에 따라 데이터베이스도 더 많은 데이터, 트랜잭션, 사용자를 수용할 수 있도록 함께 향상되어야 합니다. 또한 고객들은 데이터베이스 응용 프로그램을 랩톱으로 이동하고 지사로 배포할 때 투자한 대가를 받고자 합니다.

이러한 요구를 만족시키기 위해 Microsoft는 Windows 95나 98 운영 체제를 실행하는 휴대형 랩톱 컴퓨터에서 가져온 단일 데이터베이스 엔진을 Windows NT® Server Enterprise Edition 운영 체제를 실행하는 테라바이트 규모의 대칭형 다중 프로세서 클러스터에 적용합니다. 이 모든 시스템은 중요한 업무용 시스템이 요구하는 보안성 및 신뢰성을 유지합니다.

데이터 웨어하우징

트랜잭션 처리 시스템은 회사 데이터베이스 인프라의 주요 구성 요소이며, SQL Server 7.0은 SQL Server 6.0과 6.5로 구축한 성능 및 가격/성능상의 우수성을 계속 유지합니다. 또한 기업들은 데이터 포괄성을 향상시키기 위해 적극 투자하고 있습니다. SQL Server의 목표는 데이터 웨어하우징 비용과 복잡성을 줄이면서 이 기술을 보다 많은 사용자들이 사용할 수 있도록 하는 데 있습니다.

SQL Server 7.0에는 병렬 쿼리 프로세싱, 통합된 온라인 분석 처리(OLAP) 서버인 Plato, 데이터 변환 서비스(DTS), Microsoft Repository, Visual Studio® 개발 시스템, 통합된 복제 기능 등과 같은 새로운 데이터 웨어하우징 기능들이 도입되었습니다. 또한 쿼리 프로세서는 복잡한 쿼리와 대용량 데이터베이스를 처리할 수 있도록 향상되었습니다.

리더십과 혁신

혁신적으로 개선된 SQL Server는 전자 상거래, 이동 컴퓨팅, 지사 자동화, 업무용 응용 프로그램, 데이터 웨어하우징 등과 같이 데이터베이스 산업에서 초고속으로 발전하는 다양한 응용 프로그램 분야의 선두주자가 될 것입니다.

SQL Server 이전 버전들은 소규모 쿼리 및 온라인 트랜잭션 처리(OLTP)에서 확인할 수 있었듯이 레코드 단위 탐색과 인덱스 단위 탐색에 있어서 뛰어난 성능을 발휘했습니다. SQL Server 7.0의 목표는 의사 결정 지원, 대규모 쿼리, 복잡한 쿼리, 데이터 웨어하우징, OLAP 등을 위한 쿼리 프로세서를 향상시키는 것입니다. 하지만 이것이 전부는 아닙니다.

쿼리 최적화 프로그램을 위한 추가 선택

SQL Server 이전 버전에서는 쿼리를 최적화하는 방법이 제한적이었습니다. 예를 들어 SQL Server 6.5에서 조인을 실행하는 방법은 한 가지뿐이었습니다. 그것은 중첩된 루프를 반복하는 것입니다. SQL Server 7.0에는 대규모 쿼리를 위한 선택 알고리즘인 해시 조인과 병합 조인이 추가되었으며, 이로써 최적화 프로그램에는 여러 가지 선택 옵션이 추가되었습니다.

향상된 쿼리 실행

둘째 목표는 선택된 계획이 보다 효율적으로 실행되도록 하는 것입니다. 보다 신속한 스캔, 정렬 향상, 대용량 메모리 지원은 모두 잠재적인 성능상의 이점을 제공합니다.

쿼리 병렬 실행

스 트라이프 디스크 집합 및 SMP 시스템은 점차 대중화되어 가고 있습니다. SQL Server 6.5는 서로 다른 쿼리를 서로 다른 프로세서에 할당하여 병렬 I/O와 상호 쿼리 병렬 처리를 구현했습니다. 그러나 단일 쿼리의 서로 다른 부분을 병렬 실행할 수는 없었습니다. SQL Server 7.0은 단일 쿼리를 여러 개의 하위 작업으로 나눈 뒤 여러 프로세스로 분산하여 병렬 처리되도록 합니다.

데이터 웨어하우스 쿼리에 맞는 최적화

스타 스키마와 스타 쿼리는 데이터 웨어하우징 응용 프로그램의 일반적인 형식입니다. 스타 쿼리는 사실 테이블이라는 대형 중앙 테이블을 차원 테이블이라는 하나 이상의 소형 테이블로 조인합니다. SQL Server 7.0은 이 쿼리들을 자동으로 인식하며 비용을 기준으로 여러 종류의 스타 조인 계획 중에서 선택합니다.

데이터베이스 관리자 개입 최소화

데이터베이스 관리자 수는 부족하고 이들에게 지급해야 할 보수는 매우 비싼 실정입니다. 기본적인 작업 처리에 개입할 필요성을 최소화한다면 데이터베이스 관리자는 보다 복잡한 작업을 처리할 여유를 갖게 될 것입니다.

문제 쿼리 분석 및 해결을 위한 도구 개선

SQL Server 7.0은 문제 쿼리를 식별하고 분석하며 해결할 수 있는 프로파일러, 쿼리 분석기, 인덱스 튜닝 마법사 같은 도구를 제공합니다. 데이터베이스 관리자는 이러한 도구를 사용하여 문제의 근원을 정확하게 찾아낼 수 있습니다. 대개는 이 도구들이 해결책까지 제시합니다.

쿼리 실행

쿼리 실행은 쿼리 최적화 과정에서 선택한 계획을 수행하는 프로세스입니다. 쿼리 실행의 목적은 계획을 신속하게 수행하는 것입니다. 다시 말하면, 사용자 또는 사용자가 실행하는 프로그램으로 최단 시간에 응답을 주는 것입니다. 그러나 CPU, I/O, 메모리 같은 리소스를 최소로 사용하여 계획을 수행한다는 의미는 아닙니다. 예를 들어 병렬 쿼리는 대개 비병렬 쿼리보다 많은 리소스를 사용하지만 사용자에게 결과를 반환하는 속도는 더 빠르므로 가끔은 바람직합니다.

구현된 쿼리 실행 기술들은 쿼리 최적화 프로그램에서 사용할 수 있는 선택 사항들을 결정합니다. 이 설명서에서는 쿼리 최적화 프로그램이 이용하는 기술에 대해 명확하게 이해할 수 있도록 쿼리 최적화를 설명하기 전에 쿼리 실행에 대해 먼저 설명합니다.

효율적인 쿼리 프로세스의 토대가 되는 것은 바로 디스크와 메모리 사이의 효율적인 데이터 전송입니다. SQL Server 7.0은 디스크 I/O에 여러 가지 향상된 기능을 통합했습니다.

순차 I/O와 임의 I/O

I/O 작업(디스크에서 데이터 읽기)은 컴퓨터 시스템 작업들 중에서도 비용이 많이 드는 작업에 속합니다. I/O 작업에는 두 가지 종류가 있습니다. 하나는 디스크에 저장된 순서와 동일하게 데이터를 읽는 순차 I/O이고, 다른 하나는 디스크의 한 위치에서 다른 위치로 건너뛰면서 데이터를 무작위 순서로 읽는 임의 I/O입니다. 특히 대량의 데이터를 읽는 경우에는 임의 I/O가 순차 I/O보다 비용면에서 더 비효율적일 수 있습니다.

Microsoft SQL Server 7.0은 임의 I/O를 최소화하고 대형 클러스터된 인덱스가 없는 힙 테이블의 빠른 스캔을 허용하는 디스크 구조를 유지합니다. 다시 말하면, 데이터 행이 특정 순서로 저장되지 않습니다. 이것은 의사 결정 지원 쿼리를 위해 중요한 기능입니다. 또한 연속적인 처리 단계에서 인덱스 정렬 순서가 필요하지 않은 경우에도, 클러스터된 인덱스 및 클러스터되지 않은 인덱스에 대해 이러한 디스크 순서 스캔을 사용할 수 있습니다.

SQL Server 이전 버전들은 각 페이지가 테이블 데이터를 소유한 다음 페이지에 대한 포인터를 갖는 페이지 체인을 사용했습니다. 이로 인해 임의 I/O가 사용되었고 미리 읽기가 허용되지 않았습니다. 이는 서버가 페이지를 읽은 후에야 다음 페이지의 위치를 알 수 있었기 때문입니다.

SQL Server 7.0은 다른 방법을 사용합니다. 인덱스 할당 맵(IAM)은 테이블이나 인덱스가 사용하는 페이지를 매핑합니다. IAM은 디스크 순서로 된 특정 테이블에 대한 데이터 페이지의 비트맵입니다. 서버는 모든 페이지를 읽기 위해 비트맵을 스캔함으로써 어떤 페이지를 어떤 순서로 읽을 것인지 결정합니다. 그런 다음 순차 I/O를 사용하여 페이지를 검색하고 미리 읽기를 수행할 수 있습니다.

서버가 테이블을 읽지 않고 인덱스를 스캔할 수 있으면 그렇게 합니다. 이것은 그 인덱스가 쿼리에 응답하는 데 필요한 필드를 모두 갖고 있는 포함 인덱스인 경우에 유용합니다. 서버는 b-트리 인덱스를 정렬 순서가 아닌 디스크 순서로 읽어 쿼리에 응답할 수도 있습니다. 이렇게 되면 순차 I/O가 이루어지고 처리 속도가 빨라집니다.

대형 I/O

SQL Server 7.0의 모든 데이터베이스 페이지의 크기는 이전 릴리스에서는 2KB였는데, 현재는 8KB로 늘어났습니다. 또한 SQL Server가 데이터를 읽는 단위는 16KB에서 64KB로 늘어났습니다. 이 두 가지 변경 사항은 단일 I/O 요청이 있는 경우 서버가 대량의 데이터를 읽을 수 있게 함으로써 성능을 향상시킵니다. 이것은 대용량 데이터베이스(VLDB) 및 단일 요청이 수많은 행을 처리할 수 있는 의사 결정 지원 쿼리의 경우에 특히 중요합니다.

미리 읽기 스캔

SQL Server 7.0은 실제 쿼리 프로세서 요청 전에 여러 익스텐트를 읽음으로써 스트라이프 디스크 집합을 이용합니다. 따라서 힙 테이블과 b-트리 인덱스의 빠른 스캔이 가능합니다.

사전 반입 힌트

디스크 순서 스캔을 사용하면 대량의 데이터 스캔 속도를 빠르게 할 수 있습니다. 또한 SQL Server 7.0은 클러스터되지 않은 인덱스를 사용하여 데이터 반입 속도를 향상시킵니다.

클 러스터되지 않은 인덱스를 사용하여 데이터를 검색하면 특정 값에 해당하는 인덱스가 검색됩니다. 그 값이 발견되면 인덱스는 디스크 주소를 가리킵니다. 기존의 방법은 디스크 주소를 사용하여 해당 행에 대한 I/O를 즉시 발행하는 것이었습니다. 따라서 한 행에 동기 I/O이 한 번 수행되며, 기껏해야 한 번에 하나의 디스크가 작동하여 쿼리를 평가합니다. 이것은 스트라이프 디스크 집합을 이용하지 않습니다.

SQL Server 7.0는 다른 방법을 사용합니다. SQL Server 7.0는 클러스터되지 않은 인덱스에서 더 많은 레코드 포인터를 계속해서 찾습니다. 여러 레코드 포인터를 수집한 뒤 저장소 엔진에 사전 반입 힌트를 제공합니다. 이 암시는 쿼리 프로세서가 이 특정 레코드를 곧 필요로 하게 될 것임을 저장소 엔진에 알려줍니다. 그러면 저장 저장소 엔진은 동시에 여러 작업을 실행할 수 있도록 스트라이프 디스크 집합을 이용하여 여러 I/O를 동시에 발행할 수 있습니다.

쿼리 프로세서의 여러 다른 영역은 병합 조인, 인덱스 작성, 스트림 집계 같은 정렬 알고리즘에 의존합니다. SQL Server 7.0에서는 정렬 성능이 크게 향상되었습니다.

내 부적으로 향상된 여러 가지 기능으로 인해 비교 작업을 단순화하고 I/O 작업 용량을 늘릴 수 있으며 비동기 I/O가 가능하고 대용량 메모리를 처리할 수 있는 등 정렬 작업 속도를 높일 수 있게 되었습니다. 또한 SQL Server 7.0은 데이터 입출력 과정에서 정렬 작업과 쿼리 작업 간에 데이터 파이프라인 기능을 제공합니다. 따라서 SQL Server 이전 버전에서 사용되었고 중간 작업 테이블 기록 및 스캔을 필요로 하는 쿼리 계획 단계를 생략할 수 있게 되었습니다.

SQL Server 6.5는 주문 레코드에서 서너 개의 주문 항목으로 이동하는 등의 단위 이동에 탁월한 기능인 중첩된 루프 반복을 사용했습니다. 그러나 중첩된 루프 반복은 전형적인 데이터 웨어하우스 쿼리 같은 대량 레코드 조인에는 효율적이지 않습니다.

SQL Server 7.0은 병합 조인, 해시 조인, 해시 팀 등 세 가지 기술을 새로 도입했습니다. 이 중 해시 팀은 다른 어떤 관계형 데이터베이스에서도 사용할 수 없는 혁신적인 기술입니다.

병합 조인

병 합 조인은 내부 조인, 외부 조인, 세미 조인, 논리곱, 합집합 논리 연산 등을 수행하기 위해 두 가지 정렬된 입력 데이터를 동시에 전달합니다. 병합 조인은 b-트리 인덱스에 대한 정렬된 스캔을 이용하며, 조인 필드가 인덱스된 경우 및 인덱스에 나타나는 열이 쿼리를 포함하는 경우에 일반적으로 사용되는 방법입니다.

해시 조인

해시 조인은 반복 가능한 무작위 함수가 포함된 입력 값을 제거하며 해시 테이블의 값들을 비교하여 일치되는 것이 있는지 확인합니다. 사용할 수 있는 메모리보다 입력된 데이터 양이 적은 경우에는 해시 테이블이 메모리에 남고, 입력된 데이터 양이 많은 경우에는 디스크 상의 오버플로 파일이 채택됩니다. 해시는 인덱스되지 않은 대형 테이블에 대해, 특히 중간 결과를 위해서 일반적으로 사용되는 방법입니다.

해시 연산은 GROUP BY 절, 구분, 논리곱, 합집합, 차이, 내부 조인, 외부 조인, 세미 조인 등을 처리하는 데 사용할 수 있습니다. SQL Server 7.0은 캐시가 최적화된 메모리 해시, 대용량 메모리, 재귀 분할, 하이브리드 해시, 비트 벡터 필터링, 역할 반전 등을 포함하여 널리 사용되는 해시 기술을 모두 구현합니다.

해시 팀

SQL Server 7.0에서 혁신적인 기술은 바로 "해시 팀"입니다. 간단히 말해서, 쿼리는 대개 여러 실행 단계로 구성되어 있습니다. 따라서 쿼리 최적화 프로그램은 여러 단계에서 가능한 유사한 연산을 사용해야 합니다. 예를 들어 각 제품 번호 및 공급업체마다 주문 항목이 몇 개나 입력되었는지 알고 싶은 경우가 있습니다.

결과적으로 쿼리 프로세서는 이 실행 계획을 생성합니다.

이 쿼리 계획은 lineitem 테이블과 partsupp 테이블 간에 병합 내부 조인을 실행하여 내부 조인 개수(스트림 집계)를 계산한 다음 결과를 part 테이블과 조인합니다. 이 쿼리에는 정렬 작업이 필요 없습니다. 이 쿼리는 인덱스 상에 정렬된 스캔을 사용하여 lineitem 및 partsupp 테이블부터 시작하여 정렬된 순서대로 레코드를 검색하는 작업부터 시작합니다. 그러면 병렬 조인에 정렬된 입력 데이터가 제공되고, 병렬 조인은 집계에 정렬된 입력 데이터를 제공하며, 집계는 마지막 병합 조인에 정렬된 입력 데이터를 제공합니다.

연산자에서 연산자로 이동하는 중간 결과 순서를 기억함으로써 별도의 정렬 작업을 없애는 개념인 흥미로운 정렬 아이디어는 현재 시중에 나와 있는 모든 주요 쿼리 프로세서가 이미 구현하고 있습니다. 그러나 SQL Server 7.0은 한 단계 더 나아가 해시 조인에도 같은 개념을 적용합니다. 같은 쿼리에서 lineitems의 중요한 인덱스가 삭제됨에 따라 대형 lineitems 테이블에서 비용이 많이 드는 정렬 작업을 사용하여 이전 계획을 확대해야 하는 상황이라고 가정해 봅시다.

이 쿼리 계획은 병합 조인 대신 해시 조인을 사용합니다. 병합 조인 중 하나는 삭제된 인덱스의 영향을 받지 않으므로 실행 속도는 여전히 매우 빠릅니다. 이 두 가지 해시 연산은 특별히 루트 및 팀 구성원으로 표시됩니다. 데이터가 해시 조인에서 그룹화 연산으로 이동할 때 해시 조인에서 행을 분할하는 작업은 그룹화 연산에서 이용됩니다. 이렇게 되면 그룹화 연산의 입력 중 하나에 해당하는 오버플로 파일을 제거하므로 쿼리에 소요되는 I/O 비용이 줄어듭니다. 장점은 복잡한 쿼리를 처리하는 속도가 빨라진다는 것입니다.

Microsoft SQL Server 6.5에서는 쿼리가 여러 조건자를 포함하고 있는 경우에도 각 테이블에 가장 적합한 인덱스를 하나만 선택했습니다. SQL Server 7.0은 각 인덱스를 기반으로 작은 데이터 하위 집합을 선택한 다음 두 하위 집합에 대해 모든 조건에 맞는 행만 반환하는 논리곱 연산을 수행하는 방법으로 여러 개의 인덱스를 활용합니다.

예를 들어 특정 고객 유형의 주문 횟수와 주문 날짜를 계산할 수 있습니다.

SELECT count (*)
FROM orders
WHERE o_orderdate between '9/15/1992' and '10/15/1992' and
o_custkey between 100 and 200

SQL Server 7.0은 o_custkey 및 o_orderdate 모두에 대한 인덱스를 수집한 다음 조인 알고리즘을 사용하여 두 하위 집합 간의 인덱스 논리곱을 구할 수 있습니다. 이 실행 계획은 orders 테이블에서 두 인덱스를 모두 이용합니다.

인 덱스 논리곱의 변형이 인덱스 조인입니다. 어떤 인덱스를 사용할 때 특정 쿼리에 필요한 모든 컬럼을 인덱스 자체에서 사용할 수 있다면 전체 행을 반입할 필요는 없습니다. 인덱스가 쿼리에 필요한 모든 열을 포함하고 있기 때문에 이것을 포함 인덱스라고 합니다.

포 함 인덱스는 일반적이며 이해하기 쉬운 기술입니다. SQL Server 7.0은 한 단계 더 진보하여 포함 인덱스를 인덱스 논리곱에 적용합니다. 단일 인덱스는 쿼리를 포함할 수는 없고 다중 인덱스가 공동으로 쿼리를 포함할 수 있다면 SQL Server는 이러한 인덱스를 조인할 것을 고려합니다. 어떤 대안을 선택하느냐는 쿼리 최적화 프로그램의 예상 비용에 따라 달라집니다.

Microsoft SQL Server 7.0은 단일 쿼리를 여러 하위 작업으로 나누어 이를 SMP 시스템의 다중 프로세서에 분산하는 인트라쿼리 병렬 처리 기능을 도입했습니다.

아 키텍처는 다중 병렬 스레드를 만드는 일반적인 병렬 처리 작업을 사용합니다. 스캔, 정렬, 조인 같은 각 작업은 병렬 처리에 대해 모르지만 병렬로 실행될 수는 있습니다. 각 작업 병렬 처리 작업에 결합되어 "전체 병렬"이 이루어지기 때문입니다. 그러나 Microsoft가 앞으로 출시될 SQL Server에 추가할 예정인 병렬 업데이트의 경우는 예외입니다. 스캔, 정렬, 조인, GROUP BY 등 다른 모든 작업은 병렬 처리됩니다.

SQL Server는 SMP 시스템에서 병렬 처리가 실행 중임을 감지하고 병렬 쿼리 실행의 각 인스턴스에 대해 최상의 병렬 처리 수준을 결정합니다. SQL Server는 현재 사용 중인 시스템의 작업 부하와 구성을 확인함으로써 최적의 스레드 수를 결정하고 해당 스레드에 병렬 쿼리 실행을 분산합니다. 쿼리 실행이 시작되면 그 쿼리는 실행이 완료될 때까지 같은 수의 스레드를 사용합니다. SQL Server는 병렬 쿼리 실행 계획이 프로시저 캐시에서 검색될 때마다 최적의 스레드 수를 결정합니다. 결과적으로 어떤 경우에는 쿼리를 실행할 때 스레드가 하나만 사용될 수도 있고, 같은 쿼리를 다른 시간에 실행하면 스레드가 두 개 이상 사용될 수도 있습니다.

병렬 처리에 영향을 주는 요인

SQL Server 7.0에서 병렬 쿼리 처리를 사용할 수 있을지는 아래 질문에 대한 대답에 따라 자동으로 결정됩니다.

  • SQL Server가 두 개 이상의 프로세서가 있는 컴퓨터(SMP 시스템)에서 실행 중입니까? 프로세서가 두 개 이상인 컴퓨터에서만 병렬 쿼리를 사용할 수 있습니다.
  • SQL Server 7.0 데스크톱, 표준, 엔터프라이즈 버전 중 어떤 SQL Server 7.0을 사용하고 있습니까? SQL Server 데스크톱 버전은 프로세서를 2개까지 지원할 수 있으며 표준 버전은 4개까지, 엔터프라이즈 버전은 8개까지 지원할 수 있습니다.
  • SQL Server에서 작업 중인 동시 사용자 수는 몇 명입니까? SQL Server는 CPU 사용을 모니터하고 쿼리를 시작할 때 병렬 처리 수준을 조절합니다. CPU 처리량이 많을수록 낮은 병렬 수준이 선택됩니다.
  • 병 렬 쿼리 실행에 사용할 수 있는 메모리가 충분합니까? 각 쿼리를 실행하는 데는 특정한 메모리 공간이 필요합니다. 병렬 쿼리는 비병렬 쿼리보다 많은 메모리를 필요로 합니다. 병렬 쿼리 실행에 필요한 메모리 공간은 병렬 수준과 비례하여 증가됩니다. 주어진 병렬 처리 수준에 대한 병렬 계획의 메모리 요구량이 만족되지 않으면 SQL Server는 병렬 수준을 자동으로 낮추거나, 주어진 작업 부하 상황의 쿼리에 대한 병렬 계획을 완전히 포기하고 순차적 계획을 수행합니다.
  • 실 행되고 있는 쿼리 종류는 무엇입니까? 여러 CPU 사이클을 사용하는 쿼리는 병렬 쿼리 실행에 가장 적합합니다. 대형 테이블 조인, 실질적 집계, 대량의 결과 집합 정렬 등이 그 예입니다. 트랜잭션 처리 응용 프로그램에서 종종 발견되는 단순한 쿼리를 보면, 병렬로 실행하는 데 필요한 추가 조정 작업이 잠재적으로 성능을 향상시키는 것보다 낫다는 사실을 알 수 있습니다. 병렬 처리에 적합한 쿼리와 그렇지 않은 쿼리를 구분하기 위해 SQL Server는 쿼리 실행 예상 비용과 병렬 처리 비용 임계값을 비교합니다. 권장 사항은 아니지만, 관리자는 기본 비용 임계값을 변경할 수 있습니다.
  • 주 어진 스트림에 충분한 행이 있습니까? 쿼리 최적화 프로그램은 스트림 행 수가 너무 적다고 판단하면 병렬 연산자를 사용하지 않습니다. 순차적 계획을 수행하면 시작, 분산, 조정 작업에 소요되는 비용이 병렬 실행을 통해 얻게 되는 이득을 초과할 경우에 시나리오를 수행하지 않을 수 있습니다.

Microsoft SQL Server 7.0은 병렬 쿼리를 비롯하여 다중 클라이언트, 병렬 백업, 병렬 복구 등을 사용하여 병렬 로드를 지원합니다. 자세한 내용은 "Implementing Large DSS Databases with SQL Server 7.0"(부품 번호 098-81102) 및 "Benchmark: High Transaction Throughput During Online Database Backup"(부품 번호 098-812333) 백서를 참조하십시오.

쿼리 최적화

Microsoft SQL Server 7.0 쿼리 최적화 프로그램은 여러 단계로 진행됩니다. 먼저 쿼리를 만족시키는 단순하지만 합리적인 실행 계획을 찾습니다. 찾은 계획이 비용 임계값보다 시간을 덜 소모한다면(예를 들어, 1초도 걸리지 않는다면) 쿼리 최적화 프로그램은 더 효과적인 계획을 찾지 않습니다. 이것은 쿼리 최적화 프로그램이 계획을 실행하는 데 필요한 정도보다 더 많은 리소스를 사용하여 최선의 계획을 결정하는 지나친 최적화를 방지합니다.

처음 선택된 계획이 비용 임계값보다 더 많은 시간을 소모한다면 최적화 프로그램은 다른 계획을 계속해서 찾아 항상 비용이 가장 적게 드는 계획을 선택합니다. 여러 단계가 사용되는 것은 가장 효과적인 계획을 선택하는 데 걸리는 시간과 그 계획을 최적화하는 데 걸리는 시간 사이에 이상적인 타협점이 됩니다.

대 부분의 쿼리 프로세서를 사용하여 실행 계획을 사전에 컴파일하고 저장할 수 있습니다. 저장 프로시저를 컴파일하는 것이 한 가지 예입니다. 사전 컴파일은 실행 계획을 다시 사용할 수 있게 하므로 보다 효율적입니다. 실행 계획을 사용하면 특정 변수를 매개 변수로서 계획으로 보낼 수 있습니다. 또한 반복되는 실행 요청을 준비하기 위한 새로운 개방형 데이터베이스 연결(ODBC) 인터페이스는 이러한 효율성을 이용합니다.

그러나 대부분의 상용 응용 프로그램 및 모든 임의 쿼리는 저장 프로시저를 사용하지 않고 동적 SQL을 사용합니다. SQL Server 7.0은 동적 SQL에 대해 만들어진 계획을 캐시에 저장하는 자동 매개 변수라는 새로운 기능을 구현하여 상수를 매개 변수로 변환합니다. 그 결과, 저장 프로시저를 사용하지 않는 응용 프로그램에 대해서도 저장 프로시저의 효율성을 대부분 제공하게 되므로 컴파일을 위한 노력을 줄일 수 있습니다.

또한 SQL Server 7.0은 응용 프로그램이 매개 변수를 식별하는 경우 매개 변수로 변환된 쿼리를 완전히 지원합니다. 이것은 ODBC, OLE DB 및 준비/실행이 갖고 있는 전형적인 특징입니다.

예전에 배운 수치 연산 집합을 떠올려 본다면 숫자에 전이 속성이 있음을 알 수 있을 것입니다. 즉, A = B이고 B = C이면 전이 속성에 의해 A = C가 됩니다. 이 속성은 쿼리에도 적용될 수 있습니다.

SELECT *
FROM part, partsupp, lineitem
WHERE ps_partkey = l_partkey and l_partkey = p_partkey and
ps_availqty > l_quantity and ps_supplycost > p_retailprice

ps_partkey와 p_partkey가 둘 다 l_partkey와 같으므로 ps_partkey는 p_partkey와 같아야 합니다. 쿼리 프로세서는 ps_partkey가 p_partkey와 같다는 셋째 조인 조건자를 도출함으로써 이 원리를 활용합니다. 예를 들어, 이 쿼리에서 쿼리 프로세서는 이 특정 조인 조건자가 쿼리에 지정되어 있지 않은 경우에도 parts 테이블의 partkey를 partsupp 테이블의 partkey로 조인합니다. 이것은 전이 조건자 때문에 가능한 것입니다.

관 련된 하위 쿼리는 모든 SQL 쿼리 프로세서에 대해 특별한 도전을 제시합니다. SQL Server는 일부 특수한 기술을 관련된 하위 쿼리에 적용하며, 가능한 경우에는 그 하위 쿼리들을 세미 조인으로 단순화합니다. 단순화의 이점은 모든 조인 알고리즘이 적용될 수 있다는 것입니다. 바꾸어 말해서, 쿼리 최적화 프로그램은 대규모 쿼리에 대해서는 덜 효율적인 중첩된 반복 조인을 사용하지 않고 해시 조인이나 병합 조인을 사용할 수 있습니다.

SQL 표준에 따르면 쿼리는 아래와 같이 특정한 순서로 처리해야 합니다.

  1. FROM 및 WHERE 절 실행
  2. GROUP BY 절을 사용하여 데이터 줄이기
  3. HAVING 절에 조건 적용

같 은 결과를 도출하는 모든 계획 또한 정확합니다. 따라서 일부 쿼리에 대해서는 WHERE 절에 필요한 하나 이상의 병합 조인 작업이 실행되기 전에 GROUP BY 절을 평가하여 조인 입력 및 조인 비용을 줄일 수 있습니다. 이것을 GROUP BY 절이라 합니다. 아래는 GROUP BY 절 예제입니다.

SELECT c_name, c_custkey, count (*), sum (l_tax)
FROM customer, orders, lineitem
WHERE c_custkey = o_custkey and o_orderkey = l_orderkey and
o_orderdate between '9/1/1994' and '12/31/1994'
GROUP BY c_name, c_custkey

GROUP BY 절을 살펴보면, 쿼리 프로세서는 기본 키 c_custkey가 c_name을 결정함으로써 c_custkey와 더불어 c_name을 그룹화할 필요가 없도록 지정합니다. 그런 다음 쿼리 최적화 프로그램은 c_custkey와 o_custkey를 그룹화하는 것이 같은 결과를 가져오도록 지정합니다. orders 테이블은 고객 키(o_custkey)를 포함하고 있으므로, 쿼리 프로세서는 customer 테이블로 조인하기 전에 orders 테이블에 대한 레코드가 생길 때마다 쿼리 프로세서는 고객 키를 기준으로 그룹화할 수 있습니다. 이것은 아래 실행 계획에서 입증됩니다.

먼저 쿼리 프로세서는 지정한 날짜 범위 내에 있는 orders 테이블과 lineitem 테이블에 대해 병합 조인을 사용하여 모든 주문 항목을 가져옵니다. 둘째 단계는 해시 집계 즉, 그룹화 작업입니다. 이 단계에서 SQL Server는 고객 키 수준에서 주문 항목들을 모은 다음 수를 세어 l_tax의 합계를 계산합니다. 그런 다음 해시 조인 출력 결과를 정렬하고 customer 테이블로 조인하여 요청된 결과를 이끌어냅니다. 이 쿼리 계획의 장점은 그룹화 작업을 미리 수행했기 때문에 최종 조인을 위한 입력 데이터를 크게 줄일 수 있다는 점입니다.

Microsoft SQL Server 7.0은 파일 그룹을 사용한 분할을 지원합니다. 이 아키텍처는 분할의 여러 장점, 특히 그 중에서도 전체 데이터베이스보다 낮은 세분성으로 관리할 수 있는 기능을 활용합니다. 나중에 출시될 SQL Server에서는 아키텍처가 디스크 분할을 지원하게 될 것입니다. 그러나 대규모 의사 결정 지원 쿼리의 경우 SQL Server 7.0은 분할 뷰를 구현합니다. 데이터베이스 관리자는 이 분할 뷰를 사용하여 제약 조건을 포함하는 여러 테이블을 만들 수 있습니다. 본질적으로는 각 파티션마다 하나의 테이블을 만들 수 있습니다. 그런 다음 테이블은 분할 뷰를 통해 논리적으로 재결합됩니다. 다음은 그 예입니다.

CREATE table Sales96Q1 U constraint "Month between 1 and 3"
CREATE table Sales96Q2 U constraint "Month between 4 and 6"
U
CREATE view Sales96 as
SELECT * from Sales96Q1 union all
SELECT * from Sales96Q2 union all
U

이 데이터 정의 언어(DDL)는 각 영업 분기마다 하나씩 4개의 테이블을 만듭니다. 각 테이블은 특정 제약 조건을 포함합니다. 그런 다음 DDL은 4개의 테이블을 모두 재결합하는 뷰를 작성합니다. 프로그래머는 업데이트에 대한 분할 특성을 알고 있어야 하지만, 의사 결정 지원 쿼리인 경우에는 분할이 투명하게 이루어집니다. 쿼리 프로세서는 Sales96 뷰에 대한 쿼리를 수신할 때 쿼리 제약 조건에 맞지 않는 테이블을 자동으로 식별하여 제거합니다.

SELECT *
FROM Sales96 -- remember, this view has four tables
WHERE s_date between '6/21/1996' and '9/21/1996'

이 쿼리를 발행하면 쿼리 프로세서에서는 뷰의 두 테이블(Sales96Q2 및 Sales96Q3) 만 사용하는 계획을 생성합니다. WHERE 절이 다른 두 테이블의 제약 조건과 모순되므로 이 쿼리에 부적절하기 때문입니다. 또한 각 분기별로 서로 다른 액세스 경로가 사용될 수 있습니다. 예를 들어, 2분기에 속하는 며칠에 대해서는 인덱스 스캔이, 3분기의 경우에는 테이블 스캔이 사용될 수 있습니다. 이것은 잘 알려진 열에 속하는 대형 테이블의 하위 집합을 선택하는 쿼리의 성능을 향상시키는 데 유용한 방법입니다. 시간과 위치가 그 전형적인 예입니다.

SQL Server는 특정 제약 조건이 선택 기준에 모순되는 경우에는 내용이 없는 결과를 모두 검색합니다. 관리자가 뷰를 선언하지 않은 경우라 하더라도, 선택 기준이 제약 조건과 모순되면 최적화 프로그램은 이를 인식하고 적절한 계획을 생성합니다.

의사 결정 지원을 위해 구축된 데이터베이스, 특히 데이터 웨어하우스와 데이터 마트는 종종 OLTP 데이터베이스와 상당히 다른 테이블 구조를 갖는 경우가 많습니다. 일반적인 방법은, 대규모의 임의 쿼리에 향상된 성능을 제공할 뿐만 아니라 사용자가 데이터베이스 정보를 직관적으로 탐색할 수 있도록 디자인된 데이터베이스 스키마 유형인 스타 스키마를 구현하는 것입니다.

스타 스키마는 정보가 사실(분석 중인 대상의 핵심인 숫자 데이터)과 차원(사실의 속성)으로 분류될 수 있는지 확인하는 데서부터 시작합니다. 사실의 예로는 매출액, 단위, 예산, 예상액 등을 들 수 있으며, 차원의 예에는 지역, 시간, 제품, 판매 채널 등이 포함됩니다. 사용자가 "이러한 사실을 이러한 차원으로 보고 싶다" 또는 "분기별 매출액과 단위를 알고 싶다" 같이 말하는 것은 바로 쿼리를 표현하는 것입니다.

스타 스키마는 데이터를 중앙의 사실 테이블로 구성하고 차원 테이블을 포함함으로써 시작 단계에서 확인한 사실을 활용합니다.

sql7

Microsoft SQL Server 7.0은 스타 스키마에 맞게 쿼리를 최적화하기 위한 몇 가지 기술을 제공합니다. 쿼리 프로세서는 이들 쿼리를 자동으로 인식하고 여기서 설명한 특정 기술 및 모든 기술 또는 몇 가지가 조합된 기술을 적용합니다. 적용할 기술은 전적으로 비용을 기반으로 선택되므로, 이 최적화를 수행하도록 하기 위해 암시를 제공할 필요가 없습니다.

스타 스키마의 테이블들이 포함한 레코드 수는 모두 다릅니다. 일반적으로 사실 테이블이 더 많은 레코드를 가지고 있습니다. 이러한 차이는 대부분의 쿼리 최적화 기술에서 중요한 특성입니다.

간 단한 실행 전략은 전체 사실 테이블을 읽은 뒤 이것을 각 차원 테이블로 조인하는 것입니다. 이것은 쿼리에 지정된 필터 조건이 없는 경우에는 합리적인 전략이 될 수 있습니다. 그러나 필터 조건이 있는 경우, 스타 쿼리 최적화는 인덱스를 모두 사용하여 전체 사실 테이블을 읽어야 할 필요성을 제거합니다,

카디전 곱과 복합 인덱스

일반적으로 차원 테이블의 레코드 수가 사실 테이블의 레코드 수보다 적으므로, 카디전 곱을 계산한 뒤 그 계산 결과를 이용하여 다중 열 인덱스에서 사실 테이블 행을 검색하는 것이 좋습니다.

이 기법은 아래 예제를 보면 쉽게 이해할 수 있습니다. 앞의 예제를 참조하여 사실 테이블인 sales 테이블이 1,000만 개의 행을 포함하고 있다고 가정합시다. period 테이블에는 20개, market 테이블에는 5개, product 테이블에는 200개의 행이 있습니다. 사용자는 프런트 엔드 도구를 사용하여 이 쿼리를 생성합니다.

SELECT sales.market_id, period.period_id, sum(units), sum(dollars)
FROM sales, period, market
WHERE period.period_id = sales.period_id and
sales.market_id = market.market_id and
period.period_Desc in ('Period2','Period3','Period4','Period5')
and market.market_Desc in ('Market1','Market2')
GROUP BY sales.market_id, period.period_id

간단한 방법은 period 테이블을 sales 테이블로 조인하는 것입니다. 고른 데이터 분산이라고 가정할 경우 입력 데이터는 1,000만 개의 행을, 출력 데이터는 4/20(가능한 20개 중 4개의 기간) 또는 200만 개의 행을 포함하게 됩니다. 이 예제에서는 해시 조인이나 병합 조인을 사용할 수 있으며, 사실 테이블에서 1,000만 개 행을 모두 읽는 방법 또는 인덱스 검색을 통해 200만 개의 행을 검색하는 방법 중 비용이 저렴한 쪽이 사용됩니다. 그런 다음 이 부분적인 결과는 감소된 market 테이블로 조인된 후 최종적으로 집계된 800K의 행을 출력합니다.

사실 테이블(예: period_id 또는 market_id 열)에 다중 열 인덱스가 있는 경우에는 카디전 곱 전략이 사용될 수 있습니다. period 테이블에서 선택된 행은 4개이고 market 테이블에서는 2개이므로 카디전 곱은 8입니다. 이 여덟 가지 값 조합은 출력되는 800K의 행을 검색하는 데 사용됩니다. 이 방법을 사용하는 다중 열 인덱스를 스타 인덱스라고 부르기도 합니다.

세미 조인 축소 및 인덱스 논리곱

사 실 테이블로의 조인이 복합 인덱스에 포함된 필드가 아닌 다른 필드에 대해 발생하는 경우, 쿼리 최적화 프로그램은 다른 인덱스를 사용하여 각 차원 테이블에 대한 조인으로부터 한정적인 행의 논리곱을 수행함으로써 사실 테이블에서 읽혀지는 행 수를 줄일 수 있습니다.

예를 들어 선택 사항이 period_id 및 product_id 필드에 있는 경우, 쿼리 최적화 프로그램은 복합 인덱스를 사용할 수 없습니다. 이 두 필드가 인덱스의 선행 하위 집합이 아니기 때문입니다. 그러나 period_id와 product_id에 별도의 단일 열 인덱스가 있다면, 쿼리 최적화 프로그램은 200만 개의 인덱스 항목을 검색하여 period 테이블과 sales 테이블 사이에서 특정 조인을 선택할 수 있습니다. 그리고 별도로 400만 개의 인덱스 항목 검색하여 product 테이블과 sales 테이블 사이에서 특정 조인을 선택할 수 있습니다. 두 경우 모두 인덱스를 사용하여 조인할 수 있습니다. 따라서 이 두 가지 선행 조인은 sales 테이블의 전체 행이 아닌 레코드 ID 집합을 계산합니다. sales 테이블에서 실제 행을 검색하기 전에 두 집합의 논리곱이 계산되어 확실히 한정적인 행을 결정합니다. 실제 행을 검색하는 프로세스는 비용이 가장 많이 드는 작업입니다. 두 가지 조인을 만족시키는 행만이 결국 800K의 중간 결과 행에 포함되며, 결과적으로는 이들 행만이 sales 테이블에서 실제로 읽혀집니다.

결합된 기술

때때로 세미 조인 축소에 카디전 곱 및 복합 인덱스를 결합하여 사용할 수도 있습니다. 차원 테이블 세 개에서 선택하는 경우를 예로 들어 봅시다. 그 중 두 개는 복합 인덱스의 초기 필드이고 셋째 차원에는 개별 인덱스가 들어 있다고 가정할 때, 쿼리 최적화 프로그램은 처음 두 조인을 만족시키기 위해 카디전 곱을 사용하고 셋째 조인을 만족시키기 위해 세미 조인을 사용한 다음 결과를 결합할 수 있습니다.

이러한 기술의 효율성에 영향을 주는 요인이 몇 가지 있습니다. 이 요인에는 기본 테이블에 비해 사용되는 인덱스 크기 그리고 사용되는 인덱스 집합이 쿼리에 필요한 모든 열을 포함하고 있어서 기본 테이블에서 행을 검색할 필요성이 없는지 여부 등이 포함됩니다. 쿼리 최적화 프로그램은 이러한 요인을 고려하여 비용이 가장 적게 드는 계획을 선택합니다.

그 계획이 병렬 처리에 대한 비용 임계값보다 더 많은 비용을 소비한다면, 스캔, 조인, 논리곱, 행 반입 같이 필요한 모든 작업이 다중 스레드에 의해 병렬로 실행될 수 있습니다.

테이블의 특정 행이 업데이트되면 그 테이블의 모든 인덱스도 업데이트되어야 합니다. OLTP 작업 같은 소규모 업데이트인 경우에는 기본 테이블의 각 행을 업데이트할 때 인덱스를 행 단위로 업데이트하는 것이 적합합니다.

데 이터 웨어하우스 새로 고침 같은 대규모 업데이트인 경우에 행 단위 업데이트를 사용하면 인덱스 레코드에 임의 I/O가 대량 발생하므로 비효율적일 수 있습니다. 보다 나은 방법은, 일단 업데이트를 지연시켰다가 모든 기본 테이블이 업데이트되고 나면 인덱스별로 변경 사항을 사전 정렬하는 동시에 모든 변경 사항을 인덱스에 병합하는 것입니다. 이렇게 하면 각 인덱스 잎 페이지가 기껏해야 한 번 정도 사용된 후 SQL Server가 b-트리 인덱스를 가로지르도록 할 수 있습니다.

이 방법이 가장 비용 효율적이면 쿼리 최적화 프로그램은 이 방법을 채택합니다. 이 방법이 제공하는 장점은 대량의 데이터 웨어하우스 새로 고침이 더 효율적으로 수행된다는 것입니다.

또한 쿼리 최적화 프로그램은 인덱스가 중첩된 루프 조인, 병합 조인, 해시 조인 중에 가장 비용 효율적인 것을 선택하여 참조 무결성 제약 조건을 수행하는 데 필요한 조인 작업을 계획합니다.

분산 쿼리

Microsoft SQL Server 7.0은 데이터를 로컬로 저장하고 검색할 뿐만 아니라, 관계형 데이터베이스와 비관계형 데이터 원본을 포함한 다른 데이터 저장소에 대한 게이트웨이로 사용할 수도 있습니다.

SQL Server 7.0은 분산 쿼리 즉, 두 대 이상의 서버에 있는 데이터를 포함하는 쿼리를 실행합니다. 분산 쿼리는 서버에서 검색, 업데이트 및 커서를 지원하며, MS DTC(Microsoft Distributed Transaction Coordinator)를 사용하여 노드에서 트랜잭션 기능을 보장합니다. 또한 서버에서 보안성을 유지합니다.

어떤 원격 서버가 인덱스나 SQL 쿼리를 지원한다면, SQL Server 쿼리 최적화 프로그램은 각 원격 서버로 보내질 수 있는 가능한 가장 대규모의 쿼리를 결정합니다. 즉, 쿼리 최적화 프로그램은 각 원격 서버에 대해 가능한 최대의 데이터 축소를 할당합니다. 예를 들어 WHERE 절이나 10개의 레코드만 반환하는 집계를 포함하는 100만 개의 행 테이블에 대해 원격 쿼리가 발행되는 경우, 그 100만 개의 행은 원격 서버에서 처리되고 10개의 레코드만 네트워크를 통해 전송됩니다. 이것은 네트워크 트래픽을 감소시키며 전체 쿼리 수행 시간을 줄여줍니다. 데이터 원본에 대해 이루어지는 전형적인 작업은 선택, 조인, 정렬입니다.

분산 쿼리는 모든 OLE DB 데이터 원본을 지원하거나 ODBC 데이터 원본을 지원하므로 유형이 서로 다를 수 있습니다. SQL Server 7.0 CD는 Oracle 7.x, Oracle 8.x, Microsoft Excel, Microsoft Access, dBASE, Paradox 및 Microsoft Visual FoxPro® 데이터베이스 개발 시스템을 위한 OLE DB 드라이버와 다른 관계형 데이터베이스를 위한 OBDC 게이트웨이를 포함합니다. 기타 서버 데이터베이스(IBM DB2, SYBASE, Informix)를 위한 OLE DB Provider는 타사에서 구입할 수 있습니다.

원격 서버가 SQL 표준 구문이 아닌 구문을 지원하거나 원격 데이터 원본이 SQL이 아닌 쿼리 언어를 지원하는 경우에는, OPENQUERY 연산자가 제공되므로 쿼리 구문을 변경하지 않은 상태로 전달할 수 있습니다.

쿼리 작업

서버에 있는 향상된 쿼리 처리 기능뿐 아니라, Microsoft SQL Server 7.0은 데이터베이스 쿼리로 작업할 수 있는 향상된 도구를 제공합니다.

SQL Server 7.0에 제공되는 대화형 그래픽 도구인 쿼리 분석기를 사용하면, 데이터베이스 관리자나 개발자는 쿼리를 기록하고, 여러 쿼리를 동시에 실행하고, 결과를 확인하고, 쿼리 계획을 분석하고, 쿼리 성능 향상을 위한 지원을 받을 수 있습니다. SHOWPLAN 옵션은 SQL Server 쿼리 최적화 프로그램에서 선택한 데이터 검색 방법을 그래픽으로 표시해 줍니다. 이 옵션은 쿼리의 성능 특성을 이해하는 데 매우 유용합니다. 또한 쿼리 분석기는 인덱스되지 않은 열의 추가 인덱스와 통계에 대한 제안을 제공하므로 쿼리를 효율적으로 처리할 수 있는 쿼리 분석기의 기능을 더욱 개선합니다. 특히, 쿼리 분석기는 손실된 통계를 표시해 주므로 쿼리 최적화 프로그램이 조건자 선택도에 대해 예측할 수 있게 하며, 마우스 클릭 몇 번으로 해당 통계를 작성할 수 있도록 해줍니다.

쿼리 비용이란 특정 하드웨어 구성에 대한 해당 쿼리를 실행하는 데 필요한 예상 경과 시간(초 단위)을 말합니다. 다른 하드웨어 구성도 비용 단위와 경과 시간 사이에 깊은 상관 관계가 있지만, 비용 단위는 초가 아닙니다. 쿼리 관리자는 사용자가 쿼리에 대해 보다 높은 비용 제한을 지정할 수 있도록 합니다. 이 제한을 초과하는 쿼리는 실행되지 않습니다.

쿼리 비용은 실제 경과 시간이 아닌 예상 쿼리 비용을 기반으로 하므로, 쿼리 관리자는 어떤 실행 시간 오버헤드를 갖지 않습니다. 또한 실행 시간이 긴 쿼리는 미리 정의된 제한 시간에 도달할 때까지 실행되는 것이 아니라 시작도 되기 전에 중단됩니다.

Microsoft SQL Server Profiler는 시스템 관리자가 엔진 이벤트를 모니터할 수 있게 해주는 그래픽 도구입니다. 다음은 엔진 이벤트의 예입니다.

  • 로그인 연결, 실패, 연결 해제
  • Transact-SQL SELECT, INSERT, UPDATE, DELETE 문
  • 원격 프로시저 호출(RPC) 배치 상태
  • 저장 프로시저의 시작 또는 끝
  • 저장 프로시저 안에서 명령문의 시작 또는 끝
  • SQL 일괄 처리의 시작 또는 끝
  • 데이터베이스 개체에 대해 설정되거나 해제되는 잠금
  • 열린 커서
  • 쿼리 최적화 프로그램이 조건자 선택도를 예측하게 만드는 손실된 통계

각 이벤트에 대한 데이터를 캡처하여 파일 또는 SQL Server 테이블에 저장했다가 나중에 분석할 수 있습니다. 이벤트 데이터를 필터링하여 관련된 하위 집합만 모을 수 있습니다. 예를 들어 특정 데이터베이스에 영향을 주는 이벤트 또는 특정 사용자에 대한 이벤트만 모으고 다른 모든 이벤트는 무시할 수 있습니다. 또한 실행하는 데 30초 이상 걸리는 쿼리에 대한 데이터만 모을 수도 있습니다.

SQL Server Profiler를 사용하면 캡처한 이벤트 데이터를 SQL Server에서 재생할 수 있으므로, 처음 발생했을 때 저장했던 이벤트를 다시 실행하여 효율성을 높일 수 있습니다. 문제를 일으키는 모든 이벤트를 캡처한 뒤 테스트 시스템에서 그 이벤트를 재생함으로써 SQL Server에서 문제를 해결할 수 있으며, 문제를 복제하거나 격리시킬 수 있습니다.

Microsoft SQL Server 인덱스 튜닝 마법사는 사용자의 작업 부하를 분석하고 사용 중인 데이터베이스에 최적의 인덱스 구성을 추천하는 강력한 새 도구입니다.

인덱스 튜닝 마법사는 아래와 같은 기능을 제공합니다.

  • 쿼리 최적화 프로그램의 비용 계산을 사용하여 최상의 복합 인덱스 비교, 대조 및 선택
  • 특정 데이터베이스에 대해 작업 부하를 위한 최선의 복합 인덱스(추적 파일 또는 SQL 스크립트) 권장
  • 인덱스, 작업 부하, 테이블 사용, 쿼리 비용 분석 제공
  • 기존 인덱스를 삭제하여 유지 관리 비용을 줄이도록 전체 작업 부하 조정
  • 기존 인덱스를 삭제하지 않고 문제가 있는 쿼리 집합에 대해 데이터베이스 조정
  • 다른 디스크 공간 제약 조건에 따라 인덱스 구성 권장 사항 프로토타입핑

인 덱스 튜닝 마법사는 비효율적인 인덱스를 삭제하거나 인덱스되지 않은 열에 보다 효율적인 새 인덱스와 통계를 만드는 데 사용할 수 있는 SQL 문을 작성합니다. SQL 문은 필요에 따라 수동 실행이 가능하도록 저장할 수 있습니다.

인덱스를 만들면, Microsoft SQL Server는 인덱스된 열에 값을 분산하는 작업과 관련된 통계 정보를 자동으로 저장합니다. 또한 SQL Server 7.0은 인덱스되지 않은 열에 대한 통계를 제공합니다. 쿼리 최적화 프로그램은 이 통계를 사용하여 특정 쿼리의 인덱스 사용 비용과 중간 쿼리 결과 크기를 예측할 수 있습니다.

쿼리 최적화 프로그램은 쿼리를 최적화하는 데 필요한 중요한 통계가 손실되었음을 발견하면 자동으로 필요한 통계를 작성합니다. 이처럼 자동으로 작성된 통계는 데이터베이스에 저장되며 다른 쿼리를 최적화하는 데 사용될 수 있습니다. 그러나 이 통계가 다시 필요하지 않으면 잠시 후 사라집니다. 또한 SQL Server는 통계 정보를 변경된 테이블에 있는 데이터로서 자동 업데이트하며, 이 통계는 시간이 가면 다시 낡은 정보가 됩니다.

통계는 표본 추출에 의해 작성되며 매우 효율적인 방식으로 새로 고쳐집니다. 표본 추출은 데이터 페이지에서 무작위로 이루어지며, 통계에 필요한 열을 포함하는 최소 크기의 인덱스를 만들기 위해 테이블 인덱스 또는 클러스터되지 않은 인덱스에서 추출됩니다. 테이블의 데이터 양과 변경되는 데이터 양에 따라 통계 정보 업데이트 빈도가 결정됩니다. 예를 들어, 10,000개의 행을 포함하는 테이블에서 1,000개의 행이 변경되는 경우에는 업데이트가 필요할 것입니다. 10,000개 중 1,000개는 큰 비율이기 때문입니다. 그러나 1,000만 개의 행을 포함하는 테이블에 1,000개쯤 변경되는 것은 그다지 의미가 없습니다.

결론

이 설명서에서는 Microsoft SQL Server 7.0 쿼리 프로세서에서 혁신적으로 향상된 기능에 대해 설명했습니다. SQL Server 7.0 쿼리 최적화 프로그램은 여러 가지 정교한 기술을 사용하여 최적의 실행 계획을 결정하며, 이러한 계획을 효율적으로 신속하게 수행하기 위한 다양한 새로운 옵션을 제공합니다. 이 쿼리 기능 중 일부는 시중에 나와 있는 다른 상용 데이터베이스 서버에서도 사용할 수 있지만, Microsoft Windows 운영 체제 제품군에서 쉽게 사용할 수 있거나 제대로 통합되는 것은 없습니다.

추가 정보

Microsoft SQL Server 7.0에 대한 자세한 내용은 아래를 참조하십시오.

  • Microsoft SQL Server 7.0 온라인 설명서
  • Microsoft TechNet
  • Microsoft BackOffice® Resource Kit
  • Microsoft TechNet에서 사용할 수 있는 다양한 Microsoft Knowledge Base 기사

SQL Server 교육은 Microsoft 교육 서비스 또는 공인 교육 기관을 통해 받을 수 있습니다. 자세한 내용은 Microsoft 고객만족센터(080-985-2000)로 문의하십시오.

이 문서에 포함된 정보는 발행일 현재 논의중인 문제에 대한 Microsoft Corporation의 현재 입장을 대변하는 것입니다. Microsoft는 변화하는 시장 조건에 부응해야 하므로, 이 문서는 Microsoft의 공식적인 약속으로 해석되어서는 안되며 Microsoft는 게시일 이후 제시되는 어떠한 정보에 대해서도 정확성을 보장하지 않습니다.

이 설명서는 오직 정보를 제공하기 위한 것입니다. MICROSOFT는 이 설명서에서 어떠한 명시적이거나 묵시적인 보증도 하지 않습니다.

©1998 Microsoft Corporation. All rights reserved.

Microsoft, BackOffice, BackOffice 로고, Visual FoxPro, Windows, Windows NT, Visual Studio 97은 미국, 대한민국, 및/또는 기타 국가에서의 Microsoft Corporation의 등록 상표 또는 상표입니다.

여기에 언급된 다른 등록 상표 및 상표 이름은 각각 그 소유자의 상표일 수 있습니다.

Microsoft 부품 번호: 098-80763

Posted by 영혼도둑
Study/Mssql2008. 8. 19. 16:26

캐쉬된 실행계획을 보기위해 사용한 쿼리

dbcc freeproccache
로 생성된 모든 실행계획 삭제할수 있음

 select
       cacheobjtype, objtype, usecounts, sql
       from sys.syscacheobjects
      where
       sql not like 'sp_executesql%'
      and sql not like '%sys.%'
      and sql not like '%cache%'

-- Adhoc 쿼리를 count 로 소팅
select
       cacheobjtype, objtype, usecounts, sql
       from sys.syscacheobjects
      where
 --      sql not like 'sp_executesql%'
       sql not like '%sys.%'
      and sql not like '%cache%'
  and objtype='Adhoc'
order by usecounts desc
Posted by 영혼도둑

티스토리툴바