SQL SERVERS DICTIONARY

This post contains the definition for SQL Server terms which may be helpful to know the basics. This will be  republished as new terms are added . 

INDEX Terms:

Covering Index : A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation. To be considered as a covering index, all columns referenced by the query must be contained in the index. So this means all columns in the SELECT, JOIN, WHERE, GROUP BY, HAVING etc etc.

Key Lookup : A key lookup is an extra read which the query has to make in the absence of a suitable covering index. In the query, the number of columns involved in the select statement may exceed the number of columns in the non-clustered index and when this happens, for each row in the result set, a key lookup is performed. So 500 rows in the result set equates to 500 extra key lookups. 

Bookmark lookup - During Search operation the Columns which  are not in non clustered index have to be fetch from the base table in a select query .

SQL ARCHITECTURE: 

SQL OS : It is a thin user-mode layer that sits between windows and sql server. It is used for low level operations such as I/O Scheduling, Memory Management and resource management. 

SQL CLUSTER:

Looks alive check : It is a basic resource health check to verify that the service(SQL service in our context) is running properly.To perform this , cluster service queries the windows service control manager to check the status of the service.By default looks alive check will happen in every five seconds.

IS Alive check: An exhaustive check to verify that a resource is running properly. If this check fails, the resource is moved offline and the failover process is triggered. During the Is alive check  the cluster service connects to the SQL server instance and execute select @@SERVERNAME.It will check only the SQL server instance availability and does not check the availability of user databases .

Heart beat : It is health check mechanism in cluster A single UDP packet sent between nodes in the cluster via the private network to confirm that  nodes are still online. By default cluster service will wait five seconds(one heart beat sent in every second)  before considering a cluster node to be unreachable.