본문 바로가기

SQL Server 2005

(28)
날짜와 복합키를 이룬 IDENTITY 컬럼의 값 순환시키기 날짜 기준으로 테이블을 범위 파티셔닝하는 대량 로그 테이블인 경우, 날짜 컬럼과 IDENTITY속성의 컬럼을 PRIMARY KEY로 설정하게된다. 문제는... IDENTITY속성 컬럼의 데이터 타입이... 보통은 int일텐데... int로 커버할 수 있는 약 21억건 - 양수만 따졌을 때 - 이 부족하다라고 주장하는 동료가 있을 수 있다는 점이다. (하긴... 하루 115만건의 레코드를 5년간 보관하면 21억건 정도가 되긴한다.) 우리가 해결해야 하는 문제는 대충 이렇다. 1. 날짜를 기준으로 테이블을 범위 파티셔닝하고, 슬라이딩 윈도우를 구현한다. 2. 모든 테이블은 식별자를 가진다. 단, 날짜만으로는 Primary Key가 될 수 없다. 3. 가능한한 Primary Key 사이즈가 작았으면 좋겠다. ..
diskpart.exe를 사용한 파티션 오프셋 설정으로 디스크 I/O 최적화 하기 MS SQL에서 데이터를 저장하는 최소 단위는 page이고 8개의 페이지가 1개의 extent를 이룬다. 여기서 page의 크기는 8KB이므로 1개의 extent는 64KB인데, extent는 디스크 I/O의 최소 단위가 된다. 즉, SQL서버의 세계에서 디스크를 통한 데이터 입출력은 64KB단위라는 것. 이와 관련하여 RAID 컨트롤러의 stripe block size, 파티션 시작 오프셋, 파티션 볼륨의 allocation unit size를 어떻게 셋팅할 것인가하는 문제가 생기는데... Default 설정 값으로는 SQL Server에 최적화되지 않기 때문이다. 일단 파티션 시작 오프셋값을 조정해야하는 이론적 근거는 다음과 같다. OS의 파티션 시작은 Default로 디스크의 64번째 섹터부터이다...
SQL Server의 procedure cache 크기 SQL 서버는 두가지 타입의 cache에 메모리를 할당한다. 1. procedure cache 2. buffer cache procedure cache는 실행한 stored procedure의 쿼리 실행 계획이 저장되는 공간이고, buffer cache는 디스크로부터 읽어 들인 데이터를 저장하는 공간이다. 그런데 SQL Server를 세팅하고 관리할 때 SQL Server 인스턴스에 할당할 전체 메모리의 크기는 설정할 수 있지만, 위 두가지 메모리 영역 별로 메모리를 할당 하는 방법 및 수단은 존재하지 않는다. (심지어 두 영역에 어떤 비율이나 공식이나 방법으로 메모리가 할당되는지에 대한 문서조차 없다.) 각설하고... SQL Server가 메모리를 할당하는 방식은... procedure cache에 할..
FOR XML PATH Concatenation을 사용하는 재치있는 방법 FOR XML PATH 를 사용해서 서로 다른 Row에 있는 문자열을 하나로 합치는 팁. 예를 들어... 원본 테이블이 아래와 같고 a b c 1 1 가 2 1 나 3 1 다 4 2 라 5 2 마 6 3 바 원하는 결과는 아래와 같다고 할때, b c 1 가,나,다 2 라,마 3 바 FOR XML PAHT와 상관 서브 쿼리를 사용하면 아래 쿼리 한번으로 결과를 얻을 수 있다. 12345678SELECT b, STUFF(( SELECT N',' + c FROM sourceTable WHERE b = st1.b FOR XML PATH('') ), 1, 1, N'') AS cFROM sourceTable st1GROUP BY b;cs 좀 더 실전적인 예제 : SQL Server magazine 2008.07 p..
sp_procedure_params_rowset sp_procedure_params_rowset ADO 2.5 기준으로 설명을 하자면... (ADO .NET에서도 동일한 원리로 같은 문제가 발생한다.) Command개체의 Parameters컬렉션에 포함된 Refresh 메서드를 사용해 sp를 호출하는 경우, "sp_procedure_params_rowset"이라는 system sp가 먼저 실행되는데.. 이는 호출 할 sp의 메타 데이터를 리턴해 주는 역할을 한다. Refresh 메서드가 주는 잇점이란... 코딩의 간결함이다. 즉, 호출할 sp의 파라미터를 코딩할 때 데이터 타입을 정의하지 않아도 DB로부터 메타데이터를 리턴 받아 자동으로 바인딩해 주기 때문이다. 문제는... 코딩의 편의로 인해 시스템 성능이 희생당하는 것. 물론 성능 문제를 수치로 얘..
DMV를 사용하여 sp_who2 + DBCC Inputbuffer(spid) 작업을 한번에 하기 SELECT A.session_id, B.text FROM sys.dm_exec_connections A CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) B SELECT ISNULL(D.text, '') AS SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) AS [Status], A.login_name AS [Login], ISNULL(A.host_name, ' .') AS HostName, ISNULL(CAST(C.BlkBy AS varchar(10)), ' .') AS BlkBy, DB_NAME(B.Database_ID) AS DBName, B.command, ISNULL(B.cpu_time..
데이터베이스 명명 규칙 - Database Naming Conventions 영국계 회사로 이직하고 보니 모든 문서를 영문으로 작성해야하는 부담이 생겼다. ㅡㅡ;; ABSTRACT This document defines the naming rules of database objects. CONVENTIONS All Objects 1. Do not use numbers, spaces, reserved keywords and special characters in the DB object name. Databases 1. For all parts of the database name use Pascal Case. 2. Examples § Account, Statistics, Sale Tables 1. Table names should be plural. 2. For table nam..
ER Studio 사용하기 회사에서 ER/Studio와 관련한 tech talk을 진행 하면서 작성했던 내용이다. 1. 환경 설정 (SQL 2005 기준) Tools > Options를 선택하고 아래와 같이 설정한다. 물론 회사마다 개인마다 차이가 있으니 아래 설정이 꼭 바람직하다고 할 수는 없다. - Entity 이름의 최대 길이 = 128자 (SQL서버의 sysname타입은 nvarchar(128)이기 때문) - Entity에 Attribute를 추가했을 때 기본 적용할 데이터 타입은 integer로 한다. - NULL은 허용하지 않는 것을 기본으로... - 데이터 타입과 NULL허용여부는 Logical탭과 동일 - 기본 데이터 베이스를 SQL 2005로 선택 - 이부분은 사람마다 편하게 느끼는게 다를 듯... 모르면 아래처럼..