LOCK 프로세스 정보확인

데이터베이스/MS-SQL 2014. 1. 3. 15:33 Posted by 초절정고수


DBCC INPUTBUFFER(SPID)

'데이터베이스 > MS-SQL' 카테고리의 다른 글

SQL Server 2005 암호화 함수  (0) 2013.12.11
DeadLock(교착상태) 모니터 하기  (0) 2013.11.27
tempdb 관리  (0) 2013.11.27
mdf 파일만 가지고 복구시키기  (0) 2013.11.27
MSSQL 문자열 함수  (0) 2013.11.01

SQL Server 2005 암호화 함수

데이터베이스/MS-SQL 2013. 12. 11. 17:39 Posted by 초절정고수

MD5

SELECT SUBSTRING(sys.fn_varbintohexstr(HASHBYTES('MD5', 'abcd12345')), 3, 32)


SHA1




'데이터베이스 > MS-SQL' 카테고리의 다른 글

LOCK 프로세스 정보확인  (0) 2014.01.03
DeadLock(교착상태) 모니터 하기  (0) 2013.11.27
tempdb 관리  (0) 2013.11.27
mdf 파일만 가지고 복구시키기  (0) 2013.11.27
MSSQL 문자열 함수  (0) 2013.11.01

DeadLock(교착상태) 모니터 하기

데이터베이스/MS-SQL 2013. 11. 27. 18:53 Posted by 초절정고수

원본 글 :  http://sqlmvp.kr/140164120810

 

 

최근 들어 서비스에서 DeadLock(교착상태이슈가 자주 발생 하여 이번 아티클은 DeadLock을 모니터링하는 방법에 대해서 알아 보도록 하겠습니다..

DeadLock(교착상태)?

한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하는 현상. (서로 맞물린 상태의 차단)

l 순환 교착(cycle DeadLock) : 서로 다른 개체를 차단할 때 발생

l 변환 교착(Conversion DeadLock) : 같은 대상에 대해 둘 이상의 세션이 동시에 잠금을 변경하려고 할 때발생

교착 상태를 일으킬 수 있는 리소스

l 잠금 : 개체페이지메타데이터응용 프로그램 등의 리소스에 대한 잠금을 획득하려고대기 하는 경우.

l 작업자 스레드 : 사용 가능한 작업자 스레드를 대기하는 태스크가 교착 상태를 일으킬 수 있음.대기태스크가 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착상태 발생.

l 메모리 : 동시 요청이 사용 가능한 메모리보다 많은 메모리 부여를 대기하는 경우.

l 병렬 쿼리실행 관련 리소스 : 병렬쿼리에 속하지 않는 하나 이상의 다른 프로세스를 포함할 경우 서로 차단하여발생서버에서 새 쿼리 실행이 시작되거나 시스템에 작업자 스레드가 부족하여 시스템 작업이 예기치 않게변경되면 교착 상태가 발생.

l MARS(Multiple Active Result Sets)리소스 : MASRS에서여러 활성 요청의 인터리브를 제어하는데 사용.

DeadLock 감지하기.

교착 상태 검색은 데이터베이스 엔진 인스턴스의 모든 태스크에 대한 검색을 주기적으로 시작하는 잠금 모니터에서수행합니다.

l 기본 간격은 5초로 수행.

l 잠금 모니터스레드가 교착 상태를 발견하면 잠금 상태의 빈도에 따라 5초에서 최하100밀리초까지 교착 상태 검색 간격이 짧아 짐.

l 잠금 모니터스레드가 교착 상태 검색을 중지하면 데이터베이스 엔진은 검색 간격을 다시 5초로 늘림.

실습 환경

CREATE PROC SESSION_1

AS

BEGIN TRAN

UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100

WAITFOR DELAY '00:00:03';

UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100

ROLLBACK TRAN

GO

CREATE PROC SESSION_2

AS

BEGIN TRAN

UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100

WAITFOR DELAY '00:00:03';

UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100

ROLLBACK TRAN

GO

서로 다른 세션에서SESSION_1, SESSION_2 실행

1. sp_lock

1.png

2. DBCC TRACEON (1204, -1)

2.png

교착상태와 관련된 각 노드에 의해 형식이 지정된 교착 상태 정보를 보고.

3.png

3. DBCC TRACEON (1222, -1) --SQL Server 2005 이상사용

프로세스별  리소스별 순서로 교착상태의 정보를 보고

4.png

4. Profiler

5.png

6.png

5. Perfmon

7.png

6. DMV

SELECT TEXT, BLOCKING_SESSION_ID, COMMAND, DATABASE_ID,WAIT_TYPE,WAIT_RESOURCE, *

FROM SYS.DM_EXEC_REQUESTS AS REQUEST

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(REQUEST.SQL_HANDLE) AS SQL_TEXT

WHERE SESSION_ID > 50 AND SESSION_ID <> @@SPID

AND BLOCKING_SESSION_ID <> 0

7_1.png

7. Job Alert

8.png

9.png

DeadLocK 최소화 하기

l 적절한 인덱스설정.

l 자원의 한쪽방향 액세스,

l 짧은 트랜잭션

l 테이블 크기최소화

l 잠금 제한시간 설정(SET LOCK_TIMEOUT)

l 적절한 격리수준(READ UNCOMMITTED)

l 하드웨어성능 향상

l 지속적인모니터링

 

참조 및 참고 사이트

l http://msdn.microsoft.com/ko-kr/library/ms178104(v=sql.105).aspx

l http://www.sqler.com/459038

l http://www.sqler.com/320551

l http://sqlserverpedia.com/wiki/Trace_Flags

'데이터베이스 > MS-SQL' 카테고리의 다른 글

LOCK 프로세스 정보확인  (0) 2014.01.03
SQL Server 2005 암호화 함수  (0) 2013.12.11
tempdb 관리  (0) 2013.11.27
mdf 파일만 가지고 복구시키기  (0) 2013.11.27
MSSQL 문자열 함수  (0) 2013.11.01

tempdb 관리

데이터베이스/MS-SQL 2013. 11. 27. 18:53 Posted by 초절정고수

http://www.sqler.com/index.php?mid=bColumn&document_srl=589035




Tempdb 데이터베이스를 많이 사용하면 SQL Server에서 페이지를 할당 하려고 할때 경합이 발생 할 수 있다.

--tempdb를 많이 사용하는 예

  • 임시 테이블(로컬 또는 전역)을 반복해서 만들고 삭제
  • 테이블 변수
  • CURSORS와 관련된 작업 테이블
  • ORDER BY
  • GROUP BY
  • HASH PLANS

 -- 경합 확인 방법

SELECT * FROM SYS.SYSPROCESSES 시스템 테이블 출력에서

waitresource가 2:1:1(PFS 페이지)또는 2:1:3(SGAM 페이지)로 표시되며 경합 정도에 따라 SQL Server가 응답하지 않는것 처럼 나타날 수 있다.

--원인

혼합 익스텐트에서 페이지 할당의 일환으로 SQL Server는 PFS(Page Free Space) 페이지를 검색하여 어떤 혼합 페이지를 할당할 수 있는지 확인해야 합니다. PFS 페이지는 모든 페이지에서 사용할 수 있는 여유 공간을 추적하고 각 PFS 페이지는 약 8000 페이지를 추적합니다. PFS 및 SGAM 페이지를 변경하기 위해 적절한 동기화가 유지되며 이를 통해 잠시 동안 다른 수정 작업을 막을 수 있습니다.

-- 해결 방법

  • 동일한 크기의 tempdb 데이터 파일 수 늘리기.
  • 별도의 디스크에 tempdb 할당
  • tempdb 자동증가 해제 (비례 채우기 알고리즘은 모든 파일에 할당을 유포하는 대신 GAM 할당에 가장 큰 파일을 사용)
  • -T1118 매개변수 사용.(mixed exten가 아닌 uniform extent 모드로 고정. 처음부터 64K extent 생성.)


-- 참고 링크


'데이터베이스 > MS-SQL' 카테고리의 다른 글

SQL Server 2005 암호화 함수  (0) 2013.12.11
DeadLock(교착상태) 모니터 하기  (0) 2013.11.27
mdf 파일만 가지고 복구시키기  (0) 2013.11.27
MSSQL 문자열 함수  (0) 2013.11.01
datepart 함수의 인수값 정리  (0) 2013.09.27

mdf 파일만 가지고 복구시키기

데이터베이스/MS-SQL 2013. 11. 27. 18:41 Posted by 초절정고수

SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

Fix : Error Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

This errors happens when corrupt database log are attempted to attach to new server. Solution of this error is little long and it involves restart of the server. I recommend following all the steps below in order without skipping any of them.

Fix/Solution/Workaround:

SQL Server logs are corrupted and they need to be rebuilt to make the database operational.
Follow all the steps in order. Replace the yourdatabasename name with real name of your database.


1. Create a new database with same name which you are trying to recover or restore. (In our error message it is yourdatabasename). Make sure the name of the MDF file (primary data file) and LDF files (Log files) same as previous database data and log file.

2. Stop SQL Server. Move original MDF file from older server (or location) to new server (or location) by replacing just created MDF file. Delete the LDF file of new server just created.

3. Start SQL Server. Database will be marked as suspect, which is expected.

4. Make sure system tables of Master database allows to update the values.
USE MASTER
GO
sp_CONFIGURE 'allow updates'1
RECONFIGURE WITH OVERRIDE
GO

5. Change database mode to emergency mode.
–Following statement will return the current status of the database
SELECT *
FROM sysdatabases
WHERE name 'yourdatabasename'

—-Following statement will update only one row in database
BEGIN
UPDATE 
sysdatabases
SET status 32768
WHERE name 'yourdatabasename'
COMMIT TRAN

6. Restart SQL Server (This is must, if it is not done SQL Server will through an error)

7. Execute this DBCC command in query window of Management Studio, this will create new log file. Keep the name of this file same as LDF file just deleted from new server :
DBCC TRACEON (3604)
DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')
GO

DBCC accepts two parameters : first parameter is database name and second parameter is physical path of the log file. Make sure the path is physical, if you put logical file name it will return an error.

8. Reset the database status using following command.
sp_RESETSTATUS yourdatabasename
GO

9. Turn off the update to system tables of Master database running following script.
USE MASTER
GO
sp_CONFIGURE 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO

This should be resolve the problem mentioned above. I always check consistence of the database as well as I reset the status of the database to original status.

10. Reset the database status to previous status
–Following statement will update only one row in database
BEGIN
UPDATE 
sysdatabases
SET status (value retrieved IN first query OF STEP 5)
WHERE name 'yourdatabasename‘
COMMIT TRAN
GO'

Note : If during steps 8, 9 , 10 if there is error if database is in use.
Set the database to status single user.
sp_DBOPTION 'yourdatabasename''single user','true'
Once the steps 8,9,10 are completed if database is not already in multi user mode run this script.
sp_DBOPTION 'yourdatabasename''single user','false'
If there is any issue while doing above process to fix the error let me know. Make sure that you have done all the steps in order and restarted SQL Server where it is mentioned.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL (many topics)


'데이터베이스 > MS-SQL' 카테고리의 다른 글

SQL Server 2005 암호화 함수  (0) 2013.12.11
DeadLock(교착상태) 모니터 하기  (0) 2013.11.27
tempdb 관리  (0) 2013.11.27
MSSQL 문자열 함수  (0) 2013.11.01
datepart 함수의 인수값 정리  (0) 2013.09.27

MSSQL 문자열 함수

데이터베이스/MS-SQL 2013. 11. 1. 00:23 Posted by 초절정고수


※ 문자열 함수 정리

1) Ascii() - 문자열의 제일 왼쪽 문자의 아스키 코드 값을 반환(Integer)

예) SELECT Ascii('abcd')

>> 결과는 a의 아스키 코드값인 97 반환

1-1) Char() - 정수 아스키 코드를 문자로 반환(Char)

예) SELECT Char(97) >> 결과는 a 반환 

1-2)Nchar() - 지정한 정수 코드의 유니코드 문자 반환

예) SELECT Nchar(65) 결과 >> A

1-3)Unicode() - 식에 있는 첫번째 문자의 유니코드 정수 값을 반환

예)SELECT Unicode('abcde') 결과 >> 97


3) Charindex() - 문자열에서 지정한 식의 위치를 반환 ????

예) SELECT Charindex('b','abcde') >> 결과 : 2 1,2,3 
SELECT Charindex('b','abcde',2) >> 결과 : 2
SELECT Charindex('b','abcde',3) >> 결과 : 0

-- 인수값이 3개일때 마지막은 abcde 에서의 문자열 검색 시작위치를 말하며

2인경우는 bcde 라는 문자열에 대해서 검색

3인 경우는 cde 라는 문자열에 대해서 검색 하게 된다.

5) Left() - 문자열에서 왼쪽에서부터 지정한 수만큼의 문자를 반환 1,2,3 이네.. mssql은

예) SELECT Left('abced',3) 결과 >> abc
Right() - 문자열의 오른쪽에서 부터 지정한 수 만큼 반환(Left() 와 비슷 )

예) SELECT Right('abcde',3) 결과>> cde

6) Len() - 문자열의 길이 반환

예) SELECT Len('abced') 결과>>5

7) Lower() - 대문자를 소문자로 반환

예) SELECT Lower('ABCDE') 결과 >> abcde

Upper() - 소문자를 대문자로 반환

예) SELECT Upper('abcde') 결과>> ABCDE

8) Ltrim() - 문자열의 왼쪽 공백 제거

예) SELECT Ltrim(' AB CDE') 결과>> AB CDE

Rtrim() - 문자열의 오른쪽 공백 제거

예) SELECT Rtrim(' ab cde ') 결과>> ' ab cde' <-- 공백구분을위해 ' 표시 

10) Replace - 문자열에서 바꾸고 싶은 문자 다른 문자로 변환

예) SELECT Replace('abcde','a','1') 결과>>1bcde

11) Replicate() - 문자식을 지정한 횟수만큼 반복

예) SELECT Replicate('abc',3) 결과>> abcabcabc

12) Reverse() - 문자열을 역순으로 출력

예) SELECT Reverse('abcde') 결과>> edcba

15) Space() - 지정한 수만큼의 공백 문자 반환

예) SELECT Space(10) 결과 >> ' ' -- 그냥 공백이 나옴

확인을 위해서 SELECT 'S'+Space(10)+'E' 결과 >> S E

16) Substring() - 문자,이진,텍스트 또는 이미지 식의 일부를 반환

예) SELECT Substring('abcde',2,3) 결과>> bcd

※ 기타 함수 Tip

19) Isnumeric - 해당 문자열이 숫자형이면 1 아니면 0을 반환

>> 숫자 : 1 , 숫자X :0

예) SELECT Isnumeric('30') 결과 >> 1

SELECT Isnumeric('3z') 결과 >> 0

20) Isdate() - 해당 문자열이 Datetime이면 1 아니면 0
lsdate(4444) >>결과 0 >> 날짜 : 1 , 날짜 X :0 



[출처] MSSQL 문자열 함수|작성자 빵호

[출처] MSSQL 문자열 함수|작성자 빵호


'데이터베이스 > MS-SQL' 카테고리의 다른 글

SQL Server 2005 암호화 함수  (0) 2013.12.11
DeadLock(교착상태) 모니터 하기  (0) 2013.11.27
tempdb 관리  (0) 2013.11.27
mdf 파일만 가지고 복구시키기  (0) 2013.11.27
datepart 함수의 인수값 정리  (0) 2013.09.27

datepart 함수의 인수값 정리

데이터베이스/MS-SQL 2013. 9. 27. 20:45 Posted by 초절정고수

DATEPART 함수는 지정한 날짜의 특정 부분을 나타내는 정수를 반환합니다.


1. 구문

DATEPART ( datepart , date )


2. 인수

날짜 부분 

약어 (datepart) 

 year

yy, yyyy 

quarter 

qq, q 

month 

mm, m 

day of year 

dy, y 

day 

dd, d 

week 

wk, ww 

weekday 

dw 

 hour

hh 

minute 

mi, n 

second 

ss, s 

millisecond 

ms 


3. 사용 예

select datepart(mm, getdate())    --현재 을 취득




'데이터베이스 > MS-SQL' 카테고리의 다른 글

SQL Server 2005 암호화 함수  (0) 2013.12.11
DeadLock(교착상태) 모니터 하기  (0) 2013.11.27
tempdb 관리  (0) 2013.11.27
mdf 파일만 가지고 복구시키기  (0) 2013.11.27
MSSQL 문자열 함수  (0) 2013.11.01