찐빵의 IT World

1. 로그인 계정 

보안 - 로그인 을 누르면 나오는 것들이 바로 로그인 계정이다. SQL Server에 로그온 할 때 사용하는 계정을 말한다.

down 로그인 계정을 더블클릭 - 사용자 매핑 

down 사용자 계정을 down 데이터 베이스에 매핑된 사용자로 체크를 해 주고,

down 사용자 계정으로 로그인 후, down 데이터베이스에 있는 테이블을 SELECT 해 보았지만 SELECT 권한이 없다고 나왔다.

 그래서 위 그림과 같이 아래에 멤버 자격에 db_datareader의 자격을 주었더니 정상적으로 SELECT가 가능했다.

2. USER 계정

로그인 계정과 매핑이 되는 사용자 계정이 데이터베이스에 만들어져 있어야 그 데이터베이스에 접근을 할 수 있다.

위의 경우 사용자 매핑을 한 것이라 할 수 있다.

이렇게 사용자 매핑을 하게 되면 아래 그림과 같이 해당 데이터베이스에 USER가 나타나게 된다. 

Down 데이터베이스 - 보안 - 사용자에 down이라는 USER가 생겼다.

이 down이라는 USER를 더블클릭하여 일반탭을 보면 위에 사용자 매핑 과정에서 했던 대로 저장이 되어 있다.

여기서 db_accessadmin의 역할 멤버 자격을 줘봤더니 로그인 계정이 나타나는 맨아래 있는 보안 - 사용자 - down을 더블클릭하여 속성을 보아도 똑같이 db_accessadmin의 자격이 주어져 있었다.

결론적으로 로그인 과 사용자 두가지는 동기화가 되어 지는 것이다.

 궁금한 것이 몇가지 생겨서 해본 실험

① 실험 1 - 사용자 매핑으로 하지 않고, down 데이터베이스에서 사용자를 직접 만들며 기본스키마를 db_datareader , 권한은 db_ddladmin 으로 주어 테이블을 생성하였다.

결과 : db_datareader.table1 이라는 테이블이 생성되었다. / 하지만 datareader가 기본 스키마라고 해서 select 권한이 있는 건 아니었다. 

② 사용자가 소유한 스키마 부여

사용자가 소유한 스키마에 db_reader를 줘봤다(원래는 공백이었다)

결과 : 그래도 SELECT는 불가능

③ 사용자 - down - 더블클릭 - 보안개체 - 테이블 T에 대한 선택 권한 부여 

결과 : 이 경우에는 SELECT 가능

데이터베이스의 스키마를(을) 소유하며 삭제할수 없습니다.라는 오류가 나올 때

②에서 소유한 스키마를 삭제 해주어야 한다.

방법

해당 데이터베이스 - 보안 - 스키마 에서 소유한 스키마를 더블클릭 - 스키마 소유자를 스키마 이름(기본값)으로 하던지 해서 바꿔준다.

해외 서버에서 작업을 하다보면 한글이 아닌 그 나라의 언어를 사용하는 경우가 있다.

특히 중국이나 대만 서버에서 작업을 할 때 한문이 나오면 정신이 혼미해진다.

에러 메시지를 원하는 언어로 변경하여 보자.

에러 메시지를 한문으로 보여주고 있다. 

▼다음 명령어를 실행 후 확인해보면 에러메시지가 지정한 언어로 나오는 것을 볼 수 있다.

-- 한국어

 SET LANGUAGE KOREAN

 GO

 -- 영어

 SET LANGUAGE ENGLISH

 GO

 -- 일본어

 SET LANGUAGE JAPANESE

 GO

 -- 설정할 수 있는 언어가 저장된 테이블

 SELECT * FROM MASTER.DBO.SYSLANGUAGES

 GO

SET LANGUAGE는 권한에 제약이 없고, 현재의 세션에서만 적용된다.

참고 : http://msdn.microsoft.com/en-us/library/aa259215(v=sql.80).aspx

원문 : http://lovedb.tistory.com/99

배경

어떤 데이터를 DB에서 직접 고치게 되면, 늘 걱정하는 것 중 하나가 완벽하게 모든 영향받는 테이블들까지

잘 고쳤는가 하고 스스로에게 물어보는 것일게다.

그럴 때면, 해당 컬럼 이름이 포함된 모든 테이블을 뒤지는 게 상책이다.

소스

select b.name, a.name 
from sys.all_columns a, sys.all_objects b
where a.object_id = b.object_id and b.type_desc = 'USER_TABLE'
and a.name = 'AccountID' --// 해당 컬럼 이름을 여기에 넣어서 찾으면 된다.

잡담

sys.all_objects 는 참 유용한 녀석이다. 테이블 뿐만 다음과 같은 타입을 모두 취급하니,

시간되면 천천히 찾아 볼 일이다. ㅎㅎ

CLR_STORED_PROCEDURE
SYSTEM_TABLE
VIEW
SQL_TABLE_VALUED_FUNCTION
DEFAULT_CONSTRAINT
SQL_STORED_PROCEDURE
EXTENDED_STORED_PROCEDURE
AGGREGATE_FUNCTION
USER_TABLE
SERVICE_QUEUE
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
CLR_SCALAR_FUNCTION
SQL_SCALAR_FUNCTION
PRIMARY_KEY_CONSTRAINT
 

출처 : http://blog.daum.net/nextkey/122

통계 - 선택도

통계 보기

DBCC SHOW_STATISTICS (테이블이름,인덱스이름) 

실행한 화면

2번째 표의 All Density : 전체 밀도

컬럼 전체의 밀도 즉 이것은  평균적인 밀도이며,  쿼리가 바인드변수 피킹이나, 파라메터 스니핑을 하지 못할 때 사용되는 밀도 입니다. 반면, 바인드변수 피킹이나 파라미터 스니핑이 가능한 상황이라면, histogram 을 이용하게 됩니다. 둘 중 어떤것을 이용 했으냐에 따라 내 쿼리의 선택도 판단은 달라 집니다. 데이터 선택도는 둘 중 무엇을 사용하고 내가 쿼리하는 데이터가 뭐냐에 따라 틀립니다.

밀도가 1에 가까워 지면 거의다 같은 데이터 이고, 밀도가 0에 가까워지면 전체 행에 같은 행이 거의 없다는 말입니다.

만약 밀도가 1이면, 모두 같은데이터이고, 밀도가 0.1 이고 전체행수가 10 이라면 같은 데이터가 (0.1 * 10) 1개 있다는 말입니다.

선택도가 0에 가까운 것이 밀도는 낮고 선택도는 좋다는 말입니다.

출처 및 더 자세한 정보 : http://www.sqler.com/558930

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = 'F:\TempDB\tempdb.mdf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = 'F:\TempDB\tempdb.ldf', SIZE = 1024MB, FILEGROWTH = 100MB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = 'F:\TempDB\tempdb2.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = 'F:\TempDB\tempdb3.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = 'F:\TempDB\tempdb4.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = 'F:\TempDB\tempdb5.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = 'F:\TempDB\tempdb6.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
GO

이렇게 데이터파일 및 로그파일을 수정하면 원래 쓰던 파일은 남아있다. 잘 변경되었나 확인 후 삭제하여도 된다.

 파일삭제

ALTER DATABASE [TEMPDB] REMOVE FILE TEMPDEV6

 현재 사용중인 데이터파일 확인

select file_id, name, type_desc, physical_name, size, is_percent_growth, growth, max_size 
from sys.database_files 
order by name;

또는

EXEC SP_HELPFILE

SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id=DB_ID()

WHERE 절을 빼면 모든 데이터베이스의 기록을 볼 수 있다.

SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트 또는 삭제 작업을 수행할 때마다 인덱스를 자동으로 유지 관리합니다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 정보가 조각화되어 데이터베이스 내에 흩어지게 될 수 있습니다. 조각화는 키 값을 기준으로 하는 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치하지 않을 때 나타납니다. 심하게 조각화된 인덱스는 쿼리 성능을 저하시키고 응용 프로그램의 응답을 늦출 수 있습니다.

인덱스를 다시 구성하거나 다시 작성하여 인덱스 조각화 문제를 해결할 수 있습니다.

INDEX REBUILD : 인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 이렇게 하면 조각화를 제거하고, 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고, 인덱스 행을 연속된 페이지로 다시 정렬할 수 있습니다. ALL을 지정하면 테이블의 모든 인덱스가 단일 트랜잭션으로 삭제되고 다시 작성됩니다.

INDEX REORGANIZE : 인덱스를 다시 구성할 때는 최소한의 시스템 리소스가 사용됩니다. 이때는 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음을 수행합니다. 다시 구성 작업을 수행하면 인덱스 페이지도 압축됩니다. 이때 압축은 기존 채우기 비율 값을 기준으로 수행됩니다.

DBCC INDEXDEFRAG 대신 ALTER INDEX REORGANIZE를 사용하여 인덱스의 리프 수준 페이지를 논리적 순서로 다시 정렬합니다. 이 작업은 온라인 작업이므로 문이 실행 중일 때 인덱스를 사용할 수 있습니다. 또한 작업이 중단되더라도 이미 완료된 작업은 손실되지 않습니다. 이 방법의 단점은 데이터를 다시 구성하는 작업이 인덱스를 다시 작성하는 작업만큼 효과적이지 않다는 것과 통계가 업데이트되지 않는다는 것입니다.

인덱스 조각화 검색하기, 그 후에 비율에 따라서 맞는 작업을 수행

힙의 조각화 줄이기

힙의 익스텐트 조각화를 줄이려면 테이블에 클러스터형 인덱스를 만든 다음 해당 인덱스를 삭제

이렇게 하면 클러스터형 인덱스를 만드는 동안 데이터가 다시 구성되고 데이터베이스에서 사용할 수 있는 공간의 분포를 고려하여 최적화된다. 그런 다음 클러스터형 인덱스를 삭제하여 힙으로 다시 만들면 데이터가 이동하지 않고 최적의 분포를 유지

출처 : http://msdn.microsoft.com/ko-kr/library/ms189858(v=sql.120).aspx

 잠금 에스컬레이션(Lock Escalation)

많은 수의 미세 잠금을 더 적은 수의 큰 잠금으로 변환

동시성 경합 가능성 ↑, 시스템 오버헤드 ↓ 프로세스이다.

● 행 또는 인덱스 키 범위를 잠그는 경우 → 행 또는 키를 포함하는 페이지에도 의도 잠금 배치

● 페이지를 잠그는 경우 → 페이지를 포함하는 더 상위 수준의 개체에 의도 잠금 배치

잠금 에스컬레이션 임계값 - ALTER TABLE SET LOCK_ESCALATION

단일 Transaction-SQL 문이 분할 되지 않은 단일 테이블이나 인덱스에 대해 5,000개 이상의 잠금을 획득한 경우(ex 5천개의 행 잠금)

5천개 이상 잠기면 데이터베이스 엔진은 에스컬레이션을 진행시키려 한다.

 전체 잠금 호환성 매트릭스 

Microsoft SQL Server에서 사용할 수 있는 모든 잠금 모드의 호환성을 확인할 수 있는 표

 INDEX SCAN 과 INDEX SEEK

 SQL Server의 인덱스가 B(anlanced) Tree 구조로 처리

 Index Seek는 B-Tree 구조상의 Root 페이지부터 Leaf Level까지 검색 경로를 따라 수행되는 방법. Leaf Level을 제외한 상위 각 레벨에서 1페이지씩을 검색하게 된다.

 Index Scan은 Leaf Level의 첫번째 페이지부터 데이터 검색을 수행하는 방법. 일반적으로 얘기하는 Table Scan과 동일한 방법이지만, 그 대상이 Leaf Level의 인덱스 페이지라는 것이지요.
 
 그러나, Clustered Index의 경우 Leaf Level이 곧 Data Page이기 때문에, Index Scan이란 용어 대신 Clustered Scan이라는 용어로 표현이 됩니다.