[MS-SQL] 디스크 병목 현상 미리 계산하는 방법 DB&NoSql/MS 2016. 7. 12. 11:10

성능 모니터를 이용하여 SQL Server 의 Disk I/O 를 측정하여 Disk 병목 현상이 있는지 확인 후 디스크 증가 등의 성능 튜닝을 진행한다.

 

<성능 모니터 카운터>
Disk Reads/sec : 선택된 디스크 (또는 디스크 어레이) 에서 초당 수행된 읽기 작업 횟수
Disk Writes/sec : 선택된 디스크 (또는 디스크 어레이) 에서 초당 수행된 쓰기 작업 횟수
Avg. Disk Queue Length : 샘플 간격 동안 선택된 디스크에 대해 큐에 있는 읽기 및 쓰기 요청의 평균 수
Avg. Disk Sec/Read : 샘플 간격 동안 선택된 디스크에서 데이터 읽기에 소요된 평균 시간(초)
Avg. Disk Sec/Write : 샘플 간격 동안 선택된 디스크에서 데이터 쓰기에 소요된 평균 시간(초)

 

<임계값>

카운터

임계값

초당 디스크 당 I/O 수

100 이하

Disk Reads/sec

0.015 (15ms) 이하

Disk Writes/sec

0.015 (15ms) 이하

Avg. Disk Queue Length

2 이하

 

<I/O 계산>
디스크 당 I/O 수 = [읽기 + (2 * 쓰기)] / 디스크 수

 

#RAID 10 어레이로 구성된 8개 디스크 드라이브의 예

카운터

측정값

Disk Reads/sec

420

Disk Reads/sec

300

Avg. Disk Queue Length

43

Avg. Disk Sec/Read

0.032

Avg. Disk Sec/Read

0.025

 

[420 + (2 * 300)] / 8 = 127.5 디스크 당 물리적 I/O 수

결과값 127.5는 초당 100 I/O라는 한계값을 초과하므로, 이 경우 약간의 디스크 병목을 가지게 된다. 읽기 및 쓰기에 걸린 시간 값만을 조사하여도 병목이 발생함을 알 수 있다. 또, 평균 큐 길이가 43으로 I/O 요청이 큐에서 잠시 기다림으로 인해 긴 대기시간이 발생함을 의미한다.

 

<디스크 최소 개수>

[420 읽기 + (2 * 300 쓰기)] = 1020 I/O
1020 전체 I/O / 100 디스크 당 I/O = 10.2 필요한 디스크 수 
10.2 디스크는 가질 수 없으므로 12개의 디스크로 반올림 (RAID 10을 위해서는 짝수개의 디스크 필요)

 

 

출처 : SQL Server 2000 성능 튜닝 (하성희 역)
,
[MS-SQL] BULK Insert & BCP 명령어 DB&NoSql/MS 2016. 7. 12. 11:09
 
BCP 란.
대량의 데이터를 파일을 통하여 빠르게 처리하는 명령어( 유틸리티) 
특징 
1. 특징 콜솔상에서 명령어를 입력
2. 원격지에 있는 데이터도 추출 할 수 있다.
3. 추출한 데이터는 로컬에 쌓인다. 

명령어 예제. 
 CMD 상에서 아래 명령어를 입력 한다.
bcp CHACHACHAF_REPLICATION..TB_CAR out bcp_data_titles2.txt -c -q -T -S 127.0.0.1,5432 -U 아이디 -P 비번
bcp CHACHACHAF_REPLICATION..TB_PRESENT_TROPHY out bcp_data_TB_PRESENT_TROPHY2.txt -c -q -T -S 127.0.0.1,5432 -U 아이디 -P 비번
bcp CHACHACHA2_DDR..TB_DDR out bcp_data_TB_DDR.txt -c -q -S 175.207.8.112,1434 -U 아이디 -P 비번

쿼리로 가능  ?  (비권장)
쿼리로 CMD 명령어가 가능하지만 권한 문제로 할 수 없음 오픈해 주면 가능 하지만 보안 문제가 있다고한다. 하지만
웹서비스 풀 쿼리를 이용하는데서 발생 할 수 있는 문제인듯하다. 게임에서는 프로시저를 통하기 때문에 발생 하지 않을듯 하다. 

예제 
DECLARE @query VARCHAR (500)
SET @query = 'bcp CHACHACHAF_REPLICATION..TB_PRESENT_TROPHY out D:\MS_SQl_DB_FILE\bcp\bcp_data_TB_PRESENT_TROPHY2.txt -c -q -T -S 127.0.0.1,5432 -U 비번 -P 아이디
EXEC MASTER ..xp_cmdshell @query
  





SELECT * FROM TB_CAR
SELECT * FROM TB_PRESENT_TROPHY
/*

bcp CHACHACHAF_REPLICATION..TB_CAR out bcp_data_titles2.txt -c -q -T -S 127.0.0.1,5432 -U sa -P ab1020!!

bcp CHACHACHAF_REPLICATION..TB_PRESENT_TROPHY out bcp_data_TB_PRESENT_TROPHY2.txt -c -q -T -S 127.0.0.1,5432 -U sa -P ab1020!!

bcp CHACHACHA2_DDR..TB_DDR out bcp_data_TB_DDR.txt -c -q -S 175.207.8.112,1434 -U sa -P q1w2e3r4t5y6

*/

BULK INSERT CHACHACHAF_REPLICATION.dbo .TB_CAR FROM 'D:\MS_SQl_DB_FILE\bcp\bcp_data_titles2.txt'



BULK INSERT CHACHACHAF_REPLICATION.dbo .TB_PRESENT_TROPHY FROM 'D:\MS_SQl_DB_FILE\bcp\bcp_data_TB_PRESENT_TROPHY2.txt'





DECLARE @query VARCHAR (500)
SET @query = 'bcp CHACHACHAF_REPLICATION..TB_PRESENT_TROPHY out D:\MS_SQl_DB_FILE\bcp\bcp_data_TB_PRESENT_TROPHY2.txt -c -q -T -S 127.0.0.1,5432 -U sa -P ab1020!!'
EXEC MASTER ..xp_cmdshell @query




bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T


bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T


  

  

 

,
[MS-SQL] TimeStamp 값 치환 DB&NoSql/MS 2016. 7. 12. 11:07

java 에서 나온 타임스템프값 

 1459180800000
DB로 할대는 뒤 3자리 제거

 

select CONVERT (varchar ( 30), dateadd( second , 1459180800/*timestamp 값*/, cast( '1970-01-01 00:00:00.000' as datetime )) , 121 ) vtimestamp
go
select CONVERT (varchar ( 30), dateadd( second , 1459138500/*timestamp 값*/, cast( '1970-01-01 00:00:00.000' as datetime )) , 121 ) vtimestamp
 go

 

,
[MS-SQL] Count 속도 계선 DB&NoSql/MS 2016. 7. 12. 11:04

하나의 테이블에 모든 로우의 개수를 알고 싶을때 보통 아래와 같이 작성을 많이 한다.


SELECT COUNT(*) FROM TestTable;


뭐 저렇게 사용해도 상관은 없지만 속도측면에서 좀 더 나은 방법이 있어서 소개 한다.


SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TestTable') AND indid < 2;


대충 보면 이해가 될 것이니 설명은 패스한다.


속도차이는 COUNT를 하려는 테이블의 총 로우 개수가 100개 미만이라면 COUNT가 더 빠르다.


100개 이상이라면 위에 적어놓은 쿼리가 훨씬 빠르다.


속도 차이가 나는 이유는 COUNT 키워드를 실행하게 되면 table scan을 하게되지만 


대체 쿼리에서는 해당 rows값을 가져오기 때문에 더 빠르다.


물론 내가 테스트해본거니 확인은 다들 해보고 적용하기 바란다.


* 해당 쿼리는 테이블의 전체 로우 개수를 가져올때만 유효하다.

,
[MS-SQL]스냅숍 설정 DB&NoSql/MS 2016. 7. 12. 11:03

▶ snapshot replication (원하는 테이블 컬럼 가져올 수 있음)

1. DB 인스턴스 2개 설정

    --> 게임DB
    --> 운영툴(구독 DB)
2. 게임 DB : snapshot 설정
     --> 배포 설정 
     --> 구독 설정 주기는 게임 환경에 맞게 설정 테이블 종류에 맞게 설정 (테이블, 프로시저, 뷰  가능 , 필드및 필더 가능 )
3. 운영툴(구독 DB ) 구독 설정 
     --> 구독 설정 
     --> 관련된 통계 프로시저 작성
     --> 잡설정
4. 단점 
     --> 한 스키마에서 다양한 게임 DB 정보를 취합하기 어려움...??

▶ DB snapshot (통자로 데이터 복사됨)

1. 특정 시간마다 스키마 snapshot를 생성 
     --> 게임DB 1,2 
2. 프로시저를 통하여 데이터 Select Insert 
    
3. view 제공

4. 단점 
     --> 작업 자동화 힘듬 DB 추가 될때마다 프로시저 수정 요지 있음.


▶ 직접 구현 
1.  본서버-> 통계서버로 테이블 복사 
2.  통계서버 -> 작업 진행 
3. 운영툴 제공 
 
4. 단점 
   --> 개발자 작업량



▶ 직접 구현 구상안 1
1. snapshot replication 데이터 복사
     --> 게임 DB (배포,계시), 1:1 구독 DB 구현 
2. 통계 테이블로 데이터 취합  ( 스케줄러 )
     --> 구독 DB 정보를  취합 ( 프로시저 작업 ) 
     --> 통계 정보 가공 ( 기본정보, 요구 사항 처리 ) 
3. view 제공
      --> 통계 정보 화면 표현 

▶ 직접 구현 구상안 2
1. BCP로 직접 데이터  추출 
     1) CMD 명령어 Batch 파일 마들어서 추출 
     2) 명령 프로그램 구현 
     3) SQL 스케줄러로 구현 ( 권한 필요함 ) -http://zaco.tistory.com/249
2. SQL 스케줄러로 테이블 입력 ( Bluk Insert  > BCP IN 보다 빠름) 
     1) bluk inser
     2) 통계 데이터 추출 

 




스키마 전체 스넵샵 설정

CREATE DATABASE CHACHACHAF_GAME_SHAPSHOT

ON (NAME = CHACHACHAF_GAME, FILENAME = 'D:\MS_SQl_DB_FILE\SNAPSHOT\CHACHACHAF_GAME_SHAPSHOT.mdf' )

as SNAPSHOT OF CHACHACHAF_GAME;



USE CHACHACHAF_GAME_SHAPSHOT
  
,
ms-sql sa 비번 변경 DB&NoSql/MS 2010. 6. 8. 09:44

sp_password @old=null, @new='1472',@loginame='sa'
,
MS-SQL 2000 ,2005, 2008 테이블 맟 필드 유무 확인 DB&NoSql/MS 2010. 4. 30. 11:35
테이블과  이름  확인 하는 쿼리문 
DECLARE @Check  INT 
set @Check =( select count(name)NO_CHECK  from syscolumns 
where id = object_id('테이블명')) and name='필드명'
,
[ms-sql] sa 암호 분실 DB&NoSql/MS 2010. 3. 30. 13:21

아래 방법중 한가지로 해결합니다.
 
첫째 방법
 
MS SQL SA 계정의 암호를 분실했을 경우
d0022886_02104015.gif

 

C:\Program Files\Microsoft SQL Server\MSSQL\Binn>osql -E
sp_password @old = null, @new = 'newpassword',  @loginame ='sa'

1. osql -E 를 통해서 윈도우인증을 통해서 MSDE에 접속합니다.

2. 그런다음 sp_password 프로시져를 통해서 sa계정의 패스워드를 재설정합니다.

 

출처: http://systemx.egloos.com/480207

 

 

 

둘째 방법

 

MS-SQL sa 암호를 분실했을때 이를 변경시키는 방법

1. administrator 혹은 administrator 그룹에 속한 계정으로 들어간 후 sql 서버에 로그인을 한다.
2. 쿼리 분석기를 수행하고 윈도우 인증으로 로그인을 한다.
3. 다음의 sql 구문을 수행한다.

sp_password null, '[새 sa 암호]', 'sa';

4. 암호가 잘 바뀌었는지 확인한다. -_-;

출처: http://sqler.pe.kr/
http://www.inet.co.kr/faq/stories.php?story=03/10/14/9114497

,
[MSSQL-DMV] 각 테이블에 할당된 공간과 실제 데이터와 인덱스가 차지하고 있는 공간 확인 DB&NoSql/MS 2010. 3. 30. 12:45


 

set nocount on;

 

select

         object_name(max(object_id)) [Object Name]

       , sum(used_page_count) * 8 as 'Total Used(KB)'

       , sum(reserved_page_count) * 8 as 'Total Reserved(KB)'

       , sum(case when index_id = 0 then used_page_count else 0 end) * 8 as 'Heap(KB)'

       , sum(case when index_id = 1 then used_page_count else 0 end) * 8 as 'Cluster(KB)'

       , sum(case when index_id < 2 then used_page_count else 0 end) * 8 as 'Index(KB)'

       , max(row_count) as 'RowCount'

       , sum(in_row_used_page_count) * 8 as 'Row Used(KB)'

       , sum(in_row_reserved_page_count) * 8 as 'Row Reserved(KB)'

       , sum(lob_used_page_count) * 8 as 'LOB Used(KB)'

       , sum(lob_reserved_page_count) * 8 as 'LOB Reserved(KB)'

       , sum(row_overflow_used_page_count) * 8 as 'Overflow Used(KB)'

       , sum(row_overflow_reserved_page_count) * 8 as 'Overflow Reserved(KB)'

from sys.dm_db_partition_stats with(nolock)

where object_name(object_id) not like 'sys%'

group by object_id

order by 2 desc

,
MS-SQL 시드값 확인 DB&NoSql/MS 2010. 3. 30. 11:36

시드값 확인
dbcc checkident('테이블 이름', noreseed)

시드값 변경

dbcc checkident('테이블 이름', reseed, 변경값)

* 변경값은 현재시드값을 그 쪽으로 이동시켜주는 것이 됩니다. 예를 들어 증가값이 1로 identity가 설정되어있고 변경값에 40을 입력하여준 뒤 데이터를 추가시키면 컬럼값은 41이 되어서 데이터가 입력이 되게 됩니다(그리고 이 상태에서 계속 순차적으로 값이 올라갑니다)


 출처 :http://azbdc.tistory.com/268
,

티스토리툴바