Resource DB in SQL Server

Resource DB in SQL Server 

It is introduced in SQL Server 2005.

It is one of the SQL Server system Databases but we can’t see it use SQL Server management studio.

It is a read-only database that contains all the system objects that are included with SQL Server.

The physical file are mssqlsystemresource.mdf and mssqlsystemresource.ldf.

This Files of Resource DB are located in the SQL Server default installation path (<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\)

Resource Database makes upgrading SQL Server to a New Version an easier and a faster procedure. In the previous versions of SQL Server, upgrading to a new version required dropping and creating system objects. However, since the resource database contains all system objects, an upgrade can now be achieved simply copying the resource database (mssqlsystemresource.mdf and mssqlsystemresource.ldf) files to the local server

This Files should not be moved.

Changing the location of the resource database is not supported or recommended.

DBAs should backup Resource Database along with other System Databases in SQL Server as part of Disaster Recovery and most importantly document the location of each and every system and user databases.

SQL Server cannot backup Resource Database hence DBA will have to perform file-based or disk-based backup by considering mssqlsystemresource.mdf and mssqlsystemresource.ldf files as if they are .EXE files. Using the XCOPY, ROBOCOPY or COPY command you can copy the .MDF and .LDF files even when SQL Server is up and running.

Restoring Resource Database means copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective location which you have documented.

In case if there is a hardware failure and you need to rebuild your environment by restoring master database on to a new drive location. Then before restoring master database using WITH MOVE option a copy of Resource Database’s .mdf and.ldf files should be present.

Conclusion:

The resource database is the fifth, "hidden" database that is a read-only database and contains all system objects of SQL Server. It is used to make the upgrade process of SQL Server faster and easier. This database cannot be backed up and restored (using traditional SQL Server methods) and also, the location of its data and log files cannot be changed. It has a fixed database ID of 32767, which is the maximum number of databases per instance.

Ghost records and cleanup task

Ghost records

Records that are deleted from a leaf level of an index page aren't physically removed from the page - instead, the record is marked as 'to be deleted', or ghosted. This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost. This is to optimize performance during a delete operation. Ghosts are necessary for row-level locking, but are also necessary for snapshot isolation where we need to maintain the older versions of rows.

Ghost record cleanup task

Records that are marked for deletion, or ghosted, are cleaned up by the background ghost cleanup process. This background process runs sometime after the delete transaction is committed, and physically removes ghosted records from pages. 

The ghost cleanup process runs automatically on an interval (every 5 seconds for SQL Server 2012+, every 10 seconds for SQL Server 2008/2008R2) and checks to see if any pages have been marked with ghost records. If it finds any, then it goes and deletes the records that are marked for deletion, or ghosted, touching at most 10 pages with each execution.

Disable the ghost cleanup

On high-load systems with many deletes, the ghost cleanup process can cause a performance issue from keeping pages in the buffer pool and generating IO.As such, it is possible to disable this process with the use of trace flag 661. However, there are performance implications from disabling the process.

Disabling the ghost cleanup process can cause your database to grow unnecessarily large and can lead to performance issues. Since the ghost cleanup process removes records that are marked as ghosts, disabling the process will leave these records on the page, preventing SQL Server from reusing this space. This forces SQL Server to add data to new pages instead, leading to bloated database files, and can also cause page splits. Page splits lead to performance issues when creating execution plans, and when doing scan operations.

WARNING: Disabling the ghost cleanup process is not generally recommended. Doing so should be tested thoroughly in a controlled environment before being implemented permanently in a production environment.

T-log file and Virtual Log Files (VLFs) and Log blocks and Write-ahead transaction logging

Transaction log file : The database transaction log maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. There must be at least one log file for each database.

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

The basic structure of an LSN is [VLF ID:Log Block ID:Log Record ID]

Here's an example of an LSN:00000031:00000da0:0001, look at the output of sys.dm_db_log_info DMV and examine the vlf_create_lsn column.

Many types of operations are recorded in the transaction log. These operations include:

• The start and end of each transaction.

• Every data modification (insert, update, or delete). Modifications include changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

• Every extent and page allocation or deallocation.

• Creating or dropping a table or index.

Virtual Log Files (VLFs)

The SQL Server Database Engine divides each physical log file internally into several virtual log files (VLFs). Virtual log files have no fixed size, and there's no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it's creating or extending log files. The Database Engine tries to maintain a few virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files can't be configured or set by administrators.

VLF's creation formula has been changed for SQL Server 2014 (12.x) and later versions

·       If growth < 64 MB, create 4 VLFs that cover the growth size (for example, for 1-MB growth, create 4 VLFs of size 256 KB).

·       If growth > 64 MB up to 1 GB, create 8 VLFs that cover the growth size (for example, for 512-MB growth, create 8 VLFs of size 64 MB).

·      If growth > 1 GB, create 16 VLFs that cover the growth size for example, for 8-GB growth, create 16 VLFs of size 512 MB).

What is the impact of too many VLF’s? 

One or more databases take a very long time to finish recovery during SQL Server startup.

Restoring a database takes a very long time to complete.

Attempts to attach a database take a very long time to complete.

When you try to set up database mirroring, you encounter error messages 1413, 1443, and 1479, indicating a timeout.

You encounter memory-related errors like 701 when you attempt to restore a database.

Log blocks: Each VLF contains one or more log blocks. Each log block consists of the log records.

A log block is the basic unit of I/O for transaction logging.

In summary, a log block is a container of log records that's used as the basic unit of transaction logging when writing log records to disk.

Log Truncation: Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space that is allocated to its physical log files. 

However, before the log can be truncated, a checkpoint operation must occur. 

A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, a log truncation can free the inactive portion.           

Write-ahead transaction logging : SQL Server uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

To understand how the write-ahead log works, it's important for you to know how modified data is written to disk. SQL Server maintains a buffer cache (also called a buffer pool) into which it reads data pages when data must be retrieved. When a page is modified in the buffer cache, it isn't immediately written back to disk; instead, the page is marked as dirty. A data page can have more than one logical write made before it's physically written to disk. For each logical write, a transaction log record is inserted in the log cache that records the modification. The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. 

How differential backup works and impact of copy_only backup on Differential backup

Overview of differential backups

A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and when the differential backup is created.

The following illustration shows how a differential backup works. The figure shows 24 data extents, 6 of which have changed. The differential backup contains only these six data extents. The differential backup operation relies on a bitmap page that contains a bit for every extent. For each extent updated since the base, the bit is set to 1 in the bitmap.

Note 

The differential bitmap is not updated by a copy-only backup. Therefore, a copy-only backup does not affect subsequent differential backups.

Query Store

Query Store:  This is used for performance troubleshooting and check the optimal execution plan used and if a query is taking longer than expected time.

Description

It is a relatively new feature introduced in SQL 2016 (every SQL Server edition). 

It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database.

Query Store is per-database-level feature which means that it can be enabled on every SQL database separately by using SQL Server Management Studio or T-SQL. It is not an instance level setting.

This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade.

All data that SQL Server Query Store capture are stored on disk.

Here are the common scenarios where the SQL Server Query Store feature can be useful:

 Find the most expensive queries for CPU, I/O, Memory etc.

Get full history of query executions.

Get information about query regressions (a new execution plan generated by query engine is worse than the older one).

Quickly find performance regression and fixing it by forcing the previous query plan whose performances are much better than a newly generated plan

Determine how many times a query was executed in the given range of time. 

Always-on, Quorum and Direct seeding

When defining a Windows Failover Cluster to build an SQL Server Always On Availability Group over it, will we use this cluster option “Add all eligible storage to the cluster”? why?

No. We need to uncheck the Add all eligible storage to the cluster option.

This is due to the fact that Always On Availability Group feature works based on having dedicated storage for each replica without having shared storage between the replicas.

What are the quorum configuration modes that are available in Windows Failover Clustering?

• Node Majority Quorum configuration mode in the Windows Failover Cluster, with each cluster node, gives one Vote. This option fits a cluster with an odd number of nodes
• Node & Disk Majority Quorum configuration mode in the Windows Failover Cluster, with each cluster node, gives one vote, with an additional vote for the cluster quorum disk. This option fits the clusters with an even number of nodes
• Node & File Share Majority Quorum configuration mode in the Windows Failover Cluster, with each cluster node, gives one vote, with an additional vote for a shared file
• No Majority, where the status of the cluster depends completely on the availability of the Quorum disk

What is the difference between configuring the secondary replica as readable or read-intent only?

• The readable secondary allows read-only access to the secondary databases
• Read-Intent Only secondary replica allows the secondary server to serve the read-only workload ONLY, where the connection string of the application should explicitly mention the                  Application Intent=Readonly parameter

What is SQL Server Always On Availability Group Read-Only Routing List?

A new option introduced in SQL Server 2014 version, in which the read-only workload will be redirected to the first available secondary replica specified in a predefined routing list, taking into consideration that the application explicitly mentions the Application Intent=Readonly parameter.

What is the difference between the Direct Seeding and Full Backup and Log Backup initial synchronization processes in Always On Availability Group?

In the Full backup and log backup method, the initial synchronization between the primary and secondary replicas is performed by taking full and transaction log backups of the primary database to a predefined shared folder then restore it to the secondary replicas.

The Direct Seeding method, introduced in SQL Server 2016, the secondary databases will be initialized automatically using Microsoft SQL Server Virtual Device Interface (VDI) backup that is performed to the secondary replica over the network without the need for any network share.

Commonly encountered wait types in SQL Server

Commonly encountered wait types include:

PAGELATCH_ --Latches synchronize access to internal SQL Server memory structures. Many classes of Latches, but BUFFER is most common i.e. control access to buffer cache.

PAGELATCH_UP –Means Tasks are waiting for tempdb resources. Additionally, the wait type is PAGELATCH_UP, and the wait resource points to pages in tempdb.

PAGEIOLATCH_EX—Buffer latches including the PAGEIOLATCH_EX wait type are used to synchronize access to BUF structures and associated pages in the SQL Server database. The most frequently occurring buffer latching situation is when serialization is required on a buffer page.

WRITELOG—When a SQL Server session waits on the WRITELOG wait type, it is waiting to write the contents of the log cache to disk where the transaction log is stored.

IO_COMPLETION --wait represents SQL Server waiting for non-data page I/O.  It is common for this wait to occur during tempdb spill-over from sort and hash operations

ASYNC_NETWORK_IO—The async_network_io (in SQL 2005/2008) and networkio (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough.

CXPACKET—This wait type is involved in parallel query execution and indicates that the SPID is waiting on a parallel process to complete or start.                                                                                            Excessive CXPACKET waits may indicate a problem with the WHERE clause in the query. Query tuning/ Index tuning may require.

SOS_SCHEDULER_YIELD—SQL Server instances with high CPU usage often show this wait type, which can indicate a need for further research and action.

LCK_M_* -- locking/blocking scenario--A session waiting on LCK_M_* wait is the victim.

RESOURCE SEMAPHORE --is an internal algorithm in SQL Server that grants requested memory to SQL Server queries based on their compiled execution plan to run and execute on the instance.  RESOURCE SEMAPHORE works on a first come first serve basis so all new queries will be in the queue and waiting queries will get their request memory based on their arrival in the queue.

HADR_SYNC_COMMIT --This wait type is only used for databases participating in an Availability Group. When modifications occur on the Primary Replica in synchronous mode it must commit all of these on the Secondary Replicas before committing on itself. This wait type accumulates during this period.

Suggested solutions:

1. If SLAs allow, investigate whether the Availability Group can be switched to asynchronous mode.
2. Ensure that the network between replicas is not over-utilized, if so consider upgrading.
3. Rewrite modification DML that performs small (row-by-row) changes. Batch modifications result in less transaction log usage and messages across the network.
4. Remove unnecessary indexes, less messages and transaction log usage on modifications.
5. Scale out database design. 

 

Difference between the SQL Server CHECKPOINT and Lazy Writer

Let’s have a quick summary to see the difference between the CHECKPOINT and the Lazy Writer process.

CHECKPOINT	LAZY WRITER
SQL Server uses CHECKPOINT to flush dirty pages in the disk to keep database recovery as per the defined threshold. check	SQL Server uses the Lazy Writer process to flush the dirty and clean pages for keeping free space in the buffer cache to accommodate new pages
CHECKPOINT generates the transaction log records and follows the write-ahead logging process	A lazy writer does not make any entry in the transaction log
CHECKPOINT flushes only the dirty pages	It flushes both dirty and clean pages
We can manually execute the CHECKPOINT or control the CHECKPOINT behavior by setting the appropriate parameters	DBA cannot control on the Lazy Writer process
CHECKPOINT is also dependent on the recovery model	Lazy Writer does not have any relationship with the database recovery model
We can monitor the CHECKPOINT event by using the undocumented system function as well as the trace flags. Starting from SQL Server 2012, it also logs an entry in the error log for the long CHECKPOINT	We cannot monitor the Lazy Writer process
We can check CHECKPOINT LSN in the database boot page	We cannot check the Lazy writer process

Types of SQL Server CHECKPOINTS

Types of SQL Server CHECKPOINTS : 

Automatic CHECKPOINT --

It is a background process in SQL Server and default, most common checkpoint. We have a recovery interval server configuration in the sp_configure command,

The default value for the recovery interval is set to zero, which means the target recovery interval is one minute.

Internal CHECKPOINT --

SQL Server controls these internal Checkpoint events. DBA cannot interfere in these operations.

SQL Server also issues internal SQL Server checkpoint based on a few key events in SQL Server. These events are as follows.

1) During database backup

2) Database Snapshots. It might be explicitly database snapshot or snapshot due to the DBCC CheckDB command

3) SQL Server also performs Checkpoint for a clean shutdown of SQL Services

4) Add\Remove database files using the Alter database command

5) Switching recovery models from FULL or Bulk-logged to Simple

6) In the simple recovery model, if the log file becomes 70% full

Manual CHECKPOINT --

We can also issue Manual Checkpoint in SQL Server using the SQL Server CHECKPOINT command. This command runs under the current database context only.

Indirect CHECKPOINT---

Indirect Checkpoint is the new feature starting from SQL Server 2012. It is the default SQL Server Checkpoint type from SQL Server 2016. In this mode, we can specify target recovery time for a specific database.

We can specify the target recovery time using the following Alter database command.

ALTER DATABASE [Database name] SET TARGET_RECOVERY_TIME = Duration_InSeconds;

How Automatic Failover Works in DB Mirroring?

If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

The witness and mirror servers register that the principal server is unavailable.

If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database.

The former mirror database moves online as the new principal database, and recovery cleans up all uncommitted transactions by rolling them back as quickly as possible.

When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror, making its database the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible.

What is Automatic Page Repairing?

After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error, this whole process is called Automatic Page Repair.

What are the page types which are not repairable using Automatic Page repair option?

File header page (page ID 0).

Page 9 (the database boot page).

Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.