Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

Sys.dm_os_waiting_tasks를 활용하여 실행중인 병렬쿼리의 정보를 확인한다.

SELECT

[owt].[session_id],

[owt].[exec_context_id],

[ot].[scheduler_id],

[owt].[wait_duration_ms],

[owt].[wait_type],

[owt].[blocking_session_id],

[owt].[resource_description],

CASE [owt].[wait_type]

WHEN N'CXPACKET' THEN

RIGHT ([owt].[resource_description],

CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

ELSE NULL

END AS [Node ID],

--[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_os_tasks [ot] ON

[owt].[waiting_task_address] = [ot].[task_address]

INNER JOIN sys.dm_exec_sessions [es] ON

[owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] ON

[es].[session_id] = [er].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

[es].[is_user_process] = 1

ORDER BY

[owt].[session_id],

[owt].[exec_context_id];

GO

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/updated-sys-dm_os_waiting_tasks-script/

 

2015-07-15 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQLServer, 병렬쿼리, DMV, sys.dm_os_waiting_tasks, parallelism, CXPACKET

DBCC DROPCLEANBUFFERS가 작동하지 않을 때

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

DBCC DROPCLEANBUFFERS는 버퍼 풀에서 빈 버퍼를 모두 제거하는 명령이다. 이 명령을 사용하면 서버를 종료하고 다시 시작하지 않아도 완전히 빈 버퍼 캐시를 사용하여 쿼리를 테스트할 수 있다.

 

버퍼풀에서 빈 버퍼를 삭제하려면 CHECKPOINT를 사용하여 빈 버퍼 캐시를 만든다. CHECKPOINT는 현재 데이터베이스에 대한 모든 커밋되지 않은 페이지를 디스크로 기록하고 버퍼를 비운다.

 

다음 내용은 DBCC DROPCLEANBUFFERS 사용하어 빈 버퍼를 제거한 상태에서 SELECT를 이용하여 데이터를 조회하였는데 물리적 읽기가 아닌 논리적 읽기가 발생되었다 왜 이런 현상이 발생 할까?

 

DBCC DROPCLEANBUFFERS는 단지 버퍼풀에서의 클린 페이지만 삭제한다. 클린 페이지는 메모리에서 읽거나 마지막으로 디스크에 기록된 이후 변경되지 않은 것이다. 더티페이지는 마지막으로 변경한 이후 디스크에 기록되지 않은 것이다. 더티페이지를 디스크에 기록하여 클린 페이지가 되지 않으면 DBCC DROPCLEANBUFFERS는 더티페이지를 삭제하지 않는다. 따라서 해당 테이블이 업데이트 되고 DBCC DROPCLEANBUFFERS를 실행하면 테이블의 페이지는 메모리에 남아 있어 물리적 읽기를 요구하지 않는다.

 

데이터베이스에서 모든 페이지가 메모리에서 플러시되려면 DBCC DRPOCLEANBUFFERS를 실행하기전 수동으로 CHECKPOINT를 실행해야한다. 아래 스크립트는 데이터베이스에 대한 페이지가 메모리에 존재하는지 확인한다.

 

SELECT *,

[DirtyPageCount] * 8 / 1024 AS [DirtyPageMB],

[CleanPageCount] * 8 / 1024 AS [CleanPageMB]

FROM

(SELECT

(CASE WHEN ([database_id] = 32767)

THEN N'Resource Database'

ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

SUM (CASE WHEN ([is_modified] = 1)

THEN 1 ELSE 0 END) AS [DirtyPageCount],

SUM (CASE WHEN ([is_modified] = 1)

THEN 0 ELSE 1 END) AS [CleanPageCount]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id]) AS [buffers]

ORDER BY [DatabaseName]

GO

 

 

 

 

[참고자료]

  • DBCC DROPCLEANBUFFERS

https://msdn.microsoft.com/ko-kr/library/ms187762(v=sql.120).aspx

  • When DBCC DROPCLEANBUFFERS doesn't work

http://www.sqlskills.com/blogs/paul/when-dbcc-dropcleanbuffers-doesnt-work/

 

 

2015-07-10 / 강성욱 / http://sqlmvp.kr

 

MSSQL, SQLSERVER, CLEANBUFFERS, DBA, CHECKPOINT, SQL MEMORY

손상된 부트페이지 복구하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

데이터베이스 장애 중 큰 이슈는 부트페이지 손상이다. 부트 페이지가 손상된 경우에는 데이터베이스를 온라인 또는 긴급 모드로 전환될 수 없다. 이번 포스트는 손상된 부트페이지를 복구하는 방법에 대해서 알아본다.

 

부트 페이지를 손상하고 복원하기 위한 테스트 데이터베이스를 생성한다. 생성된 데이터베이스를 분리하여 데이터파일에 사용자가 접근할 수 있도록 한다.

-- Drop old database

USE [master];

GO

 

IF DATABASEPROPERTYEX (N'Company', N'Version') > 0

BEGIN

ALTER DATABASE [Company] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [Company];

END

 

-- Create database and table

CREATE DATABASE [Company] ON PRIMARY (

NAME = N'Company',

FILENAME = N'D:\SQLskills\Company.mdf')

LOG ON (

NAME = N'Company_log',

FILENAME = N'D:\SQLskills\Company_log.ldf');

GO

 

USE [Company];

GO

 

CREATE TABLE [Sales] (

[SalesID] INT IDENTITY,

[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),

[SalesDate] DATETIME DEFAULT GETDATE (),

[SalesAmount] MONEY DEFAULT CONVERT (MONEY, 100 * RAND ()));

 

CREATE CLUSTERED INDEX [SalesCI] ON [Sales] ([SalesID]);

GO

 

-- Populate the table

SET NOCOUNT ON;

GO

 

INSERT INTO [Sales] DEFAULT VALUES;

GO 5000

 

-- Create some nonclustered indexes

CREATE NONCLUSTERED INDEX [SalesNCI_CustomerID] ON [Sales] ([CustomerID]);

 

CREATE NONCLUSTERED INDEX [SalesNCI_SalesDate_SalesAmount] ON [Sales] ([SalesDate]) INCLUDE ([SalesAmount]);

GO

 

-- Create a good backup

BACKUP DATABASE [Company] TO DISK = N'C:\SQLskills\OldCompany.bck'

WITH INIT;

 

-- And detach it

USE [master]

GO

 

EXEC sp_detach_db N'Company';

GO

 

데이터파일의 분리가 완료 되었으면 16진수 편집기(헥사에디터)를 사용하여 데이터파일을 오픈한다.

 

 

오픈한 데이터파일에서 부트페이지의 오프셋(offset)으로 이동한다. 오프셋은 항상 9페이지에 있기 때문에 8192*9 = 73728 오프셋에 있다.

 

선택 창이 나타나면 10진수(dec)와 선두부터 시작하는 옵션을 선택한다.

 

데이터베이스 이름을 포함하여 부팅 페이지의 내용을 볼 수 있다.

 

데이터베이스 이름에 이르기까지 모든 라인을 선택한 후 마우스 오른쪽 버튼을 클릭하고 채우기를 선택한다.

 

선택한 영역을 00으로 채운다.

 

00으로 채우고 나면 아래 그림과 같다.

 

데이터 변경이 완료 되었으면 저장한 다음 에디터를 종료한다. 이제 데이터파일은 손상된 상태로 설정이 완료 되었다.

 

데이터베이스를 연결을 시도해본다.

USE [master];

GO

 

-- Try attaching it again

EXEC sp_attach_db @dbname = N'Company',

@filename1 = N'D:\SQLskills\Company.mdf',

@filename2 = N'D:\SQLskills\Company_log.ldf';

GO

 

연결이 실패하면서 오류가 반환된다.

Msg 1813, Level 16, State 2, Line 5

Could not open new database 'Company'. CREATE DATABASE is aborted.

Msg 824, Level 24, State 2, Line 5

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

 

손상된 데이터베이스를 복구하기 위해 손상된 데이터베이스 파일을 복사하고 더미 데이터베이스를 생성한 다음 오프라인으로 설정한다.

CREATE DATABASE [Company] ON PRIMARY (

NAME = N'Company',

FILENAME = N'D:\SQLskills\Company.mdf')

LOG ON (

NAME = N'Company_log',

FILENAME = N'D:\SQLskills\Company_log.ldf');

GO

 

ALTER DATABASE [Company] SET OFFLINE;

GO

 

생성된 더미 데이터파일을 삭제하고 원래 손상된 데이터베이스 파일을 복사한다. 그리고 온라인으로 실행 한다.

ALTER DATABASE [Company] SET ONLINE;

GO

 

하지만 연결이 실패되고 작동하지 않는다.

Msg 5181, Level 16, State 5, Line 33

Could not restart database "Company". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 33

ALTER DATABASE statement failed.

Msg 824, Level 24, State 2, Line 33

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

현재 데이터베이스 상태는 서스펙트 상태로 나타난다.

SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');

GO

 

데이터베이스 복구를 위해 응급모드로 변경을 시도하지만 실패 한다.

ALTER DATABASE [Company] SET EMERGENCY;

GO

ALTER DATABASE [Company] SET SINGLE_USER;

GO

 

Msg 824, Level 24, State 2, Line 43

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

손상된 부트페이지는 데이터베이스에 액세스 할 수 없기 때문에 작동하지 않는다. 손상된 부트페이지를 복원하기 위해 16진수 편집기를 사용해서 문제를 해결할 수 있다. 현재 생성되어 있는 데이터베이스를 제거하고 다시 손상된 데이터파일을 복사한다.

ALTER DATABASE [Company] SET OFFLINE;

GO

 

-- ***** Copy off the corrupt files

 

DROP DATABASE [Company];

GO

 

이전의 정상적인 백업 파일을 복원한 다음 헥사에디터에서 파일을 오픈할 수 있도록 오프라인으로 설정한다.

RESTORE DATABASE [Company] FROM

DISK = N'C:\SQLskills\OldCompany.bck'

WITH REPLACE;

GO

 

ALTER DATABASE [Company] SET OFFLINE;

GO

 

백업 파일에서 복원된 데이터베이스의 부트페이지로 이동한다.

 

복원된 사본에는 부팅페이지가 손상되지 않은 것을 볼 수 있다. 부트페이지 오프셋을 복사한다. 이 오프셋은 모든 데이터베이스에 동일하다.(12000 ~ 14000 까지 복사)

 

손상된 파일의 부팅 오프셋으로 이동하여 복사한 부팅 페이지를 덮어쓴다.

 

빨간색으로 복사된 것을 덮어쓴 것을 확인 할 수 있다. 저장하고 편집기를 종료한다

 

현재 복원된 파일을 삭제하고 손상된 파일의 이름을 바꾼다. 그리고 데이터베이스를 온라인 한다.

ALTER DATABASE [Company] SET ONLINE;

GO

 

복구된 부트페이지와 트랜잭션로그가 일치하지 않기 때문에 오류가 발생한다. 긴급모드로 변경하여 CHECKDB를 실행하여 복구 한다.

ALTER DATABASE [Company] SET EMERGENCY;

GO

ALTER DATABASE [Company] SET SINGLE_USER;

GO

DBCC CHECKDB (N'Company', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

 

 

DBCC CHECKDB 작업이 완료되면 데이터베이스에 액세스가 가능하다.

 

[참고자료]

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-a-broken-boot-page/

 

 

2015-07-09 / 강성욱 / http://sqlmvp.kr

 

 

SQLServer, MSSQLSERVER, MSSQL, 데이터베이스 복구, DB복구,DB손상, DBA

SQL Server Page Life Expectancy (PLE)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server Page Life Expectancy(PLE)는 페이지 참조 없이 데이터가 버퍼 풀에 남아 있는 시간으로 성능 카운터의 SQL Server:Buffer Manager, SQL Server:Buffer Node에서 현재 값을 확인 할 수 있다.

 

  • SQL Server:Buffer Manager / Page life expectancy : 페이지가 참조 없이 버퍼풀에 남아 있는 시간(초)
  • SQL Server:Buffer Node / Page life expectancy : 페이지가 참조 없이 노드에서 버퍼풀에 남아 있는 시간(초).

 

여기에 표시되는 값으로 버퍼풀에 데이터가 머무르는 시간을 확인 할 수 있다. 하지만 여기에서 나타내는 PLE 값이 정말 유용한 정보인지 생각해 보아야 한다. 오늘날의 대부분 시스템들은 NUMA를 사용한다. 그래서 버퍼 풀은 각 NUMA 노드로 분리된 자신의 버퍼풀 목록을 관리한다.

 

Buffer Manager: Page life expectancy카운터는 각 노드의 버퍼풀의 PLE를 추가한 다음 평균을 계산한다. 이 수치는 우리가 생각하는 산술평균보다 값이 낮다. (버퍼 관리자의 비율 평균 : http://en.wikipedia.org/wiki/Harmonic_mean). 이 뜻은 각 NUMA 노드에서 어떤 일이 일어나는지 진정한 의미가 부여 되지 않음을 의미한다.

 

예를 들어 8 NUMA 노드에서 PLE가 4000이면 전반적인 PLE는 4000이다.

4 / (1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000)) / 1000 = 4000

 

하나의 노드 PLE가 2200으로 떨어지면 전체는 3321이 된다.

4 / (1/(1000 x 2200) + 1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000)) / 1000 = 3321

 

하나의 노드 PLE가 200으로 떨어진다면 전체는 695가 된다.

4 / (1/(1000 x 200) + 1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000)) / 1000 = 695

 

이 수치는 서버가 전반적으로 강한 압력을 받고 있다고 생각되지만 하나의 노드를 제외한 나머지는 아무런 문제가 없다. 따라서 버퍼풀의 압력에 대한 정확한 뷰를 확인하여면 각 노드에서의 PLE를 확인하여 경고에 대한 적절한 임계값을 찾아야 한다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

http://blogs.msdn.com/b/psssql/archive/2015/05/14/sql-server-page-life-expectancy-ple.aspx

 

2015-05-20 / 강성욱 / http://sqlmvp.kr

 

Sqlserver, mssqlserver, Page life expectancy, PLE, 버퍼풀, buffer pool, NUMA

+ Recent posts