Recovery Model

 Contents

1 Recovery Model Overview

2 Simple vs Full vs Bulk-Logged

3 Ref

1. Recovery Model Overview

• A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. 

• Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. 

• A database can be switched to another recovery model at any time.

2. Simple vs Full vs Bulk-Logged

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. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server). Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in the simple recovery model: -Log shipping -Always On or Database mirroring -Media recovery without data loss -Point-in-time restores	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. For more information, see Complete Database Restores (Simple Recovery Model). For a more in depth explanation of the Simple recovery model, see SQL Server Simple Recovery Model provided by the folks at MSSQLTips!
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). For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).	Normally none. If the tail of the log is damaged, changes since the most recent log backup must be redone.	Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For information about using log backups to restore to the point of failure, see Restore a SQL Server Database to a Point in Time (Full Recovery Model). Note: If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases. For more information, see Recovery of Related Databases That Contain Marked Transaction.
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 information about operations that can be minimally logged, see The Transaction Log (SQL Server). Log backups may be of a significant size because the minimally-logged operations are captured in the log backup. For information about database backups under the bulk-logged recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).	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 supported.

3. Ref

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver16

Tools - DTA

 

Contents 1 10.2 Database Engine Tuning Advisor (Dta) Tool 1 1.1 10.2.1 Recommendations - index /statistics /partitions 1

1. 10.2 Database Engine Tuning Advisor (Dta) Tool 

• Database Engine Tuning advisor (DTA) tool is used analyse / understand .sql queries and .trc (profiler generate trace .trc file) audit reports. This tool gives us recommendations on possible database objects to be created for improving the performance of the queries. 

1. 10.2.1 Recommendations - index /statistics /partitions 

• The recommendations include:

• i. index creation  / index alter 

• ii. statistics creation / statistics update

• iii. partitions creation  / partitions alter

• 
  

• from ssms tool > go to top : tools >> database engine tuning advisor >> specify server name > 

• input = trace file from profiler tool abvoe. select input database and workload database

• start analysis.. this gives "recommendations".

• select each recommendation manually >> generate script (last column in recommendation table)

• run the script on the database.

• or

• right click query in ssms >> analyse query in db enine tuning advisor tool. this gives "recommendations" > run scripts

• Ref - TechBrothersIT, 

Execution Plan

 

Contents 1 12 Execution Plan 2 1.1 12.1 Types of Execution Plans Statistics  (query statistics) : 2 1.1.1 12.1.1 Estimated Execution Plan 2 1.1.2 12.1.2 Live Execution Plan 2 1.1.3 12.1.3 Actual Execution Ption; 2 - 12.1.4 generated after the query execution 2 1.2 12.2 Possible Plan Types: 2 1.2.1 12.2.1 Table Scan 2 1.2.2 12.2.2 Index scan : 2 1.2.3 12.2.3 index seek : 2 1.2.4 12.2.4 spooling : 2 1.3 12.3 Query Cost Types: 3 1.3.1 12.3.1 IO cost 3 1.3.2 12.3.2 CPU cost 3 1.3.3 12.3.3 sub tree cost 3 1.3.4 12.3.4 operator cost 3

1. 12 Execution Plan

• "Execution Plan" Is Used By "Query Optimizer" Component To Ensure The Best Usage Of Resources (Memory & Cpu) 

• the above execution plans also give recommendations about missing indexes for a specific query. also, query execution stats. 

• but for complete set of recommendations we use dta tool [database engine tuning advisor tool].

• 
  

1. 12.1 Types of Execution Plans Statistics  (query statistics) :

1. 12.1.1 Estimated Execution Plan  

• plan for query execution; generated before the query execution

2. 12.1.2 Live Execution Plan 

• plan for query execution; generated during  the query execution >=2016

3. 12.1.3 Actual Execution Ption; 

• 12.1.4 generated after the query execution

2. 12.2 Possible Plan Types:

1. 12.2.1 Table Scan

• Applicable for heap (table without clustered index). the entire table is scanned from 1st row to last row.

2. 12.2.2 Index scan : 

• index pages content is scanned for data retrieval. all index pages are accessed for reads

3. 12.2.3 index seek : 

• index pages content is searched for data retrieval. only required index pages are involved

4. 12.2.4 spooling : 

• required data is loaded to tempdb for faster calculations, comparisons, loops, audits, etc..

3. 12.3 Query Cost Types:

1. 12.3.1 IO cost

• refers to the disk io for reads & writes on the database files.  for higher io cost, track for missing indexes & implement dta tool.

2. 12.3.2 CPU cost

• Refers to the thread management factor based on "numa" nodes on your processor. Each processor has two cores in a socket.

• ex: if you have a core 2 duo processor means you have four processor nodes.  Ensure lesser cpu cost. For higher cpu cost values, boost sql server priority and set proper thread count. 

• ssms > connect to server > right click server > properties

3. 12.3.3 sub tree cost

• refers to the cost involved in analysing the parse tree and compile tree.

reduce the number of sub queries and self joins, if possible. 

also ensure to check the health of the database. 

recompile the long running stored procedures. 

4. 12.3.4 operator cost

• this refers to query predicates, keywords and operations within sql queries.

ex: merge join option is faster compared to hash join option.

for higher operator cost, it is advisable to ensure stats updates and sp recompilations.

** avoid nested loops in the execution plans. for this, we "merge join" option.