ASYNC_IO_COMPLETION Wait Type In SQL Server

The ASYNC_IO_COMPLETION wait type is commonly encountered by a database administrator. It occurs every time SQL Server performs a file-related action at storage level and waits for it to complete. As the wait type is related to storage, you might encounter more often when a backup happens. However, if you encounter the ASYNC_IO_COMPLETION wait type.

What is the ASYNC_IO_COMPLETION Wait Type?

The ASYNC_IO_COMPLETION wait type occurs when a task is waiting for storage-related action to complete. In general, this wait type is more seen while a backup is happening in your server. The SQL Server monitors the signal from the storage subsystem for faster completion. In case of a delay from the storage side, the ASYNC_IO_COMPLETION wait type occurs. To be simpler, the faster your storage subsystem, the lower you see the ASYNC_IO_COMPLETION wait type.

If you are seeing this wait type in your server, it means that your SQL Server task is communicating with the storage subsystem and there should not be any matter of concern. It generally flags the wait type when a database creation or a database backup operation takes place. You should take the wait type into consideration when the wait times are higher than the baseline measurements.  

Sys.dm_os_wait_stats

You can check the ASYNC_IO_COMPLETION wait type using the sys.dm_os_wait_stats DMV.

Before running the DMV, lets clear the information from the sys.dm_os_wait_stats DMV using a simple DBCC command.

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

I will now run a full backup in my server. As soon as I execute the full backup, the information gets loaded into the DMV.

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

How to Lower ASYNC_IO_COMPLETION Wait Type?

In most cases, high ASYNC_IO_COMPLETION wait is due to a database backup. To find if the wait type was because of a backup, then query the DMV with other backup waits.

select * from sys.dm_os_wait_stats  where wait_type
in ('ASYNC_IO_COMPLETION', 'BACKUPIO','BACKUPBUFFER') 

You can also try to lower the ASYNCH_IO_COMPLETION waits by configuring instant file initialization. Instant file initialization is a feature introduced by Microsoft for Windows 2003 to speed up the disk space allocation.

Bottomline

The ASYNC_IO_COMPLETION wait type is triggered by actions related to the storage subsystem within your SQL Server instance, such as database backups and new database creation. While these waits are generally normal, unusually high wait times can suggest storage issues. Before involving your storage administrator, confirm there is a true performance issue. One way to do this is by checking storage latency, as high latency can increase ASYNC_IO_COMPLETION wait times. Also, verify if the increased wait times coincide with database backups. Enabling instant file initialization by adding your SQL Server service account to the Perfmon volume maintenance tasks local policy can help reduce ASYNC_IO_COMPLETION wait times.

You may also refer:

BACKUPBUFFER Wait Type In SQL Server

How A Forced Failover Can Cause Data Loss In SQL Server?

I was working with one my clients in Always-On and unfortunately in the process of fixing an error, one of the members in our team forcefully failed over Always-On availability group from one instance to another. There is a need to understand the risks associated with force failover as it can lead to data loss.

This occurs because the target replica may be unable to communicate with the primary replica, making it impossible to ensure that the databases are synchronized. Figure below illustrates how a forced failover can cause data loss on the primary replica and how this loss can extend to a secondary replica.

1. In the previous example, the primary replica's last hardened LSN is 100 before it goes offline, while the asynchronous secondary replica's LSN is 50.

2. After a forced failover, the secondary replica becomes the new primary and sets its last hardened LSN to 50.

3. When the old primary comes back online, its synchronization status is suspended.

4. If synchronization is resumed, the old primary will sync with the new primary, sending its last hardened LSN as 100. Upon finding the new primary's last hardened LSN is 50, it will roll back its transaction log to LSN 50 and start accepting transaction log blocks from the new primary from that point onward. Thus, data loss is propagated from the primary to the secondary replica if synchronization is resumed.

Refer the related articles -
Data Synchronization Modes In Always On In SQL Server
How Does A Synchronous And Asynchronous Secondary Replica Resynchronize With The Primary Replica?

Difference Between sp_who and sp_who2 In SQL Server

The sp_who and sp_who2 are the two most used system stored procedure to get information on users, sessions, and processes of an instance in an SQL Server. In a production environment, it becomes an important aspect for a DBA to get the SQL Server processes running. Though both systems' stored procedures are used to get the users, sessions, and processes information, there are some key differences.

sp_who

• The sp_who has limited columns to show. The results are all related to the processes running in an SQL Server instance.
•  The sp_who is official and documented by Microsoft.  
• The sp_who is supported by Microsoft.

Sp_who2

• The sp_who2 has more columns to show than the sp_who. Like sp_who, the results are all related to the processes running in an SQL Server instance.
•  The sp_who2 is not official and is documented by Microsoft.
• The sp_who2 is not supported by Microsoft. 

Bottomline

Both sp_who and sp_who2 stored procedures are handy for a DBA to get the process information in SQL Server. For less column information you can prefer to go with sp_who whereas for more column information you can prefer to go with sp_who2. You can judge which stored procedure to use based on your requirement.

What Is KILLED/ROLLBACK In SQL Server?

The KILLED/ROLLBACK is a common condition that database developers/DBAs come across. It will be a pounding situation for many DBAs in case a SPID goes into a KILLED/ROLLBACK in an outage window. This is because the KILLED/ROLLBACK can take enough time to roll back the killed session.

So, what is the KILLED/ROLLBACK state? How to get things back to normal?

The KILLED/ROLLBACK is a situation where a DBA kills a SPID to end a process, but SQL reverses the work done. This mainly happens when a session is hung or takes a long execution time. Once the status of the command changes to KILLED/ROLLBACK, the SQL does the real rollback process. In most cases, the KILLED/ROLLBACK processes get rolled back completely. But in rare cases, the SQL might not have real control over the command. In such cases, the KILLED/ROLLBACK SPID can stay in the same state forever. 

Query to check the SPID in KILLED/ROLLBACK

select spid,
kpid,
cmd,
loginame,
lastwaittype
from sys.sysprocesses

Solution

• Wait for the SPID to completely rollback. If it is related to a hung backup job, then you can restart the SQL Backup Services. This should fix your situation
• If the SPID has still not rolled back, then restart the SQL Service. 

Note: As per my experience, the rollback situations are time-consuming. It takes more CPU, Memory, and IO. If you ever killed a SPID that has gone into a ROLLBACK situation, then keep a note when you get into a similar situation as you did now. This might help some of your time. 

Bottomline

The KILLED/ROLLBACK is a real rollback situation where SQL is trying to get the database to a consistent state. However, if the SQL doesn’t have enough control over the process, the SPID can be in KILLED/ROLLBACK forever. A restart of SQL Backup Service/SQL Server Services is postponing the SPID’s rollback. The database becomes available once the recovery step runs after the SQL Service restart.  

REDO_THREAD_PENDING_WORK Wait Type In SQL Server

REDO_THREAD_PENDING_WORK is an AlwaysOn High Availability Wait Type which is without a HADR_ prefix. In most cases, this wait type can be ignored as they are not very important to be noticed. But it is good for all the DBAs to immediately identify the wait types being generated in the SQL Server Instance.

What is REDO_THREAD_PENDING_WORK?

Inside an AlwaysOn Availability Group, the REDO_THREAD_PENDING_WORK wait type occurs on the secondary replica. Further, a wait type accumulates over a course of time in the server when there is no work to be carried out. As we all are much aware that an AlwaysOn Availability Group processes log records, hardens them on transaction logs, and then confirms the same to the primary replica. While using the synchronous mode, the primary replica waits to commit on the client until a transaction commits in the secondary replica. Whereas, in the asynchronous mode, the commit message is sent to the client without waiting for hardening on the secondary replica.

Until now, we have seen the internals of an AlwaysOn High Availability for a synchronous and asynchronous mode. The REDO_THREAD_PENDING_WORK wait type comes to the picture when there are any modifications inside the secondary replica log records. The REDO thread does not impact the commit confirmation from the secondary replica. Furthermore, this states that the secondary replica transactions may not the exact same as the primary replica even after the transactions are committed on both sides.

At this point, you might think that the information could be lost during a failure. However, you must note a point that all the information will be recorded in the secondary replica logs. The Secondary replica will have all the information it requires to perform in the secondary replica log files. In case of a failure, the SQL Server will either redo or undo the internal processes from the transaction log.

How to check the REDO_THREAD_PENDING_WORK wait type in Secondary Replica?

The wait type occurs in secondary replica because of accumulated wait time naturally. This can be monitored using a DMV called sys.dm_os_wait_stats. This DMV can be used to fetch the waiting_tasks_count, wait_time, max_wait_time, and signal_wait_time. All the durations mentioned from the DMV will be in milliseconds.

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

Note: In most cases, this wait type can be ignored. 

What Are The Backup Types In SQL Server

Backup is considered one of the biggest criteria in disaster recovery plans and strategy. Being a DBA, it is very important to take care of the database with backups all in place. A SQL Server database becomes complete with the data files, filegroups, and log files. Further, these files are inclusive of both user and system databases. Understanding the database backups can help us in better planning of disaster recovery strategy.

Let’s check a simple overview of backup types in SQL Server.

1. Full Backup

This is the most common backup type in SQL Server. A full backup pushes all the data and objects in the data files for a given database. The objects are nothing but the tables, views, procedures, functions, triggers, indexes, etc.

While other backups are little toiling to restore, the full backup becomes one of the simplest restoration processes. This is due to a single .bak file that comprises all the data including the log transactions.

2. Differential Backup

This is the second common backup that a DBA thinks of. A differential backup pushes all the data and objects for a given database that are modified since the last full backup. So basically, it means that the data modified after the recent full backup can rationally be smaller in size. However, the size of the differential backup is hypothetical and can vary depending on the number of changes done after the last full backup.

The differential backup file where the data is pushed is the .bak same as the full backup but consists of an additional statement of WITH DIFFERENTIAL in the backup script. The advantage of having a combination of full backup and differential backup strategy can reduce the risk of data loss. Additionally, they can even reduce the size of the backup files. Restoring a differential backup will mandatorily require the last full backup which will be considered a base.

3. Transaction Log Backup

Transaction log backup is one such backup that a DBA understands to restore a backup for a point-in-time recovery. The transaction log backup pushes all the log records into the .LDFfile since the last log backup. This backup type works only when the database is set to “Full” or “Bulk-logged” recovery model.

Once a transaction log backup is created, the .LDF logical file will be ready for reuse. In the production environment, the log backups are taken periodically to ensure point-in-time recovery for databases.

4. Full File Backup

Full file backup pushes all the data and objects to the specified data files or filegroups. This backup type is basically used lesser when compared to the other three backups mentioned above. Full file backup becomes a useful option when your database is big and has a huge data file.

5. Differential File Backup

Differential file backup pushes all the data and objects to the specified data files or filegroups that have changed since the last full file backups. So, if you are considering a differential backup with a huge size, then a differential file backup can be created. However, the differential file backup is considered not relevant as they are mostly related to just one data file.

6. Partial Backup

Partial backup is a backup type that pushes a complete writable portion of the database but excludes any read-only files or filegroups. The option was first introduced in SQL Server 2005. Additionally, there are chances where we may have read-only filegroups in our databases. So, this option of partial backup can help take the backup by excluding all the read-only filegroups in the database.

The backup type works only for full or differential backups but doesn’t work for transaction log backups. While a filegroup is changed from Read-Only to Read-Write, the backup will be taken automatically in the next Partial backup. However, while a filegroup is changed from Read-Write to Read-Only, a new filegroup should be created.

7. Differential Partial Backup

Differential partial backup pushes all the data and objects that have changed since the last partial backup. The feature was added in SQL Server 2005 and was designed for databases where the filegroups are broken up. A restore of the differential partial backup requires the last full partial backup. Like the full and differential backup scenario, the differential partial backup is smaller in size when compared to the partial backup. Due to which, the differential partial backups are always faster.

8. COPY ONLY Backup

A COPY ONLY backup is a backup type that works for either full or differential backup. The backup type with COPY ONLY option is designed to avoid any disruption to backup sequence for the respective database. Additionally, the backup option was introduced in SQL Server 2005. Therefore, the hassle of backup sequence disruption can be avoided using the COPY ONLY backup option. This scenario can be helpful when there are multiple Adhoc backups happening on a given database and the actual sequence is being interrupted.

What Is HADR_SYNC_COMMIT Wait Type In SQL Server

The HADR_SYNC_COMMIT wait type is a wait type that is commonly seen if your servers are configured with an AlwayOn recovery model. Further, the HADR_SYNC_COMMIT wait type was introduced in SQL Server 2012. If you have already worked on the Database Mirroring recovery model, then the wait type DBMIRROR_SEND in the database mirroring is relatively similar to as that of the HADR_SYNC_COMMIT wait type.

What is the HADR_SYNC_COMMIT Wait Type?

Before we start, one thing to get cleared is that, in AlwaysOn High-Availability, the transaction hit on a primary server first gets committed on the secondary and only then comes back to commit on primary.

The HADR_SYNC_COMMIT wait type represents the time taken by the secondary replica to acknowledge the harden log records to the primary replica. As soon as a transaction is started in the primary replica, the HADR_SYNC_COMMIT wait type starts. Once it starts, the transaction is moved to the secondary replica for hardening into the log. The HADR_SYNC_COMMIT wait time will only end when the secondary replica acknowledges that the transaction was committed on the secondary replica. This scenario happens when the AlwaysOn Availability Group is set to synchronous mode.

In a production environment, it is normal to experience HADR_SYNC_COMMIT wait type on your AlwaysOn Availability Group setup.

Reasons for HADR_SYNC_COMMIT Wait Types:

1. Intermittent network connection between primary and secondary replica.
2. Performance issues on storage subsystem on a secondary replica.
3. High log queue being transferred from primary to the secondary replica.

How to check a wait type with HADR_SYNC_COMMIT

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'HADR_SYNC_COMMIT'

From the code above, it is easy for you to figure out the wait types we see on primary as well as the secondary replica. I ran the above code on my primary replica and got the results as below:

From the figures, you may notice the waiting_tasks_count and wait_time_ms. Both columns represent the number of commands and the wait time respectively taken to commit on secondary and acknowledge on the primary.

How to check the average wait time using this data: 

From our gathered data, I am calculating the average wait time. Here, we may notice that the average wait time calculated is 8 ms which is a decent value.

 

Fix/Analysis: How to lower the HADR_SYNC_COMMIT wait type?

1. Change the Synchronous mode to Asynchronous mode of your secondary replica (this can completely remove the HADR_SYNC_COMMIT wait type)
2. Analyze the log send size, log send rate, redo queue size, redo rate, and mainly synchronization performance from AlwaysOn Availability Group dashboard.

Connect to your server à Expand “Always On High Availablity” à Right-click on your Availability Group à Click on “Show Dashboard” à Right-click on any column and view information

3. Troubleshoot using the DMV – sys.dm_hadr_database_replica_stats. This contains most of the information related to AlwaysOn. Additionally, you can check for other DMV too related to AlwaysOn. All of them start with a prefix as dm_hadr.
4.  Add counters on Availability Replica and check for performance-related issues trapped there.
5. Optimize your query (this is one of the foremost options to recommend to fellow DBAs and Database developers)

Conclusion

As the HADR_SYNC_COMMIT wait type is purely related to the performance of the secondary replica, it is always recommended checking the secondary replica initially. Try to optimize the query, lower the network intermittency, and avoid overload to the server to lower the wait type.

Note: In the production environment, it is recommended not to constantly switch between the synchronous and asynchronous mode. This could lead to data loss on disaster recovery situations. In case of emergence, perform the switch to Asynchronous, however, get them back to synchronous as early as possible. 

You also refer: "LCK_XXX Wait Types"

Difference Between Clustered Index And Non-Clustered Index In SQL Server

Indexes are purely used to increase the performance of your queries. The creation of indexes can be something resembled that of an index page on a textbook that can help you directly go to the page with reference.

A similar concept applies while creating indexes in SQL Server. Additionally, an index associated with a table is in-turn latched up with the disk structure. Whenever, there is a call for an index, the associated on-disk pointer retrieves the data quickly for high performance. 

If your table is without an index, then there happens to be a HEAP index. HEAP is nothing but a table without a clustered index. In this situation, the data is stored randomly causing the whole table scan whenever data retrieval operation happens. Keeping the HEAP index apart, let’s dive to see the differences between a clustered and a non-clustered index.

A table can consist of two types of indexes:

1. Clustered Index
2. Non-Clustered Index

Clustered Index

A clustered index is mapped on to a table that helps in sorting and storing the data rows with respect to key values. The only possible way of storing the data in a sorted manner is while creating a clustered index. And this is the actual reason behind why a table can have just a single clustered index.

Let’s check the scenario of creating a clustered index on a table.

C1. Create a table as [Clustered_Index_TEST]

create table [Clustered_Index_TEST]
(
ID int constraint PK_Clustered_Index_TEST_ID primary key (ID)
,[Students] nvarchar(50) null
,[Marks] int
)

C2. Insert some records in [Clustered_Index_TEST] table

insert into [Clustered_Index_TEST]
values (2,'Jason','97') 

insert into [Clustered_Index_TEST]
values (1,'James','54') 

insert into [Clustered_Index_TEST]
values (3,'Adams','68') 

insert into [Clustered_Index_TEST]
values (6,'Tyler','71')

C3. Select records from [Clustered_Index_TEST] table where ID=2 for an example

select * from [Clustered_Index_TEST]
where ID =2

Observations: If you notice in the execution plan, the number of reads on the table is 1 and Logical Operation is Clustered Index Seek. We can conclude here saying that the clustered index on the ID column searched is going to be fetched directly using the index rather than a complete table scan. 

Further, the table’s ID column was inserted in a jumbled manner, but the clustered index helped in sorting them.

POINT TO NOTE: Every table in SQL Server created should have a mandatory clustered index created for better performance. 

Non-Clustered Index

Non-clustered Index is mapped on a table wherein the data rows are not in a sorted manner. The non-clustered indexes have a separate structure that holds key values. Every time there is a call for a non-clustered index, the respective key values fetch the data row that the index holds.

This scenario can resemble that of a website link. The link of a website resides at one place and the content page behind the link resides at another. Every time, we click the link (non-clustered index with key-value), the content page of the link is fetched (data rows where the non-clustered index with key points).

Let’s check the scenario of creating non-clustered index on a table.

NC1. Create a table as [NonClustered_Index_TEST]

create table [NonClustered_Index_TEST]
(
ID int
,[Students] nvarchar(50) null
,[Marks] int
) 

NC2. Insert some records in [NonClustered_Index_TEST] table

insert into [Clustered_Index_TEST]
values (2,'Jason','97') 

insert into [Clustered_Index_TEST]
values (1,'James','54') 

insert into [Clustered_Index_TEST]
values (3,'Adams','68') 

insert into [Clustered_Index_TEST]
values (6,'Tyler','71')

NC3. Create a non-clustered index on ID column 

create nonclustered index Ind_NCI_TEST_ID on NonClustered_Index_TEST(ID)

NC4.  Select records from [NonClustered_Index_TEST] table where ID=2 for an example

select * from  [NonClustered_Index_TEST]
where ID =2

Observation: If you notice from the execution plan, the number of reads on the table is 4 and the logical operation performed is Table Scan. It means that every time a record is fetched from the table, the SQL Server will scan the entire table rather than just one record. 

POINT TO NOTE: Please have the practice to create a clustered index on a table. If exists already as a HEAP index on a table, please convert them to the clustered index for high performance.

You may find this helpful: 'Difference between Shared lock, Exclusive lock, and Update lock'

Comparing CROSS APPLY and INNER JOIN in SQL Server

Do CROSS APPLY and INNER JOIN produces the same result set?

I have been talking with many of my DB friends, and they have come up with a question of comparing the CROSS APPLY and INNER JOIN. The APPLY is an SQL Server operator that was introduced in 2005 for comparing the expressions on table A column with the expressions on the table B column. I have come up with a scenario of usage of both CROSS APPLY and INNER JOIN to make the comparison and understanding easier. 

CROSS APPLY

The usage of CROSS APPLY is very simple, which returns the expressions that are matched between the left table and the right table or vice versa.

INNER JOIN

The usage of INNER JOIN is simpler than that of a CROSS APPLY, where it returns the same result set as that of a CROSS APPLY. The result set is nothing but the matching expressions between the left table and the right table or vice versa.

Let’s take an example of CROSS APPLY and INNER JOIN for comparison

1. Create a new table as Hospital

CREATE TABLE [Hospital](

[HospitalID] [int] NOT NULL PRIMARY KEY,

[Name] VARCHAR(250) NOT NULL,

) ON [PRIMARY]

2. Create a new table as Doctors

CREATE TABLE [Doctors](

[DocID] [int] NOT NULL PRIMARY KEY,

[FName] VARCHAR(250) NOT NULL,

[LName] VARCHAR(250) NOT NULL,

[HospitalID] [int] NOT NULL REFERENCES [Hospital](HospitalID),

) ON [PRIMARY]

3. Insert values into Hospital table

INSERT [Hospital] ([HospitalID], [Name]) 

VALUES (1, N'Hospital1')

INSERT [Hospital] ([HospitalID], [Name]) 

VALUES (2, N'Hospital2')

INSERT [Hospital] ([HospitalID], [Name]) 

VALUES (3, N'Hospital3')

INSERT [Hospital] ([HospitalID], [Name]) 

VALUES (4, N'Hospital4')

INSERT [Hospital] ([HospitalID], [Name]) 

VALUES (5, N'Hospital5')

INSERT [Hospital] ([HospitalID], [Name]) 

VALUES (6, N'Hospital6')

GO

4. Insert values into Doctors table

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])

VALUES (1, N'Pradeep', N'TS', 1 )

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])

VALUES (2, N'Johnson', N'Hall', 2 )

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])

VALUES (3, N'Tom', N'Krueger', 3 )

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])

VALUES (4, N'Bill', N'Hokkins', 3 ) 

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])

VALUES (5, N'Kim', N'Bills', 3 ) 

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])

VALUES (6, N'James', N'Clark', 3 )

Comparing CROSS APPLY and INNER JOIN

We’ll try to implement the CROSS APPLY operator between the Hospital table and the Doctors table and check the results with the execution plan.

SELECT * FROM Hospital H

CROSS APPLY

(

SELECT * FROM DOCTORS D

WHERE D.HospitalID=H.HOSPITALID

)B

GO

Now, let’s try to implement the INNER JOIN operator between the Hospital table and the Doctors table and check the results with the execution plan. 

SELECT * FROM HOSPITAL H

INNER JOIN DOCTORS D

ON

H.HOSPITALID=D.HOSPITALID

RESULT: The CROSS APPLY and INNER JOIN produces the same result sets with the same execution plan.

Question: Where does CROSS APPLY works efficiently and outcomes the results of an INNER JOIN?

Just imagine if we would require just the top 1 result from table B verse the matching records on table A, then the CROSS APPLY is worthy for us. This condition cannot be achieved just using an INNER JOIN operator in SQL Server, which would require an additional CTE concept implementation along with INNER JOIN logic.