ASYNC_NETWORK_IO Wait Type In SQL Server

Like the ASYNC_IO_COMPLETION wait type, ASYNC_NETWORK_IO pertains to throughput; however, it focuses on the throughput of the network connection between your SQL Server instance and its clients, rather than the storage subsystem. It's important to note that ASYNC_NETWORK_IO waits are normal and do not necessarily indicate a network issue, as these waits can occur even when querying the SQL Server instance directly on the server.

What is the ASYNC_NETWORK_IO Wait Type?

ASYNC_NETWORK_IO waits typically happen when client applications are unable to process query results quickly enough or when there are network performance issues. Most often, the former is the case, as many applications handle SQL Server results row-by-row or struggle with large data volumes, causing SQL Server to wait to send query results over the network. This waiting period is logged as the ASYNC_NETWORK_IO wait type. Additionally, ASYNC_NETWORK_IO waits can occur when querying remote databases via a linked server.

Sys.dm_os_wait_stats

You can check the ASYNC_NETWORK_IO wait type using the sys.dm_os_wait_stats DMV. We might not require a complicated environment to test the ASYNC_NETWORK_IO wait type.

Before running the DMV, lets clear the information from the DMV with a simple DBCC command. 

use master
go
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Once the information is cleared from the DMV, lets run a simple SELECT statement from the SQL Server instance using SSMS.

select  * from DBA_Async_Test

After querying from the above table, we can see a spike in the ASYNC_NETWORK_IO wait in the sys.dm_os_wait_stats.

select * from sys.dm_os_wait_stats where wait_type='Async_network_io'

We saw a spike in the wait type because the SQL Server Instance was not able to get all the result in the SSMS as fast as the instance itself. When the instance is not able to get the results fast on to the SSMS, we start to see more of this wait type.

How To Lower ASYNC_NETWORK_IO Waits?

The answer is pretty simple. To lower ASYNC_NETWORK_IO Wait, we just must modify the query by limiting the result set as much as possible. Let’s look at the simple example as to how we can lower the wait type.

We will be clearing the wait stat information from sys.dm_os_wait_stats again to record the reading one more time.

use master
go
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

This time, we will be limiting the records from to take to just TOP 100.

select  TOP 100 * from DBA_Async_Test

If you notice that the ASYNC_NETWORK_IO wait has been minimized. The SSMS was able to get the results fast enough and had not to wait for the completion of the query for longer duration. We can also reduce the ASYNC_NETWORK_IO wait by adding WHERE conditions to our query.

Note: If you still think that the above test case failed in your scenario, then please check the network configuration. At times, there are possibilities that the network configuration as well can slow down fetching the result sets.

We can check the network configuration by opening the task manager and navigating on to the “Ethernet” option.

If you observe high network utilization along with increased ASYNC_NETWORK_IO wait times, the network might be the bottleneck. In such instances, consulting your network administrator could be beneficial. Network configurations are complex, involving various components such as switches, routers, firewalls, network cables, drivers, firmware, and possibly virtualized operating systems. Each of these elements can impact network throughput and contribute to ASYNC_NETWORK_IO waits.

Bottomline

ASYNC_NETWORK_IO waits happen when an application requests query results from a SQL Server instance over the network and is unable to process the returned results quickly enough. While these waits are generally normal, unusually high wait times might indicate changes in query results or network issues. To reduce application related ASYNC_NETWORK_IO wait times, consider decreasing the number of rows and/or columns returned to the application.

You may also refer : 

ASYNC_IO_COMPLETION Wait Type In SQL Server

Automatic, Planned, And Forced Failover Sequence Of Actions In SQL Server

This article covers the sequence of actions the Always-On high availability takes when the primary replica goes offline. When a primary replica goes offline, the failover target assumes the primary role, recovers its databases, and brings them online as the new primary databases. When the former primary replica becomes available, it switches to the secondary role, and its databases become secondary databases.

Let's look at the three types of failover:

• Automatic failover (without data loss)
• Planned manual failover (without data loss)
• Forced failover (with possible data loss)

You can check the below table that summarizes the supported failover types under various availability and failover modes. For each pair of availability replicas, the effective availability and failover mode is determined by the combination of the modes of the primary replica and one or more secondary replicas.

Automatic Failover Internal Actions

To enable automatic failover, the conditions outlined in the table with common topologies must be met. An automatic failover triggers the following sequence of actions:

• The state of the primary databases is changed to DISCONNECTED if the primary replica is still running, and all clients are disconnected.
• The secondary database rolls forward any log entries waiting in the recovery queue.
• At this point, the secondary replica becomes the new primary replica, and its databases become the primary databases. Immediately the new replica rolls back uncommitted transactions. Rollback occurs in the background while clients continue to use the database. As part of the internal process by the Always-On, the committed transactions are not rolled back.
• Until the secondary databases connect and resynchronize with the new primary database, they are marked as NOT SYNCHRONIZED. Before rollback recovery starts, secondary databases can connect to the new primary databases and quickly transition to the SYNCHRONIZED state.
• When the original primary replica comes back online, it becomes the secondary replica, and its databases become secondary databases. It resynchronizes with the corresponding primary replica and databases. Once the databases have resynchronized, failover is possible again, but in the reverse direction this time.

Planned Failover Internal Actions

A planned failover triggers the following sequence of actions:

• At first, the Windows Server Failover Cluster (WSFC) is notified in order to ensure that there are no any new user transactions occur on the original primary replica databases.
• The database on the secondary replica rolls forward any logs waiting in the recovery queue.
• Now, the secondary replica becomes primary replica, and the primary replica turns into secondary replica.
• The new primary replica rolls back any uncommitted transactions and brings the databases online as the primary databases.
• As part of the process, none of the committed transactions will be rolled back. Additionally, the database will remain marked as “NOT SYNCHRONIZED” until the secondary databases connect and resynchronizes with the primary replica database.
• Once the new secondary replica comes online, it resynchronizes with the new primary replica databases. When everything is back online and synchronizing as before, it becomes eligible for the reverse failover. Meaning, from new primary replica to the old primary replica. 

Forced Failover Internal Actions

A forced failover triggers the following sequence of actions:

• The WSFC sends a request to the primary replica to go offline, ensuring no new user transactions occur on the original primary databases.
• The secondary database rolls forward any logs waiting in its recovery queue.
• The secondary replica then becomes the new primary replica, while the original primary replica assumes the role of the new secondary replica.
• The new primary replica rolls back any uncommitted transactions and brings the databases online as primary databases.
• Until the secondary databases connect and resynchronize with the new primary database, they are marked as NOT SYNCHRONIZING. Committed transactions are not rolled back during this process.
• When the original primary replica comes back online, it becomes the secondary replica, and its databases become secondary databases. It resynchronizes with the corresponding primary replica and its databases. After resynchronization, failover is possible again, but in the reverse direction.

Note: Force failover can be risky and can cause data loss. Perform forced failover only if you want to restore service to your availiability database immediately. 

You may also refer: Data Synchronization Modes In Always On In SQL Server

How Does A Synchronous And Asynchronous Secondary Replica Resynchronize With The Primary Replica?

It is always annoying to see either of the replicas failing, be it a primary or a secondary. However, Always-On Availability Group has an excellent feature to cover us from the situation.

In this article, we will be covering as to how a synchronous or an asynchronous secondary replica resynchronizes with primary replica.

Synchronous Secondary Replica Resynchronizing With The Primary Replica

1. When the synchronous secondary replica goes offline, its status changes from Synchronized to 'Not Synchronizing.' Once this status change occurs, the primary replica stops waiting for an acknowledgment from the secondary that it has hardened a commit and begins treating it as an asynchronous replica. This ensures that commits on the primary replica are not delayed by an unhealthy synchronous secondary replica.

2. When the secondary replica is brought back online, it establishes a connection with the primary replica and sends its End of Log (EOL) Log Sequence Number (LSN) to the primary replica.

3. Upon receiving this, the primary replica begins sending the log blocks that were hardened after the EOL LSN to the secondary replica.

4. As the secondary replica starts receiving and hardening these log blocks, its status changes to Synchronizing. This indicates that the secondary replica is connected to the primary and is catching up, essentially functioning as an asynchronous replica.

5. The secondary replica continues to harden the log blocks, apply the hardened transactions with the REDO thread, and send this information back to the primary replica.

6. This process continues until the Last Hardened (LH) LSN of both the primary and secondary replicas match. Once they do, the status of the secondary replica changes to Synchronized, and the primary replica starts treating it as a synchronous replica.

7. The primary replica begins waiting for an acknowledgment from the secondary replica for the commit before informing the user that the transaction has been committed successfully.

Asynchronous Secondary Replica Resynchronize With The Primary Replica

1. When the asynchronous secondary replica goes offline, its status changes from Synchronizing to 'Not Synchronizing.' The primary replica responds in the same way.

2. Once the secondary replica is brought back online, it establishes a connection with the primary replica and sends its End of Log (EOL) LSN to the primary replica.

3. Upon receiving this, the primary replica begins sending the log blocks that were hardened after the EOL LSN to the secondary replica.

4. As soon as the secondary replica starts receiving and hardening these log blocks, its status changes to Synchronizing. This indicates that the secondary replica is connected to the primary and is catching up.

Refer - Data Synchronization Modes In Always On In SQL Server

Data Synchronization Modes In Always On In SQL Server

In this article we will learn about the data synchronization modes in Always On high availability. There are two modes of data synchornization - Synchronous commit and Asynchronous commit.

We've all heard the saying, "You can't have it all." For instance, you can't enjoy all the perks of single life and be married, or have a busy weekend and still get plenty of rest. Similarly, when choosing the right data synchronization mode for Always On Availability Groups, you must consider trade-offs and opportunity costs.

In synchronous-commit mode, the focus is on high availability rather than performance, resulting in increased transaction latency. The primary replica waits to confirm the transaction to the client until the secondary replica has written the log to disk.

In asynchronous-commit mode, the priority is minimizing transaction latency at the expense of high availability. The primary replica confirms the transaction to the client right after logging the record locally, without waiting for any secondary replicas.

As a wise person once said, "The bad news is you can't have it all. The good news is that when you know what's truly important, you don't want it all anyway."

Synchronous-Commit Mode

Data synchronization in synchronous-commit mode works as follows:

1. A client initiates a transaction against the database in the availability group on the primary replica.

2. The primary replica generates transaction log blocks. Concurrently, the secondary replica requests the log blocks from the primary, negotiating the appropriate log sequence number (LSN) starting point and other necessary information. The primary replica’s log cache fills with these log blocks.

3. When the log block is full or the primary replica commits the operation, the log blocks from the log cache are flushed to the log file for persistence. In an Always On Availability Group setup, these log blocks are also copied to the log pool as they are flushed to the disk on the primary replica.

4. A thread called log capture reads the log blocks from the log pool and sends them to the secondary replica. For multiple secondary replicas, there is one log capture thread per replica, ensuring parallel log block transmission across replicas. The log content is compressed and encrypted before being sent to the secondary replicas.

5. On the secondary replica, a thread called log receive obtains the log blocks from the network.

6. These log blocks are written to the log cache on the secondary replica.

7. A redo thread, one per database, constantly runs on the secondary replica. As the log blocks are written to the log cache, the redo thread reads these blocks and applies the changes to the data and index pages in the secondary database to synchronize it with the primary replica. When the log block on the secondary replica is full, or upon receiving a commit log record, the content of the log cache is hardened onto the log disk on the secondary replica.

8. If the secondary replica is configured for synchronous mode, it acknowledges the commit to the primary node, indicating that the transaction is safely hardened, and the user can be informed that the transaction is committed. This ensures no data loss in case of a failover since the log is hardened on the secondary.

9. The redo thread operates independently of the log block generation on the secondary and their copying and persistence. If the redo thread is a few minutes behind, it may not find the log blocks in the log cache and will instead retrieve them from the log disk, as illustrated by the dotted line in the diagram above.

Asynchronous-Commit Mode

Data synchronization in Asynchronous-commit mode works as follows:

1. A client initiates a transaction against the database in the availability group on the primary replica.

2. The primary replica generates transaction log blocks. Concurrently, the secondary replica requests the log blocks from the primary, negotiating the appropriate log sequence number (LSN) starting point and other necessary information.

3. When the log block is full or the primary replica commits the operation, the log blocks from the log cache are flushed to the log file for persistence. In an Always On Availability Group setup, these log blocks are also copied to the log pool as they are flushed to the disk on the primary replica.

4. If all secondary replicas are in asynchronous availability mode, a successful I/O to the local transaction log is sufficient to send an acknowledgment of a successful commit back to the application. Simultaneously, a thread called log capture reads the log blocks from the log pool and sends them to the secondary replica. For multiple secondary replicas, there is one log capture thread per replica to ensure parallel transmission. The log content is compressed and encrypted before being sent to the secondary replicas.

5. A thread called log receive runs on the secondary replica, receiving the log blocks from the network and writing them to the log cache.

6. The log blocks are written to the log cache on the secondary replica.

7. A redo thread constantly runs on the secondary replica. As the log blocks are written to the log cache, the redo thread reads these blocks and applies the changes to the data and index pages in the secondary database, synchronizing it with the primary replica. When the log cache on the secondary replica is full or receives a commit log record, it hardens the content onto the log disk on the secondary replica.

8. The redo thread operates independently of the log block generation and copying on the secondary replica. If the redo thread lags by a few minutes, it may not find the log blocks in the log cache and will instead retrieve them from the log disk, as indicated by the dotted line in the diagram.

Note: 
Starting with SQL Server 2016, the synchronization throughput of Availability Groups has increased approximately tenfold due to enhancements in the data synchronization process. These improvements include parallel and faster compression of log blocks on the primary replica, an optimized synchronization protocol, and parallel decompression and redo of log records on the secondary replica. In SQL Server 2012 and 2014, the redo process was executed serially by a single thread, limiting it to a single CPU core. This caused secondary replicas to struggle with high-write workloads, leading to long downtimes during a failover. In SQL Server 2016, the redo process is executed in parallel, utilizing all available CPU cores. These enhancements have improved database recovery times on failover and increased the data freshness on secondary replicas.

What Is Change Data Capture In SQL Server?

I was working on a project where we had to record the activities on a database when tables or rows get modified. We thought about Microsoft's feature called the Change Data Capture in short called as CDC in SSMS. 

What is Change Data Capture (CDC)?

Change Data Capture (CDC), first introduced in SQL Server 2008, is a valuable feature that tracks and captures changes made to SQL Server database tables without requiring additional programming efforts. Initially, CDC could only be enabled on databases in the SQL Server Enterprise edition, but starting with SQL Server 2016, this restriction was removed.

CDC monitors INSERT, UPDATE, and DELETE operations on database tables, recording detailed information about these changes in mirrored tables. These mirrored tables have the same column structure as the source tables, with additional columns to describe the changes. For each INSERT operation, CDC writes a record showing the inserted values. For each DELETE operation, it writes a record showing the deleted data. For each UPDATE operation, CDC writes two records: one showing the data before the change and another showing the data after the change.

Enabling CDC On The Database

Before enabling the CDC for any table, we should enable the CDC at the database level by using sp_cdc_enable_db system stored procedure. Any DBA working on this enable/disable should have a sys admin permission.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_enable_db;
GO

Now we can check if the CDC has been enabled using sys.databases DMV.

Enabling CDC On The Table

After enabling CDC on the database, we can enable the CDC at the individual table level by using sp_cdc_enable_table system stored procedure. You can enable with the below command.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'dtproperties',
    @role_name = NULL;  -- or specify a role for access control
GO

Similarly, to make sure that the CDC has been enabled at the table level, use sys.tables DMV. 

Disabling CDC

To disable CDC, the command are similar to how we used for enabling CDC. First we will be disabling the CDC at the table level using below command.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name = N'dtproperties',
    @capture_instance = N'dbo_dtproperties';  -- This is optional if you have a single capture instance
GO

Then disable the CDC at the database level. 

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_disable_db;
GO

Note: You can disable the CDC directly at the database level if you want to cut down a step.

Bottomline

CDC is a powerful feature for tracking changes to your data, but it’s essential to manage it properly to ensure your system's performance and integrity.

What Is A Temporary Table In SQL Server?

Temporary tables are commonly used by database developers and DBAs to store data temporarily. Temporary tables are also known as temp tables in SQL Server. We will be representing as temp table moving forward in this article. Temp tables are generally used for faster data retrieval and increased query performance. Let’s look deeper and learn about the temp tables in SQL Server.

What is a temp table in SQL Server?

The temp table is used to store data temporarily for faster data retrieval. It works like a normal table to perform select, insert, update, or delete commands. Temp tables are created with a prefix with either a single # or a double ##. Additionally, temp tables are automatically dropped when a session is closed in SQL Server.

Types of temp tables

There are two types of temp tables. Local temp table, and a global temp table.

Local temp tables

Local temp tables are created with a single # prefix on a table name. It means that the local temp tables are active and usable to the session that created them. No other user can access the temp table when the session is active upon creating a local temp table. However, if you are creating a local temp table within a stored procedure, the temp table will be dropped automatically.

Creating a temp table and working with DDL/DML commands.

create table #Temp_Table
(
[id] int,
[Hospital] varchar(100),
[Location] varchar(100)
)

insert into #Temp_Table
select 1, 'Apollo', 'New York'

Update #Temp_Table set Hospital = 'Apollo NY' where ID=1

Delete from #Temp_Table where ID=1

select * into #Temp_Table_2 from #Temp_table

drop table #Temp_table

Global temp table

Global temp tables are created with a double ## prefix on a table name. It means that the temp table is active and usable for other users as well. But the only protocol to follow is that the session that opened for creating the global temp table must be open and active for others to access. The moment the master session closes, the global temp table will be dropped, and other users lose access to the global temp table. 

create table ##Temp_Table
(
[id] int,
[Hospital] varchar(100),
[Location] varchar(100)
)

insert into ##Temp_Table
select 1, 'Apollo', 'New York'

Update ##Temp_Table set Hospital = 'Apollo NY' where ID=1

Delete from ##Temp_Table where ID=1

select * into ##Temp_Table_2 from ##Temp_table

drop table ##Temp_table

Where are the temp tables stored in SQL Server?

All the temp tables are stored in the tempdb. All the temp tables sit in the temporary tables folder under the tempdb.

Bottomline

Temporary tables are used for faster data retrieval. In many cases, temp tables come handy while optimizing a query. Both local and global temp tables will be stored in the tempdb. 

You may also refer: 
CTE in SQL server

HADR_LOGCAPTURE_WAIT And HADR_WORK_QUEUE Wait Type In SQL Server

The HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE are waits related to AlwaysOn Availability Group. The AlwaysOn Availability Group concept was introduced in SQL Server 2012. It was introduced to replace database mirroring. The AlwaysOn wait types can be easily recognized by the HADR_ prefix. We will be looking into the HADR_LOGCAPTURE_WAIT and HADR_WORKQUEUE waits in detail.

What are the HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE wait types?

The HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE wait types are benign and occur naturally within the AlwaysOn configuration.

HADR_LOGCAPTURE_WAIT

HADR_LOGCAPTURE_WAIT occurs when waiting for the log records to be available. We can encounter this wait type when new log records are generated by connections. Additionally, the wait type can be expected when the log scan is read from the disk. HADR_LOGCAPTURE_WAIT occurs in the primary database inside the AlwaysOn Availability Group. 

HADR_WORK_QUEUE

The HADR_WORK_QUEUE wait type occurs when the AlwaysOn Availability Group is waiting for a new worker thread to be assigned. This is an expected action by the AlwaysOn functionality. Additionally, the wait type is an indication of free threads waiting for work. 

Sys.dm_os_wait_stats

You can check the HADR_LOGCAPTURE_WAIT and HARD_WORK_QUEUE wait type information from the DMV – sys.dm_os_wait_stats.

Bottomline

Both HADR_LOGCAPTURE_WAIT and HARD_WORK_QUEUE wait types are good. They are natural to encounter if we have the AlwaysOn enabled servers. Having these wait types does not indicate a performance overhead. There is no attention needed in case you see these wait types.

More Wait Types For your reference:

What is REDO_THREAD_PENDING_WORK Wait Type?
What is HADR_SYNC_COMMIT Wait Type?

SELECT INTO TEMP TABLE Vs INSERT INTO TEMP TABLE Statement In SQL Server

If you are a database developer or admin, then the SELECT INTO TEMP TABLE and INSERT INTO TEMP TABLE will be almost your everyday task. This article will help you explore the SELECT INTO and INSERT INTO TEMP TABLE in detail.

The SELECT INTO TEMP TABLE is a simple method of creating a new table and copying the data from the source table. The SELECT INTO TEMP TABLE does the following operations internally.

• Creates a new table like the source table with the exact same column with data type
• Reads and inserts to the new table from the source table

SELECT INTO TEMP TABLE Statement

The SELECT INTO TEMP TABLE is used to create a new table and copy the data over to either a user table or a temporary table.

select * into #Tmp_Employees from employee

The above example will insert all records into the #Tmp_Employees temporary table from the employee user table.

Suppose we want to insert specific columns of the employees' table into the temporary table, then we must specify the column names in the SELECT INTO statement.

select empno,ename, job into #Tmp_Employees from employee

In both the examples of select into with * and with specific columns, the column name remains the same as the source table.

INSERT INTO TEMP TABLE Statement

Again, the INSERT INTO TEMP TABLE statement is used to insert the source table data into the temporary table. In this case, the table creation will have to be manually created by the user. The SQL Server does not involve creating the temporary table internally.

INSERT INTO statement can be used either for inserting into an existing user table, or a newly created temporary table.

The below example illustrates the insertion of all columns into a temporary table from the employee table.

create table #Tmp_Employees
(
empno int,
ename varchar(100),
job varchar(100),
deptno smallint,
comm smallint,
ID int
)

insert into #Tmp_Employees
select * from employee

The statement selects all columns with the help of a * from the employee table and inserts it into #Tmp_Employees temporary table.

The below example illustrates the insertion of particular columns into a temporary table from the employee table.

insert into #Tmp_Employees (empno, ename, job)
select empno, ename, job from employee

The statement selects just empno, ename, job columns from employee table and inserts them into #Tmp_Employees temporary table. 

SELECT INTO Vs INSERT INTO TEMP TABLE Statement

• SELECT INTO creates a destination temporary table automatically. It reads data from the source table and inserts it into the temporary table.
• INSERT INTO doesn’t create a destination temporary table automatically. We have to explicitly insert it into the existing user table or a temporary table from the source table.

Performance Metrics

Starting from SQL Server 2014, the SELECT INTO performance has shown better. This is because the statements have been running parallel to improve performance. However, through the course of my experience, here is my take below.

SELECT INTO works well if the columns to create are lesser. If the number of columns increases with complex logic underlying, the SELECT INTO performance degrades.

INSERT INTO works well if the columns to be inserted are more.

In general, the SELECT INTO performance is better than the INSERT INTO. Be sure of the number of columns you are supplying in both SELECT INTO and INSERT INTO statements. 

Bottomline

In this article, we have learned about the difference between the SELECT INTO and INSERT INTO statements. In a practical environment, both methods are extensively used based on the scenario. Use them in appropriate conditions to get the best results or performance. 

More articles for your reference:
Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever
Difference Between Truncate and Delete Command in SQL Server

BACKUPBUFFER Wait Type In SQL Server

A lot of times the DBA will come across one wait type and that is the BACKUPBUFFER. The BACKUPBUFFER wait type will occur every time there is a backup happening in your SQL Server instance. It doesn’t matter if the backup operation is written on a tape mount or not, the SQL Server will still generate the BACKUPBUFFER wait type. Let’s look in detail at the BACKUPBUFFER wait type.

What is the BACKUPBUFFER Wait Type?

The BACKUPBUFFER wait type is generated whenever there is a backup operation happening in an SQL Server instance. When a backup runs, the SQL Server allocates a buffer for the backup process. The wait type will occur regardless of any backup method used. It can be a full, differential, or log backup. Once the buffer allocates, the buffer gets filled with data in your database and will be written to the backup file.

Additionally, the backup buffers with regards to amount and size are automatically calculated by SQL Server. However, the values can be configured manually by passing parameters to the backup or restore command.

 

How to check the backup buffer amount and size?

To check the backup buffer amount and size, we will first have to enable two trace flags, 3213 and 3605. These two trace flags will output backup and restore details into the SQL Server error log.

DBCC TRACEON (3213);
DBCC TRACEON (3605); 

Note: Enable these trace flags in SQL Server under a DBA’s guidance or Microsoft support. 

We now have an idea of how the backup operation pushes the information inside the SQL Server error log. Additionally, we also got the idea that the SQL Server backup process uses a buffer to store data in the backup file.

What we need to understand is, when the BACKUPBUFFER wait type occurs in an SQL Server instance? The BACKUPBUFFER wait type comes in whenever the buffers are not available to write directly in the backup file. The backup process will wait until the buffer is available. When the backup process waits, the BACKUPBUFFER wait type will keep generating. The wait type shows up until the buffer is directly available.

Sys.dm_os_wait_stats

You can check the wait type details in the sys.dm_os_wait_stats DMV. 

select * from sys.dm_os_wait_stats  where wait_type ='BACKUPBUFFER'

How to Lower BACKUPBUFFER Waits?

The BACKUPBUFFER wait type is not a concern of performance issues in an SQL Server instance. Whenever there is a backup operation running in your SQL Server machine, the BACKUPBUFFER wait type can come up. We can commonly lower the BACKUPBUFFER wait times by adding more buffers for the backup operation. This alteration can be done by specifying the BUFFERCOUNT in the backup command.

Note: Making a change in the BUFFERCOUNT can result in out-of-memory problems in case large buffers get generated. Use the option under a DBA’s guidance. 

Bottomline

The BACKUPBUFFER wait type occurs whenever a backup runs in an SQL Server instance. The value of this wait type can increase when the backup operation is waiting for more buffers to allocate. In most cases, the BACKUPBUFFER wait type is not a performance overhead.