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. 

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.