Storage engine articles

Share the master's blog, write the main content, and do not dare to translate it. If you forget to review it after reading it, you can also promote the master's blog.

Importance of choosing the right lob storage technique n/Char: it is easy to use when the data length is fixed and can be used to limit the column size, avoid fragmented production due to too long pages. The disadvantage is that the application is hard to identify. If the data length is smaller than the column definition, the disk space may be wasted, the IO volume will increase, and the memory space will be wasted.

N/varchar (1-8000): For varchar smaller than 8000 bytes, this avoids space waste. However, if the size changes, paging may cause fragmentation. After SQL Server, the column bytes in a table can exceed 8060 bytes, and the excess part will be saved in the overflow page. when reading this data, I/O will be performed once more, it may also cause difficulties in determining whether the data page is in or out of the page. If the data is large and Nearly 8000 bytes, only a few pages can be saved on one page, if this data is not commonly used, it will be inefficient. N/varchar (max) column in-row: This type is basically the same as above. one advantage is that it can exceed 8000 bytes, when more than 8000 bytes are automatically saved on the overflow page, additional Io is required to access it. the maximum size of this byte is 2 GB compared with that of filestream. If the table contains a lob data column, then, the clustered index cannot be rebuilt in the online mode.

N/varchar (max) column out-of-row: the disadvantage of this type is that additional Io is required for access. When this column is sorted, a large number of Io occurs. It is a good choice if it is not commonly used to store overflow pages, but there are still 24-byte pointers. Another advantage is that the overflow page can be placed in another file group through textimage_on, in this way, the two-side Io can be achieved without any impact. N/Text column in-row: it has been abandoned. The effect is the same as that of the N/varchar (max) Non-overflow page. N/Text column out-of-row: we have abandoned the use of separate tables as above, and we can use join to obtain them when needed: This method is not very helpful for users, but requires more front-end designs and complex SQL statements, another advantage is that the primary table can use the online index to create filestream column: This type uses filestream when the column data exceeds 1 MB. obtaining data from the file system is faster than obtaining data from the buffer pool, for more information, see the White Paper.

Inside the storage engine: Ghost cleanup in Depth. When a record is deleted under the clustered index, the record is marked as a ghost record but not deleted. In this way, the deletion will become faster, and the rollback only needs to unmark the record. After the transaction is committed, there is a ghost cleanup program in the background to clear the records marked as ghost. The last data page will be retained with the most ghost record to prevent the page from being released. When a record is deleted, ghost is marked on PFS and on the data page. However, the flag on PFS does not notify the handler for processing. The cleanup process will only process this page at the next scan, or wait until the cleanup process is activated every 5 seconds. The cleanup process will scan the PFS page and process the ghost page. Skip to the next database if no cleanup is required. If no ghost record is found in this scan, the previous tag will be set and the database will be skipped after the wake-up in the next 5 seconds. Ghost cleanup Redux. When a record in heap is deleted, the record is marked as Ghost and the length is changed to 14 bytes (6 (xsn) + 8 (the actual pointer of files, pages, and rows in tempdb )). It will soon be treated as ghost data cleanup (you can refer to the SQL server technology insider series for a little details) Turning off the ghost cleanup task for a performance gain, this will reduce the need to save the page in the buffer pool for cleanup, and produce logs, resulting in physical Io. If tf661 can be enabled for a database with a large Delete volume, the ghost page will not be cleared. Do changes to index keys really do in-place updates? I pointed out the in-place update error in SQL server2008 internals, but I did not find it, it is pointed out that the in-place update should be out-place update for the modification of the index key. The author made a test to demonstrate that the key modification will not occur in-place update, but will use out-place Update (that is, delete before insert ). When an out-place update is performed on a slow page, pagination occurs. Analyze the entire process from the perspective of transaction logs: 0. Set the updated record to ghost 1. assign a new page A (which will be used as the root page or index page) A and modify the iam page. 2. format this page (that is, formatting 96-bit headers, etc.) 3. insert the index of the original data page to page. set "A" to "root" or "index page" 5. allocate a new page B (as a leaf node) 6. the second line is moved to page B. insert the modified record to page B. The author said that the ghost on the previous page will be cleaned up later, but it has never been cleared during my tests. How are per-column modification counts tracked? There are two ways to track the number of times each column is modified. SYS. sysrccol needs to use DAC to access this metadata table. Another view is sys. system_internals_partition_columns which does not need to use DAC but is not archived. It is obvious that rowmodctr in sysindex cannot be used. This will be cleared when statistics are updated. Where are sp_configure settings stored? Another reason to backup master... Many DBAs will back up the system database and restore it on another server to check the correctness of the backup, and use DBCC checkdb to check. A Consistency error occurs when the master database is restored and DBCC checkdb is used for check, because the config block is unique to the master, the error is reported because the page is allocated but the unit cannot be found. It stores the content of sp_configure. When SQL Server is started, it reads the content of this page. If it cannot be read, an error is returned. New Script: When were the sp_configure options last changed? The author shares the information about obtaining the last time the sp_configure was modified. Using DBCC config, You can see sp_configure cfgupddate, cfgupdtime update date, and update time. Cfgupddate is the offset from 1900.1.1 to the last update date. In cfgupdtime, the unit is 3.3 Ms. The Config block will be modified only when the service is restarted or when there is a major change (it is not clear what kind of change is a major change. Parent transaction ID in 2012 fn_dblog output, the advantage is that you can directly look at fn_dblog to know what operations the SQL server has done internally, rather than relying on speculation. At the end of this article, a transaction ID is a record with a transaction ID of. The main operation is to set the PFS to a blank page, which is not part of the transaction. It is also impossible for the transaction to maintain this setting, transaction maintenance will surely become congested.