June 16, 2016 at 9:42 pm
Comments posted to this topic are about the item Checkpoints
June 16, 2016 at 10:34 pm
This was removed by the editor as SPAM
June 16, 2016 at 10:47 pm
Good question, thanks.
...
June 16, 2016 at 10:53 pm
Learned something today too!
June 17, 2016 at 1:01 am
Those are the checkpoints in SQL Server 2012 and 2014 as well. The difference with SQL Server 2016 is that in 2016 the Indirect type is by default set on database creation.
Igor Micev,My blog: www.igormicev.com
June 17, 2016 at 7:26 am
Nice question to end the week.
June 17, 2016 at 9:51 am
Ed Wagner (6/17/2016)
Nice question to end the week.
+1
I am surprised that only 50 percent of answers were correct.
June 17, 2016 at 9:56 am
Igor Micev (6/17/2016)
Those are the checkpoints in SQL Server 2012 and 2014 as well. The difference with SQL Server 2016 is that in 2016 the Indirect type is by default set on database creation.
To do that it has to set a target recovery time for the database, and that time must not be the default (0) because setting the target recovery time to zero causes the database to use Automatic Checkpoints (with either default recovery interval or whatever recovery interval was specified for the instance using sp_configure), not Indirect Checkpoints. This is clearly documented in https://msdn.microsoft.com/en-us/library/ms189573.aspx.
Create Database (https://msdn.microsoft.com/en-us/library/ms176061.aspx) has no means to set a target recovery time other than the default, so it can't set Indirect type.
So I don't think that indirect type checkpoints are set by default on database creation in SQL Server 2016.
Tom
June 17, 2016 at 10:03 am
Revenant (6/17/2016)
Ed Wagner (6/17/2016)
Nice question to end the week.+1
I am surprised that only 50 percent of answers were correct.
+1
Perhaps a lot of people haven't read the documentation and are just guessing - I can't imagine any other reason.
Tom
June 17, 2016 at 10:07 am
TomThomson (6/17/2016)
Igor Micev (6/17/2016)
Those are the checkpoints in SQL Server 2012 and 2014 as well. The difference with SQL Server 2016 is that in 2016 the Indirect type is by default set on database creation.To do that it has to set a target recovery time for the database, and that time must not be the default (0) because setting the target recovery time to zero causes the database to use Automatic Checkpoints (with either default recovery interval or whatever recovery interval was specified for the instance using sp_configure), not Indirect Checkpoints. This is clearly documented in https://msdn.microsoft.com/en-us/library/ms189573.aspx.
Create Database (https://msdn.microsoft.com/en-us/library/ms176061.aspx) has no means to set a target recovery time other than the default, so it can't set Indirect type.
So I don't think that indirect type checkpoints are set by default on database creation in SQL Server 2016.
Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016.
in
Igor Micev,My blog: www.igormicev.com
June 17, 2016 at 10:11 am
Revenant (6/17/2016)
Ed Wagner (6/17/2016)
Nice question to end the week.+1
I am surprised that only 50 percent of answers were correct.
It's a big percentage for such a question (very internal one). Many don't even know what is the question. Many even reading the documentation could not understand it. It's a very sql-server internal question.
Igor Micev,My blog: www.igormicev.com
July 13, 2016 at 2:22 pm
Good question. A nice short dip into internals is always good.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply