Thursday, March 31, 2016

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.

Tuesday, March 22, 2016

Recovery Models in SQL SERVER

We have three recovery models for database, Full, Bulk-logged and simple.

Recovery model	Description	Work loss exposure	Recover to point in time?
Simple	No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.	Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.	Can recover only to the end of a backup.
Full	Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).	Normally none. If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.	Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For more information, seeRestoring a Database to a Point Within a Backup.
Bulk logged	Requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy operations. Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.	If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Otherwise, no work is lost.	Can recover to the end of any backup. Point-in-time recovery is not supporte

FOUR PHASE SQL SERVER DATABASE RECOVERY PROCESS

Whenever a sql server gets restarting it goes through the recovery process and there are two types of recovery both having the aim of making sure the logs and data agree.

Restart Recovery (also known as Crash Recovery): Occurs every time SQL Server is restarted. The process runs on each database as each DB has its own transaction log (SQL Server 2008 uses multiple threads to process the recovery operations on the different databases simultaneously to speed recovery)

Restore Recovery: Occurs when a restore operation is executed. This process makes sure all the committed transactions in the backup of the transaction log are reflected in the data and any tranactions that did not commit do not show up in the data.

Following the data copy phase involving copying of all the data, log, and index-pages from the backup media of a database to the database files, four consecutive phases that take place during SQL Server recovery are

1. Discovery – is to find the logical structure of the Transaction log file.

2. Analysis – is to find the best LSN starting from which rolling forward can be done during redo phase.

3. Redo – is the phase during which the changes caused by active transactions (at the time of crash) are hardened onto Data files.

4. Undo – is the phase where in, rolling back of the active transactions for consistency, takes place.

Discovery Phase:

The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.

This is the reason why it is preferred to have optimal number of VLFs in a log file.

For further information on influence of VLFs in Transaction Log throughput please refer point #8 in Kimberly Trip’s blog http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Analysis Phase:

Preparation of ‘Dirty Page Table’ (DPT) and ‘Active Transaction Table’ (ATT) are the prime motives of Analysis phase. These two tables are put to use by SQL Server during subsequent redo and undo phases respectively.

To create DPT, SQL Server requires to make a note all the pages and their LSNs that might have been dirty (à not yet hardened) at the time of crash, from the transaction log (.ldf), so that during redo phase all such pages will be rolled forward and at the end of redo phase the database would be in such a state as if it was just before crash.

As all the pages prior to last checkpoint would have been already hardened and the pages after the last checkpoint are the ones that are dirty but yet to get hardened. Hence analysis phase starts (in the sense SQL Server starts reading using the .ldf) from the last checkpoint LSN till end of transaction log.

Scanning through the transaction log from the latest checkpoint till end of transaction log prepares the list of all pages that are dirty and obviously not hardened as they are after checkpoint. This list is the DPT. The minimum of all the LSNs available from DPT will be the minimum recovery LSN. Similarly using transaction log file, active transaction table is generated.

Redo Phase:

“Rolling forward all the changes that took place after the checkpoint and just before the crash so that at the end of redo phase the db would be in a state as if it was just before the crash”, is the intent of redo phase.

Hence making use of minimum recovery LSN obtained from DPT, starting from the minimum recovery LSN and till the LSN at end of transaction log, SQL server rolls forward (hardens) all the changes that are present in all the dirty ( not yet hardened) pages and brings the db to the desired state.

Undo Phase:

Ensuring that the data integrity is not hampered so that db can be opened for access is the aim of Undo phase. For this, all the changes made by all the transactions that were active at the time of crash are to be rolled back.

Hence, SQL server, using ATT , starting from LSN at the end of transaction log will rollback all the changes caused by all the active transactions till the LSN of beginning of oldest transaction(among the active transactions present in ATT), which is available from transaction log and opens the database for user access.

This marks the end of 4 phase recovery process!

References :

https://sqlbie.wordpress.com/2010/08/04/four-phase-sql-server-database-recovery%C2%A0process/

What happens Internally when a SQL Server instance is Shutdown and restarted

A very interesting question popped up today: what happens when a SQL Server instance is restarted?

When an instance of SQL Server is shutdown (i.e. on the way to be restarted) what happens is:

all databases have checkpoints
all SQL Server Agent jobs are terminated and the Agent is shut down
all database files are released to the OS, i.e. SQL Server service releases the locks to the mdf and ldf files (and all other files)
the memory used by the SQL Server instance is released
all metadata collected for the DMVs and DMFs is discarded
an event in the Default trace file is written marking the SQL Server instance shutdown

When an instance of SQL Server is started (i.e. after the service is authenticated and is starting) what happens is:

The service is authenticated by verifying the credentials provided in the logon account and the service is started.
the startup parameters of the SQL Server service are read, since they contain information about the location of the master database files and the error log location. The startup string looks like this:

^?View Code T-SQL

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

the port on which the SQL Server is responding are open
the memory is allocated for use by the SQL Server instance (according to the minimum memory settings of the instance)
the master database metadata is read – the master database contains metadata about all user databases on the instance and their status
all user databases are attached to the instance and file lock is acquired on the files
Undergo database recovery phases (Analysis, redo and undo phases.)
the tempdb files are allocated according to the settings for their initial size – depending on the security policy settings and the initial size of the tempdb files this may take longer or shorter time; also, keep in mind that the tempdb collation settings are picked up by the model database
an event in the Default trace is written noting that the instance is started
all events are written to the SQL Server log – whether there are just info messages or error messages
connections to the databases are accepted
metadata is collected about significant events in the DMVs and the DMFs
Recompile Stored Procedures

References :

http://sqlconcept.com/2011/05/25/what-happens-when-a-sql-server-instance-is-restarted/
http://www.sanssql.com/2012/07/what-happens-when-sql-server-instance.html