(─━┘_└━─)/ 머리만1톤(Head1Ton)

sys.dm_io_virtual_file_stats 동적 관리 뷰는 데이터 및 로그 파일에 대한 I/O 통계 정보를 제공합니다. 사용자들이 어떤 파일에 대하여 읽기나 쓰기를 수행하기 위하여 기다린 시간을 제공하므로 이 뷰를 사용하면 어떤 파일들에 대하여 I/O가 많이 발생하는지 확인할 수 있습니다. I/O로 인한 성능 저하가 의심되는 경우에는 sys.dm_io_virtual_file_stats 뷰에서 io_stall 값을 점검할 것을 권고합니다.

[구문] sys.dm_io_virtual_file_stats(

{ database_id | NULL }

, { file_id | NULL }

)

 


모든 데이터베이스의 모든 파일들의 I/O 정보 확인하는 부분 중에 파일 이름 구하는 것에 약간의 오류가 있어서 쿼리 수정한것 댓글 답니다.

select DB_NAME(a.database_id) as DBName,
a.File_ID,
b.name,
a.IO_Stall,
a.Num_Of_Reads, a.Num_Of_Writes,
a.Num_Of_Bytes_Read, a.Num_Of_Bytes_Written
from sys.dm_io_virtual_file_stats(null, null) a
inner join sys.master_files b on a.database_id = b.database_id and a.file_id = b.file_id

카탈로그 뷰는 테이블, 인덱스 등과 같이 데이터베이스 엔진에서 사용하는 정보들을 조회할 수 있는 읽기 전용 뷰입니다. 카탈로그 뷰는 각 데이터베이스의 sys 스키마에 포함되어 있습니다. 또한 SQL Server 2005에서는 이전 버전에서 사용되었던 여러 시스템 테이블과의 호환성을 유지하기 위해 호환성 뷰 집합이 있습니다.

sp_configure를 사용하면 서버의 구성 옵션과 구성 옵션의 최소값과 최대값, 설정된 대상 값, 설정값을 확인할 수 있습니다. 서버의 일부 옵션은 고급으로 지정되어 있으며 고급 옵션을 확인 또는 변경하려면 ‘show advanced options’ 옵션이 1이어야 합니다.

[구문] sp_configure [ [ @configname = ] 'name' ]

[ , [ @configvalue = ] 'value' ]

sys.configurations 카탈로그 뷰를 조회하면 각 구성 옵션에 대한 정보를 자세하게 확인할 수 있습니다.

서버 인스턴스에 대한 속성 정보를 반환하는 SERVERPROPERTY 힘수를 사용합니다.

SERVERPROPERTY 함수의 ServerName 속성과 @@SERVERNAME은 비슷한 정보를 반환하지만 결과가 다를 수도 있습니다. ServerName 속성은 Windows 서버 및 지정된 SQL Server 인스턴스에 대한 인스턴스 정보를 제공하고, @@SERVERNAME은 현재 구성된 로컬 서버 이름을 제공합니다. 만일 기본 서버 이름을 변경하지 않았다면 ServerName 속성과 @@SERVERNAME은 같은 정보를 반환합니다.

[구문] SERVERPROPERTY ( propertyname )

sp_dropserver, sp_addserver 저장 프로시저를 사용하여 로컬 서버 이름을 변경한 경우에는변경된 로컬 서버 이름 변경을 반환합니다.

SQL Server에 로그인하면 기본적으로 연결되는 데이터베이스를 기본 데이터베이스라고 합니다. 기본 데이터베이스란 특정 로그인이 SQL Server에 로그인하면 자동으로 지정한 데이터베이스로 연결되도록 하는 것을 말합니다.

SQL Server 2000에서는 최초에 로그인을 생성할 때 기본 데이터베이스를 지정할 수 있으며, 기본 데이터베이스를 변경하고자 하는 경우에는 sp_defaultdb를 사용하면 됩니다.

SQL Server 2005에서도 최초에 로그인을 생성할 때 CREATE LOGIN 문에 DEFAULT_DATABASE = 데이터베이스명 절을 지정함으로써 기본 데이터베이스를 지정할 수 있습니다. 그리고 추후에 기본 데이터베이스를 변경하고자 하는 경우에는 ALTER LOGIN 문을 사용해야 합니다. sp_defaultdb는 다음 버전의 Microsoft SQL Server에서 제거될 예정이므로 대신 ALTER LOGIN을 사용하라는 권고 사항이 SQL Server 2005 온라인 설명서에 기술되어 있습니다.


예:
ALTER LOGIN TestUser
WITH DEFAULT_DATABASE = TestDB;
GO

Windows Server 2003 이상의 운영체제에서 SQL Server 2005를 운영하는 경우에는, SQL Server가 Windows의 암호 정책을 사용할 수 있습니다.

- 로그인 생성 및 수정 시 CHECK_POLICY 옵션을 사용하면, Windows의 그룹 정책 사용 여부를 설정할 수 있습니다.
- 로그인 생성 시 MUST_CHANGE 옵션을 사용하면, 사용자가 최초 로그인 시에 암호를 변경하도록 설정할 수 있습니다.


암호 정책 변경하기 예제:

1. 관리 도구 --> 로컬 보안 정책(secpol.msc)을 실행한 후, 계정 정책 --> 암호 정책을 선택합니다.

2. 최소 암호 길이를 4문자로 설정합니다.

3. 만일 지정한 암호가 4자 이하인 경우에는 다음과 같이 오류가 발생됩니다.

CREATE LOGIN dbadmin WITH PASSWORD = 'ad4';
GO
/*
메시지15116, 수준16, 상태1, 줄1
암호의 유효성을 검사하지 못했습니다. 암호가 너무 짧아서 Windows 정책요구사항에 맞지 않습니다.
*/

4. 만일 CHECK_POLICY = OFF 옵션을 이용하여 수행하면, 오류 없이 성공적으로 생성됩니다.

CREATE LOGIN dbadmin WITH PASSWORD = 'ad4', CHECK_POLICY = OFF;

GO


최초 로그인 시 암호를 변경하도록 설정하기 예제:

1. MUST_CHANGE 옵션을 이용하여 사용자가 최초 로그인 시 암호를 변경하도록 설정합니다.

CREATE LOGIN dbadmin WITH PASSWORD = 'adc1234' MUST_CHANGE, CHECK_EXPIRATION = ON;
GO

2. dbadmin으로 로그인 시 다음과 같은 메시지를 포함한 "암호 변경" 창이 나타납니다. 새 암호와 암호 확인 란에 새로운 암호를 입력해야 로그인이 가능하게 됩니다.

암호가 만료되었습니다. 로그온하려면 다른 암호를 입력해야 합니다.



암호의 복잡성 및 암호 만료:
Windows 2003 이상의 환경에서 암호 정책을 이용할 경우, 다음과 같은 기준의 암호 복잡성을 준수해야 합니다.

*   암호는 사용자 계정 이름의 일부 또는 전체를 포함할 수 없습니다.

*   암호의 길이는 최소 6자 이상이어야 합니다.

*   암호는 다음 4가지 범주 중 세 범주의 문자를 포함해야 합니다.

-          알파벳 대문자 (A ~ Z)

-          알파벳 소문자 (a ~ z)

-          기본 숫자 10가지 (0 ~ 9)

-          영숫자가 아닌 문자(예 : !, $, #, %)

암호 만료 정책을 사용하면 특정 기간 경과 시 사용자에게 기존 암호를 변경할 것과 암호가 만료되어 계정을 사용할 수 없게 됨을 알려 주게 됩니다.

로그인 암호를 잊어 버려서 암호를 다시 설정해야 하는 경우가 종종 발생합니다.

SQL Server 2000에서는 sp_password라는 시스템 저장 프로시저를 사용하여 암호를 변경하셨을 겁니다. sp_password는 다음 버전의 Microsoft SQL Server에서 제거될 예정이므로 sp_passsword를 사용하는 응용 프로그램은 수정하고 sp_password 대신 ALTER LOGIN을 사용하라고 온라인 설명서에 나와 있습니다.

ALTER LOGIN 문을 사용하여 계정의 암호를 변경할 수 있습니다. 이전 암호를 알지 못하는 상태에서도 로그인의 암호를 변경하는 것이 가능합니다. 그렇지만 이전 암호를 알지 못하는상태에서 로그인의 암호를 변경하기 위해서는 사용자가 CONTROL SERVER 권한을 가지고 있어야 합니다. 이 권한을 가지고 있는 경우에는 OLD_PASSWORD를 지정하지 않아도 암호를 변경할 수 있습니다.

[중요] Windows Server 2003 에서 알려진 문제로 인해 LockoutThreshold에 도달한 후 잘못된 암호 수를 다시 설정하지 못할 수도 있습니다. 이 경우 후속 로그인 시도가 실패하면 즉시 잠금이 발생할 수 있습니다. 간단하게 CHECK_POLICY = ON 앞에 CHECK_POLICY = OFF를 설정하면 잘못된 암호 수를 수동으로 다시 설정할 수 있습니다

ALTER LOGIN 문을 사용하여 SQL Server 로그인 계정의 속성을 변경할 수 있습니다.

어떤 속성들을 변경할 수 있는지는 다음에 있는 구문을 참조하시면 쉽게 알 수 있습니다.

구문:
ALTER LOGIN login_name
    {
    <status_option>
    | WITH <set_option> [ ,... ]
    } <status_option> ::=
        ENABLE | DISABLE

<set_option> ::=           
    PASSWORD = 'password'
    [
            OLD_PASSWORD = 'oldpassword'
      | <secadmin_pwd_opt> [ <secadmin_pwd_opt> ]
    ]
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | NAME = login_name
    | CHECK_POLICY = { ON | OFF }
    | CHECK_EXPIRATION = { ON | OFF }
    | CREDENTIAL = credential_name
    | NO CREDENTIAL
  <secadmin_pwd_opt> ::=
    MUST_CHANGE | UNLOCK

ALTER LOGIN 문을 사용하여 다음과 같은 작업이 가능합니다.

1. 특정 로그인을 활성화 또는 비활성화할 수 있습니다.
일시적으로 로그인을 사용하지 못하도록 할 필요가 있을 때는 로그인을 삭제하는 대신 비활성화하는 방법을 사용하면 편리합니다.

예: TestUser 라는 로그인을 비활성화합니다.

ALTER LOGIN TestUser DISABLE;
GO
 

2. 로그인 암호를 변경할 수 있습니다. 암호 변경은 DBA가 반드시 알 필요가 있는 내용이므로 별도로 다루겠습니다.

예: TestUser 라는 로그인의 암호를 변경합니다.
 
ALTER LOGIN KittiLert WITH PASSWORD = '3948wJ698FFF7';
GO
 

3. 로그인 이름을 변경할 수 있습니다.

예 : TestUser 로그인의 이름을 NewUser로 변경합니다.

 
ALTER LOGIN TestUser WITH NAME = NewUser;
GO
 

4.  로그인으로 SQL Server에 연결했을 때 기본적으로 연결되는 디폴트 데이터베이스르 변경할 수 있습니다.

5. 잠긴 로그인을 잠금 해제하도록 변경할 수 있습니다.

6. MUST_CHANGE, CHECK_POLICY 또는 CHECK_EXPIRATION 옵션을 활성화 또는 비활성화할 수 있습니다.

7. 로그인을 자격 증명에 매핑할 수 있습니다.

로그인과 사용자

SQL Server에 접근하기 위해서는 로그인 계정이 필요하고, 데이터베이스에 접근하기 위해서는 사용자(user)가 필요합니다.

권한

사용자는 권한을 받아 작업을 수행할 수 있습니다. 권한에는 문장을 실행할 수 있는지에 따라 권한을 제한하는 명령문(Statement) 사용권한과 테이블, 색인, 뷰, 프로시저에 따라 권한을 제한하는 개체(Object) 사용권한이 있습니다.

역할

역할은 로그인 또는 사용자들의 집합이며 각각의 역할에는 권한이 설정되어 있습니다. 각 사용자에게 직접 권한을 주는 것 보다는 필요한 권한이 있는 역할에 사용자를 추가시키는 것이 좋습니다. 시스템에서 미리 정의된 역할에는 로그인이 사용할 수 있는 서버 역할과 사용자가 사용할 수 있는 데이터베이스 역할이 있습니다

보안 주체와 보안 개체

앞에서 sys.dm_db_index_usage_stats 동적 관리 뷰에 대하여 설명하였습니다. sys.dm_db_index_usage_stats 동적 관리 뷰 외에 sys.dm_db_index_operational_stats 라는 동적 관리 함수가 있습니다.
이 sys.dm_db_index_operational_stats 는 현재 데이터베이스 또는 지정한 데이터베이스에 있는 테이블 또는 인덱스의 파티션별 I/O, 잠금, 래치 및 액세스 작업 방법에 대한 정보를 제공합니다.

sys.dm_db_index_operational_stats를 사용하여 사용자가 테이블, 인덱스 또는 파티션을 읽거나 쓰기 위해 대기해야 하는 시간을 추적하고 상당한 I/O 작업 또는 문제가 발생하고 있는 테이블이나 인덱스를 식별할 수 있습니다. 즉, 이러한 열을 사용하여 경합이 발생하고 있는 영역을 식별할 수 있습니다.

[구문] sys.dm_db_index_operational_stats (

    { database_id | NULL }

    , { object_id | NULL }

    , { index_id | NULL | 0 }

    , { partition_number | NULL }

)

실제 많은 수의 운영 환경들에서 테이블에 만들어져 있는 인덱스들의 일부는 실제로는 사용되지 않는 인덱스입니다. 실제로 사용되지 않는 인덱스를 관리하는 것은 불필요한 오버헤드만 유발하므로 인덱스 사용여부를 확인하고 실제로 사용되지 않는 불필요한 인덱스들을 구분해 내고 삭제하는 것이 필요합니다.
SQL Server 2005에서 새롭게 제공되는 DMV 중에서 sys.dm_db_index_usage_stats 동적 관리 뷰를 사용하여 사용된 인덱스와 그 빈도를 알 수 있습니다. 이 뷰를 사용하여 응용 프로그램에서 사용하지 않는 인덱스를 확인할 수 있으며, 또한 유지 관리 오버헤드를 유발하는 인덱스를 확인할 수도 있습니다.

SQL Server 서비스를 시작할 때마다 카운터는 빈 상태로 초기화되며, 데이터베이스가 분리되거나 종료될 때마다(예: AUTO_CLOSE가 ON으로 설정된 경우) 데이터베이스와 관련된 모든 행이 제거됩니다. 그러므로 DMV에 대한 스냅샷을 영구 테이블에 복사해 둠으로써 SQL Server가 재시작되기 이전의 데이터도 관리하는 것을 권고합니다.