컴퓨토피아

OSQL 이란 도스나 콘솔상에서 SQL 문을 실행할수 있게 하는 프로그램이다. 주로 쿼리문 실행할라면 쿼리분석기, EM 을 이용하는데 어떤 경우에는 도스로 쿼리문을 실행해야 할 경우가 생긴다. 이때 이 OSQL 이란 프로그램을 사용한다.

참고로 나의 테스트 환경은 MS-SQL 7.0이다. SQL 2000도 물론 가능한데 6.5 버젼은 어떤지 모르겠다.그리고 OSQL은 MS-SQL 설치하면 자동으로 설치되는 프로그램이다. 환경변수로 잡혀있으므로 그냥 도스상 어느 디렉토리에서도 OSQL 실행 가능하다.(Copy가 명령어 어디서든지 가능한것 처럼)

간단한 OSQL 사용

자 그럼 사용해 보자. 사용법은 간단하다. 일단 도스창을 연 후 아래의 구문을 실행해 보자. 그럼 다음과 같은 결과가 보일것이다.

osql -Usa -P -Q "select * from Northwind..Region"

구문을 살펴보자 아래를 보면 색으로 구분을 줘서 더 살펴보기 쉬울꺼다.그래도 굳이 설명하자면 -U는 사용자, -P는 암호, -Q는 쿼리문이다. 따라서 아래 구문을 해석하면 'sa란 사용자로(암호는 없음) select * from Northwind..Region 쿼리문을 실행해라' 라고 볼 수 있다.

주의 : -U, -P 등 플래그들은 대소문자를 구분한다.!!

osql -Usa -P -Q "select * from Northwind..Region"

방금전 구문은 로컬 DB에 엑서스 했을 경우이다. 다른 네트워크 상의 DB 내용을 보려면 다음과 같이 하면 된다.

osql -Smungchung -Usa -P1234 -Q "select * from Northwind..Region"

위의 구문을 보면 -Smungchung 이 추가됨을 알 수 있다. -S는 서버 플래그를 나타내고 mungchung는 서버명을 나타낸다. 해석해 보자면 'mungchung란 서버에 아이디 sa 암호 1234로 로그인 한 후 select * from Northwind..Region 쿼리문을 실행시켜라' 이다.

파일을 이용한 OSQL

지금까지는 아주 단순한 쿼리문을 실행했는데 쿼리문이 길어진다고 하면 어떻게 할까? 도스창에서 그 길고긴 쿼리들 다 적기는 무리일 것이다. -_-; (오타라도 하나 나면 -_-;; 수정하기 쉽지 않다.)여기 osql에 i라는 플래그가 있다. 입력 파일 플래그를 뜻하는데 이 플래그를 사용하면 파일의 내용을 실행한다.

메모장으로 다음 내용을 입력한 후 test.sql 이라는 파일명으로 저장한다. (확장자는 사실 아무거나 해도 상관없다.test.txt로 해도 아무 상관 없다. 하지만 sql 확장자가 쿼리분석기에서 사용되므로 확장자를 sql로 하였다.)

USE NorthWind
SELECT a.ProductID, b.SupplierID, a.ProductName, b.CompanyName
FROM Products a
JOIN Suppliers b
	ON a.SupplierID = b.SupplierID
WHERE a.ProductID < 4

도스나, 콘솔창을 연 후 다음 구문을 실행한다.

osql -Usa -P -i d:\test.sql

OSQL 응용 범위

osql은 단순히 도스상에서 sql명령어 이용하라고 만들어진것은 아니다. 요새 도스 이용하는 사람도 없는데 왜 이 osql이 MS-SQL 버젼이 높아져도 없어지지 않을까? 솔직히 나도 100%로 왜 그런지는 모르지만 어디서 유용하게 쓰이는지는 안다.얄팍한 경험으로 비춰보면 osql은 다른 시스템간에 인터페이스 할때 이용되거나 백업할때 이용된다. 이 osql을 이용할경우 도스로 배치스크립트 작성해서 매일 매일 해야하는 작업이나 주단위 작업등을 간편하고 빠르게 해결 할수 있다. 나중에 기회가 된다면 이 osql을 좀더 멋지게 이용하는 방법을 적어보겠다.

OSQL 구문

다음은 osql 에서 사용되는 구문이다. 몇가지 빠진 플래그들이 있지만 다음에 나온는 플래그들만 해도 충분할것이다. ( [] 로 묶여진 플래그들은 생략 가능한 플래그다.) 좀더 자세하게 osql 에 대해서 알고자 한다면 도움말을 참고하기 바란다.

osql 
	-U<로그인ID>
	[-d]
	[-Q"쿼리문"]
	[-q"쿼리문"]
	[-c<명령종료자"]
	[-h<헤더들>]
	[-w<열너비>]
	[-s<열구분기호>]
	[-t<제한시간>]
	[-m<오류수준>]
	[-H<워크스테이션이름>]
	[-P<암호>]
	[-S<서버이름>]
	[-i<입력파일>]
	[-o<출력파일>]
	[-a<패킷크기>]
	[-l<제한시간>]

*) 참고도서 : Professional SQL Server 2000 Programming (Wrox.) 437p

출처 : http://www.mungchung.com/xe/lecture/4100

SET NOCOUNT

Transact-SQL 문의 영향을 받은 행 수를 나타내는 메시지가 결과의 일부로 반환되지 않도록 합니다.

구문

SET NOCOUNT { ON | OFF }

비고

SET NOCOUNT 옵션을 ON으로 설정하면, Transact-SQL 문의 영향을 받은 행 수를 나타내는 수가 반환되지 않

습니다. SET NOCOUNT 옵션을 OFF로 설정하면 이 수가 반환됩니다.

SET NOCOUNT 옵션이 ON으로 설정되어 있을 때도 @@ROWCOUNT 함수는 업데이트됩니다.

=> 실행한 결과의 ROW 수를 알고 싶을때 @@ROWCOUNT를 쓰면 성능향상에 도움이 된다.

SET NOCOUNT 옵션을 ON으로 설정하면 저장 프로시저의 각 명령문에 대해 클라이언트에게 DONE_IN_PROC

메시지를 보내지 않습니다. Microsoft® SQL Server™와 함께 제공된 유틸리티를 사용해 쿼리를 실행하

면,SELECT, INSERT, UPDATE 및 DELETE 등의 Transact-SQL 문 끝에 "nn개 행 적용됨"이라는 메시지가 표시

되지 않습니다.

실제 데이터를 많이 반환하지 않는 몇몇 명령문이 포함된 저장 프로시저의 경우, 이 유틸리티를 사용하면 네

트워크 소통량이 크게 줄기 때문에 성능이 눈에 띄게 향상됩니다.

SET NOCOUNT 옵션은 실행시간이나 런타임에 설정되며, 구문 분석 시간에는 설정되지 않습니다.

사용 권한

기본적으로 모든 사용자에게 SET NOCOUNT 사용 권한이 부여됩니다.

예제

다음 예제에서는 적용된 행 수에 대한 메시지가 표시되지 않습니다(osql 유틸리티나 SQL 쿼리 분석기에서 실

행 시).

USE pubs

GO

-- Display the count message.

SELECT au_lname

FROM authors

GO

USE pubs

GO

-- SET NOCOUNT to ON and no longer display the count message.

SET NOCOUNT ON

GO

SELECT au_lname

FROM authors

GO

-- Reset SET NOCOUNT to OFF.

SET NOCOUNT OFF

GO

MS-SQL Server 2008 부터 지원되는 데이터 압축 기능

•압축방식
  –행압축
    •고정길이데이터형식을가변길이데이터형식처럼저장
    •행메타데이터최적화
  –페이지압축

    •행압축
    •접두사및사전

 데이터를 압축하여 보관 하게 되므로 아래와 같은 장단점이 있다

•장점
–물리적인저장공간감소
–메모리내데이터압축
–동일한쿼리의결과반환을위해더적은데이터페이지를참조하므로I/O가감소됨
–어플리케이션수정필요없음

•단점
–CPU 자원사용률이증가함

  압축 전후의 차이는 CPU 사용율이 증가하나 Query속도는 비슷하다.

DW 또는대용량데이터마트의경우CPU 자원에여유가있으면페이지압축검토

행압축이데이터공간을절약하고,CPU 사용률이10% 정도증가할경우행압축검토

•인덱스재작성시평균CPU 자원사용량
  –행압축: 약1.5배필요
  –페이지압축: 약2~5배필요

(실제 구현)

http://msdn.microsoft.com/ko-kr/library/cc280449.aspx

(펌 자료)

SQL Server 2008(Enterprise Edition)에서는 테이블 및 인덱스 모두에 대해 행 압축과 페이지 압축을 지원합니다.

１     행 압축
행 압축을 사용하면 데이터 형식과 연결된 데이터의 물리적 저장소 형식이 변경됩니다. 예를 들어 테이블에  데이터 형식을 BIGINT 를 사용했다고 가정한고 값이 10이 입력되었다고 하면 행 압축을 하면 10을 저장하기 위해서 BIGINT 데이터 형식으 크기 8 바이트가 아니라 값을 기준으로 필요한 바이트 즉 1바이트만 사용하게 됩니다.

２     페이지 압축
페이지 압축은 테이블, 테이블 파티션, 인덱스 및 인덱스 파티션에 대한 압축과 유사합니다. 페이지 압축을 사용할 경우 인덱스의 리프 수준이 아닌 페이지는 행 압축만 사용하여 압축됩니다. 테이블 및 인덱스의 리프수준을 압축은 접두사 압축 후 사전 압축 작업을 진행하여 압축을 합니다.  

가)    접두사 압축

접두사 압축을 진행하면 접두사는 헤더로 이동하고 열 값은 접두사의 참조로 변경됩니다.

접두사 압축전 테이블 페이지	접두사 압축 후 테이블 페이지

나)    사전 압축

사전 압축은 페이지에서 반복된 값을 검색하여 CI 영역에 저장합니다.

접두사 압축 후 테이블 페이지	사전 압축 후 테이블 페이지

페이지 압축은 데이터가 첫 데이터 페이지에 추가되는 시점에 데이터의 행이 압축됩니다. 페이지가 가득 차지 않으면 페이지 압축으로 얻는 이득이 없어 행이 가륵 차면 추가할 다음 행에서 페이지 압축 작업을 시작합니다. 그리고 압축하기 전에 페이지 압축으로 얻는 공간에서CI 구조에 필요한 공간을 뺸 값이 크지 않으면 해당 페이지에 대한 페이지 압축을 하지 않습니다. 
 

CREATE TABLE T1 (c1 int, c2 nvarchar(1024) ) WITH (DATA_COMPRESSION = PAGE | ROW);

 

나)    테이블 변경

ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE | ROW);

 

4    

성능 테스트
가)    개요

①     샘플 데이터 1000만 건에 대해서 압축안함, 행 압축, 페이지 압축에 대해서 데이터 크기 및 Select 쿼리 성능 및 Insert 쿼리 성능 측정

나)    데이터 크기

 

데이터 사이즈는 테이블 구조에 따라 틀리겠지만 Page 압축을 했을떄 48%, Row 압축시77%의 압축율을 보입니다.

가)    Select 쿼리 성능

나)    대량 데이터 Insert 쿼리 성능

단일 데이터 입력은 거의 성능차이가 없기떄문에 대량 데이터 입력으로 쿼리 성능을 측정하였습니다. 대량 데이터 입력(백만건)시 Page 압축은 압축 안함과 비교하여 CPU 자원을 압 235%로 자원을 더 사용하며 Duration 또한 249% 더 증가한다. Write 의 경우는 압축율에 비래하여 50% 정도로 감소한다.

5   결론

테이블 압축을 사용을 하면서 데이터 공간의 절약을 할 수 있으며 또한 데이터 조회시 압축을 함으로써 압축을 복원하는데 CPU 자원을 약간 더 사용을 하지만 Reads 및 Duration 에 많은 성능 이점을 가지고 있습니다. 대량 데이터 Insert 시점에는 다소 성능이 떨어집니다. OLTP 환경에서 데이터 건수가 많고 조회가 많은 테이블에 대해서는 테이블 압축을 적용하면 많은 이점을 얻을 수 있을거라 판단됩니다. 또한 OLAP 환경에서도 대량 데이터를 입력하는 Insert 쿼리는 다소 떨어지지만 저장공간 및 집계 및 조회 성능 이점이 많을거라 판단됩니다.