SQL Server 성능모니터링 툴 : SQL Live Monitor( https://sqlmonitor.codeplex.com/ )

codeplex에 오픈 소스로 공개되어 있는 툴이며 SQL Server의 실시간 모니터링이 가능하다.

보통은 윈도우의 성능모니터링 툴을 이용하여 SQL Server 관련 성능 카운터를 추가하여 모니터링을 해야하지만

SQL Live Monitor의 경우 GUI에 주요 성능 카운터에 대한 지표를 실시간으로 모니터링 할 수 있게 제공하고 있음.

용량도 약 100K 미만으로 엄청 가볍고 csv 파일 형태로 저장도 해주므로 지표 수집 후 서버 성능 분석에도 도움이 될듯 하다.

#########################################################################################

Description

a .NET application that provides realtime performance data on the target SQL Server instance.

No installation required, data displayed in realtime, and can also be logged to CSV for offline analysis. Will also capture SQL data for processing uing PAL.

Features

This tool is designed to provide realtime performance data on SQL Server, as well as data capture for offline analysis. The tools features are:

• Realtime SQL and System performance data
• Colour coded alerts
• Capture data logging to CSV - sample interval configurable
• PAL Perfmon counter logged to .blg or .csv for offline analysis using PAL - sample interval configurable
• Detailed view of certain SQL Areas via DMVs
• List Top 20 Queries by CPU, IO and Execution Count
• View SQL Waits by category
• View SQL Scheduler and CPU worker threads
• Monitor Kernel Pools and System PTE's
• Supports SQL 2000,SQL 2005, SQL 2008, SQL 2012, SQL 2014
• Captures data from local and remote servers
• Captures data from default and named instances (stand alone & clustered)
• Supports both SQL and Windows Authetication
• Supports x86 and x64
• No installation required
• Application small in size with minimal overhead when running
• Run multiple instances on same PC

Last edited Jun 17, 2014 at 4:59 PM by glensmall, version 12

갑작스레 MSSQL 성능 모니터링 지원 요청으로 몇가지 찾아보다 참조자료가 있어서 퍼옴.

열심히 구글링을 하다가 내용만 복사했더니.... 원문 링크를 잃어 버리다니....ㅠ

#################################################################################

MS-SQL을 사용하는 DB서버의 속도가 느려진다거나 성능 저하가 느껴질때

물리적인 서버의 성능을 테스트하여 성능향상을 고려할수 있습니다. 논리적인부분 배제함

1. CPU

성능모니터링을 이용한 CPU 모니터링

% Processor Time : 80% 이하가 정상 

% Privileged Time : 80% 이하가 정상 

% User Time : 80% 이하가 정상 

Processor Queue Length : 2 이하가 정상 

비정상 수치의 대책 : 프로세서 추가, 더빠른 프로세서로 교체, 보다많은 캐쉬 확보

2. 메모리

성능모니터링을 이용한 메모리 모니터링 

Available Bytes : 4 메가 이상이 정상 

Pages/sec (paging in/out) : 0~20일 때 정상

Page Reads/sec : 5 이하가 정상 

Page Writes/sec : 5 이하가 정상 

Buffer Cache Hit Ratio : 90% 이상이 정상 

비정상 수치의 대책 : 메모리 추가

3. 디스크

성능모니터링을 이용한 디스크 모니터링

검사항목 

% Disk Read Time : 40% 이하가 정상 

% Disk Write Time : 40% 이하가 정상 

% Disk Time : 40% 이하가 정상 

Avg. Disk Queue Length : 2 이하가 정상 

비정상 수치의 대책 : 디스크 추가 , RAID 고려, 서버교체를 통한 고속의 신형HDD로의 교체

SQLJDBC

SQL2005의 sqljdbc.jar 파일로 SQL 2008도 사용 가능함.
JDK 1.4이상 버젼에서 사용가능함.

driver class는 아래와 같이 변경!
com.microsoft.jdbc.sqlserver.SQLServerDriver - > com.microsoft.sqlserver.jdbc.SQLServerDriver

url 설정은 다음과 같이 -
jdbc:microsoft:sqlserver -> jdbc:sqlserver

** jdk1.3 이하 버젼의 경우 MSSQL 2000에서 사용했던 JDBC(3개의 *.jar 파일)만 사용 가능.

-- 마스터키 만들기
-- http://msdn2.microsoft.com/ko-kr/library/ms174382.aspx

-- 인증서 만들기
-- http://msdn2.microsoft.com/ko-kr/library/ms187798.aspx

-- 대칭키 생성
-- http://msdn2.microsoft.com/ko-kr/library/ms188357.aspx

-- 데이터를 암호화 해서 넣을때
-- http://msdn2.microsoft.com/ko-kr/library/ms174361.aspx

-- 데이터를 복호화 해서 가지고 올때
-- http://msdn2.microsoft.com/ko-kr/library/ms181860.aspx


예제))

-- 마스터 Key 생성
SELECT * FROM sys.symmetric_keys

IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!votmdnjem(22qhekzmrptjfwjd)'   -- 패스워드(22보다 크게 설정)
GO

-- 마스터Key을 삭제 한다.
DROP MASTER KEY
--1. Master Key는 각각의 데이터 베이스에 대하여 분리되어 생성된다.
-- 데이터베이스 상에 하나의 마스터 KEY을 생성할 수 있다.
-- 하나의 데이터베이스 인스턴스에 두개의 마스터 key는 존재할 수 없다.

-- 인증서 생성
SELECT * FROM sys.certificates

IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N'TiwazPasswordFieldCertificate'
)
CREATE CERTIFICATE TiwazPasswordFieldCertificate WITH SUBJECT = 'Password Fields';
GO

DROP CERTIFICATE TiwazPasswordFieldCertificate
go

-- Creating a Symmetric Key
SELECT * FROM sys.symmetric_keys 

/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey   -- Key 명칭
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TiwazPasswordFieldCertificate;

CREATE SYMMETRIC KEY TiwazPasswordFieldSymmetricKey   -- Key 명칭
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TiwazPasswordFieldCertificate;

-- Algorithm
-- DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256
-- Microsoft Windows 2000, Window XP에서는 AES_128, AES_192 and AES_256의 알고리즘은 사용할 수 없다.
-- Win2K인 경우 TRIPLE_DES Algorithm을 사용한다.

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey                -- 대칭 Key
DECRYPTION BY CERTIFICATE TiwazPasswordFieldCertificate;    -- 인증 Key 명칭

-- 암호화 및 복호화시 주의할 점은 원래의 데이터 타입으로 복호화 되어야 한다는 것이다.
-- Varchar 은
SELECT CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), '테스터')))
SELECT CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'테스터')))

-- Encrypt 'Varchar' string and then decrypt encrypted data
SELECT
  CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), '테스터'))),
  CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), '테스터'))),
  CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'테스터1'))),
  CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'테스터1')))

SELECT login_password
     , EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),login_password ) ENCRYPT_LOGIN_PASSWORD
     , DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),login_password ))  AS NON_CONVERT_LOGIN_PASSWORD
     , CONVERT(varchar,DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),login_password ))) AS CONVERT_LOGIN_PASSWORD
  from skf_employee

-- 파라메터로 암호화 하기
DECLARE @sample_data nvarchar(MAX)
SET @sample_data = N'위의 내용은 SQL SERVER 2005 및 SQL SERVER 2008의 암호화를 설명한 것이다.'

SELECT
CONVERT(nvarchar(MAX), DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), @sample_data)))

간단 사용 방법은 다음과 같다.
** 세부 자세한 내용은 MSDN 링크를 참조


-- 데이터베이스 mdf 파일 및 *_log.ldf 파일 정보 확인
sp_helpdb midas

--
--BACKUP LOG midas with NO_LOG

-- 트렌젝션 로그 축소
http://technet.microsoft.com/ko-kr/library/ms178037.aspx
DBCC SHRINKFILE([데이터베이스]_log, truncateonly)
또는
DBCC SHRINKFILE([데이터베이스]_log, 줄이고자 하는 파일 사이즈, truncateonly)

-- 데이터베이스 축소
http://technet.microsoft.com/ko-kr/library/ms189080.aspx
DBCC SHRINKDATABASE([데이터베이스])

JDBC 테스트 중 sqljdbc.jar가 포함된 runnable jar 파일을 실행하면 아래와 같은 Exception을 일으키며 파일이 실행되지 않는다.

Exception in thread "main" java.lang.SecurityException: invalid SHA1 signature f
ile digest for com/microsoft/sqlserver/jdbc/SQLServerException.class
at sun.security.util.SignatureFileVerifier.verifySection(Unknown Source)

at sun.security.util.SignatureFileVerifier.processImpl(Unknown Source)
at sun.security.util.SignatureFileVerifier.process(Unknown Source)
at java.util.jar.JarVerifier.processEntry(Unknown Source)
at java.util.jar.JarVerifier.update(Unknown Source)
at java.util.jar.JarFile.initializeVerifier(Unknown Source)
at java.util.jar.JarFile.getInputStream(Unknown Source)
at sun.misc.URLClassPath$JarLoader$2.getInputStream(Unknown Source)
at sun.misc.Resource.cachedInputStream(Unknown Source)
at sun.misc.Resource.getByteBuffer(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$000(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)

내 나름대로 정의한 이유인즉 sqljdbc에 포함된 class 파일에 SHA1 방식으로 암호화 맵핑이 되어 있는데 JVM에서 풀지 못하여 Exception이 발생하지 않을까 한다.

그래서 간단한 해결 방법을 찾아보자~!

1. Microsoft 홈페이지로 들어가서 sqljdbc 파일을 다운로드 받는다.(해당 링크는 2.0 버젼)
http://www.microsoft.com/downloads/ko-kr/details.aspx?displaylang=ko&FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9

2. 다운로드 받은 후 설치를 하면 디렉토리 안에 2.0 버젼의 경우 sqljdbc.jar와 sqljdbc4.jar 파일이 있음을 확인 할 수 있다.

3. sqljdbc.jar 또는 sqljdbc4.jar 파일을 압축 해제 한다.

3. 압축 해제한 디렉토리에서 META-INF 폴더로 이동한다.

4. 폴더에 있는 SIGNED.SF / SIGNED.RSA 파일을 삭제한다.
단, 2005 이하 버전용 sqljdbc의 경우 zigbert.sf / zigbert.rsa 이며 동일하게 삭제 한다.

5. MANIFEST.MF 파일을 열어보면 아래와 같은 내용을 확인 할 수 있다.
아래 부분의 1, 2, 3 Line 을 제외한 나머지 Line을 모두 지운 후 다시 sqljdbc.jar 파일로 압축을 한다.

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.2
Created-By: 1.5.0_15-b04 (Sun Microsystems Inc.)

Name: com/microsoft/sqlserver/jdbc/SQLServerException.class
SHA1-Digest: wJmi22LxY0sGSj6Fo/b+189olOA=

.....

6. sqljdbc.jar 파일을 해당 프로젝트에 다시 추가한후 runnable jar에 포함하여 실행 가능한지 테스트 한다.

윈도우에서 설치만 하면 잘 돌던 MySQL을 fedora 12에 설치 후 구동 중

몇몇 명령어에 아래와 같은 에러를 뿌리더라 -_ㅠ

mysql> describe board;
 ' ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_6bb_0.MYI' (Errcode: 13) '

이유인즉 리눅스 환경에서 /tmp 디렉토리의 권한 중 방문자의 쓰기 권한이 없어서 발생

무식한 방법이지만.... /tmp 디렉토리의 권한에 쓰기 권한을 준다 -_-;;

[root@localhost /]# chmod 777 /tmp
[root@localhost /]# ls -al
...
drwxrwxrwx    25  root  root   4096  2010-12-14  16:54   tmp
..

변경 완료 후
mysql> describe board;
....
테이블 정보 출력~!


** MySQL의 tmp 파일 저장 경로 설정 방법이오니 참고 바람.
http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html

MySQL을 사용할때 command 창으로만 사용한 경험만 있어서 SQL-Deveoloper 같은 툴이 없나 찾아보게 되었는데요.
GNU 프로젝트의 한가지로 google 쪽에서 개발이 되고 있는듯? 합니다.
초보자도 쉽게 설치 및 사용 가능하며 아래의 경로에서 다운로드 가능합니다.

다운로드 : http://code.google.com/p/sqlyog/

사용법은 오라클 기본 관리 도구인 SQL-Deveoloper와 유사하며 아래의 MySQL 텝으로만 사용 가능합니다.

1. 연결 설정

2. 연결 후

SQL Server 에서 세션 죽이기 -

1. 현재 연결된 세션을 조회 한다.
SQL> select * from master.dbo.syslockinfo

2. 죽이고자하는 세션의 spid 를 입력하여 죽여 준다 -
SQL> kill [spid]

-- 시스템 통계 함수
SELECT @@CPU_BUSY -- 서버를 마지막으로 시작한 이후의 작업에 CPU가 소비한 시간을 밀리초 단위로 반환합니다.
SELECT @@IDLE --마지막 시작 이후 서버가 유휴 상태인 시간을 밀리초 단위로 반환합니다.
SELECT @@IO_BUSY --마지막으로 시작한 이후 입력 및 출력 작업을 수행하는 데 서버가 소비한 시간을 밀리초 단위로 반환합니다.
SELECT @@PACK_RECEIVED --마지막으로 시작한 이후 서버가 네트워크에서 읽은 입력 패킷 수를 반환합니다.
SELECT @@PACK_SENT --마지막으로 시작한 이후 서버가 네트워크에 기록한 출력 패킷 수를 반환합니다.
SELECT @@PACKET_ERRORS --마지막으로 시작한 이후 서버 연결에 발생한 네트워크 오류 수를 반환합니다.
SELECT @@TIMETICKS --틱 당 밀리초 수를 반환합니다.
SELECT @@TOTAL_ERRORS -- 마지막으로 시작한 이후 서버에 의해 발생한 디스크 읽기/쓰기 오류 수를 반환합니다.
SELECT @@TOTAL_READ -- 마지막으로 시작한 이후 서버가 읽은 디스크 읽기(캐시 읽기 아님) 수를 반환합니다.
SELECT @@TOTAL_WRITE -- 마지막으로 시작한 이후 서버가 기록한 디스크 수를 반환합니다.
SELECT * FROM ::fn_virtualfilestats(<database_id, int, >, <file_id, int, >) --로그 파일을 포함하여 데이터베이스 파일의 입축력(I/O) 통계를 반환합니다.

-- 텍스트 및 이미지 함수
SELECT PATINDEX(<pattern, nvarchar, >, <expression, nvarchar, >) -- 지정한 식에서 패턴이 처음 나타나는 시작 위치나 패턴이 없는 경우 0을 반환합니다.
SELECT TEXTPTR(<column, , >) --varbinary 형식의 text, ntext, image 열에 해당하는 텍스트 포인터 값을 반환합니다.
SELECT TEXTVALID(<table.column, nvarchar, >, <text_ptr, , >) --특정 텍스트 포인터의 유효성 여부를 검사하는 text, ntext, image 함수 입니다.

-- 행집합
SELECT * FROM CONTAINSTABLE(<table, , >, <column, , >, <contains_search_condition, varchar, >, <top_n_by_rank, int, >) --단어나 구에 정확하게 일치하거나 비슷하게 일치하는 문자 기반 데이터 형식이 들어 잇는 열의 테이블을 반환합니다.
SELECT * FROM FREETEXTTABLE(<table, , >, <column, , >, <freetext_string, varchar, >, <top_n_by_rank, int, >) --의미에 해당하는 값의 데이터 유형이 문자 기반이지만 정확한 텍스트 단어는 아닌 열의 테이블을 반환합니다.
SELECT * FROM OPENQUERY(<linked_server, , >, <query, varchar, >) -- OLE DB 데이터 원본인 특정 연결 서버에서 지정한 통과 쿼리를 실행합니다.
SELECT * FROM OPENROWSET(<provider_name, varchar, >, <provider_string, varchar, >, <Object, , >, <query, varchar, >) --OLE DB를 사용하여 원격 데이터에 연결 및 액세스하는 한 번의 임의 메서드
SELECT * FROM OPENDATASOURCE(<provider_name, varchar, >, <init_string, varchar, >) --연결된 서버 이름을 사용하지 않고 네 부분으로 된 개체 이름의 일부로 임의 연결 정보를 제공합니다.
SELECT * FROM OPENXML(<idoc, int, >, <rowpattern, nvarchar, >, <flags, byte, >) --XML 문서를 통해 행 집합 뷰를 제공합니다.

-- 커서 함수
SELECT @@CURSOR_ROWS --열려 있는 마지막 커서에서 한정하는 행 수
SELECT @@FETCH_STATUS -- 모든 커서에 대해 실행한 마지막 커서 FETCH문의 상태
SELECT CURSOR_STATUS(<@param1, varchar, >, <cursor_name, nvarchar, >) --프로시저가 특정 매개 변수에 대한 커서 및 결과 집합을 반환하는지의 여부를 보여주는 스칼라 함수

-- 날짜 및 시간 함수
SELECT DATEADD(<datepart, , >, <number, int, >, <date, datetime, >) -- 특정 날짜에 대한 빈도 추가를 기준으로 새 datetime 값을 반환합니다.
SELECT DATEDIFF(<datepart, , >, <startdate, datetime, >, <enddate, datetime, >) -- 지정한 두 날짜 간에 겹쳐지는 날짜 및 시간의 경계 수를 반환합니다.
SELECT DATENAME(<datepart, , >, <date, datetime, >) -- 지정한 날짜의 지정한 부분을 나타내는 문자열을 반환합니다.
SELECT DATEPART(<datepart, , >, <date, datetime, >) -- 지정한 날짜의 지정한 부분을 나타내는 정수를 반환합니다.
SELECT DAY(<date, datetime, >) --지정한 날짜의 일부분을 나타내는 정수를 반환합니다.
SELECT GETDATE() -- 현재 시스템 날짜 및 시간을 반환합니다.
SELECT MONTH(<date, datetime, >) --지정한 날짜의 월 부분을 나타내는 정수를 반환합니다.
SELECT YEAR(<date, datetime, >) --지정한 날짜의 연도 부분을 나타내는 정수를 반환합니다.
SELECT GETUTCDATE() --현재 UTC 시간을 나타내는 날짜/시간 값을 반환합니다.

-- 수치 연산 함수
SELECT ABS(<numeric_expression, numeric, >) -- 특정 숫자 식의 절대적 양수 값을 반환합니다.
SELECT ACOS(<float_expression, real, >) -- 코사인 특정 float식인 각도를 라디안으로 반환합니다.
SELECT ASIN(<float_expression, real, >) -- 사인이 특정float식인 각도를 라디안으로 반환합니다.
SELECT ATAN(<float_expression, float, >) -- 탄젠트가 특정 float식인 각도를 라디안으로 반환합니다.
SELECT ATN2(<float_expression1, float, >, <float_expression2, float, >) -- 사인이 특정 두 float 식 사이인 각도를 라디안으로 반환합니다.
SELECT CEILING(<numeric_expression, numeric, >) --특정 숫자 식보다 크거나 같은 정수 중 가장 작은 값을 반환합니다.
SELECT COS(<float_expression, float, >) -- 특정 식에 대한 특정 각도(라디안)의 삼각 코사인을 반환합니다.
SELECT COT(<float_expression, float, >) --특정 float식에 대한 특정 각도(라디안)의 삼각 코탄젠트를 반환합니다.
SELECT DEGREES(<numeric_expression, numeric, >) --라디안 단위로 각도를 지정하면 해당 각도를 도 단위로 반환합니다.
SELECT EXP(<float_expression, float, >) -- 특정 float 식의 지수 값을 반환합니다.
SELECT FLOOR(<numeric_expression, numeric, >) -- 특정 숫자 식보다 작거나 같은 정수 중 가장 큰 값을 반환합니다.
SELECT LOG(<float_expression, float, >) -- 특정 float 식의 자연 로그를 반환합니다.
SELECT LOG10(<float_expression, float, >) -- 특정 float 식의 base-10 로그를 반환합니다.
SELECT PI() --PI 상수 값을 반환합니다.
SELECT POWER(<numeric_expression, numeric, >, <Y, numeric, >) --특정 식의 값을 지정한 값의 거듭 제곱으로 반환합니다.
SELECT RADIANS(<numeric_expression, numeric, >) -- 숫자 식을 도 단위로 입력하면 라디안으로 반환합니다.
SELECT RAND(<seed, int, >)  --0부터 1까지의 임의 float 값을 반환합니다.
SELECT ROUND(<numeric_expression, numeric, >, <length, int, >, <function, int, >) --특정 길이나 정밀도로 반올림한 숫자 식을 반환합니다.
SELECT SIGN(<numeric_expression, numeric, >) --특정 식의 양수(+1), 영(0), 음수(-1) 기호를 반환합니다.
SELECT SIN(<float_expression, float, >) --특정 각도의 삼각 사인을 근사 숫자(float) 식으로 반환합니다.
SELECT SQUARE(<float_expression, float, >) -- 특정 식의 제곱을 반환합니다.
SELECT SQRT(<float_expression, float, >) -- 특정 식의 제곱근을 반환합니다.
SELECT TAN(<float_expression, real, >) -- 입력한 식의 탄젠트를 반환합니다.