SQL Server 2012:SQL Server architecture--The life cycle of a query (part 3rd) (end)

Original: SQL Server 2012:SQL Server architecture--The life cycle of a query (part 3rd) (end)

A simple update query

You should now know the query life cycle of reading only the data, and next determine what happens when you need to update the data. This section responds by looking at a simple update query that modifies the data read in the example just now.

Fortunately, until the access method (Access Methods), the update operation is exactly the same as the SELECT statement process just before.

This access Methods needs to modify the data so that the details of the modifications are stored on the hard disk before the I/O request is delivered. This is the work of the transaction manager (Transaction Manager).

transaction manager (Transaction Manager)

Transaction manager (Transaction Manger) There are 2 interesting components: The lock manager and the log manager. The lock manager is responsible for providing concurrency for data, which is configured by using the lock delivery isolation level.

Note:

In the Select query lifecycle that you just mentioned, the lock manager is also useful, and here it goes, it is mentioned because it is part of the transaction manager (Transaction Manager).

The really interesting thing here is the log manager, the access method (Access Methods) code where the request to make the change is recorded, and the log Manager writes these changes to the transaction log (transaction log), This is the pre-write log (Write-ahead Logging:wal).

Writing the transaction log (transaction log) is part of the data modification transaction, and it always requires physical writing to the hard disk, because SQL Server can use it to reread those changes even when the system crashes (you will learn this more in the next Restore section).

In the transaction log (transaction log), the actual storage is not a list of modification statements, but rather a modification of the results of the statement resulting in the page change details. This is SQL Server in order to be able to undo the modification, which also makes the transaction log (transaction log) content difficult to read, of course, you can use third-party tools to help.

Back to the update query life cycle, the update operation has been written to the log. When the transaction log has confirmed physical writes, the actual data is modified. This is why the transaction log (transaction log) operation is important.

Once the access method (Access Methods) receives the acknowledgement, it sends the modification request to the Buffer manager to complete.

The transaction manager (Transaction Manager), access method (Access Methods), records our updated transaction log (Transaction log), and completes the buffer manager for data modification requests.

Buffer Manager (Buffer Manager )

The page that needs to be modified is already in the cache, and the buffer manager is only going to modify the desired page, which is initiated by the access method (Access Methods). After the page in the cache is modified, the acknowledgment is sent back to the access method (Access Methods) and finally sent back to the client.

The key point here (and the biggest) is that the UPDATE statement only changes the data in the data cache , not the actual database file on disk. This is done for performance reasons, and now this is also known as the so-called Dirty pages (Dirty page), because it is not the same as the corresponding page on the hard disk.

This is not contrary to the modified durability defined in the acid attribute (durability of the modification), because you can use the transaction log to reconstruct the change. For example, if your server suddenly loses power, there is nothing in physical memory (such as a data buffer). How dirty pages are and when to write back to a database file is described in the next section.

The life cycle of the update operation is as shown. The buffer Manager changes the contents of the page in the cache and sends an acknowledgment to the access method (Access Methods). As you can see, the data files have not been accessed during this time.

Restore (Recovery)

In the previous section, you read the life cycle of the update query, which says SQL Server uses the pre-write log (Write-ahead Logging:wal) method to maintain the durability of any changes (durability of any changes).

Changes are written to the transaction log (transaction log) First, and then only in memory. This is done based on performance reasons and can be restored from the transaction log (transaction log) if you need to undo it. More relevant new concepts and processes are described in the Restore section (Concepts and terminology).

dirty Pages (Dirty pages)

Pages that are read back into memory from disk are identified as clean pages because it is the same as its copy. Similarly, once a page in memory is modified it is identified as a dirty page (Dirty page).

Use the empty cache (DBCC dropcleanbuffers) to clear the clean pages from the cache (Note: Remove all buffers from the buffer pool.) is handy when you troubleshoot development and test environments, because it forces subsequent reads from the disk to be implemented, not cached, without touching any dirty pages.

Dirty pages (Dirty page) are changed from hard disk loading to memory and are now not the same as on disk. Use the following dynamic view to see how many dirty pages each database has.

1 SELECT db_name(database_id) as 'Database',Count(page_id) as 'Dirty Pages'2  fromsys.dm_os_buffer_descriptors3 WHEREIs_modified=14 GROUP  by db_name(database_id)5 ORDER  by Count(page_id)DESC

Database Dirty Pages

People   2524

Tempdb   61

Master   1
As indicated above, there are 20M dirty pages in the People database (2524 * 8/1024).
These dirty pages (Dirty page) are periodically written back to the database whenever there is insufficient cache idle or checkpoint (checkpoint). For faster allocation of pages, SQL Server always keeps a certain number of available empty pages in the cache, which are tracked in the list of available caches.
When a worker thread initiates a read request, it gets a list of 64 pages in the cache and checks if the cache list is below a specific threshold (threshold). If so, the page in the list is marked as expired (age-out), which causes any dirty pages (Dirty page) to be written back to the hard disk. Another thread, called the lazy writer, is also based on the free cache list, which is insufficient.

Lazy Writers (lazy writer)

The lazy writer periodically checks the size of the list of free buffers (available buffer lists). When the value is low, it scans the entire data cache to mark pages that have not been used for some time as expired (age-out) and writes back to the hard disk in memory before they are idle.

The lazy writer also monitors available physical memory on the server, freeing the memory of the free buffer list from the cache to the system in the case of very low memory. When SQL Server is busy, it increases the size of the free buffer list (buffer pool) to meet the requirements for the available physical memory and the server's maximum memory configuration threshold.

Checkpoint procedure (Checkpoint process)

A checkpoint (check point) is a point in time created by SQL Server to ensure that any committed transactions have written their changes back to the hard disk. A checkpoint (check point) becomes a restore point that the database can begin with.

The checkpoint procedure (check point process) is used to ensure that all dirty pages (Dirty page) related to committed transactions have been written back to the hard disk. In order to use the writer effectively, it also writes the uncommitted Dirty page (Dirty page) back to the hard disk, unlike the lazywriter, where the checkpoint (check point) does not remove the page from the cache It simply writes the dirty page (Dirty page) back to the hard disk and marks the page in the cache page as clean.

By default, in a busy server, SQL Server initiates a checkpoint (check point) every minute, which is flagged in the transaction log. If the SQL Server instance or database restarts, the restore process reads the log to learn that no action is required from the log after the previous checkpoint.

Log sequence number (Sequence number:lsn)

The log sequence number (log Sequence number:lsn) identifies the record in the transaction log, it is sorted, so SQL Server can know the order in which the event occurred.

The smallest LSN number is taken before a restore, such as a roll forward or a rollback. Consider this not only the checkpoint (check point) log sequence number (log Sequence number:lsn), but also other more important. This means that the restore also needs to worry about whether a dirty page (Dirty page) has not been written back to the hard disk before the checkpoint (check point). This can happen in large systems with a large number of dirty pages (Dirty page).

Therefore, the time between checkpoints (check point) represents a lot of work to be done, and after the last checkpoint (check point) occurs, roll forward any committed transactions, or roll back any transactions that are not committed. With a checkpoint per minute (check point), SQL Server tries to guarantee that the restore time is less than 1 minutes from the beginning of a database, during which point it does not automatically perform checkpoints, unless there is a 10MB log write.

A checkpoint (check point) can be executed by a T-SQL command on check points or triggered by other events in SQL Server. For example, when you initiate a backup command, the checkpoint (check point) is executed first.

Trace flag 3502 is the error number at which the checkpoint (check point) starts and ends. For example, after you have added the tracking number since startup, after performing a series of large writes, we can see the following entry in the error log, and we can see that the checkpoint (check point) is executed once in 30-40 seconds.

Trace flag provides a way to change SQL Server behavior, usually to help us troubleshoot or to enable or disable specific features for testing purposes. There are hundreds of tracking numbers (trace flag) present but the official only public part; Click to view its public list and how to use it:

recovery interval (Recovery Interval)

recovery interval (Recovery Interval) is a server configuration option that can be used to adjust the time difference between checkpoints (check point), so you can set the number of times since the start of the database can be restored, recovery interval (Recovery Interval).

By default, the recovery interval (Recovery Interval) is set to 0, which enables SQL Server to select an appropriate interval (Interval), which is typically close to 1 minutes to automatically perform a checkpoint (check point).

Changing this value to greater than 0 o'clock represents the amount of time you want the interval to be between checkpoints (check point). Most of the time there is no need to modify, compared to the restore times, you care more about the checkpoint (check point) process, you decide whether to set.

The recovery interval (Recovery Interval) is configured only in test and experimental environments, and in order to effectively stop automatic checkpoints (check point), you can configure surprisingly high values for the purpose of monitoring things or for better performance. Unless you catch the world record speed for SQL Server, you should not modify this value in a real production environment.

To stop too much impact on the disk subsystem, SQL Server even suppresses checkpoint I/O, so it is self-managing. If you have seen the sleep_bpool_flush wait type on the server, this is because SQL Server has checkpoint I/O suppression in order to maintain the performance of the global system.

Restore Mode (Recovery Models)

SQL Server has 3 kinds of database restore methods (Recovery Models): Full, Batch log (bulk-logged), and simple. The way you choose affects how your transaction log is used, how big it grows, your backup strategy, and your restore options.

complete (full)

With full restore, all operations are required to be fully written in the transaction log, and the backup policy requires complete (full) Backup and transaction log (transaction log) backups.

Starting with SQL Server 2005, complete (full) backups do not empty (truncate) the transaction log (transaction log). In doing so, the sequence of transaction log backups will not be corrupted, providing an additional restore option in case your full backup is corrupted.

If the SQL Server database requires a higher level of restore, you should use the full restore method.

Batch log (bulk-logged)

This is a very special way to restore, and if you want to do this, you can improve the performance of specific bulk operations by using a minimal amount of log writes. Other operations are written to the log as fully as the full restore method. This can improve performance because only the information that is required to roll back the transaction is written to the log. The redo information is not written to the log, which also means that you have lost a point-in-time restore (point-in-time-recovery).

These batch logs include:

• BULK INSERT (BULK insert)
• Use executable bcp (using the bcp executable)
• SELECT into
• Creating an index (CREATE index)
• Modify index Rebuild (ALTER index REBUILD)
• Drop Index

Batch log (bulk-logged) and transaction log (Transaction log) backups

Use the batch log (bulk-logged) mode to get your batch log (bulk-logged) operation to complete faster. It does not reduce disk space requirements for your transaction log (transaction log) backup.

Easy (Simple)

If a simple restore is set on the database, all committed transactions are emptied (truncate) in the transaction log that occurs in each checkpoint (check point). This is to ensure that the log size is kept to a minimum and that no transaction log (transaction log) backup is required (if not), it is good or bad to see your restore level requirements for a database.

You can choose a simple restore if all changes that were potentially lost from the last full or differential (differential) backup still meet your business needs.

SQL Server 2012:SQL Server architecture--The life cycle of a query (part 3rd) (end)