Last week i created my first SQL Instance in Amazon RDS, Its pretty simple to create an instance and like any other Managed database service it does offers automated backups ,patching,monitoring etc and its easy to scale up as per your needs.
As Sql Error log is DBA's great friend i thought of going through the error log and it always used to give me some great information and something interesting stroked me, the startup parameter they have used...........
2018-01-24 15:55:47.620 Server Registry startup parameters:
-d D:\RDSDBDATA\DATA\master.mdf
-e D:\RDSDBDATA\Log\ERROR
-l D:\RDSDBDATA\DATA\mastlog.ldf
-k 20.000000
-T 3226
-T 7806
yes its says about the master db file,error log location , Trace flag 3226 used to suppress logging of successful backup in errorlog , Trace Flag 7806 which is used to enable DAC on SQL Express edition(i created a express instacen). So whats -K option ??? i checked in Microsoft docs on Database Engine Service Startup Options but didn't get any information and i didn't get any usefully information from worlds favorite search engine too 😓 or i didn't user the correct key words to search 😋
After a long time searching got details on '-K' from the Checkpoint documentation. It is used to throttle I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints.
In some cases, checkpoints might issue a substantial amount of I/O, causing the I/O subsystem to get flood with write requests, which can severely affect read performance.SQL Server includes a commandline option that allows throttling of checkpoint I/O by specifying –k parameter, followed by a decimal number, which can be added to the list of startup parameters for the SQL Server service. The value specified indicates the number of megabytes per second that the checkpoint process can write. By using this –k option, the I/O overhead of checkpoints can be spread out and have a more measured effect.By default, the checkpoint process makes sure that SQL Server can recover databases within the recovery interval that you specify.So by using -k option may cause longer recovery time based on the specified value.The –k option doesn’t apply to indirect checkpoints. So in this case checkpoint I/O is throttled to 20 megabytes per second
I have discussed this with a few DBA's on this option and no one had used this option or came across a scenario where they have observed checkpoint was causing I/O issues. If you guys have come across such a scenario please do share them in comments
