Most common Transaction log myths

1.       A Truncate stamen can’t be rolled back

TRUNCATE can’t be Rolled Back Using Log Files after Transaction Session Is Closed.

While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE cannot be rolled back using log files in full recovery mode if data files have been overwritten.

DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it cannot be rolled back but DELETE can be rolled back.

In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.

In case of TRUNCATE, The exact deleted values are not logged in the online SQL Server transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages However, while using T-SQL code TRUNCATE can be rolled back for that particular session.

Point in time restore using log file cannot roll back TRUNCATE if the data file which is deallocate is over-written. That is the difference. That is “TRUNCATE may or may not be recovered always from log files.”

2.       The truncation process reduce the size of a physical log file

During the truncation process, only the active portion of the online SQL Server transaction log file is scanned. Some parts of the scanned portion are marked as inactive and they will be used as free space to write down new transactions. There isn’t a change in the online transaction log size because the inactive parts remain intact, nothing is deleted or removed

Every SQL Server transaction log is made of Virtual Log Files (VLFs). During the truncation process, only the Logical log is scanned. A Logical log is made of active VLFs. A Log Sequence Number (LSN) is used to uniquely identify every transaction in the online transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log

The online SQL Server transaction log file is circular by internal organization. When logging reaches the end of the transaction log, it starts again from the beginning by overwriting the parts marked as inactive

3.       Having multiple online SQL Server transaction log files will increase performance

This myth is based on the belief that having multiple online transaction log files will result in parallel writing of transactions into the files and therefore result in performance gain. SQL Server can’t operate with more than one online transaction log file at the time, so any kind of parallel I/O is not possible

Having multiple transaction log files is needed only in the situations where the initial SQL Server transaction log can’t record more transactions due to a lack of free space on the disk

4.       The SQL Server transaction log won’t grow if the database is in the Simple recovery model.

             However, it happens just in some specific situations – when there is a long running transaction or                    transaction that creates many changes

            In the Simple recovery model, the online transaction log is cleared automatically. SQL Server                       automatically reclaims log space to keep space requirements small – but that doesn’t mean it won’t               grow. The online transaction log must provide enough information for a database rollback, therefore               it must provide enough space for all necessary information. As all transactions must be written into                 the online transaction log, in case of a large number of changes in a transaction, there might not be                 enough space in the log, so it must be expanded.

5.       A SQL Server transaction log backup will be the same size as the online transaction log itself.

The online transaction log must have enough information to rollback active transactions, so some space is reserved for eventual rollbacks. If a rollback occurs, SQL Server doesn’t want to expand the online transaction log because if the expanding fails, the SQL Server database can become inconsistent or go into the Suspect mode. That’s why the online transaction log has some reserved space and is usually bigger than the SQL Server transaction log backup

Moreover, a transaction log backup contains only the transactions made after the last transaction log backup. If the online transaction log contains the transactions that have already been backed up, they will not be present in the new SQL Server transaction log backup, therefore the transaction log backup will be smaller for that amount of space

6.       A full or differential database backup clears the online transaction log.

The inactive parts of the online SQL Server transaction log are marked for clearing only when a transaction log backup is created

Full and differential database backups don’t contain much SQL Server transaction log information, only the transactions necessary to recover the database into a consistent state. These transactions are not a backup of the online transaction log, therefore these transactions are not marked for overwriting in the online transaction log

7.       The TRUNCATE TABLE and DROP TABLE commands are not logged into the online transaction log.

The exact deleted values are not logged in the online SQL Server transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages

This myth is also based on the fact that these commands take little time to execute, they are almost instantaneous

8.       My SQL Server is too busy, I don’t want to make SQL Server transaction log backups.

One of the biggest performance intensive operations in SQL Server is an auto-grow event of the online transaction log file. By not making transaction log backups often enough, the online transaction log will become full and will have to grow. The default growth size is 10%. The busier the database is, the quicker the online transaction log will grow if transaction log backups are not created

Creating a SQL Server transaction log backup doesn’t block the online transaction log, but an auto-growth event does. It can block all activity in the online transaction log

9.       A SQL Server transaction log backup isn’t needed for a point in time restore. A full database backup is enough.

This myth comes from using the RESTORE command with STOPAT clause to restore from a full database backup. The STOPAT clause specifies a point in time for the RESTORE LOG command, and it works well when it’s used with a transaction log backup. The fact that it can be used with a full database backup makes you believe that transaction log backups are not needed to recover to a specific point in time

RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH NORECOVERY

GO

RESTORE LOG AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH RECOVERY, STOPAT = 'Dec 31, 2013 10:59:00 PM'

GO

Although the SQL Server database cannot be restored to a point in time, SQL Server doesn’t clearly identify the problem, and it allows you to use the STOPAT clause without a transaction log backup specified

10.   SQL Server transaction log backups are not needed for successful disaster recovery if the full database backup is taken daily.

It also depends on how much data you can lose. If you can afford to lose up to 24 hours of data, then you don’t need transaction log backups and you should use the Simple recovery model

If the information you can lose is measured in minutes and hours, regular transaction log backups are necessary, as the maximum you will lose is the time between to transaction log backups

11.   The SQL Server transaction log shrinking will make free space in the online transaction log so I don’t need to create transaction log backups.

The shrink operation is not a good maintenance practice because it doesn’t solve the transaction log size issue permanently. After the initial shrinking, the transaction log will grow again. As the auto-growth event is one of the most intensive SQL Server operations, it should be avoided. The recommended method to keep the size of the online transaction log is to create transaction log backups regularly. Or, switching to the Simple recovery model, if you can tolerate data loss.

12.   Log records are written to disk immediately.

FALSE. There is a 60KB log cache in the RAM that log records are written to, not the disk. That cache is written to the disk, to the transaction log file, as soon as all 60KB is filled, any session of that database issues a commit (a real commit, not the nested one that just decreases @@TRANCOUNT), and on some other events. All log records flushed together to the disk will have the same block offset (LSN consists of VLF number, block offset, and log record number). Therefore, log block cannot be larger than 60KB (cache’s size).

Query Store new feature in SQL Server 2016

Query Store new feature in SQL 2016

Query store feature is new functionality to look into query plan history for performance tuning, it helps to find out the performance degradation by analysing performance difference in query plan.

 

How Query store helps:

Collects all query texts along with relevant properties

Store all plan choices and performance metrics.

Allows you to force plans from history. (One can force database to choose particular plan instead sql engine do it for you)
Identify queries that have gotten slower recently
Make sure this works across server restart, upgrade and query recompiles.

All the compiled or executed query plan will be stored in user database with statistics with history data as well.

One can choose how much data it will store and what would be analysing time for query store.

 
Use ‘Alter Database’ or query store properties in GUI for database statement to enable query store.

eg: Alter Database QueryDB Set Query_store = ON;  (not possible for Master and tempdb database)

 

Execution plan for query over time get costlier or evolved due to multiple reason like statistics changes, Schema Changes, index modification etc. he procedure cache (where cached query plans are stored) only stores the latest execution plan. Plans also get evicted from the plan cache due to memory pressure. As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

Since a query store can store multiple execution plans per query processor one can force query to use particular plan to execute.

There are 4 different option available for query plan  troubleshooting under QUERY STORE option in GUI

Regressed Queries	Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse). Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved.
Top Resource Consuming Queries	Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval. Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption.
Tracked Queries	Track the execution of the most important queries in real-time. Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.
Overall Resource Consumption	Analyse the total resource consumption for the database for any of the execution metrics. Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.

By using compare option one can compare the multiple plan of single query and find the problem.

To force a plan, select a query and plan, and then click Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Configuration option available for Query Store:

OPERATION_MODE: Can be READ_WRITE or READ_ONLY.

CLEANUP_POLICY:  Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store.

DATA_FLUSH_INTERVAL_SECONDS: Determines the frequency at which data written to the query store is persisted to disk. To optimize for performance, data collected by the query store is asynchronously written to the disk. The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS.

MAX_STORAGE_SIZE_MB: Configures the maximum size of the query store. If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data.

ALTER DATABASE QueryDB SET QUERY_STORE (MAX_STORAGE_SIZE_MB = );

INTERVAL_LENGTH_MINUTES: Determines the time interval at which runtime execution statistics data is aggregated into the query store. To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. This fixed time window is configured via INTERVAL_LENGTH_MINUTES. 

ALTER DATABASE QueryDB SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

SIZE_BASED_CLEANUP_MODE: Controls whether the clean-up process will be automatically activated when total amount of data gets close to maximum size.

QUERY_CAPTURE_MODE: Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries.

MAX_PLANS_PER_QUERY: An integer representing the maximum number of plans maintained for each query.

To set all option in single query:

ALTER DATABASE QueryDB

SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY =
    (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000
);
 

Following Catalog views and stored procedure to use Query store function:

sys.database_query_store_options (Transact-SQL)

·         sys.query_context_settings (Transact-SQL)
·         sys.query_store_plan (Transact-SQL)
·         sys.query_store_query (Transact-SQL)
·         sys.query_store_query_text (Transact-SQL)
·         sys.query_store_runtime_stats (Transact-SQL)
·         sys.query_store_runtime_stats_interval (Transact-SQL)

·         sp_query_store_flush_db (Transact-SQL)
·         sp_query_store_reset_exec_stats (Transact-SQL) (to clear runtime statistics for a given plan)
·         sp_query_store_force_plan (Transact-SQL) (To force a query with specific plan id)
      EXEC sp_query_store_force_plan @query_id = 12, @plan_id = 32;

·         sp_query_store_unforce_plan (Transact-SQL) (To unforce a query with specific plan id)
   EXEC sp_query_store_force_plan @query_id = 12, @plan_id = 32;

·         sp_query_store_remove_plan (Transct-SQL)  (use plan id)
·         sp_query_store_remove_query (Transact-SQL) (use query id)

Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE QueryDB SET QUERY_STORE CLEAR;

Time based clean-up of data can be set like:

ALTER DATABASE QueryDB SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

Contained Databases

Contained Databases

Contained Databases is new feature introduced in SQL 2012. It provide a means for effectively decoupling SQL Server databases (and their users, collations, and other assets) from the underlying SQL Server instance itself. , contained databases are much more "portable" in the sense that they can be easily moved from one server to another -- without worries about orphaned users and other issues that have typically made moving databases problematic. Thus, not only are contained databases a big win in corporate environments where DBAs need to shunt databases around for load-balancing purposes, they're also a huge win for ISVs (and developers) who want to more easily copy, move, or deploy databases with their software. Similarly, for web developers who rely on SQL Server as a back-end database, contained databases help make developers less dependent on hosting platforms as their databases become more portable

 

N.B: The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012. 

 

 

 

To create a contained database we must enable sql instance for containment using:

 

GUI -> Server properties -> Advance – choose Enable contained database.         Or

TSQL -> Exec sp_configure ‘contained database authentication’, 1

              Reconfigure

 

Then we must set the database option to Partial while creating new database in containment type option. Or using TSQL

 

Create/Alter Database Con_test Containment = Partial

 

 

Once DB Is ready , we can create user on database with password and this user won’t be dependent on server login, this can be done by GUI or TSQL :

 

Create user Con_User with password =’********’ default_Schema= [Dbo]

 

N.B; There is one very important note we must know that while connecting server using contained database user we must set the default database to contained db from option window in GUI in server connection. Same while connecting application using connection string.

 

Using SQLCmd we must use –d switch or else we can get login error ->

 

Ø   SQLCMD –S .\Servername –U con_user –P ********* -d Con_db

 

 

 

While changing to uncontained database to contained we might need to convert existing user to contained user:

 

First find out out the uncontained entity using ->

Select * from sys.dm_db_uncontained_entities

Select * from Sys.database_priciples

 

To migrate from uncontained user to contained user :

Exec Sp_migrate_user_to_contained

         @Username = ‘TestUser’,

         @Rename = N’Keep_Name’,

            @Disable = N‘Disable_login’/’do_not_Disable_login’

 

Caution: Make sure not to disable login while migrating unless you really want to. Because, this login might have a user mapping in some other databases which will break things severely.

 

Now this can be used to login to database using existing password of user.

 

 

Backup/Restore is the same as other database and we don’t need to worry of orphan user anymore.

 

 

There are few disadvantages also of using it:

 

1.     Connection string must specify the database name.

2.     Cross db query not possible using same name/password user as both are different in in different contained database until it on server level as well.

3.     Collation cannot rely on tempdb we might need to tweak codes as well.

 

 

 

Locks in SQL Server:

SQL Server allow locking different type resources to maintain accurate read and writes, locking at smaller resources like rows, key page maintain high concurrency but cause overhead on engine as lots of lock to be maintained. And locking on bigger resources like table, DB cause low concurrency but little overhead for fewer number of locks.

SQL Server can place locks on different type of resources:

·         RID: Row identifier. Used to lock a single row within a table.

·         KEY: Row lock within an index. Used to protect key ranges in serializable transactions.

·         Page: 8 kilobyte data page or index page.

·        Extent: Contiguous group of eight data pages or index pages.

·        Table: Entire table, including all data and indexes.

·        DB: Database.

SQL Server place lock on resources using multiple lock modes:

1.    Shared (S): Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

2.    Update (U): Update (U) locks prevent a common form of deadlock. A typical update pattern consists of a transaction reading a record, acquiring a shared (S) lock on the resource (page or row), and then modifying the row, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs. To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.

3.    Exclusive (X): Exclusive locks (X) are used to lock data being modified by one transaction thus preventing modifications by other concurrent transactions. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level. Because DML statements first need to read the data they want to modify you'll always find Exclusive locks accompanied by shared locks on that same data.

1.    Intent (I) :  An intent lock (I) indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table. Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

a.     Intent shared (IS): Indicates the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing S locks on those individual resources.

b.    Intent exclusive (IX): Indicates the intention of a transaction to modify some (but not all) resources lower in the hierarchy by placing X locks on those individual resources. IX is a superset of IS.

c.     Shared with intent exclusive (SIX): Indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources.

2.     Schema Lock (Sch-) : Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed. Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.

3.    BULK Update (BU): Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

Key - Range locks

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction. In the example we can see that there are two types of key-range locks taken:

• RangeX-X - exclusive lock on the interval between the keys and exclusive lock on the last key in the range
• RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range

Locking Hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements to direct SQL Server™ 2000 to the type of locks to be used. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.

 HOLDLOCK: Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.

·         NOLOCK: Do not issue shared locks and do not honour exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

·         READCOMMITTED: Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.

·         ROWLOCK: Use row-level locks instead of the coarser-grained page- and table-level locks.

·         TABLOCK: Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.

·         UPDLOCK: Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.

·         XLOCK: Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity.

EG: WITH (NOLOCK)