아래 로그를 보면 checkpoint I/O에 대한 내용이다.
TF3504를 걸면 저런 로그를 확인할 수 있다. 그냥 참고용.
FlushCache is the SQL Server routine that performs the checkpoint operation. The following message is output to the SQL Server error log when trace flag (3504) is enabled.
2012-05-30 02:01:56.31 spid14s FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms (avoided 11796 new dirty bufs) for db 6:0
2012-05-30 02:01:56.31 spid14s average throughput: 24.49 MB/sec, I/O saturation: 68365, context switches 80348
2012-05-30 02:01:56.31 spid14s last target outstanding: 1560, avgWriteLatency 15
Prior to SQL Server 2012 the trace flag had to be enabled in order to output the information to the SQL Server error log. (Trace flag was the only way to obtain the output.)
SQL Server 2012 adds an additional condition (is long checkpoint) to the logic. If the trace flag is enabled or the checkpoint ‘TRUE == IsLong’ the message is added to the SQL Server error log.
Is Long Checkpoint: A long checkpoint is defined as a ‘FlushCache / checkpoint’ operation on a database that has exceeded the configured recovery interval.
If your server does not have the trace flag enabled (use dbcc tracestatus(-1) to check) the message is indicating that the checkpoint process, for the indicated database, exceeded the configured recovery interval. If this is the case you should review your I/O capabilities as well as the checkpoint and recovery interval targets.
Not meeting the recovery interval target means that recovery from a crash could exceeded operational goals.
키워드 : TF3504, checkpoint, flushcache
'SQL Server' 카테고리의 다른 글
|Editions and Supported Features for SQL Server 2016 (0)||2017.05.22|
|SQL Server 연결 풀링 - 성능 카운터(ADO.NET) (0)||2017.05.18|
|How It Works: When is the FlushCache message added to SQL Server Error Log (0)||2017.04.26|
|SQL 테이블 PK 추가 및 삭제하기 (0)||2017.04.10|
|SQL Server 가용성그룹 로그백업 LSN 에 대하여 (0)||2017.02.24|
|SQL Server 업그레이드 관리자 (0)||2016.11.08|