Vinod Kumar (Blog home)

In by day-job I have a need to guide customers with specific deployment scenario’s on SQL Server. Often I end up talking about the storage, transaction engine and deployment options from SAN, DAS etc. This conversation can get really complex and many times we have a need to educate customers on using one over the other. It is a tough call because there are specific features to discuss in each scenario, there is a budget constraint of customer and the environment SQL Server is running and this involves multiple teams. From SQL Developers, SQL DBAs, Network Admins, Performance testing teams and System Admins all these can be actually different functions inside an given organization.

Storage is typical and sometimes tough given the number of parameters to work with. Let me give some of the high-level considerations when on this point.

1. When we are working with databases a special eye needs to be put for input/output (I/O) load of the application. The I/O characteristics is a function of business requirements and and components enabled for SQL Server. Typical questions to ask for your I/O characteristics are: 
   1. How are the read versus write ratio for the database?
   2. Has performance counters monitored for the typical I/O volume (I/O per second) on the system for a window of usage? 
   3. Based on the usage patterns, can we quantify the I/O done sequential and how much is random?
2. Has the business called out the SLA and RTO requirement to narrow down the availability and performance needs for the database system.
3. Based on 1 and 2, has a capacity planning done to determine the hardware required to support the business. 
4. Has the SQL Server been configured to make best use of the hardware in step 3.
5. Is proper monitoring in place to track the performance of the database while the workload changes over a period of time.

The whitepapers on database architecture and storage is long and beyond the scope of this blog. See Microsoft SQL Server Storage Top 10 Best Practices and Physical Database Storage Design for more detailed recommendations from the SQL Server team.

Since this is in continuation to the other topics on basics, a lot has been omitted because they are covered in other locations. Also make sure to read SQL Server: Database Page Basics, SQL Server: Database Basics, SQL Server: Locking basics, SQL Server: TempDB Basics and SQL Server: Transaction Logs and Recovery.

SQL Server – Access Methods

1. SQL doesn’t directly retrieve from disk, it makes requests to buffer manager which serves up the page in cache before rendering out.
2. When scan starts, SQL Server can use look-ahead mechanisms to qualify rows and index entries on a page. The retrieving of rows that meet specified criteria is known as a qualified retrieval.
3. Basics of Row and Index Operations:
   1. Each component maintains its respective on-disk data structures – rows of data or B-Tree indexes.
   2. Understand and manipulate information on data and index pages
   3. Row operation code retrieves, modifies and performs operations on individual rows
   4. Special operations are needed to handle LOB data (Large Object)
   5. Index operation code maintains and supports searches on B-Tree which are used for SQL Server indexes.
4. Basics of Page Allocation Operations:
   1. The allocation operations code manages a collection of pages for each database and keeps track of which pages in the db have already been used, for what purpose and how much space is available on each page.
   2. Each database is a collection of 8 KB pages spread across one more physical files.
   3. 13 types of disk pages, specifics are explained in the Page basics blog:
      1. Data is stored on data or LOB pages
      2. Index rows are stored on index pages
      3. Page Free Space (PFS) pages keep track of which pages in a db are available to hold new data
      4. Global Allocation Map (GAM), Shared Global Allocation Map (SGAM), Index Allocation Map (IAM) keep track of other pages
      5. Bulk Changed Map (BCM) and Differential Changed Maps (DCM) are used to make backup and recovery more efficient
      6. Rest of the page types are used for logging and recovery
5. Versioning Operations, also look at SQL Server: TempDB Basics for few more details on versioning:
   1. Manages access to the version store
   2. Row versioning allows SQL Server to maintain older versions of changed rows

Database Transaction Services

1. Provides support for Atomicity, Consistency, Isolation and Durability
2. Write-ahead logging ensures that the record of each transaction’s changes is captured on disk in the transaction log before a transaction is acknowledged as committed.
3. Log records are always written to disk before the data pages where changes were made are actually written.
4. Writes to the transaction log are synchronous while writes to data pages can be asynchronous.
5. Allows for transaction that cross databases within the same SQL Server Instance.
6. For cross-instance transactions, it coordinates with Microsoft Distributed Transaction Coordinator (MS DTC). Use it with utmost caution and understand the overheads of the two phased commit here.
7. Also transaction services coordinates locking to provide Isolation.
8. SQL Server supports two concurrency models
   1. Optimistic Concurrency – provides consistent data by keeping older versions of rows with committed values in version store. Readers do not block writers and writers do not block readers. Writers do block writers
   2. Pessimistic Concurrency – guarantees correctness and consistency by locking data so it cannot be changed
9. SQL Server has five isolation levels:
   1. Read Uncommitted [pessimistic]
   2. Repeatable Read [pessimistic]
   3. Serializable [pessimistic]
   4. Snapshot [optimistic]
   5. Read Committed [optimistic/pessimistic - depends on database setting]
10. Behavior of transactions depends on the isolation level and concurrency model one is working with

Database Locking Operations

1. Acquires and releases various types of locks
   1. Share locks – reading
   2. Exclusive locks – writing
   3. Intent locks – taken at a higher granularity to signal a potential "plan" to perform some operation
   4. Extent locks – for space allocation
2. Manages compatibility between the lock types, resolves deadlocks and escalates locks.
3. Controls table, page, and row locks as well as system data locks.
4. Locking is the best way SQL Server can bring a balance to consistency and concurrency and it is SQL Server’s way of implementing Isolation Levels discussed above. Read the other post around “SQL Server: Locking basics” discussed in detail.

Database File Sizes

1. An ideal recommendation would be to have data files of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.
2. Pre-size data and log files well ahead of time and plan for the growth.
3. Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.
4. Configure an alerting mechanism to send emails or log information of available free space. Use other monitoring mechanism to send emails if required.
5. Also have alert mechanism to look at AUTOGROWTH happening in the system for both Application databases and TempDB.

Disk Topology and SAN interfaces

Small Computer System Interface (SCSI)

1. Supports forcing data to be written to disk, improving recoverability.
2. SCSI with Tagged Command Queuing (TCQ) supports multiple I/O requests.
3. Supports hot-swapping.
4. SCSI can have up to 15 drives per channel.
5. Less restrictive on physical cable length.
6. Overloading the channels increases the chance of reaching the transfer rate limit.

Integrated Device Electronics (IDE)

1. Supports hot-swapping.
2. IDE has high transfer rates only if there is one drive attached per channel.
3. Typically greater capacity than SCSI.
4. Typically cheaper per GB than SCSI drives.
5. Can only handle one outstanding I/O request per channel.

Serial Advanced Technology Attachment (SATA)

1. SCSI with TCQ supports multiple I/O requests.
2. Supports hot-swapping.
3. Most are explicitly designed to support only one drive per channel; however, multiple SATA channels of 2 to 12+ on interface cards are also available.
4. Typically greater capacity than SCSI.
5. Typically cheaper per GB than SCSI drives.

Serial-attached SCSI (SAS)

1. Very fast. Supports SCSI protocol.
2. Allows for a larger number of disks than SCSI.
3. Applicable to direct-attached storage (DAS) only.
4. Replacement technology for parallel SCSI. Backward compatible with SATA drives.

SAN

1. Can serve multiple servers.
2. No limitations on the number of disks that can be accessible.
3. Easier to install additional servers. Easier to manage many servers.
4. Easier to reallocate disk storage between servers.
5. Maintenance costs tend to be lower than DAS.

DAS

1. Greater maximum bandwidth.
2. Easier to manage for a smaller number of servers.
3. Initial overhead costs are lower than SAN.
4. Deployed per server directly attached.
5. The number of disks is limited by the number of slots in the server and the type of interface used.

For both DAS and SAN, the following categories of performance should be measured:

1. I/O per second
2. Megabytes per second
3. Latency

Performance of both DAS and SAN environments is affected by so many variables that simple recommendations are not possible. Examples of variables include drivers, configuration, underlying and supporting foundational technologies and host bus adapters (HBAs).

Fiber-Channel-switched fabric may be beneficial for SAN environments, because Fiber Channels can provide multiple links through the fabric, and can thereby enable I/O path parallelism so that the SAN can process I/O requests more efficiently.

Where to start?

Generally, when prioritizing data among faster disks for SQL Server databases, use the following ranking, these are generic and need evaluation on a case-to-case basis:

1. Start with Tempdb data and the tempdb transaction logs
2. Database transaction log files
3. Search database
4. Database data files
5. In a heavily read-oriented portal site, prioritize data over logs.

Don’t overlook HBA configuration while deployment

1. Use up-to-date HBA drivers as recommended by the vendor.
2. Utilize storage vendor specific drivers from the HBA manufactures website.
3. Tune your HBA driver settings as needed by the databases and access patterns.
4. Ensure that the storage array firmware is up to the latest recommended level.
5. Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly.

Well, some of the conversations I have with customers go as long as a day on these topic and I am sure this is not exhaustive. So please feel free to drop in comments of areas that I have missed out here for the benefit of others. Thanks again for reading this far !!!

TempDB is an interesting and really important database inside SQL Server’s architecture. We hardly get a chance to look at it closely and learn from it. This database is used by all the other databases and is sometimes a unsung hero for our functioning of SQL Server. In this post, let me continue with the same logic of understanding the basics of the TempDB Database. This is not exhaustive of everything that this database is used but can be a great start. TempDB is just like any other database from an architecture perspective of pages, GAM, SGAM, PFS, Extents etc but its functioning and importance is way beyond any other system database available inside SQL Server.

1. TempDB is a system database which is a shared database for the whole SQL Server instance.
2. There is only one TempDB database per-instance.
3. Re-created at startup, clone of model database, however, recovery model isn’t copied; tempdb uses simple recovery.
4. TempDB is used for temporary user objects like local temporary tables, global temporary tables, table variables, cursors, worktables, sorts inside query, index rebuilding operation, online indexing, table valued functions etc.
5. TempDB is also used for row versioning when used with snapshot isolation level or read-committed snapshot settings.
6. Can’t set OFFLINE, READONLY options neither can you drop tempdb database
7. Simple recovery model: tempdb’s log is constantly being truncated, so it can never be backed up.
8. Operations within tempdb are minimally logged so that transactions on temporary objects can be rolled back.
9. Highly recommend using of CHECKSUM for new installations for TempDB. This is enabled from SQL Server 2008 version.
10. There are few restrictions for tempDB like:

1. Additional filegroups cannot be created.
2. We cannot drop the database or create snapshots on this database.
3. We cannot change the collation or change the ownership of this database from dbo.
4. The database cannot be set to OFFLINE and filegroups cannot be set to READ_ONLY.

1. User Objects – all users have privileges to create and use local and global temporary tables. sp_spaceused can show the size occupied by these objects. User objects include both user-defined tables and indexes and system catalog tables and indexes.
2. Internal Objects – not visible via normal tools, not in catalog view as they are in-memory only. Three basic types:
   1. Work tables – created by the server when spooling to hold intermediate results during a large query, or running DBCC CHECKDB or CHECKTABLE, working with XML or varchar(MAX) variables, processing SQL Service Broker objects or working with static or keyset cursors
   2. Work files – used when the server is processing a query that uses a hash operator either for joining or aggregating data
   3. Sort units – created when a sort operation takes place ex: ORDER BY clause. SQL Server uses sorting to build indexes or to process queries involving grouping or when certain types of joins require data to be sorted before performing the join
3. Version Store – supports technology for row-level versioning of data. Older versions of updated rows are kept in tempdb:

1. When an AFTER trigger is fired
2. When a DML command is executed on a database that allows snapshot transactions
3. When multiple active result sets (MARS) are invoked from a client app
4. During online index builds or rebuilds when there is concurrent DML on the index

1. Disk running out of space for log files and data files. Keep them in check always.
2. Look at allocation bottlenecks because of DML operations.
3. Look at slow IO bottlenecks where the database files have been placed.
4. Look at metadata structure contention because of heavy dropping and creation of temporary objects.

Thanks for reading this far. Will love to hear your feedbacks on if you want me to cover any other basic topics like these in the future too.

In our day-today life we have needs for photocopies. Now if we take that concept and mimic the same inside SQL Server it is called as Snapshots. Inside SQL Server the concept of snapshots were there with replication for a while, but from SQL Server 2005 a new feature called as Database Snapshot were introduced.

The feature usage of Database Snapshots have been minimal in organizations, but there are some unique ways people use snapshots and a lot don’t know how this feature works too. This feature is different from SAN based snapshots and not to be confused. As we are in the topic, understanding that snapshots can be implemented as a  software solution or hardware vendor approach. In this blog post we will look at the feature within SQL Server called as database snapshots.

1. Database snapshot is a process where DBAs can create a point-in-time read-only copy of any database. They can create multiple snapshots of the same database at different points in time.
2. Space needed for each snapshot is typically much less because snapshot only stores pages that have changed.
3. Database snapshots allows you to do the following:
   1. Turn a database mirror into a reporting server (you cannot read from a mirror but you can create a read only snapshot of the mirror and read from that)
   2. Generate reports without blocking or being blocked by production operations.
   3. Protect against administrative and user errors.
   4. This is yet another way to protect against database application upgrades and functioning like a backup before the process
4. Each file in a snapshot is created as a sparse file (feature of NTFS). The database recovery models don’t have an impact on creating snapshots.
5. Use the DMV sys.databases to check the source databases for snapshots.
6. Write: Snapshot files contain only data that has changed from source, and for every file, SQL creates a bitmap that is kept in cache with a bit for each page of the file indicating whether the page has been copied to the snapshot. Every time a page in the source is updated, SQL Server checks the bitmap for the file to see if the page has already been copied, if not, copies it. This operation is called copy-on-write or COW process.
7. Read: When a process reads from a snapshot, if first accesses the bitmap to see whether the page it wants is in the snapshot file or the source. When it reads from a snapshot database, no locks are taken regardless of what isolation level it’s in (true for reads from file and from source database).
8. When SQL Server is shut down or the database is closed, the bitmaps are lost and need to re-created at startup. It checks whether each page of the database is in the sparse file as it is accessed and it records the information for future use using the memory maps.
9. Allocations to the sparse files are made in units called regions, each region is 64KB. When a region is allocated, all the pages are zeroed except for the page that has changed leaving room for 7 more pages. A new region isn’t allocated till all 8 pages are used.
10. The snapshot operated in pages as discussed above to fill up the regions.
11. Keep monitoring your IO overheads that come because of COW for the snapshot databases.
12. Also because of sparse file configuration, keep an eye on the drive space where the snapshots are present.
13. If a snapshot exists for a source database, the source database cannot be dropped, detached or restored.
14. You can replace a source database with one of the snapshots thereby reverting the source database to the state when the snapshot was taken using the RESTORE command.
15. The restore cannot be performed for offline databases and corrupted databases.
16. Snapshots can also be used to recover deleted tables from the source database.
17. Snapshots cannot be created for the model, master, resourcedb or tempdb database
18. Database snapshots have no relation to the concepts of snapshot backups, snapshot isolation of transactions or snapshot replication.
19. We cannot backup, restore, attach or detach an snapshot database.
20. FILESTREAM datatype is not supported by database snapshots.
21. With the introduction of ALWAYSON technologies in SQL Server 2012, snapshots cannot be created on the databases part of availability groups.
22. NOTE: Please DONOT use Database snapshots as a replacement for backups. The usual backup strategies must be done and in place.

Thanks for reading this far. Feel free to drop a comment to tell us if you have used the database snapshot feature in your production environments.

As you have been reading multiple posts around the database fundamentals topic in the past few weeks. Here is yet another post that will discuss around the basics of SQL Server Database Pages concept. I have seen there are many junior DBA’s who come from other platforms and ask what is a page, why is it restrictive, Is there a way to configure, are there optimizations I need to be aware etc. For most part the answer to this question is – there is hardly anything we can configure as parameters, but there is a reason why one must learn why SQL Server uses these defaults, what are the nuances we need to be aware and how can we understand the inner working. In this post, we just take a look at the basics of what constitute a database page and what are the various functions of each of these page types. This is not exhaustive, but still the learning can never stop right :).

1. Logical pages are 8KB in size by design and default.
2. The pages are numbered sequentially from 0 to N in each file, while the size of the file decides the number of pages that it contains.
3. SQL Server can identify any page using the database-id, file-id and page-number combination. Many times there are error messages with this nomenclature for reference.
4. With all math done, it means SQL Server has 128 pages per MB.
5. When new space is allocated to a file because of expanding file, the first page of the newly created space is page# is N + 1.
6. Though shrinking removes pages, SQL Server also ensures page numbers within a file are always contiguous.

SPACE ALLOCATIONS

1. Space in a database file is managed in units called extents.
2. Extent is made up of 8 logically contiguous pages therefore having a capacity of 64KB.
3. 96 byte of each page is allocated for header information such as what type of page, amount of free space on page, object owing it etc.
4. Post the header information is the data rows placed serially.
5. Bottom of the page contains a row offset – there is one entry for every row inside that page and its offset from the start of the page.
6. Row Offsets are in reverse sequence from the sequence of the rows.
7. Maximum amount of data that one can store in a single row is 8060 bytes. Whenever we are able to fit the rows in this limit – it is called In-Row Data.
   1. From SQL 2008, the page restriction has been relaxed with an concept called as Row-Overflow Data. Read more from MSDN.
8. SQL Server doesn’t allocate entire extents to tables which have small amounts of data.
9. Extents are of 2 Types:
   1. Uniform extents: As name suggests it is owned by a single object; all eight pages in the extent can be used only by this single object
   2. Mixed extents: These are shared between objects. Up to eight objects can share an extent (a.k.a each object using a single page)
10. Whenever a new table or index needs allocation it is given a mixed extents; when the table or index grows to eight pages, future allocations can use uniform extents. Between versions of SQL Server the # of pages post which uniform extents are given may vary.
11. If a table or index needs more space and is still less than 8 pages total, SQL Server must find a mixed extent with space available, else if it is 8 pages or larger, uniform extents are located.
12. When there is no mixed extents with free space, a new extent marked as mixed extent is allocated and SGAM updated accordingly.
13. Basic Page Layouts:
    1. Page 0 is the File Header
    2. Page 1 is the Page Free Space (PFS)
    3. Page 2 is GAM
    4. Page 3 is SGAM
    5. Page 6 is Differential Changed Map (DCM)
    6. Page 7 is Bulk Changed Map (BCM)
14. 2 special types of pages to record which extents have been allocated and what type it is being use for:

    1. Global Allocation Map (GAM) pages (always on page 2)

       1. These pages record which extents have been allocated for any type of use.
       2. GAM has a bit to indicate value 0 means in use and 1 means free extent.
       3. After header and other overhead are accounted, there are 8000 bytes or 64000 bits to cover 64000 extents.
       4. Each GAM page covers 4 GB of data and a GAM page exists in a file for every 4 GB of file size.
    2. Shared Global Allocation Map (SGAM) pages (always on page 3)
       1. Record which extents are currently used as mixed events and have at least one unused page.
       2. Similar to GAM, it covers 64000 extents every 4GB of data.
       3. Has a bit for each extent in the interval it covers: 1 if the extent is being used a mixed extent and has free pages, 0 if the extent isn’t being used a mixed extent or it’s a mixed extent with no free pages

Index Allocation Maps (IAM)

1. Any structure of data or index needs an IAM page.
2. IAM pages keep track of the extents in a 4 GB section of a database file.
3. An allocation unit is a set of pages belonging to a single partition in a table or index. The pages of can be of three storage types:
   1. Pages with regular in-row data
   2. Pages with Large Object (LOB) data
   3. Pages with row-overflow data
4. IAM page contains a 96 byte page header, followed by IAM header which contains 8 page-pointer slots.
5. IAM pages contain a set of 8 bits that map a range of extents onto a file. The IAM header has the address of the first extent in the range mapped by the IAM.
6. A bit in the IAM bitmap represents an extent in the range: 1 means extent is allocated to the object owning the IAM, 0 means the extent isn’t allocated to the object owning the IAM.
7. IAMs are allocated as needed for each object. Each of the IAM covers a possible range of 512,000 pages.

Misc. Notes

1. PFS (page 1) – keeps track of how each particular page in a file is used.
2. PFS also tracks if the page is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full or 96 to 100 percent full.
3. There is a PFS page approximately 8,000 pages in size after the first PFS page.
4. Differential Changed Map (DCM page 6) – keeps track of which extents in a file have been modified since the last full database backup.
5. Bulk Changed Map (BCM page 7)- is used when a page in a file is in a minimally or bulk-logged operation.
6. Like the GAM and SGAM pages, DCM and BCM pages have 1 bit for each extent in the section of the file they represent.
7. There is a GAM page 64,000 extents after the first GAM page on page 2 and another SGAM page 64,000 extents after the first SGAM page on page 3.
8. Log files donot have the concepts of pages, they are a series of log entries written sequentially.
9. In the previous versions the text, ntext and image data are stored in separate pages and are not inside the Data pages.

I think I have touched most parts of how database pages exist inside SQL Server. Though there are even more subtle nuances on the page functioning, we will keep it outside of this post for later. Please refer to MSDN BOL for more information on the page architectures.

Database learning can never stop. Based on the previous posts on the basics I saw few comments asking to cover some of the basics in a one liner mode. In this blog post, we will talk about the database fundamentals of system DB’s, what are the DB files and what are the filegroups. I get an opportunity to talk to a lot of developers who come from the compete platform and are often asking these fundamental and basic questions. I am sure this blog post will help them understand these fundamentals.

System Databases

1. Master: composed of system tables that keep track of server installation as a whole and all other databases that are eventually created. Master DB has system catalogs that keep info about disk space, file allocations and usage, configuration settings, endpoints, logins, etc.
2. Model: template database. Gets cloned when a new database is created. Any changes that one would like be applied by default to a new database should be made here
3. Tempdb: re-created every time SQL Server instance is restarted. Holds intermediate results created internally by SQL Server during query processing and sorting, maintaining row versions, etc. Recreated from the model database. Sizing and configuration of tempdb is critical for SQL Server performance.
4. Resource [hidden database]: stores executable system objects such as stored system procedures and functions. Allows for very fast and safe upgrades.
5. MSDB: used by the SQL Server Agent service and other companion services. Used for backups, replication tasks, Service Broker, supports jobs, alerts, log shipping, policies, database mail and recovery of damaged pages.

Database Files

1. Primary data files: every database must have at least one primary data file that keeps track of all the rest of the files in the database. Has the extension .mdf.
2. Secondary data files: a database may have zero or more secondary data files. Has the extension .ndf.
3. Log files: every database has at least one log file that contains information necessary to recover all transactions in a database. Has the extension .ldf.

Creating a Database

1. New user database files must be at least 3 MB or larger including the transaction log
2. The default size of the data file is the size of the primary data file of the model database (2 MB) and the default size of the log file is 0.5 MB
3. If LOG ON is not specified but data files are specified during a create database, the size of the log file is 25% of the sum of the sizes of all the data files.

Expanding or Shrinking a Database

1. Automatic File Expansion:

1. The file property FILEGROWTH determines how automatic expansion happens
2. File property MAXSIZE sets the upper limit on the size

1. Same as doing DBCC SHRINKDATABASE (dbname, 25). Leave 25 % free space in the database after the shrink
2. Thread performs autoshrink as often as 30 minutes, very resource intensive

Filegroups

1. Can group data files for a database into filegroups for allocation and administration purposes.
2. Improves performance by controlling the placement of data and indexes into specific filegroups on specific drives or volumes.
3. Filegroup containing the primary data file is called the primary filegroup, there is only one primary filegroup.
4. Default filegroup: there is at least one filegroup with the property of DEFAULT, can be changed by DBA.
5. Use cases when -not- to use filegroups:

1. DBA might decide to spread out the I/O for a database: easiest way is to create a database file on a RAID device.
2. DBA might want multiple files, perhaps to create a database that uses more space than is available on a single drive: can be accomplished by doing CREATE DATABASE with a list of files on separate drives

1. DBA might want to have different tables assigned to different drives or to use the table and index partitioning feature in SQL Server.

1. Allows backup of parts of the database.
2. Table is created on a single filegroup, allows for backup of critical tables by backing up selected filegroups.
3. Same for restoration. Database can be online as soon as primary filegroup is restored, but only objects on the restored filegroups will be available.

Given the nature of content, also look at the other topics we have discussed before.

Concurrency Basics

Locking Basics

Transaction Log Basics

Thanks for reading this far. Feel free to pass your comments too.

The learning journey can never stop. As we learn the basic building blocks of SQL Server there is always a need to do refreshers. This blog is on the same lines. I would urge you to read the Blog around Concurrency over at Pinal’s site and then continue the reading below because the basics of Locking is incomplete if we don’t understand what are the concurrency basics. Just like you have traffic signals that regulates traffic to make sure there are no grid locks or long waits for commuters. The parallel lanes for traffic is synonymous to concurrency. SQL Server uses locks as an mechanism to bring the consistency at the same time build the concurrency for the system. Locks are inevitable in any database world and those are also fundamental building blocks.

1. Locks are applied in both optimistic and pessimistic concurrency models.
2. Locking is used for maintaining consistency for concurrent transactions.
3. Read operations acquire shared locks, exclusive locks by write operations while update locks are got during initial portion of an update operation when SQL Server is searching for the data to update.
4. SQL Server releases locks automatically and manages compatibility between various locking modes, resolves deadlocks and lock escalations are done to reduce overheads.
5. SQL Server controls locks on tables, on the pages of the table, on index keys and on individual rows of data based on the operation done.

Lock Modes

1. Shared Locks
   1. Automatically acquired while reading data.
   2. This lock can be on a page, table, index key or individual row based on the isolation level.
   3. Multiple processes can hold shared locks on the same data and read from it too.
   4. No other process can get an exclusive lock on data that has a shared lock. Neither can a process acquire a shared lock when there is an exclusive lock already on the table.
   5. Shared locks are not held for the whole duration of read in the default isolation level.
2. Exclusive Locks
   1. This lock is acquired on data when modifications are done by DML statements like INSERT, UPDATE or DELETE operation.
   2. At any point in time exclusive locks can be held by only one process.
   3. In an exclusive locking mode no other process can take locks in this process.
   4. Exclusive locks are taken for the duration of the transaction length as the process can either commit or rollback.
3. Update Locks

1. This is an hybrid of exclusive and shared locks.
2. This lock is taken when SQL Server searches the table to find the resource that needs to be modified.
3. This can also be initiated using using query hints. It is desirable not to use the same.
4. Even in this lock, it is not the sufficient level to change data. SQL Server must take exclusive lock to modify data.
5. Used as an intermediate lock before escalating to exclusive locks.

Let us look at the common abbreviation and locking mode descriptions:

1. S – Shared: Allows processes to read concurrently but cannot change the resource locked.
2. X – Exclusive: Prevents processes from modifying or reading data in the data.
3. U – Update: Prevents external processes from acquiring an update or exclusive lock.
4. IS – Intent shared: Acquired when resource is locked with a shared lock.
5. IU – Intent update: Acquired when the resource is locked with an update lock.
6. IX – Intent exclusive: Acquired when the resource is locked with an exclusive lock.
7. SIX – Shared with intent exclusive: Shows that a shared lock pages have either a page or row with exclusive locks.
8. SIU – Shared with intent update: Shows that a shared lock pages have either a page or row with an update lock.
9. UIX – Update with intent exclusive: Shows that a update lock pages have either a page or row with exclusive locks.
10. Sch-S – Schema stability: Used to show a query using the object is being compiled at that moment.
11. Sch-M – Schema modification: Used to indicate the table’s structure is being updated at this moment.
12. BU – Bulk update: Lock acquired when bulk copy operation (like bcp) is copying data into a table.

Lock Granularity

1. SQL Server can lock resources at the table, page, partition or row level.
2. If locks are escalated, SQL Server can also lock a single partition of a table or index.
3. SQL Server can also lock index keys and ranges of index keys if needed.
4. DMV sys.dm_tran_locks view keeps track of each lock and contains information about the resource which is locked and an identifier for the specific resource

Key Locks

1. SQL Server use of key locks depends on the isolation level of the transaction.

1. If isolation level is Read Committed, Repeatable Read or Snapshot, SQL Server locks the actual index keys accessed while processing the query.
2. If isolation level is serializable, to counter phantoms reads, key-range locks are acquired for the range of values queried.
3. Incase of tables with clustered index, leaf node contains the data and hence the locks are acquired at this level.

1. RangeS-S
2. RangeS-U
3. RangeIn-Null
4. RangeX-X
5. RangeIn-S
6. RangeIn-U
7. RangeIn-X
8. RangeX-S
9. RangeX-U 

Other Locking Assets

1. We saw locks on objects, pages, keys, partitions and rows. Three are other SQL Server can lock too.
2. Extents are units of 64KB disk space that can be locked. This is a special type of lock that is taken when an object needs to expand and a new extent is allocated.
3. Generally connections hold a lock on at least one database object and will have resource_type = DATABASE at a minimum running in the session.
4. You occasionally have locks on individual partitions, indicated in the lock metadata as HOBT locks. Occurs when locks are escalated and escalation to that partition level is allowed

Lock Duration

1. The length of time that a lock is held depends primarily on the mode of the lock and transaction isolation level in effect.
2. Shared locks in Read Committed and Snapshot isolation levels are released as soon as SQL Server has read and processed the locked data
3. Shared locks behave the same as exclusive locks in Repeatable Read and Serializable isolation levels – they are not released until the end of the transaction
4. Update locks are also held until the end of the transaction unless it has been promoted to an exclusive lock in which case it behaves like the exclusive lock

Lock Ownership

1. Lock duration is directly affected by lock ownership
2. Ownership == scope of the lock
3. Four types of lock scopes
   1. Transactions – Durations depend on isolation level and lock mode (shared locks and update/exclusive locks)
   2. Cursors – requested explicitly when a cursor is declared. If a cursor is opened using a locking mode of SCROLL_LOCKS, a cursor lock is held on every row fetched until the next row is fetched or the cursor is closed. Even if the transaction commits before the next fetch, the cursor lock is not released
   3. Transaction_Workspaces – acquired every time a database is accessed and the resource associated with these locks is always a database. A workspace holds database locks for sessions – usually there is one workspace per session and all DATABASE locks acquired in the session are kept in the same workspace object. In distributed transactions, multiple sessions are enlisted into the same workspace so they share the database locks
   4. Sessions – must be requested explicitly using the sp_getapplock procedure, and apply only to APPLICATION locks. Its duration is until the session disconnects or the lock is released explicitly

Thanks for reading this far. I know there is more to learn and write like Lock Timeout, Locking Hints, Version Stores, Snapshot Isolation Level, Row Versioning, Deadlocks, Lock Escalations, Row-level Locking and Page-level Locking, Lock Blocks, internal locking etc !!! I will surely keep you posted similarly in future.

In my day-today customer interactions I meet a lot young, smart DBA’s who are still stepping into the ranks of making their applications robust. As part of their daily activities they are getting guided by some seniors who impose some of their views based on their past experience. Unfortunately, in this process the fundamentals are lost and as we build our career we need to understand the basics. One of the topics I explain almost every customer is about how Transaction Logs work, why they keep growing, how to make it cycle/reuse, what happens after backup etc. So I plan to write such posts in the future too, these are lists for reference making learning fun and easy. I am writing this post in the style of notes taken in a class, hope that will make reading easy and leaning faster :).

Transaction Log Basics

1. Transaction log records changes made to the database and stores enough information to allow SQL Server to recover the database.
2. Recovery is the process of reconciling the data files and the log.
3. Any changes to the data that the log indicates have been committed must appear in the data files and any changes that are not marked as committed must not appear in the data files
4. Log also stores information on how to roll back transactions if a ROLLBACK TRAN is received from the client or a deadlock generates an internal ROLLBACK.
5. One or more transaction log files are associated with a database when the database is created.
6. Log record is labeled with a Log Sequence Number (LSN) which is guaranteed to be unique.
7. Log entries part of the same transaction are linked so that they can be located easily for undo or redo activities.
8. SQL Server keeps track of the current position in the log by the LSN and when a page is changed the LSN corresponding to the log entry for that change is written into the header of the data page.
9. Dirty pages can be written to the disk only when the LSN on the page is less than or equal to the LSN for the last record written to the log.
10. The Buffer Manager guarantees that the transaction log will be written before changes to the database are written (write-ahead logging).
11. Buffer Manager also guarantees that log pages are written in a specific order making it clear which log blocks must be processed after a system failure regardless of when the failure occurred.
12. Log records for a transaction are written to disk before the commit transaction is sent to the client process. Actual changed data might not have been written out to data pages yet.
13. Though log writes are asynchronous, at commit time thread must wait for the write to complete to the point of writing the commit record in the transaction log. Data page writes are completely asynchronous.
14. Logging involves "demarcating" the begin and end of each transaction with information about changes made to the data in between. At the end, its marked with a commit record.
15. An aborted transaction undoes the changes made to the original data and these changes are written to the log and marked as "compensation log records".
16. There are two types of recovery (goal for both is to make sure log and data are in sync):
    1. Restart Recovery – runs every time SQL Server is started. Runs on each database (in parallel) and the SQL Server error log tells you how many transactions were rolled forward and back. This is also called as Crash Recovery.
    2. Restore Recovery – also known as Media Recovery is run by request when a restore operation is requested via the backup route.
17. Both the above types have to deal with 2 important situations:
    1. When transactions are recorded as committed in the log but not written to the data file.
    2. When changes to the data file don’t correspond to committed transactions. This can happen when a implicit indirect or explicit checkpoint forced this case.

Phases of Recovery

Now that I have set some context to the basic units of transactional logs and why they are used. We need to also understand the stages of recovery process.

1. Phase 1: Analysis Phase – Forward pass starting at the last checkpoint record in the transaction log. Determines and constructs a Dirty Page Table (DPT) consisting of pages that might have been dirty when SQL Server stopped. Active Transactions Table is also built consisting of uncommitted transactions
2. Phase 2: Redo Phase - Returns the database to the state it was in at the time SQL Server was stopped. Starting point for this forward pass is the start of the oldest uncommitted transaction. The minimum LSN in the DPT is the first page that is expected to be redone. But all logged operations starting at the oldest open transaction need to be redone for necessary locks to be acquired
3. Phase 3: Undo Phase – Uses the list of Active Transactions found in Phase 1 to roll each transaction back individually. Any transaction that was not committed is undone
4. Fast Recovery Phase – available in Enterprise and Developer edition allows for a database to be online as soon as redo phase is finished

Page LSNs and Recovery

1. Every database page has a LSN in the page header that reflects the location in the transaction log of the last log entry that modified a row on this page.
2. Also keeps track of the LSN which was on the data page before the change recorded by the latest log record.
3. During redo, LSN of each log record is compared to the LSN of the data page that the log entry modified.
   1. If it’s equal to the previous page LSN in the log record, the operation indicated in the log entry is redone.
   2. If it’s equal or higher than the actual LSN for the log record, REDO operation is skipped.
4. A transaction log cannot be truncated prior to the point of the earliest transaction that is still open, no matter how many checkpoints have occurred.

Virtual Log Files

1. A transaction log for any database is maintained as a set of virtual log files (VLFs) who size is determined internally by SQL Server by the total size of all the log files and the growth increment used when enlarging the log.
2. Log file is grown in units of entire VLFs and be shrunk only to a VLF boundary:
   1. When a log file is first created it always has between 2 and 16 VLFs.
   2. If its 1 MB or less – log file size is divided by minimum VLF size 31 * 8KB to determine number of VLFs.
   3. If its between 1 and 64 MB – log is split into 4 VLFs.
   4. If greater than 64 MB but less than 1 GB 8 VLFs are created.
   5. If greater than 1 GB, 16 VLFs are created.

      The above rules of thumb can be different depending on the version of SQL Server you are working.

3. 4 states for a VLF:
   1. Active – active portion of the log begins at the minimum LSN representing an active transaction. The active portion of the log ends at the last LSN written
   2. Recoverable – portion of the log preceding the oldest active transaction is needed only to maintain a sequence of log backups for restoring the database to a former state
   3. Reusable – If transaction log backups are not being maintained or if log was already backed up, VLFs before the oldest active transaction are not needed and can be reused
   4. Unused – One or more VLFs at the physical end of the log files might not have been used yet if not enough log activity has taken place. Or if earlier VLFs have been marked as reusable and then reused
4. Automatic Truncation – understand when the Log backups are not existent:
   1. You have configured the database to truncate a log on a regular basis by setting the recovery model to SIMPLE.
   2. You have never taken a full database backup.
   3. All log records prior to the oldest active transaction are invalidated and all VLFs not containing any part of the active log are marked as reusable. This process doesn’t shrink the physical file it is used in a cyclic manner.
5. Automatic Shrinkage – A database is truncated to allow for shrinking. If autotruncate and autoshrink option is set, the log is physically shrunk at regular intervals. This is something not recommended, though this option exists.

Database Recovery Models

As we learn about transaction logs, we cannot miss understanding the recovery models and the effects of the same. There are 3 modes of recovery models:

1. FULL Recovery Model
   1. Provides the least risk of losing work in case of a damaged data file.
   2. All operations are fully logged – in addition to logging every row changed via DML, the entire row is written to the transaction log using BCP or BULK INSERT operation.
   3. Also fully logs CREATE INDEX operations.
   4. Use this if you need to recover to a Point-in-time from your backup.
2. BULK_LOGGED Recovery Model
   1. Allows you to restore a database completely in case of media failure and gives the best performance and least log space usage for certain bulk operation.
   2. Minimal logging compared to FULL recovery model.
   3. Minimal logging consists of logging information that is required to roll back the transaction without supporting point-in-time recovery.
   4. Every data file in SQL has a special page called Bulk Changed Map (BCM) page also called Minimally Logged Map (ML Map) page. Each bit on a BCM page represents an extent and if the bit is 1 it means that this extent has been changed by a minimally logged bulk operation since the last transaction log backup.
   5. BCM map is located on page 7 of a 4GB span and all the bits are reset to 0 every time a log backup occurs.
3. SIMPLE Recovery Model
   1. Offers the simplest backup and restore strategy.
   2. Transaction log is truncated whenever a checkpoint occurs (which happens regularly).
   3. Only backups that can be made are ones that don’t require log backups.

Choosing the right recovery model is very important for functioning of your databases.

Backing Up and Restoring a Database

SQL Server has four main types of backups:

1. Full Backup – Copies all pages from a database onto a backup device.
2. Differential Backup – Copies only the extents that were changed since the last full backup was made. Done by examining the bits on the DCM pages for each data file in the database. When any page in an extent is changed, the corresponding bit in the DCM page is set to 1
3. Log Backup – Copies all the log records that have been written to the transaction log since the last full or log backup was made
4. File and Filegroup Backup – these backups are intended to increase flexibility in scheduling and media handling compared to full backups, in particular for very large databases. Useful when database contains varying update characteristics or priority levels

Thanks for reading this far. Hope the basics are clear now. Given these pointers, feel free to explore more into MSDN documentation.