Untitled

MongoDB 스키마 디자인의 함정

역주: http://d.hatena.ne.jp/hiroppon/20130326/1364265864 의 글을 번역.
위의 글은 다음 글을 일본어로 번역하면서 코멘트를 추가한 것임
http://blog.serverdensity.com/mongodb-schema-design-pitfalls/

MongoDB를 간단하게 시작할 수 있는 이유 중 하나는 “스키마 디자인을 생각하지 않아도 된다”는 것이다. 단순히 데이터를 넣고 나중에 쿼리하면 된다. 따라서 초기에 개발을 시작할 때 편리하며 나중에 문서의 구조를 변경할 때에도 이점이 된다. 그러나…

Schemaless가 스키마 설계를 하지 않아도 된다는 의미는 아니다!

따라서 다른 데이터베이스들처럼 성능 향상 및 확장 가능하게 하기 위해서는 역시 스키마 디자인을 고려해야 한다.
이러한 점은 다음의 기사에서도 언급되고 있다.
본 글에서는 MongoDB의 스키마 설계의 함정에 대해 다룬다.

문서를 키우지 않는다

기존 문서에 새로운 필드를 추가하거나 크기를 크게 하거나 (필드 이름 + 값) 하면 원래의 영역에 맞지 않게 되어 문서를 데이터 파일의 빈 영역으로 이동해야 한다. 이 작업은 데이터가 다시 쓰이게 되므로 성능에 영향을 준다. 또한 이러한 일이 대량으로 발생하면 MongoDB는 문서에 대한 여백 비율 (padding factor)을 조정한다. 따라서 문서는 기본적으로 더 많은 공간을 소비하게 된다. 그러나 원 영역 내에서의 (in-place) 업데이트는 빠르다.

프로파일러를 사용해서 문서가 이동된 것을 감지 할 수 있다. 만약 moved 필드가 true인 경우 문서는 재작성 (이동) 된 것이다. 이것을 시정하면 성능 개선의 여지가 있다. (아래)
MongoDB 문서를 디스크에 저장할 때 어느 정도의 패딩을 한다. 따라서 추후 문서를 업데이트할 때 약간의 크기 증가가 있었다고 해도 in-place update가 가능하다. 패딩 양은 문서 크기에 대한 비율로, 예를 들면 1.001과 같이 설정되어 있다.

위와 같이 업데이트에 따른 문서 이동이 많으면 이 값이 커진다 (1.0 ~ 2.0, default : 1.0 = nopad). 만약 이 값이 2.0이라면 문서를 저장할 때는 그 2배의 공간이 필요하게 된다. 당연히 이렇게 되면 디스크나 메모리의 소비가 효율적이지 않고 성능이 떨어진다.

이전에는 이 값은 커지기만 하고 완전히 제어할 수 없었지만, 2.2에서 compact나 각종 tool 등으로 지정할 수 있도록 했다. 또한 2.4 소스에서 업데이트시 축소 방향으로도 조정이 들어가 있는 것으로 보인다. (별로 신경 쓰지 않아도 되게 바뀌었는지도 모른다 → 확인 필요)

프로필의 예
```
// 이 DB의 프로필 (레벨 2)를 활성화
PRIMARY> db.setProfilingLevel (2);

// 필드 추가
PRIMARY> db.testcol.update ({_id : ObjectId ( "514ac4666bff1b5721ca1bc1")}, {$ set {value3 : "a"}})

// 프로파일 결과는 system.profile에 저장되어 있다

PRIMARY> db.system.profile.find ({op : 'update'})
{
     "op": "update" 
     "ns": "testdb.testcol"
     "query": { "_id": ObjectId ( "514ac4666bff1b5721ca1bc1")}
     "updateobj": { "$ set": { "value3": "a"}}
     "idhack": true, 
     "moved": true,
     "nmoved": 1,
     "nupdated": 1,
     "keyUpdates": 0,
     "numYield": 0,
     "lockStats": { "timeLockedMicros": { "r": NumberLong (0) "w": NumberLong (394)} "timeAcquiringMicros": { "r": NumberLong (0) "w": NumberLong (8 )}}
     "millis": 0,
     "ts": ISODate ( "2013-03-22T04 : 30:35.504 Z")
     "client": "192.168.159.142", "allUsers": [{ "user": "crumb", "userSource": "admin"}], "user": "crumb @ admin" 
}
```
paddingFactor의 증감 확인

매번 update마다 평가되어 적당한 값으로 조정되는 모양.

또한 compact로 지정할 수 있는 paddingFactor는 compact를 위한 일시적인 값이며, 위의 paddingFactor에 관계없이 padding한다. 또한 범위도 1.0 ~ 4.0이다. 머리가 좋아진 반면 예측이 어려워진 것일까?
필드 연산자를 사용

전체 문서를 업데이트하지 않고 필드 지정을 사용하여 필요한 필드만 업데이트할 수 있다. 새 문서 전체를 전송하는 대신 업데이트할 필드에 set 및 remove 연산자를 지정할 수있다. 또한 increment 와 같이 특정 작업을 수행하는 연산자도 몇 가지 있다. 이들은 데이터베이스 통신뿐만 아니라 데이터 파일 처리면에서도 매우 효율적이다.

BSON 데이터 형식에 유의

문서는 필드의 데이터 형식을 변경하기만 해도 이동될 수 있다. 데이터를 저장할 때 그 형식도 고려할 필요가 있다. 예를 들면 (float) 0.0 (int) 0으로 한 경우 BSON data type이 달라서 문서의 이동을 일으키는 경우가 있다.

문서를 사전 할당(preallocate)한다

나중에 문서의 필드가 추가될 것을 알고 있다면 임시 값으로 미리 할당해 두었다가 나중에 $set 연산자로 실제 값으로 업데이트하여 문서의 크기가 증가하는 것을 막을 수 있다. 이때 위와 같이 데이터 형식을 명심해야 한다. 특히 null은 다른 데이터 형식이다!

그러나 사전 할당은 랜덤하게 일으켜야 한다. 갑자기 대량의 문서 insert가 발생하면 시스템에 부담이 될 것이다. 한번에 모든 작업이 일어나게 하지 말고, 미리 적절한 시간대에 작업해두는 것이 좋을 것이다.

여기서는 한 문서의 필드를 미리 할당할 뿐만 아니라 앞으로 사용할 예정인 레코드도 어느 정도 사전 배분 해두라고 하는 것 같다. 확실히 피크 시간대에는 “쓰기 작업이 한계다”라거나 “데이터 파일 추가 (2GB)”와 같은 갑작스런 I/O가 문제다 라고는 생각할 수 있지만, 솔직히 별로 어려운 점은 없었다.

단지 (나의 환경에서도) 새 insert보다 (in-place) update 쪽이 (모든 필드를 업데이트했다고 해도) 배 가까이 빠르기 때문에 곤란한 사람은 해볼만한 가치가 있을 것 같다.

필드 이름도 공간을 차지한다

수백만개 정도의 레코드 수에서는 별로 중요하지 않다. 그러나 수십억의 레코드를 처리할 경우 인덱스 크기에도 꽤나 영향을 끼친다. 디스크 용량은 상관없다 해도 메모리는 다르다. 데이터가 가능한 메모리에 올라와 있기를 바랄 것이다.

_id를 활용할 것

모든 컬렉션은 _id 인덱스를 가지고 있다. 따라서 이것을 어플리케이션 용도의 고유 인덱스로 활용할 수 있다. 예를 들면 (자사의) Server Density 용 서버 모니터링 정보로 날짜, 계정 ID, 서버 ID와 같은 구조를 가진 컬렉션의 경우, 문서를 확인하기 위해 이러한 필드 복합 인덱스 (날짜, 계정 ID, 서버 ID) 대신 _id를 사용하여 쿼리 할 수 있다.

당연하다면 당연한 것이지만 애플리케이션 차원에서 _id를 적극적으로 쓰세요- 라는 것이다. 귀가 아플 정도

covered index를 사용하고 있는가?

만약 쿼리를 실행할 때 사용할 인덱스에 모든 반환 필드가 포함 된 경우 쿼리는 인덱스 참조로 끝나며 MongoDB는 데이터 파일을 읽을 필요가 없다. 따라서 성능을 최대한 높이기 위해 모든 데이터를 메모리에 올릴 필요성이 줄어든다. (역주: 인덱스만 메모리에 올려도 된다는 말) 이것을 covered query라고 한다. covered query를 사용하면 explain 결과가 indexOnly = true 로 나온다.

일반적인 RDBMS 에도 비슷한 기능이 있기 때문에 MongoDB가 특별한 것은 아니고 오히려 일반적인 방법. 적극적으로 사용할 것.

컬렉션이나 데이터베이스를 최대한 잘 사용한다

여러 컬렉션 및 데이터베이스에 데이터를 분할하는 것을 고려한다.

컬렉션의 drop은 모든 문서를 remove하는 것보다 훨씬 빠르다. 이것은 데이터 보존 기간을 다룰 때 도움이 된다. 예를 들어 일 단위로 컬렉션을 나누어 대량의 컬렉션을 다루더라도 일반적인 처리 방법과 크게 다르지 않다. 단지 네임스페이스의 제한 등 몇 가지 주의가 필요한 항목이다.

데이터베이스 수준 잠금이 있으므로, 데이터베이스를 분리하여 부하 집중을 피할 수 있다. 예를 들어 인증 DB에서 처리량이 높은 로그 DB를 분리할 수 있다.

전체적으로 약간 알기 어려워서 대강 설명

일마다 컬렉션을 나누어두면 보관 기간이 만료된 문서의 삭제 처리는 컬렉션 drop으로 끝이므로 빨라진다. 컬렉션이 늘어나고 복잡해지는 것은 별거 아니니까 앱으로 처리하라고..

네임스페이스의 제한

- nssize 옵션에서 네임스페이스 의 데이터 파일 크기를 지정할 수 있다. 이 크기는 데이터베이스에 저장할 수있는 컬렉션 수와 직결된다. 보통 초기 값 16M으로 충분하지만 (나는 4M에서 사용함) 수만의 컬렉션을 만들고 싶다면 튜닝이 필요

데이터베이스 수준 잠금

본문의 인증 DB의 예는 이해가 어렵다. 인증 DB (admin)가 잠기면 모든 쿼리가 멈추잖아! 라고 말하고 싶은가?

MongoDB 2.2부터 전역 잠금 (mongod 전체 잠금)에서 데이터베이스 수준 잠금 (특정 DB의 처리를 잠금)로 변경되었다.
알기 쉬운 예로, compact 명령이나 repairDatabase 등의 명령으로 디스크 공간의 청소를 하려면 게시된 데이터베이스는 청소가 끝날 때까지 잠기지만 다른 데이터베이스 작업은 잠금의 영향을 받지 않는다.
그 밖에도 데이터 파일이 추가될 때의 잠금 등 여러가지 고려해야 하는 것이 잠금 타이밍이다.

어? 그런데 compact 명령이 어느새 DB를 잠그지 않는 모양.

모든 것을 테스트하라

많은 사람들이 MongoDB의 확장성을 오해하고 있다. 그 자체가 간단한 것은 아니다. 잘 생각하고 이해하고 테스트하는 수밖에 없다. 프로파일러 및 explan을 활용하여 생각대로 동작하는지 시험하라. 그리고 라이브 코드에서도 일정 기간 동안은 벤치마크를 해볼 것. 이 글에서 다루지 못한 훌륭한 예제를 다룬 링크가 있다.

5 days ago / 0 notes

exists / apply를 이용한 쿼리 비교

일별로 데이터를 집계해 넣는 테이블이 있다. 원본 데이터가 제 날짜에 들어오지 않았다면 그 날의 데이터는 빈다. 일별 데이터 건수는 몇만에서 십만단위가 될 수도 있다. (더 많을 수도 있고)

목표는 데이터가 들어온 날만 찝어서 알려주는 쿼리 작성.

처음에는 위에 있는 exists를 이용한 서브쿼리를 짰는데, 나중에 비슷한 것을 다시 만들면서 2005부터 추가된 apply 구문을 사용했다. 이게 훨씬 빠른데..

위 exist 구문의 상관쿼리를 any 연산자를 사용하여 다음과 같이 변경할 수 있다.

select dt
from
	dbo.bdt_Date d
where
	d.dt between @monStart and @monend
	and
	d.dt = any (
		select sDate from dbo.SomeLog
	)
order by 1

둘다 NL 조인으로 풀어내며, 양쪽 모두 (12월 1일~12월 31일) 31번 index seek 들어간다. 이렇게만 놓고 보면 큰 차이가 없어야 하는데.. 차이는 다른 곳에서 난다.

set statistics io on
go
dbcc freeproccache
dbcc dropcleanbuffers

...쿼리1...
go
dbcc freeproccache
dbcc dropcleanbuffers

...쿼리2...

했을 때 결과는 다음과 같다.

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

(29 row(s) affected)
테이블 'SomeLog'. 검색 수 31, 논리적 읽기 수 281, 물리적 읽기 수 3, 미리 읽기 수 693, 
LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'bdt_date'. 검색 수 1, 논리적 읽기 수 2, 물리적 읽기 수 2, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

(1 row(s) affected)
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

(29 row(s) affected)
테이블 'SomeLog'. 검색 수 31, 논리적 읽기 수 245, 물리적 읽기 수 2, 미리 읽기 수 36, 
LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'bdt_date'. 검색 수 1, 논리적 읽기 수 2, 물리적 읽기 수 2, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

(1 row(s) affected)

이걸 연 수준 데이터로 확대하면 차이는 더 늘어난다.

(363 row(s) affected)
테이블 'SomeLog'. 검색 수 366, 논리적 읽기 수 3224, 물리적 읽기 수 8, 미리 읽기 수 6977

(363 row(s) affected)
테이블 'SomeLog'. 검색 수 366, 논리적 읽기 수 2784, 물리적 읽기 수 8, 미리 읽기 수 394

물리적 읽기 수는 큰 차이가 없는데.. 미리 읽기에서 작두를 잘못탄게 아닌가 하는 생각이 좀 들고, 논리적 읽기 (버퍼 캐시에서 읽어들이는) 차이가 좀 나는 것도 있고.. 하여튼 결론은 apply ( select top(1) ) 쓰는게 낫다는 것.

3 months ago / 0 notes

SQL Server Agent 작업 실행중인지 판단

필요해서 급조해봄

use msdb
go

-- 해당 작업이 현재 실행중인가?
create function dbo.fn_JobIsRunning (
    @job_id uniqueidentifier
) RETURNS TABLE AS 
RETURN
    select is_running = case when stop_execution_date is null then 1 else 0 end
    from msdb.dbo.sysjobactivity ja 
    where ja.job_id = @job_id and ja.session_id = (
        SELECT top 1 session_id from msdb.dbo.syssessions
        order by session_id desc
    )
go

-- 낮은 권한에서도 실행할 수 있도록 함
grant select on fn_jobisrunning to SQLAgentUserRole
go

-- TEST
execute as user='agentUser'
select * from dbo.fn_jobisrunning ('48A9CAD5-41C2-4FD9-9B45-67B485122F09')
revert

4 months ago / 0 notes

저장 프로시저 레코드셋 조사

당연한 이야기지만 소스를 가지고 있거나 스펙을 받거나 하는 것이 좋다.
그러나 불가피한 경우에는 다음과 같이 조사할 수 있다.
1. SQL Server 2012 이상:
  
  sp_describe_first_result_set
2. SQL Server 2008 R2 이하:
  바로 볼 수 있는 방법이 없어서 다음과 같이 돌아가야 함.
  linked server 설정 변경이므로 좀 높은 권한이 필요할 듯
  
  아래 예제에서 서버 이름은 SERVER00, 실행시킬 프로시저는 msdb..sp_help_jobstep
```
-- 로컬 서버에 대해 잠시 data access를 켠다. 권한 필요.  
exec sp_serveroption 'SERVER00', 'data access', 'true'

-- 결과를 임시 테이블에 저장
select * into #tmp from openquery ([SERVER00], 'set fmtonly on exec msdb..sp_help_jobstep')

-- 서버 설정 원래대로
exec sp_serveroption 'SERVER00', 'data access', 'false'

-- 테이블 형식 조회
exec tempdb..sp_help '#tmp'
```
4 months ago / 0 notes
관리자 계정이 아닌 계정으로 예약된 작업 돌리기

참고 링크

http://support.microsoft.com/kb/867466/ko
- 도메인 멤버 서버에서
- 예약된 작업을 돌릴 때
- Run As에 지정된 계정이 관리자 그룹에 속하지 않음
이러면 작업 스케줄러에서 실행이 안되고 로그 (c:\windows\tasks\SchedLgu.txt) 에는 다음과 같이 남게 됨

** 오류 **
작업을 시작할 수 없습니다..
특정 오류:
0x80070005: 액세스가 거부되었습니다.
[작업 페이지 찾기] 단추를 사용하여 응용 프로그램을 찾으십시오..

해결: CACLS를 이용해서 CMD.EXE 사용 권한 변경
```
CACLS %COMSPEC% /E /G "NT AUTHORITY\BATCH":R
```
일괄 작업 계정 셋업

작업을 돌리기 위한 전용 계정을 만들었다면 그룹 정책의 일괄 작업으로 로그온에 추가해줄 것 (화면에서 더블 클릭하고 사용자 또는 그룹 추가 클릭)

4 months ago / 0 notes
파워셸 원격 세션에 non-admin 계정 사용 연결

선결 조건

서버 (ServerA로 칭함), 클라이언트 (ClientA) 에서 같은 이름, 같은 패스워드의 계정이 필요함.
ex) 서버: ServerA\AccountA, 클라이언트: ClientA\AccountA

서버 설정
1. (관리자 권한) WinRM으로 접근 허가
```
winrm quickconfig
winrm set winrm/config/client @{TrustedHosts="172.16.0.1"}

(파워셸이라면)
set-item wsman:\localhost\client\TrustedHosts "172.16.0.1"
```
  예제의 172.16.0.1은 클라이언트의 IP로 대체
2. (파워셸에서, 관리자 권한) Non-Admin 계정도 세션을 열 수 있도록 설정
```
set-pssessionconfiguration microsoft.powershell -showsecuritydescriptor
```
  창이 뜨면 접속을 원하는 계정 (서버이므로 ServerA\accountA) 추가 후 실행 (Invoke) 추가
클라이언트 설정
1. workgroup 컴퓨터에 대해 Negotiate 사용 (관리자 권한 필요)
```
winrm set winrm/config/client @{TrustedHosts="<local>"}
```
2. (파워셸에서) 인증 정보 생성
```
$c = get-credential clientA\accountA
```
  패스워드 입력창이 뜨고 입력해준다.
3. (파워셸에서) 세션 생성
```
new-pssession -computername serverA -credential $c -Authentication Negotiate
```
Configure WinRM to Use HTTP
Installation and Configuration for Windows Remote Management

파워셸에서 winrm 명령 입력시에는 ‘@{~}’ 이렇게 양쪽을 홑따옴표로 감싸줌.

4 months ago / 0 notes

js 고속화 노하우 (잡지 요약)

기술잡지를 버리려다가 JS 고속화 노하우라는게 보여서 납득이 간 것만 추려서 정리해봄. 아마 다른 곳에도 많을 거라고 생각은 하지만..

기본 오브젝트 확장시 속도가 느려질 수 있음
- Object
- Boolean
- Array
- Date
- String
- RegExp
- Number
또한 window 개체에 직접 함수나 변수를 추가하는 것을 global 오염이라고 하는데, 이 경우 기능 충돌이나 변수 덮어쓰기 등을 불러일으킬 수 있음.
```
Array.prototype["ex1"] = function(){};
String.prototype["ex2"] = function(){};
```
범위 해결 (Scope resolution) 비용
```
var flat = function() {};
var nest = {
	lv2: {
		lv3: function() {
			//..
		}
	}
};
```
flat() 보다 nest.lv2.lv3() 이 더 느린 것은 말할 것도 없음.

자주 쓰는 배열 / 해시는 alias (또는 참조 변수)로 참조

var matrixA = [1,0,0,0,1,0,0,0,1];
var matrixB = [1,0,0,0,0,1,0,1,0];

function matrixMultiply1() {
	var a = matrixA;
	var b = matrixB;

	return [a[0]*b[0]+a[1]*b[3]+a[2]*b[6],
			/* 중간 생략 */
			a[6]*b[2]+a[7]*b[5]+a[8]*b[8]];
}

function matrixMultiply2() {
	var a = matrixA;
	var b = matrixB;

	var a11=a[0], b11=b[0];
	/* 생략 */
	var a33=a[8], b33=b[8];

	return [a11*b11 + a12*b21 + a13*b31,
			/* 중간 생략 */
			a31*b13 + a32*b23 + a33*b33];
}

예로 든 행렬의 곱셈에서는 배열의 한 항목당 세번씩 참조되는데, 이것을 참조로 두고 계산하면 반복작업시 속도 향상이 있음. 해시나 노드 리스트 등에서도 사용 가능

function all1() {
	var v=[], list=document.getElementsByTagName("A"), k=list.length;

	for(var i=0,j=-1; i < k; i++) {
		if (list[i].nodeType == 1) {
			v[++j] = list[i];
		}
	}
	return v;
}

function all2() {
	var v=[], list=document.getElementsByTagName("A"), k=list.length, t;

	for(var i=0,j=-1; i < k; i++) {
		t = list[i];
		if (t.nodeType == 1) {
			v[++j] = t;
		}
	}
	return v;
}

단 this는 alias로 써봐야 의미 없음.

GC를 자주 일으키지 말 것
특히 루프문 안에서 사용될 개체를 반복 선언하는 등의 일은 피할 것.

var bad = {
	inline: function() {
		for (i=0; i < 3; i++) {
			({"a":1,"b":2,"c":3})[i];
		}
	}
};

var hash = {"a":1,"b":2,"c":3};
var good = {
	inline: function() {
		for (i=0; i < 3; i++) {
			hash[i];
		}
	}
};

var hash = {"a":1,"b":2,"c":3};
var good_alias = {
	inline: function() {
		var t = hash;
		for (i=0; i < 3; i++) {
			hash[i];
		}
	}
};

가변인수는 피할 것

function sum_bad() {
	var t = 0;
	var i = 0, l = arguments.length;
	for (; i < l; i++) {
		t += arguments[i];
	}
	return t;
}

// 최대 5개
function sum5(a,b,c,d,e) {
	return a+(b||0)+(c||0)+(d||0)+(e||0);
}

Function.apply 대신 Function.call을, 가급적이면 그것도 쓰지 말 것

function fn (a, b) { /* ... */ }

// 제일느림
fn.apply(this, [1, 2]);

// 그다음 느림
fn.call(this, 1, 2);

// 빠름
fn(1, 2);

from Web DB Press Vol.57

4 months ago / 1 note

SQLSafeCmd Freeware Edition (4.9.635)

뭘 잘못먹었나 인스톨러가 꼭 Trial로만 설치하는데, FreeEdition 이라고 DWORD값을 추가하고 값은 1을 넣어주면 됨. 서버 인스턴스명으로 자동 추가된 키값은 건드리지 말 것

레지스트리 Export:
```
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Idera\SQLsafe\Backup Agent\Licenses]
"FreeEdition"=dword:00000001
```
5 months ago / 0 notes

증가 추세선 구하기 (선형, 최소제곱법)

아래 예제에서 tmp_daily_db_usage 테이블은 데이터베이스의 일별 용량을 파악하여 기록한 것이며, usage_type에는 데이터 / 로그 구분이 들어간다. 구별 카테고리를 세개나 뒀더니 (DB명, 데이터/로그, 사용량/잔여량) 쿼리가 복잡해진 것은 안자랑 ㅡㅡ

DECLARE @dateA CHAR(10) = '2012-10-20'
DECLARE @dateB CHAR(10) = '2012-10-30'

;WITH 
ds (input_dt,Cat1,Cat2,Cat3,Value) 
AS (
	SELECT	input_dt,[dbname],usage_type,'used',usedspacemb
	FROM	dbo.tmp_daily_db_usage
	WHERE	input_dt BETWEEN @dateA AND @dateB
	UNION ALL	
	SELECT	input_dt,[dbname],usage_type,'free',freespacemb
	FROM	dbo.tmp_daily_db_usage
	WHERE	input_dt BETWEEN @dateA AND @dateB
),
ds_rnk (Cat1,Cat2,Cat3,x,y) 
AS (
	SELECT 
		Cat1,Cat2,Cat3
		,RANK() OVER (
			PARTITION BY Cat1,Cat2,Cat3 
			ORDER BY input_dt
		),CAST(Value AS FLOAT)
	FROM	ds
	WHERE Cat1 NOT IN ('master','msdb','model','tempdb')
),
average_estimate (Cat1,Cat2,Cat3,xbar,ybar) 
AS (
	SELECT	Cat1,Cat2,Cat3,AVG(x),AVG(y)
	FROM	ds_rnk
	GROUP BY Cat1,Cat2,Cat3
),
beta_estimate (Cat1,Cat2,Cat3,beta) 
AS (
	SELECT 
		t.Cat1,t.Cat2,t.Cat3
		,CASE COALESCE(SUM((x - xbar) * (x - xbar)), 0)
			WHEN 0 THEN NULL
			ELSE SUM((x - xbar) * (y - ybar)) / SUM((x - xbar) * (x - xbar))
			END AS Beta
	FROM ds_rnk t
	INNER JOIN average_estimate te ON (
		t.Cat1 = te.Cat1 
		AND t.Cat2 = te.Cat2 
		AND t.Cat3 = te.Cat3
	)
	GROUP BY t.Cat1,t.Cat2,t.Cat3
),
alpha_estimate (Cat1,Cat2,Cat3,alpha)
AS (
	SELECT 
		tb.Cat1,tb.Cat2,tb.Cat3,ybar - xbar * tb.Beta
	FROM beta_estimate tb
	INNER JOIN average_estimate ta ON (
		tb.Cat1 = ta.Cat1
		AND tb.Cat2 = ta.Cat2
		AND tb.Cat3 = ta.Cat3
	)
),
sumsquares_estimate (Cat1,Cat2,Cat3,SS_tot,SS_err)
AS (
	SELECT 
		ta.Cat1,ta.Cat2
		,ta.Cat3
		,SUM((y - ybar) * (y - ybar))
		,SUM((y - (Alpha + Beta * x)) * (y - (Alpha + Beta * x)))
	FROM alpha_estimate ta
	INNER JOIN beta_estimate tb ON (
		ta.Cat1 = tb.Cat1
		AND ta.Cat2 = tb.Cat2
		AND ta.Cat3 = tb.Cat3
	)
	INNER JOIN ds_rnk t ON (
		ta.Cat1 = t.Cat1
		AND ta.Cat2 = t.Cat2
		AND ta.Cat3 = t.Cat3
	)
	INNER JOIN average_estimate te ON (
		ta.Cat1 = te.Cat1
		AND ta.Cat2 = te.Cat2
		AND ta.Cat3 = te.Cat3
	)
	GROUP BY ta.Cat1
		,ta.Cat2
		,ta.Cat3
)
SELECT 
	ta.Cat1,ta.Cat2,ta.Cat3,Alpha,Beta
	,CASE SS_tot
		WHEN 0 THEN 1.0
		ELSE 1.0 - SS_err / SS_tot
		END AS R2
FROM alpha_estimate ta
INNER JOIN beta_estimate tb ON (
	ta.Cat1 = tb.Cat1
	AND ta.Cat2 = tb.Cat2
	AND ta.Cat3 = tb.Cat3
)
INNER JOIN sumsquares_estimate ss ON (
	ta.Cat1 = ss.Cat1
	AND ta.Cat2 = ss.Cat2
	AND ta.Cat3 = ss.Cat3
)
WHERE ta.Cat3 = 'Used'

Thanks to: Are there any Linear Regression Function in SQL Server? - stackoverflow

6 months ago / 1 note

SQL Server Agent 작업항목 나열

컬럼 설명:

job_id: 작업 UUID (추적용)
schedule_id: 스케줄 ID (작업:스케줄의 관계는 1:N임)
job_name: 작업 이름
job_desc: 작업 설명
Running: 실행중인가 (1/0)
NextRun: 다음 실행 시각
LastRun: 마지막 실행 시각
LastStatus: 실행 결과 (sysjobhistory.run_status), 0: 실패
LastDuration: 실행 시간 (초)
job_time: 실행 시점 (하루중) (ex: 오전 8시, 매 5분 등)
job_freq: 실행 시점 (월중) (ex: 매일, 매주 일요일, 매 2일 등)
DateStart: 이 날부터 일정 사용
DateEnd: 이 날까지 일정 사용

SELECT
	j.job_id,
	s.schedule_id,
	job_name = j.name,
	job_desc = j.description,
	run.Running,
	NextRun = n.NextRun,
	LastRun = l.LastRun,
	LastStatus = l.run_status,
	LastDuration = l.run_duration,
	job_time = tm_day.TimeOfDay,
	job_freq = F_Frq.Frequency,
	DateStart = eff.StDate,
	DateEnd = eff.EnDate
FROM
	msdb.dbo.sysjobs j
	inner join msdb.dbo.sysjobschedules js on j.job_id = js.job_id
	inner join msdb.dbo.sysschedules s on js.schedule_id = s.schedule_id
	cross apply (
		select case when stop_execution_date is null then 1 else 0 end
		from msdb.dbo.sysjobactivity ja 
		where ja.job_id = j.job_id and ja.session_id = (
			SELECT top 1 session_id from msdb.dbo.syssessions
			order by session_id desc
		)
	) run(Running)
	cross apply (
		select StDate=convert(datetime,convert(varchar(8),active_start_date))
			,EnDate=convert(datetime,convert(varchar(8),active_end_date))
			,StTimeStr=stuff(stuff(right(1000000+active_start_time,6),3,0,N':'),6,0,N':')
			,EnTimeStr=stuff(stuff(right(1000000+active_end_time,6),3,0,N':'),6,0,N':')
	) eff
	cross apply (
		SELECT CASE js.next_run_date 
			when 0 THEN NULL
			ELSE CAST(right(100000000 + js.next_run_date, 8) as datetime) +
				CAST(RIGHT(STUFF(STUFF(
					CAST(1000000+js.next_run_time as varchar(9))
					,4,0,':'),7,0,':'), 8) as datetime)
			END
	) n(NextRun)
	outer apply (
		SELECT TOP 1
			run_status, 
			CAST(right(100000000 + run_date, 8) as datetime) +
			CAST(RIGHT(STUFF(STUFF(
				CAST(1000000+run_time as varchar(9))
				,4,0,':'),7,0,':'), 8) as datetime) as LastRun,
			run_duration/*, message*/
		from msdb.dbo.sysjobhistory h
		where h.step_id = 0 and h.job_id = j.job_id
		order by instance_id desc
	) l
	cross apply (	/* timeofday */
		select case freq_subday_type
			when 0 then ''
			when 1 then 'at ' + StTimeStr
			else 'Every '+convert(varchar(10), freq_subday_interval)
				+' '+case freq_subday_type
					when 2 then 'Sec.'
					when 4 then 'Min.'
					when 8 then 'Hr.'
				end + ' from ' + StTimeStr + ' to ' + EnTimeStr
			end
	) tm_day(TimeOfDay)
	cross apply (
		SELECT case s.freq_type
			when 1 then N'One Time Only'
			when 4 then N'Every ' +
				case s.freq_interval 
					when 1 then N'Day'
					else convert(nvarchar(10),s.freq_interval)+N' Days'
				end
			when 8 then N'Every ' +
				case s.freq_recurrence_factor
					when 1 then N''
					else convert(nvarchar(10),s.freq_recurrence_factor)+N' Weeks on '
				end + 
				stuff(case when freq_interval& 1<>0 then N', Sun' else N'' end
					+case when freq_interval& 2<>0 then N', Mon' else N'' end
					+case when freq_interval& 4<>0 then N', Tue' else N'' end
					+case when freq_interval& 8<>0 then N', Wed' else N'' end
					+case when freq_interval&16<>0 then N', Thu' else N'' end
					+case when freq_interval&32<>0 then N', Fri' else N'' end
					+case when freq_interval&64<>0 then N', Sat' else N'' end
					,1,2,N'')
			when 16 then N'Every ' +
				case freq_recurrence_factor 
					when 1 then 'Month'
					else convert(nvarchar(10),freq_recurrence_factor)+N' Months'
				end + ' on the ' +
				convert(nvarchar(10),freq_interval) +
				case 
					when freq_interval in (1,21,31) then N'st'
					when freq_interval in (2,22) then N'nd'
					when freq_interval in (3,23) then N'rd'
					else N'th'
				 end + ' of the Month'
			when 32 then N'Every ' +
				case freq_recurrence_factor 
					when 1 then N'Month'
					else convert(nvarchar(10),freq_recurrence_factor)+N' Months'
				end + ' on the ' +
				case freq_relative_interval 
					when  1 then N'1st '
					when  2 then N'2nd '
					when  4 then N'3rd '
					when  8 then N'4th '
					when 16 then N'Last '
				end + 
				case freq_interval 
					when  1 then N'Sun'
					when  2 then N'Mon'
					when  3 then N'Tue'
					when  4 then N'Wed'
					when  5 then N'Thu'
					when  6 then N'Fri'
					when  7 then N'Sat'
					when  8 then N'Day'
					when  9 then N'Weekday'
					when 10 then N'Weekend Day'
				 end + ' of the Month'
			when 64 then 'When SQL Server Agent Starts'
			when 128 then 'Whenever the CPUs become Idle'
			else 'Unknown'
		  end
	) F_Frq(Frequency)
where
	j.Enabled = 1

Thanks to: Documenting Your SQL Agent Jobs - SELECT BLOG FROM BRAD.SCHULZ CROSS APPLY SQL.SERVER()

6 months ago / 0 notes

← Older

문서를 키우지 않는다

프로필의 예

paddingFactor의 증감 확인

필드 연산자를 사용

BSON 데이터 형식에 유의

문서를 사전 할당(preallocate)한다

필드 이름도 공간을 차지한다

_id를 활용할 것

covered index를 사용하고 있는가?

컬렉션이나 데이터베이스를 최대한 잘 사용한다

전체적으로 약간 알기 어려워서 대강 설명

네임스페이스의 제한

데이터베이스 수준 잠금

모든 것을 테스트하라

참고 링크

일괄 작업 계정 셋업

선결 조건

서버 설정

클라이언트 설정