Failover Time-Prediction

In the previous post, we learned the basic definition of SQL’s new feature AlwaysOn and the various events that occur when a failover happens.

Today, we will learn how SQL Database engine predicts the time involved during the failover which can be very helpful for deciding the Service Level Agreement(SLA) of the Recovery Time Objective(RTO)  for high availability solutions.

Depending when the SQL Server Instance last performed the checkpoint operation, there can be substantial amount of dirty pages in the buffer pool. The failover lasts as long as it takes time to write the dirty pages from buffer cache to disk which can lead to long and unpredictable time.

In SQL 2102, the Failover Cluster Instance (FCI) can use the Indirect Checkpoints to control the amount of dirty pages kept in the buffer cache. This makes the time more predictable and configurable.

To understand this more, lets see Checkpoints and its type.

What’s a CheckPoint?

Whenever a modification is made to a database the changes are not directly applied to physical files (MDF & LDF). Instead for performance reasons the Database Engine performs these modifications in memory known as buffer cache. A checkpoint is issued periodically on each database which then further flushes the changes to disk. A checkpoint also writes the information about the transaction log from memory to disk.
What are the different types of CheckPoint?
There are 4 types of Checkpoints-

Automatic Checkpoint
Indirect Checkpoint
Manual Checkpoint
Internal Checkpoint

Automatic:- It is issued automatically in background. This is a server level configuration decided for all the databases present in the instance. The recovery interval option decides the maximum time it should take to recover the databases in the event of system crash,clean shutdown or a failover. The automatic checkpoint is issued considering this recovery interval option. The default recovery interval is 0 which results in automatic checkpoints every minute and it recovers in less than a minute.

To change the recovery interval to 2 minutes we can use below example.

USE AdventureWorks2012 ;


EXEC sp_configure ‘show advanced options’, 1;




EXEC sp_configure ‘recovery interval’, 2 ;





Indirect- is again issued in background but this time it is issued to meet the user defined target recovery time for a give database. This is a new feature introduced in SQL Server 2012. It is much faster than automatic checkpoint but at the cost of high I/O spikes.

The Automatic checkpoint determines the number of transactions to determine the recovery time whereas Indirect uses the number of dirty pages. Indirect checkpoints ensures that the dirty pages are below certain threshold so that the recovery time is within the target time.

When indirect checkpoint is enabled on a database and a database which receives large DML operations, the background writer aggressively start flushing the dirty pages to disk causing additional I/O.

The below example changes the target recovery time to 60 seconds.



Manual- Issue when a manual TSQL script is executed with the syntax of CHECKPOINT.

IF issued without any parameter then it will take the default time to complete the checkpoint and if issued with the parameter value say 5 then it will attempts to complete the operation in 5 seconds.

The less time specified will result to more resource usage. Therefore one should be careful when using the parameter value.


Internal- This is issued by various server operations like, backups, a file have been added or removed by using the Alter Database, or a database snapshot is created, database shutdown is performed.


Now that we know what is the use of checkpoint and the various checkpoints let’s understand how the indirect checkpoint helps in recovering the database in short period of time only.


Prior to SQL 2012, if recovery time was required to be changed then update was required to be made at server level which resulted to set across all the databases and since the default value was 1 all we could do to update the value was to set to a longer time.

With SQL Server 2012, the target recovery time of a user database can now be changed. Meaning that, if there are more critical databases then we can change the Target recovery time of a database lets say to 30 seconds then if there is a sudden shutdown or a failover happens then this database can recover in 30 seconds and rest other databases will recover in their default 1 minute time.

Thus the discussion ends here summarizing that the FCI can use this indirect checkpoints for the databases who’s up-time is essential. To study more check the links below.

SQL Server 2012 Indirect Checkpoints



In the upcoming topics we will study more about the AlwaysOn Availability Groups. So stay tuned for more updates.





AlwaysOn Failover Cluster- Explanation

To Start learning about the new SQL Server 2012 feature AlwaysOn lets start with basic study about the Windows Server Failover Clustering.

WSFC is a server feature which is used for the high availability for the critical applications. One such application is SQL Server service which is hosted in WSFC. Moving on to the explanation about AlwaysOn, this is a feature of SQL Server which leverages the Windows Server Failover Clustering(WSFC). The AlwaysOn functionality provides high availability through the redundancy at the server level which is a failover cluster instance. The Failover cluster Instance(FCI) is a SQL Server instance which is installed in the WSFC node and the main advantage of having the AlwaysOn is that these nodes can be geographically dispersed over i.e. it supports AlwaysOn in multi subnets. On a network the FCI appears to be a SQL instance installed on a single node however it provides failover from one WSFC node to another if a current node is unavailable.

A series of events occur when a service is transferred. To describe that a defination of Resource Group is must.To describe resource group, this is the group in which the FCI runs or our SQL instance runs. The resource Group lives in more than one node. So when the FCI starts up, one of the node takes the ownership of the group and brings its SQL Server instance online. The resources in the group lists are-

  1. Network name
  2. IP addresses
  3. SQL Database engine
  4. SQL Server agent

So now lets see the series of events that occur when a failover happens. These are as below-

The dirty pages are written to the disk.
The respective SQL Services in the resource group are stopped on the active node.
The new resource group owner starts its SQL Services.
Client is automatically connected to new node with the help of same virtual network name(VNN).

Please not that in any point in time only one node can run its services.

To keep the FCI online, the WSFC should be in good quorum health. IF the WSFC loses its quorum then the entire cluster is down and manual intervention will be required.


Stay tuned for more updates to AlwaysOn.

Using SQL Sub Queries

Transact SQL supports querying through sub queries. In general sometimes there is a need to query a temporary result set  and with respect to that result set you need to further do the computation.

Sub Queries can either be a simple self-contained queries which can be queried independently in respect to outer query or it can be a co related query where in general there is always a co-relation to the outer table column. Based on this type we can have results set in either in a scalar value(single column value), or multi valued( single column with n no of rows), or a table valued.

Self contained queries

Self contained queries are easy to work on as they can be run independently with respect to the outer query. Just highlight the inner query and you can get the desired result. It is also therefore easy to understand the code in comparison to correlated sub queries.

Sub queries that results to single or scalar value is used with the where clause. For example we can find the products with the minimum unit price by using sub query as follows.


The inner query will fetch the minimum unit price from the products table. This minimum price then will be further initialized to the outer query where in the outer query is asking to show all the details of product with minimum unit price.

As said above, we can also just highlight the inner query to find out just the minimum price of products and there will be no error.


If the returns more than one value then it will result to error at run time, since you are using just a where clause which only filters to scalar or single result set. If the result set is an empty then it is converted to NULL.

A sub query can also return multiple values from a single column. Such a query can be used when more than one value is expected from a single column. For such queries can use an IN predicate.


In the above example, the inner query filters the supplierid which are from the city London, and then these supplierids are initialized to outer query so as to get the products details of those suppliers.

You can also negate the result, like if you want the details of all the products except for those suppliers who are not from the city London. This can be achieved by simply using NOT IN predicate after the where filter.

Correlated Sub Query

A correlated sub query is a bit more complex in comparison simple sub query. Though not that difficult either. A correlated query is a query which is interdependent to the outer query with respect to the columns being used as comparison between outer and inner query. A simple nested sub query usually processed only once and that result is passed to outer query which also executes once.

To understand and have a clear picture take is as an example say you want to find the minimum price of a product per category. In this type of situation you can use a correlated query where in you can equate the categoryID of inner query with the categoryID of outer query. Which will then find the minimum price among those same categories.

The flow of query works in 3 parts.

  1. The outer query obtains the result and passes to inner query.
  2. The inner query executes based on the passed value.
  3. The inner query then passes the result back to outer query which is the final result for processing.

Lets see the below example and understand.

Correlated query

The query uses two instance of a same one table so as to distinguish between themselves. The query uses a correlation with a predicate p.categoryid=pp.categoryid to filter minimum value among a particular id. So if the category id is 1 from outer query then it will find the minimum unit price for that category by comparing in inner query. Once compared it will pass the result back to outer query and this goes on till all the categoryid are not compared.

Correlated sub query can also be used with EXISTS predicate. For example see the below example.


The EXISTS predicate does not return any result set, rather it will return either a true or false result once the required condition is been satisfied. Over here the condition is this that it will return those customers who has placed the order on date “2007-02-12”. Also due to the fact that it only returns a true or false, the SQL Server Query Optimizer ignores the inner Select list and therefore optimizing inner query won’t affect any optimization.

With EXISTS predicate you can negate the result set. Say you want to know the customers who din’t placed orders on “2007-02-12”. In that just use the NOT EXISTS and you will get the required result.

Correlated queries can also be used to provide a different type of result set. Like suppose you want to have an aggregate on one field but you don’t want this to happen to other fields. For example you want to know the details of all the customers who have ordered recently. Then you can use the query as provided below


Blog at

Up ↑