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. 

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.

How To Kill All Processes For A Database In SQL Server

I was working on my client’s system whose entire instance CPU was eaten up by the SQL Server. While checking many options and backend processes, we got to know that some random processes were getting generated from a database. We were stressed to kill the sessions one by one. We followed the below options to kill all the processes in a database. 

There are two methods to kill all processes generated from a database. 

Option 1: Kill all the processes by changing the database to single-user mode

Here is the script to change the database from multi-user to single-user

ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [DBName] SET MULTI_USER;

Option 2: Kill all the processes by a looping script

SET NOCOUNT ON
Declare
@cnt int ,
@i int=1,
@dbname varchar(500),
@cmd nvarchar(max),
@spid nvarchar(max)
drop table if exists #TmpWho
CREATE TABLE #TmpWho
(id int identity(1,1) not null, spid INT, ecid INT, status VARCHAR(100), loginame VARCHAR(100),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(100),request_id int)
INSERT INTO #TmpWho
EXEC sp_who
set @cnt=@@rowcount
While (@i<=@cnt)
begin
select @spid=spid from #TmpWho
where id=@i --and @dbname= 'yourdbname'
set @cmd='KILL ' + @spid + ''
print (@cmd)
set @i=@i+1
end

I usually prefer option 1 over option 2 because in most cases killing processes one by one is going to take quite some time. Additionally, option 2 will work slowly in case we have hundreds of threads to kill.

Caution: Be very cautious while executing both the above options to kill the processes in a production environment. Execute the above scripts under a DBA's supervision.

You can kill sessions using Activity Monitor too.

Fix - 'IDENTITY_INSERT is already ON for table. Cannot perform SET operation for table.' In SQL Server

The IDENTITY attribute is a column identifier to have a unique value in a table. An identity column can have data types like INT, SMALLINT, BIGINT, TINYINT, or NUMERIC. An IDENTITY_INSERT is a table property that helps explicitly to insert values into the IDENTITY column.

Error

IDENTITY_INSERT is already ON for table <first table_name>. Cannot perform SET operation for table <second table_name>.

Fix

The IDENTITY_INSERT property can be used with either ON or OFF options. We cannot set the IDENTITY_INSERT ON for more than one table in a single session in SQL Server. If you ever try to switch the IDENTITY_INSERT ON for the second table in the same session, then you might notice an error something as above. Simply run an IDENTITY_INSERT OFF on your first table to fix the error.

set identity_insert <your_first_table> off --table 1 -- hospital

Example case scenario

I will be taking two tables in my example, hospital, and employee. Here are the steps.

Step 1: I am running an IDENTITY_INSERT ON on hospital table

set identity_insert hospital on --table 1 -- hospital

Step 2: Also, I am running an IDENTITY_INSERT ON on employee table after running on hospital table. After this step, the SQL Server will immediately give an error. 

set identity_insert employee on --table 2 -- employee 

If you notice that the moment I run the IDENTITY_INSERT on the second table, the error occurs. It means that we must switch OFF the IDENTITY_INSERT on the hospital table.

Bottomline

IDENTITY_INSERT is a property at a table level that is used for explicitly inserting value in the identity column. IDENTITY_INSERT ON/OFF is valid on a single table on a single transaction. Before going on to the second table for explicit identity column value insertion, once cross verify that you ran the IDENTITY_INSERT OFF on your first table.

Some of the identity-related posts for your reference:
How to Add or Remove Identity Property on Column from a table in SQL server
Reset Identity Column Values In SQL Server

What Is High Availability In SQL Server

Every business application has one critical need in common, and it is their data availability all the time. To achieve the same, businesses must formulate a strategy to lower user downtime and high availability.

What is High Availability?

The High Availability (HA) is configured to lower the downtime impact of your business and increase the availability of your data. Disaster can happen anytime and there is no system in the world that assures 100% of availability. The whole idea of High Availability is to mask the effects of failure or natural disaster. Further, in case of a failure, the High Availability ensures getting the critical data online as soon as possible.

High Availability Solutions

To achieve a solution with High Availability, there is a need for people with the right skills, responsibilities, and leadership in place. With people, it is more important to have the right ownership to handle the situation correctly. 

How is Availability calculated?

Every Availability is calculated and expressed in the percentage of uptime in a given year. The formulae for calculating the Availability is:

The above formulae can give you the results with a number of 9s in the decimal value. As the number of 9’s increases, the uptime increases correspondingly. There is a simple chart that illustrates the same.

 

Reasons for Downtime

There are two types of downtime.

Planned downtime 

The planned downtime is one that most businesses plan and execute during the maintenance window. 

Some of the common planned downtimes are.

• Hotfix or patching window
• Server or app maintenance
• Software and Hardware upgrades

In most of the above-planned maintenance, the impact is low as the users are informed in prior to initiating the maintenance work.

Unplanned downtime

Unplanned downtime is referred to as an unpredicted event of failure. There are many times an instance can get into an unplanned outage and some of the reasons are:

• Server failure
• Datacenter failure
• Storage failure
• Network failure
• Human error

Bottomline

Every business in the globe has risk factors for undergoing emergency outages. It can happen due to any of the mentioned unplanned disasters. To overcome the same, High Availability becomes a key role to implement in business applications to make sure data is always available for the user. It is a good practice to invest in planning for High Availability in advance. 

DBMIRROR_SEND Wait Type In SQL Server

As the name suggests, the DBMIRROR_SEND wait type is related to database mirroring. The database mirroring feature was introduced in SQL Server 2005. However, the feature was deprecated in SQL Server 2012. Let’s look in-depth at the DBMIRROR_SEND wait type.

What is DBMIRROR_SEND Wait in SQL Server?

The DBMIRROR_SEND wait type is a wait related to synchronous mirroring configuration. The wait suggests that the task is waiting for a communication backlog at the network layer for sending messages. It means that the communication layer is starting to become overloaded and affect the database mirroring data throughput. In case the disk subsystem connection runs slow, the subsequent DBMIRROR_SEND wait can go high.

Additionally, the DBMIRROR_SEND wait gets recorded in the mirror instance and not on the principal instance. However, in some cases, the wait type can get recorded in the principal instance as well as the mirror instance. If you notice a high value of DBMIRROR_SEND waits, it means the mirror instance is experiencing some latency.

There are two different operating modes in the database mirroring that impact the availability and mirror configuration. One is the synchronous mode, and the other is the asynchronous mode. Synchronous mode provides high safety. Asynchronous mode provides high performance.

Synchronous Commit

• In a synchronous commit mode, when a transaction is received, the principal will write a log to the transaction log. However, it does not commit the transaction.
• The principal will send the log record to the mirror.
• The mirror will harden the log record to disk and send an acknowledgment to the principal.
• Once the principal receives the acknowledgment, a confirmation message will be sent to the client for completing the transaction. Finally, the transaction gets committed in the transaction log on the principal. 

 Asynchronous

The Asynchronous model is similar to the synchronous model, except for the acknowledgment message received at the principal end. It means that the transactions will be committed on the principal without getting the confirmation from mirror. Though the performance of keeping the model asynchronous is high, there will be data loss. In case of a disaster, there could be a situation the transactions are still not committed to the mirror instance.

sys.dm_os_wait_stats

You can track all the dbmirror_send wait type values in the sys.dm_os_wait stats DMV.

How to lower DBMIRROR_SEND?

The wait type is mostly seen in the synchronous mirroring configurations. The DBMIRROR_SEND wait gets reduced if the mode is changed from synchronous to asynchronous. If you are performing this action, the wait type can be lowered significantly. It also comes with a cost. Setting the mode to asynchronous can lead to data loss. The option should be handled carefully in the production environment. It is not always good to set the mirroring mode to asynchronous. Decide based on the transaction that is going to run at the principal instance.

Bottomline

The DBMIRROR_SEND wait type is related to database mirroring. It is very common to see this wait when the database mirroring is configured in your instance. The Synchronous mirroring option gives a high wait value. Whereas the asynchronous mode will lower the wait.

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.  

How To Kill Processes Using Activity Monitor In SQL Server

I was working on a project and got to a situation where the sessions were generated randomly on the SQL Server machine. We had to kill all the related sessions on the SQL Server machine before performing our own task. One simple method was to kill the related sessions using the Activity Monitor.

Note: Please be cautious while killing processes in your production environment.

How to kill all processes using Activity Monitor?

Step 1: Connect to your SQL Server instance. Select ‘Activity Monitor’ from the menu. 

Step 2: Expand the ‘Session ID’, ‘Application’, and ‘Blocked By’ tabs to check your session ID and related information.

Step 3: Right-click on the respective SPID and click on ‘Kill Process’ 

You can repeat the same for the session you are willing to kill.

Additionally, we can find the active sessions using the sp_who2 active command. Most of the DBAs use the sp_who2 active to fetch the blocking session and kill them.

Now that you have learned to kill the process using the Activity Monitor, my personal perception will be to use sp_who2 active to fetch and kill them in case the motive was to find the blocking session.

WAITFOR Wait Type In SQL Server

In most cases, the WAITFOR wait type does not indicate any performance issues. Though the wait type indicates a small amount of CPU usage when the WAITFOR T-SQL command is executed.

What is the WAITFOR Wait Type?

The WAITFOR wait type is a wait type that gets created in SQL Server when the WAITFOR command gets executed. The WAITFOR command is used to stop the execution for a specific amount of time specified. Usually, the WAITFOR commands are used inside a script to pause the execution for some time. There are many reasons why developers use WAITFOR command.

While using the WAITFOR command, the entire transaction will remain open until it completes. It means that the threads will remain open and other transactions will not be allowed to access the same.

In most cases, the WAITFOR commands are not alarming. The command is explicitly used by the person who runs the query.

Example of WAITFOR Demonstration

Step 1: Clear the entries from the sys.dm_os_wait_stats.

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

You might still see a value greater than 0. This is normal. 

Step 2: Run the WAITFOR DELAY command with 5 seconds interval

WAITFOR DELAY '00:00:05';

Step 3: Check the entry again in sys.dm_os_wait_stats to see the WAITFOR wait type generated

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'WAITFOR';

You will notice an increase in the wait_time_ms for WAITFOR wait type. 

Bottomline

The WAITFOR wait type gets generated while using the WAITFOR command in SQL Server. The wait type does not indicate any performance issues. If you are using more WAITFOR commands in the query then the WAITFOR wait type as well will be increased. Suppose you wish to lower the WAITFOR wait type, then check for your scripts to remove the WAITFOR commands. 

OLEDB Wait Type In SQL Server

Most commonly, the DBAs come across a wait type known as OLEDB. The OLEDB wait type means Object Linking and Embedding Database. There could be many reasons the SQL Server uses the OLEDB Client Provider. Whenever it does so, the SQL Server records the wait time on the OLEDB wait type.

What is the OLEDB Wait Type?

The OLEDB Wait Type is a wait type that occurs whenever the SQL Server must access another SQL Server instance. It can be from one machine to another or from one instance to another. One example of OLEDB wait comes in the scene when there is a linked server used to move the traffic of respective instances to another. The OLEDB wait type can also get generated when the data gets gathered from outside sources into the SQL Server machine.

There could be instances where you might get to notice the usage of OLEDB Client Provider even when the data is moving internally in your SQL Server. Such can be seen while using the DBCC commands.

Example of OLEB demonstration:

Step 1: Clear the entries from the sys.dm_os_wait stats

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Step 2: Run the DBCC CHECKDB command for your database

DBCC CHECKDB('SQLArena_Test');

Step 3: Check the entry again from the sys.dm_os_wait_stats to see the OLEDB wait type generated

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'OLEDB';

If you notice from the above example demonstration, the OLEDB wait type gets generated when the DBCC command is run. It means that the DBCC makes use of the Client Provider. 

Lowering OLEDB Waits

The OLEDB wait type is one of the performance-indicating wait types in SQL Server. Here are some occurrences of OLEDB Waits and the ways to monitor them.

• If you are querying information from a linked server, and the same has been taking a while to connect to the remote machine, the OLEDB waits can go high. Try to increase the availability of remote server instances. 
• If there is a network intermittent issue between the source to the remote server, then again the OLEDB waits can go high. Try to make sure the network is good always to increase the performance of your application. 

Bottomline

The OLEDB wait type occurs mainly due to the use of Object Linking and Embedding Client Provider. In more cases, the OLEDB wait types are not performance issues. But on the other hand, it is a wait type that should not be ignored too. It is always a good practice to keep a look at this wait type if your production server is more relying on the remote instance.

Here are more wait types for your reference:
CHECKPOINT_QUEUE Wait Type In SQL Server
DIRTY_PAGE_POLL Wait Type In SQL Server
MSQL_XP Wait Type In SQL Server

MSQL_XP Wait Type In SQL Server

I was working on an SQL Server instance wherein I found the instance running slow for some reason. While checking the background waits, I got to find the MSQL_XP wait type having higher values. MSQL_XP wait type should not be confused with the “preemptive wait type” PREEMPTIVE_GETPROCADDRESS. Though you might have worked on the PREEMPTIVE_GETPROCADDRESS wait type, it does not record the execution time of an extended stored procedure.

What is the MSQL_XP Wait Type?

MSQL_XP wait type records the execution time of an extended stored procedure in your SQL Server instance. The wait type can come in handy in detecting deadlock situations when using Multiple Active Result Sets (MARS).

Note: MARS is a feature in SQL Server that helps the execution of multiple batches through a single SQL Server connection.

Additionally, there is an article that showcases the MARS example here. 

You can check the statistics of the MSQL_XP wait type in sys.dm_os_wait_stats. 

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

In most cases, the wait_time_ms value remains stagnant or not too much varying in the DMV. However, in case you notice a big value in sys.dm_os_wait_stats, then probably there an extended stored procedure that is taking a long time to execute. 

How to lower MSQL_XP Waits?

If you notice a high value of wait for the MSQL_XP in sys.dm_os_wait_stats, then the first thing is to identify the exact extended stored procedure event that is causing the slowdown. As the MARS is known to execute multiple batch commands from a single SQL connection, there might be chances that some Windows sessions are slowing down. Due to the same reason, there could be reasons for possible deadlock events in your Windows machine. So your target should be to identify the extended stored procedure slowing things down. Microsoft has been giving a lot of patch updates every month or so. It makes a good practice to patch your SQL Server immediately once the patches are released by them.

Bottomline:

You will need to draw attention toward the MSQL_XP wait type when there is an extended stored procedure that is slowing down and taking longer than normal to complete. In such cases, the wait_time_ms value in the sys.dm_os_wait_stats will be higher. Identify the right extended stored procedure to fix them before they cause any performance issues.

LAZYWRITER_SLEEP Wait Type In SQL Server

The LAZYWRITER_SLEEP wait type is a background wait related to the SQL Server internal lazywriter process. This wait type shows some characteristics with the checkpoint process where the dirty pages are written to database data files from the buffer cache. As a database admin, it is good to understand the reason and occurrence of the LAZTWRITER_SLEEP wait type.

What is the LAZYWRITER_SLEEP Wait Type?

The LAZYWRITER_SLEEP wait type is a background wait like CHECKPOINT_QUEUE waiting for work in your SQL Server instance. But the role of this wait type is slightly different from what is done by the CHECKPOINT_QUEUE wait type. The LAZYWRITER_SLEEP checks and scans the size of the buffer cache and determines if there is always a certain amount of space to fit the incoming new pages. The process checks to see the buffer cache space for free pages instead of swapping with the old ones.

In case there is not enough buffer cache space to write new pages, the lazywriter process will detect, between checkpoint, to see if there are any dirty pages in the buffer cache that were not accessed for a while. Such pages will be written to the database data file and removed from the buffer cache. In case your server is busy, then the lazywriter process will take some time to free up space in the buffer cache.

Performance Metrics

In most cases, the LAZYWRITER_SLEEP can be safely ignored. However, if you notice a constant movement of dirty pages to the database data files from the buffer cache, it might indicate a possible performance issue. Your SQL Server instance could be facing memory pressure.

sys.dm_os_wait_stats

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

The wait type can be checked in the sys.dm_os_wait_stats DMV. The wait_time_ms indicates the amount of time the lazywriter process spent being inactive. Though they signal a high value, they can be ignored. However, the DBAs can know how long the lazywriter process is spent inactive in the SQL Server instance.

Bottomline

The LAZYWRITER_SLEEP wait type does not wake up or stay asleep until there is a signal to check for the buffer cache. This wait type is a background wait and can be safely ignored.