728x90

32비트운영체제에서 MSSQL 2005은 메모리가 기본적으로 2기가만 지원을 하게 됩니다.

이것을 4기가까지 확장 할 수 있도록 하는 방법을 알려드립니다.


 작업환경

OS : Windows Server 2003 Standard Edition 32Bit

SQL : MSSQL 2005 Server Standard Edition

RAM : 4GB


1. 컴퓨터의 실제 메모리는 아래 보시는것처럼 4기가의 메모리를 가지고 있습니다.

사용자 삽입 이미지


2. 하지만 MSSQL2005은 2기가까지만 메모리가 설정되어 있어서 그 이상은 사용을 하지 않는것을 볼 수 있습니다.

사용자 삽입 이미지


아깝지 않습니까? 이 풍부한 하드웨어에서 반밖에 능력을 사용 못하고 있네요.

사용자 삽입 이미지


Lock Pages in Memory

3. MSSQL이 AWE를 사용하려면 Lock Rages in Memory 옵션이 지정된 윈도우 계정으로 MSSQL 엔진을 실행해야 합니다.

32비트 운영체제의 경우 SQL Server에 맞게 AWE를 구성하기 전에 Lock pages in memory권한을 얻어야 합니다.


4. 우선 MSSQL이 어떤 계정으로 구동이 되는지 알아야 합니다.

시작-> 프로그램 -> 관리 도구 -> 서비스 누른다음에 아래의 항목을 찾으면 됩니다.

'db_go' 혹은 'sql_starter'일것이다.

사용자 삽입 이미지


5. Lock Pages in Memory 옵션을 설정해야 합니다.

시작 -> 실행에서 gpedit.msc를 실행합니다.


6. 그룹 정책 개체 편집기가 나오면..

컴퓨터 구성 -> Windows 설정 -> 보안설정 -> 로컬 정책 -> 사용자 권한 할당 -> 메모리의 페이지 잠그기

더블클릭을 합니다.

사용자 삽입 이미지


7. 아까 확인한 'db_go' 사용자를 추가를 해줍니다.

사용자 삽입 이미지


awe enabled 옵션

8. 본격적으로 메모리를 늘리는 방법을 진행하도록 하겠습니다.

AWE(Address WindowingExtionsions)를 사용하여 가상 메모리에 설정된 한계봐 더 큰 실제 메모리를 액세스 할 수 있도록 설정 하도록 하겠습니다.


'awe enabled'는 고급옵션입니다. 'sp_configure' 시스템 저장 프로시저를 사용하여 설정을 변경하려면 'show advanced options'를 1로 설정해야만 'awe enabled'를 변경할 수 있습니다.

사용자 삽입 이미지

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

sp_Configure 'show advanced options',1
RECONFIGURE
go
sp_Configure 'awe enabled',1
RECONFIGURE
go

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


9. MSSQL을 다시 시작해야 합니다.

   MSSQL을 다시 시작 한 후 메모리를 설정하겠습니다.


  총 4기가중 3.5기가만 사용하도록 설정하겠습니다. 1024*3  = 3584

사용자 삽입 이미지

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

sp_Configure 'max server memory',3584
RECONFIGURE
go

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


네트워크 응용 프로그램을 위해 데이터 처리량 최대화

10. 네트워크연결에서 '파일 공유를 위해 데이터 처리량 최대화' 옵션을 선택하면 운영체제에서 파일 시스템 캐시에 I/O페이지를 캐시하여 버퍼링된 I/O작업을 수행하는 응용 프로그램에 우선 순위를 부여합니다. 이 옵션은 정상적인 작업을 수행하는 동안 SQL Server에서 사용가능한 메모리를 제한할 수 있습니다.


제어판 -> 네트워크 연결 -> 로컬 영역 연결 -> 일반 탭 -> Microsoft 네트워크용파일 및 프린터 공유 -> 속성

'네트워크 응용 프로그램을 취해 데이터 처리량 최대화'를 선택하고 확인을 누릅니다.





확인하기

여기까지 준비가 완료되었습니다.


메모리가 3.5기가까지 올라가나 확인하기 위해 무식한 쿼리를 날려보았습니다.


사용자 삽입 이미지

무하하...저 가파르게 올라가는 메모리양을 보십시오!!

성공했습니다.


[출처] MSSQL2005에서 2G이상 메모리 사용하기|작성자 쭈우

'데이터베이스 > SQL Server' 카테고리의 다른 글

mssql txt or csv 파일 데이터 Insert  (0) 2008.04.28
MSSQL CSV Bulk Insert  (0) 2008.04.28
Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환  (0) 2008.04.28
MSSQL2005 사용자 계정추가 방법  (0) 2008.04.28
MSSQL2005에 등장한 각종 순위 함수  (0) 2008.04.28
728x90

현재 데이터베이스에 사용자를 추가합니다.

Transact-SQL 구문 표기 규칙

구문
CREATE USER user_name     [ { { FOR | FROM }
      { 
        LOGIN login_name 
        | CERTIFICATE cert_name 
        | ASYMMETRIC KEY asym_key_name
      } 
      | WITHOUT LOGIN
    ] 
    [ WITH DEFAULT_SCHEMA = schema_name ]
인수
user_name

이 데이터베이스 내에서 사용자를 식별하는 이름을 지정합니다.

LOGIN login_name

데이터베이스 사용자를 생성할 SQL Server 로그인을 지정합니다. login_name은 서버에서 유효한 로그인이어야 합니다. 이 SQL Server 로그인은 데이터베이스에 액세스할 때 생성될 데이터베이스 사용자의 이름과 ID를 얻게 됩니다.

CERTIFICATE cert_name

데이터베이스 사용자를 생성할 인증서를 지정합니다.

ASYMMETRIC KEY asym_key_name

데이터베이스 사용자를 생성할 비대칭 키를 지정합니다.

WITH DEFAULT_SCHEMA = schema_name

서버가 이 데이터베이스 사용자에 대한 개체의 이름을 확인할 때 검색할 첫 번째 스키마를 지정합니다.

WITHOUT LOGIN

사용자가 기존 로그인에 매핑되지 않도록 지정합니다.

주의

FOR LOGIN을 생략하면 새 데이터베이스 사용자가 같은 이름으로 SQL Server 로그인에 매핑됩니다.

DEFAULT_SCHEMA를 정의하지 않은 채로 두면 dbo가 데이터베이스 사용자의 기본 스키마로 사용됩니다. DEFAULT_SCHEMA는 현재 데이터베이스에 없는 스키마로 설정할 수 있으며 가리키는 스키마가 생성되기 전에 설정할 수 있습니다. Windows 그룹, 인증서 또는 비대칭 키에 매핑된 사용자를 생성할 때는 DEFAULT_SCHEMA를 지정할 수 없습니다.

WITHOUT LOGIN 절은 자체 데이터베이스 내에서만 작동할 수 있는 사용자를 만듭니다. 이 사용자는 어떤 로그인에도 매핑할 수 없습니다.

SQL Server 로그인, 인증서 또는 비대칭 키에 매핑되는 사용자의 이름에는 백슬래시 문자(\)가 포함될 수 없습니다.

guest 사용자가 이미 모든 데이터베이스 내에 있기 때문에 CREATE USER를 사용하여 guest 사용자를 만들 수 없습니다. 다음과 같이 CONNECT 권한을 부여하여 guest 사용자를 사용할 수 있습니다.

GRANT CONNECT TO GUEST;
GO

데이터베이스 사용자에 대한 정보는 sys.database_principals(Transact-SQL) 카탈로그 뷰에서 볼 수 있습니다.

사용 권한

데이터베이스에 대한 ALTER ANY USER 권한이 필요합니다.

예

1. 데이터베이스 사용자 만들기

다음 예에서는 암호를 사용하여 AbolrousHazem이라는 서버 로그인을 만든 다음 AdventureWorks에 해당 데이터베이스 사용자 AbolrousHazem을 만듭니다.

CREATE LOGIN AbolrousHazem 
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
USE AdventureWorks;
CREATE USER AbolrousHazem;
GO

2. 기본 스키마로 데이터베이스 사용자 만들기

다음 예에서는 암호를 사용하여 WanidaBenshoof라는 서버 로그인을 만든 다음 기본 스키마인 Marketing을 사용하여 해당 데이터베이스 사용자 Wanida를 만듭니다.

CREATE LOGIN WanidaBenshoof 
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks;
CREATE USER Wanida FOR LOGIN WanidaBenshoof 
    WITH DEFAULT_SCHEMA = Marketing;
GO

3. 인증서에서 데이터베이스 사용자 만들기

다음 예에서는 CarnationProduction50 인증서에서 데이터베이스 사용자 JinghaoLiu를 만듭니다.

USE AdventureWorks;
CREATE CERTIFICATE CarnationProduction50
    WITH SUBJECT = 'Carnation Production Facility Supervisors',
    EXPIRY_DATE = '11/11/2011';
GO
CREATE USER JinghaoLiu FOR CERTIFICATE CarnationProduction50;
GO
참고 항목

'데이터베이스 > SQL Server' 카테고리의 다른 글

MSSQL2005에서 2G이상 메모리 사용하기  (0) 2008.04.28
Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환  (0) 2008.04.28
MSSQL2005에 등장한 각종 순위 함수  (0) 2008.04.28
인덱스 다시 구성 및 다시 작성 mssql2005  (0) 2008.04.28
[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
728x90
순위 함수의 부재로 인해 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 합니다.

Transact-SQL은 다음과 같은 순위 함수를 제공합니다.

 
RANK NTILE
DENSE_RANK ROW_NUMBER

순위함수의 내용은 http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx 링크의 온라인북을 참고하셔도 됩니다.

MSSQL2000에서는 오라클에서 쓰는 ROW_NUMBER() 같은 함수를 쓸 수 없어 유저들의 불만이 많았습니다. 다음과 같이 동적으로 카운트를 하는 서브쿼리를 통하여 비슷하게 구현할 수 있었습니다. 다음의 예제를 통해서 MSSQL2000에서 여러 단계를 거쳐야 했던 방법을 MSSQL2005에서 한 문장으로 해결되는 예제를 소개합니다.

 
create table TEST1 (user_id char(3),num int)

insert TEST1 values('aaa',3)
insert TEST1 values('aaa',2)
insert TEST1 values('bbb',15)
insert TEST1 values('bbb',2)
insert TEST1 values('aaa',6)
insert TEST1 values('aaa',4)
insert TEST1 values('aaa',3)
리스트 1 기초 데이터 입력

구분되는 일련번호가 없는 데이터를 다음과 같은 결과물을 얻으려면 어떻게 해야 되는지에 대한 문제입니다. 데이터는 user_id로 구분이 되어 있고 구분이 없는 num 값으로 구성되어 있습니다.

 
user_id   num1        num2        num3        num4        num5       
------- ----------- ----------- ----------- ----------- ----------
aaa         3             2              6              4               3
bbb        15            2             NULL          NULL           NULL
리스트 2 원하는 결과

MSSQL 2000을 이용할 경우

리스트 2 의 결과 처럼 user_id 별로 num1~num5 컬럼으로 나누어 데이터를 가져오는 것입니다. 각 로우를 구분하기 위해 indentity()함수를 이용해서 임시테이블에 id 라는 일련번호 컬럼을 만듭니다.

 
--------------------------------------------------------
-- 일련번호생성하여 #temp_1 만들기

select identity(int,1,1) id,* into #temp_1 from TEST1
리스트 3 일련번호 생성하기 위해 identity()함수를 이용한 임시테이블 만들기

각 로우가 구분되는 id라는 컬럼이 만들어 졌으므로, user_id 별로 일련번호를 Group by및 case 문을 사용하여 원하는 결과를 쿼리합니다.

 
select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count(*)
 
from #temp_1
where user_id = a. user_id
and id <= a.id) id,a.user_id,num
from #temp_1 a) t1
group by user_id
리스트 4 MSSQL2000의 임시테이블을 이용한 쿼리

 
/* user_id     num1    num2    num3    num4     num5
------- ----------- ----------- ----------- ----------- ----------
aaa             3           2          6           4           3
bbb           15          2        NULL       NULL       NULL

(2개행적용됨)
*/
리스트 5 MSSQL2000의 임시테이블을 이용한 쿼리 결과

MSSQL 2005 ROW_NUMBER( ) OVER 절 이용

ROW_NUMBER ( )  구문

 
ROW_NUMBER ( ) OVER
( [ <partition_by_clause> ] <order_by_clause> )
리스트 6 ROW_NUMBER ( )  구문

인수

<partition_by_clause>
 
from 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다.

<order_by_clause>
 
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.

MSSQL2000의 쿼리에서는 원하는 결과를 위해서 각 로우를 구분하는 id열을 가진 임시테이블을 만들고 또한, 구분되어진 id 열을 user_id에 따라 서브쿼리를 하고, count 하여야 원하는 결과를 구할수 있었습니다. MSSQL 2005에서 등장한 순위 함수를 이용하면, 다음과 같은 한 문장으로 원하는 결과를 구할수 있습니다.

 
select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number()
 
over (partition by user_id order by user_id) id,* 
from TEST1) t1
group by user_id
리스트 7 MSSQL2005의 row_number() 함수를 이용한 쿼리

 
/*
user_id                                                
------- ----------- ----------- ----------- ----------- --------
aaa     3           2           6           4           3
bbb    15         2         NULL      NULL      NULL
경고: 집계또는다른SET 연산에의해NULL 값이제거되었습니다.

(2개행적용됨)
*/
리스트 8 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과

리스트 9 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과
리스트 9 MSSQL2000과 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과 실행계획

임시테이블을 생성하는 로직을 포함하지 않더라도, MSSQL2005의 순위 함수를 사용하는 것이 성능상 유리합니다.

 
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
select identity(int,1,1) id,* into #temp_1 from TEST1

SQL Server 구문분석및컴파일시간:
 
CPU 시간= 13ms, 경과시간= 13ms.
 
테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 13ms.
 

(7개행적용됨)

select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count (*)
from #temp_1
where user_id = a.user_id
and id <= a.id) id,a. user_id ,num
from #temp_1 a) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb    15         2         NULL       NULL       NULL

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'#temp_1'. 검색수8, 논리적읽기수16, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기 수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 1ms.

select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number( )
over (partition by user_id order by user_id) id,*  from TEST1) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb     15          2           null        null        null

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 1ms.
리스트 10 실행통계

이상과 같이 실행통계의 성능을 통하여 MSSQL 2005를 이용하면 성능상 더 좋고 간단한 방법을 알아보았습니다. 다른 랭크 함수 (RANK,NTITLE,DENSE_RANK) 들의 이용과 응용은 여러분의 몫입니다. 

[출처] 제 2강 : MSSQL2005에 등장한 각종 순위 함수 |작성자 윈마스터

'데이터베이스 > SQL Server' 카테고리의 다른 글

Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환  (0) 2008.04.28
MSSQL2005 사용자 계정추가 방법  (0) 2008.04.28
인덱스 다시 구성 및 다시 작성 mssql2005  (0) 2008.04.28
[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28

+ Recent posts

티스토리툴바