1. Indirect Checkpoint
Prior to SQL Server 2012, SQL Server Database Engine Support 3 type of checkpoints: Automatic, manual and internal. Now there is a new checkpoint type added : Indirect Checkpoint.
Not like automatic checkpoint(sp_configure 'recovery interval'), Indirect Checkpoing is set on the database level:
ALTER DATABASE [DatabaseName] SET TARGET_RECOVERY_TIME = 15 SECONDS;
By default, the target recovery time is 0, and Indirect Checkpoing is disabled. Here are some excellent posts which is made by MSFT PFE.
http://sqluninterrupted.com/2012/03/19/target_recovery_time-indirect-checkpoint-sql-server-2012/
http://www.sqlserverfaq.net/2012/03/18/185/
http://blogs.technet.com/b/sqlpfeil/archive/2012/06/13/target-recovery-time-new-database-option-in-sql-server-2012.aspx
The design goal of Indirect Checkpoint is to accurately control the recovery time. In some case, Automatic Checkpoint might be able to cause spiky disk io and performance issue.
By setting a smaller value of Target Recovery Time, the Indirect Checkpoint generates much smoother constant checkpoint instead of spiky checkpoint. However the data page is flushed out to disk more frequently, the thoughput will degrade and disk IO workload will be increased. So making a full testing before you apply it on product will be a good idea.
Another change for checkpoint is the FlushCache message in the error log file
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, you need to enable it with trace flag 3504. Now SQL Server 2012 add a new check condition logical
If the trace flag is enabled or the checkpoint 'TRUE == IsLong' the message is added to the SQL Server error log.
I got this info from
http://blogs.msdn.com/b/psssql/archive/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log.aspx
I am thinking if Indirect Checkpoint is worth to try in this situation :), so we can smooth out the spiky checkpoint with constant smaller checkpoint
2. Memory Management
This's a big change in SQL Server 2012.
- Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB).
- Memory_to_reserve value does not include the multi-page allocations
(does it mean multi-page allocations has been moved into buffer pool in SQL 2012? )
- The "awe enabled" SQL Server feature is deprecated
If you still use sql server 32bit version, you can not use more than 4GB(32-bit SQL Server on 64-bit OS) memory any more
- Standard/Business Intelligence Edition support "locked pages" now.
Refer to
http://blogs.msdn.com/b/sqlosteam/archive/2012/06/20/sql-server-2012-memory-manager-kb-articles.aspx
No comments:
Post a Comment