데이터 삡삡이

--sysadmin 서버 역할의 멤버 확인

SELECT name, type_desc, is_disabled, create_date

FROM sys.server_principals

WHERE type IN ( 'S', 'U', 'R') -- SQL_LOGIN, WINDOWS_LOGIN, SERVER_ROLE

AND IS_SRVROLEMEMBER( 'sysadmin', name) = 1

ORDER BY name

GO

--db_owner 데이터베이스 역할의 멤버 확인

SELECT name, type_desc, create_date

FROM sys.database_principals

WHERE type IN ( 'S', 'U', 'R') -- SQL_USER, WINDOWS_USER, DATABASE_ROLE

AND IS_ROLEMEMBER( 'db_owner', name) = 1

ORDER BY name

GO

--db_backupoperator 데이터베이스 역할의 멤버 확인

SELECT name, type_desc, create_date

FROM sys.database_principals

WHERE type IN ( 'S', 'U', 'R') -- SQL_USER, WINDOWS_USER, DATABASE_ROLE

AND IS_ROLEMEMBER('db_backupoperator', name) = 1

ORDER BY name

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar(256) OUTPUT

AS

DECLARE @charvalue varchar(256)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

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

--해당 서버의 사용자 생성 스크립트 추출

--EXEC sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name    sysname

DECLARE @xstatus int

DECLARE @binpwd  varbinary (256)

DECLARE @txtpwd  sysname

DECLARE @tmpstr  varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name <> 'sa'

ELSE

  DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

IF (@@fetch_status = -1)

BEGIN

  PRINT 'No login(s) found.'

  CLOSE login_curs

  DEALLOCATE login_curs

  RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@xstatus & 4) = 4

    BEGIN -- NT authenticated account/group

      IF (@xstatus & 1) = 1

      BEGIN -- NT login is denied access

        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

        PRINT @tmpstr

      END

      ELSE BEGIN -- NT login has access

        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

        PRINT @tmpstr

      END

    END

    ELSE BEGIN -- SQL Server authentication

      IF (@binpwd IS NOT NULL)

      BEGIN -- Non-null password

        EXEC sp_hexadecimal @binpwd, @txtpwd OUT

        IF (@xstatus & 2048) = 2048

          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

        ELSE

          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

        PRINT @tmpstr

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '

      END

      ELSE BEGIN

        -- Null password

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '

      END

      IF (@xstatus & 2048) = 2048

        -- login upgraded from 6.5

        SET @tmpstr = @tmpstr + '''skip_encryption_old'''

      ELSE

        SET @tmpstr = @tmpstr + '''skip_encryption'''

      PRINT @tmpstr

    END

  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

  END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

--각 데이터베이스에 대한 사용자 계정 정보

DECLARE @tmpstr  varchar (1024)

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TMP_DB_USER_ROLE')

    DROP TABLE TMP_DB_USER_ROLE

GO

CREATE TABLE TMP_DB_USER_ROLE

(

Database_Name VARCHAR(20) NOT NULL

, UserName SYSNAME NOT NULL

, UserRole SYSNAME NOT NULL

, type CHAR(1) NOT NULL

, type_desc NVARCHAR(120) NULL

, default_schema_name SYSNAME NULL

)

GO

EXEC sp_msforeachdb

'

INSERT INTO TMP_DB_USER_ROLE(Database_Name, UserName, UserRole, type, type_desc, default_schema_name)

SELECT  ''?'' AS Database_Name  -- 데이터베이스이름

,dp.name AS UserName            -- 계정이름

,dp2.name AS UserRole           -- 계정역활

,dp.type                        -- S : SQL 로그인

                                -- U : Windows 로그인

                                -- G : Windows 그룹

                                -- R : 서버역활

,dp.type_desc

,dp.default_schema_name         -- 기본스키마

FROM [?].sys.database_principals AS dp

INNER JOIN [?].sys.database_role_members AS dr

        ON dp.principal_id = dr.member_principal_id

INNER JOIN [?].sys.database_principals AS dp2

    ON dr.role_principal_id = dp2.principal_id

WHERE dp2.name LIKE ''%db_jkuser%''

        AND ''?'' != ''master''

'

GO

--데이터 베이스별 사용자 역할 추가

SELECT 'USE ' + Database_Name + '

GO

exec sp_addrolemember '+'[' + 'db_datawriter' +'], '+ UserName + ';'

+ char(13) FROM TMP_DB_USER_ROLE

GO

--데이터 베이스별 사용자 역할 추가

SELECT 'USE ' + Database_Name + '

GO

exec sp_addrolemember '+'[' + 'db_ddladmin' +'], '+ UserName + ';'

+ char(13) FROM TMP_DB_USER_ROLE

GO

--데이터베이스별 로그인

SELECT 'USE' + Database_Name + '

GO

ALTER USER ' + UserName + ' WITH DEFAULT_SCHEMA = ' + default_schema_name  + ';'

+char(13) FROM TMP_DB_USER_ROLE

SELECT Id, CompanyName, contact1, contact2, email1, email2

FROM Company

GO

SELECT Id,

CompanyName,

ContactName,

EmailAddress,

Contact

FROM

(

SELECT Id, CompanyName, Contact1, Contact2, Email1, Email2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2 )

) pvt1 

UNPIVOT

(

EmailAddress For Email IN (Email1, Email2 )

) pvt2

WHERE RIGHT(Contact,1) = RIGHT(Email,1)

중첩 루프(NESTED LOOP)

: 외부 테이블(=선행 테이블)에서 비교할 값 찾음

  → 내부 테이블(=후행 테이블) 루프를 돌면서 하나씩 비교

ex. 두 테이블 데이터 양 많지 않을 때 + 적절한 INDEX가 존재할 때

- 특징

  1) 순차적 처리

: 첫 테이블 필터링 → 두 테이블 간 연결 → 최종 운반 단위 산출

  2) 내부 테이블(=후행 테이블) INDEX 필요(조인을 위한 인덱스)

  3) 메모리 사용량 ↓

  4) 페이지 I/O 발생

: 선행 테이블의 결과 통해 내부 테이블에 접근시 랜덤 I/O 발생

ex.

SELECT *

FROM TBL_1 AS A

   외부 테이블 : 처리한 ROW → 내부 테이블의 INDEX 페이지 접근

INNER JOIN TBL_2 AS B 

내부 테이블 : INDEX 필요

ON A.KEY = B.KEY

→  실행 속도 : 선행테이블 사이즈 * 후행 테이블 접근 횟수

참고 링크 : http://needjarvis.tistory.com/162

1. 가능 하면 컬럼 사이즈는 작게

ex. 일괄 int → int, smallint, tinyint 등 세분화

2. 데이터 베이스 복구 모델(Recovery Model)을 Simple로 변경

3. T610 추적플래그 사용

DBCC TRACEON (610, -1); 

GO

* T610 추적 플래그

  : 최소 로깅, 

    클러스터 인덱스에 삽입하지 않은 행은 최소한으로 기록 됨

  - 대량 로드 작업으로 인해 새 페이지가 할당 되면 새 페이지를 순차적으로 채우는 모든 행이 최소한으로 기록

    (대량 로드 발생 전에 할당 된 페이지에 삽입 된 행은 포드 중에 피이지가 분할 되어 이동한 행과 같이 완전히 로그)

  - 일부 테이블의 경우 최소 로깅이 아닌 전체 로깅 발생할 수 있음 

4. 수동 파티션 구현

:스케줄에 의한 일/월/년별 테이블 생성 → 데이터 이관, 삭제 관리

5. 데이터 베이스 MDF, LDF 크기 조절(Auto Grouwth 발생하지 않게)

: 대용량 I/U/D 작업시 자동 증가가 발생할 경우 성능(속도) 저하 발생

6. 대용량 데이터 UPDATE/DELETE 시, 대상 데이터의 적절한 INDEX 검토

   (Clustered Index 사용)

7. 대용량 데이터 UPDATE/DELETE 시, LOOP 구조로 작업 방식 변경

→트랜잭션의 부하를 분산할 것

*LOOP 구조 작업 프로세스

1) 로그 테이블 생성(작업 처리 전후, 저장해야할 데이터가 있는경우 )

2) 반복 작업을 위한 변수 생성

3) 해당 작업이 반복되어야 할 조건 기재

4) 다른 OBJECT를 참조 해야할 경우, 

중간 대상 데이터를 별도로 저장하여 일괄 작업하는 방식도 고려

5) 반복문 작성

6) 로깅

7) 에러 예외 처리

8. 최소 로깅 모드

: 구문에 WITH(TABLOCK)추가

9. INSERT 할 데이터가 여러 테이블에 분산 되어 있는 경우,

   중간에 별도 Staging DB에 모두 INSERT 한 후, 한꺼번에 INSERT 하는 방법 검토

   (이때, SSIS 패키지 활용하면 성능상 이점 존재)

현상 : 

(동일한 양에 대해)INSERT와 UPDATE 수행 시,

UPDATE가 성능저하를 더 많이 발생 시킬 확률 ↑

★ 로그 기록과 롤백을 위한 로그 기록 방식 차이

- INSERT : 이전 데이터 없음 → 로그 미존재

            작업이 수행된 데이터의 위치 정보만을 알면 롤백 수행 가능(복구 용이)

- UPDATE : 이전 데이터 값 존재 → 이전 데이터를 로그에 기록

∴ 로그 기록에서 UPDATE가 INSERT에 비해 더 많은 데이터를 기록해야 함

→ 디스크 I/O의 증가로 성능 저하

제안 : UPDATE는 INSERT로 변경

         DELETE를 INSERT로 수행

이유 : 직접 로딩과 NOLOGGING 가능

예시 : 전체 크기가 100GB인 테이블 중 50GB에 해당하는 데이터를 삭제 해야 하는 경우

- 직접 DELETE 하는 경우

   : 대량의 행 삭제로 테이블 X-Lock 발생, 대기 세션 발생 가능성 ↑

- DELETE를 INSERT로 변경

   1) 삭제하려는 테이블과 동일 구조의 TMP 테이블 생성

   2) 삭제하려는 테이블로부터 데이터가 삭제된 후 남게 되는 데이터만 조회

   3) 조회한 데이터 TMP 테이블에 INSERT 

(테이블 NOLOGGING 형태로 변경 후 직접 로딩)

   4) 테이블 RENAME

   5) INDEX 생성

* 단, TABLE RENAME 작업 수행 시

  INDEX, GRANT, CONSTRAINT, TRIGGER 등등 고려해야 함 

선택도(Selectivity) - 조건

1. 유일성의 척도

2. 고유성이 높거나 일치하는 값이 적음

: (SELECT) 테이블의 전체 행 중 조회되는 행의 비율

- 조회되는 행의 비율(/전체), 선택도 반비례

조회되는 행의 비율 ↓, 선택도 ↑

조회되는 행의 비율 ↑, 선택도 ↓

- Index 사용 판단의 기준

: 옵티마이저가 컬럼 통계(histogram step)를 사용해 선택도 계산

밀도(Density) - 열

1. 열에서 중복값이 얼마나 자주 발생하는지 측정

2. 열에서 데이터의 고유성

: 1 / (인덱스 키들의 수)

: 1 / (열의 고유값 수)

: (주어진 값에 대한 중복 횟수) / (전체 행 수)

ex. 성별 - 1/2

- 선택도는 일반적으로 밀도와 반비례

- 전체 밀도 ↓, 특정 키에 대한 선택도 ↓

같은 성씨 천씨 선택도 ↑ - 인덱스 사용

같은 성씨 김씨 선택도 ↓ - 인덱스 사용 안함

카디널리티

: 전체 로우 수 * 선택도

→ 쿼리 연산자에 의해 반환되는 행 수

연산자의 카디널리티: 옵티마이저 실행 계획 연산 판단 기준

 ex. Index, Seek, Nested Loop Join, filter

0. 사용자 계정 확인

1) 방화벽 확인

2) 로그인에 계정 확인

3) EXECUTE AS LOGIN ='[계정명]' 실행 여부만 확인

    :계정은 있되 테이블 매핑 정보가 없을 경우 해당 쿼리 실행은 되지만

     이후 데이터베이스 접근이 불가능

4) SP_READERRORLOG 확인

    :로그인 실패에 관한 로그가 기록되어 있음

1. 사용자(USER) 생성

CREATE USER yerielk FROM LOGIN [계정명]

2. 사용자 역할(ROLE) 그룹 추가

EXEC sp_addrolemember 'db_jkuser','[계정명]'

3. 권한부여 

GRANT [대상 행동] DEFINITION ON [대상 테이블] TO db_jkuser

4. 해당 권한 실행 확인

EXECUTE AS LOGIN ='[계정명]'

실행 하고자 하는 쿼리

REVERT;

* 웹 서비스 계정 - GRANT 정상 실행 확인 시 활용

한 컬럼에 저장 된 문자 수가 커서 조회가 어려울 경우

1. SSMS 도구 옵션 메뉴 → 쿼리 결과 - 텍스트로 결과 표시 내용

                                   : 각 열에 표시할 최대 문자 수를 256에서 그 이상으로 변경(최대 8192까지 가능)

2. (8192 이상일 경우)

FOR XML AUTO : 값 주어 XML 형태로 출력