Nest Software

Why TempDB?

The TempDB system database plays an important role in SQL Server performance tuning process. Because it is used as caching storage to store different types of user database objects and to store the system internal objects in order to speed up the SQL Server Database Engine related processes.

Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.

I want to try give an introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.

The temdb is a special system database used to store temporary objects and data like tables, views table variables, tables returned in functions, temporary objects and indexes. The temdb can also be used for internal operations like rebuilding indexes (when the SORT_IN_TEMPDB is ON), queries using UNION, DBCC checks, GROUP BY, ORDER BY. Hash join and Hash aggregate operations.
But you must know,”The tempdb is in simple recovery model because the information stored is temporary.”
Creating temporary objects:

There are several temporary objects in SQL Server. Let’s start with the local temporary table.

Local temporary tables:
The following example creates a temporary local table in the tempdb database with the information of the Person.Address table.
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[SpatialLocation]
,[rowguid]
,[ModifiedDate]
into
FROM [Person].[Address]
If you check the tempdb, you will find the table in the temporary table folder.
The prefix # is used to indicate that it is a local temporary table. You can access to the temporary tables from any database. These tables or objects created are visible only in the session where they were created.

Global temporary tables:
These tables are global and can be accessed from other sessions which was not in possible in local temporary tables.
The syntax is similar than the local temporary tables, but with double # #:
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[SpatialLocation]
,[rowguid]
,[ModifiedDate]
into #
FROM [Person].[Address]

Now Have a look on table type variables:
The table variables can be used instead of the global and local temporary tables. They are easier to handle and to dispose the resources, which makes the use more efficient. The following example will show how to create the table variable, how to insert data on it and how to show the variable values. The use is similar to a simple table. The following example shows how to declare a table variable, how to insert data and how to do a select:

DECLARE TABLE (id INT, col1 varchar(20))
insert into values(1,'First value'),(2,'Second value')
select * from

You can also create it for globally DECLARE @ TABLE (id INT, col1 varchar(20))
As you can see, it is very easy to use them like any other variable. In general, the local temporary tables are more efficient. The table variables are also stored in the tempdb. However, depending on the scenarios you can only use table variables or local temporary tables. For example, if you need indexes, you can only do it with a local temporary table. By the other hand, in a function, you can only use a table variable. Note that the statistics are not maintained in table variables. In routines, table variables require fewer compiles.

A special type of table variable is the Table-value parameter. This is very useful for client applications.
The following example shows how to create and use Table-value parameters:
CREATE TYPE Table_value AS TABLE
(id int
, name varchar(30) );
GO

We will also create a simple table to fill with the Table-value parameter in a stored procedure:
CREATE table product
(id int
, productname varchar(30))

The stored procedure will load the data from the Table-value parameter into the product table created.
CREATE PROCEDURE insertdata
table_value READONLY
AS
SET NOCOUNT ON
INSERT INTO product
([id]
,productname)
SELECT id,name
FROM ;
GO

Now, we are going to declare the Table-value parameter insert data there and call the stored procedure and do a select in the table to make sure that the data was inserted:
DECLARE
AS Table_value;

INSERT INTO (id, name) values
(1,'Camera'),
(2,'iPad');

EXEC insertdata ;
GO

select * from product
If everything is OK, you should be able to see the new data inserted in the products table using the stored procedure.
Nest Software

MS SQL server Recovery Models

FULL recovery model
This recovery model logs every change to every row as well as a copy of each page added to indexes or table. As such the log contains enough information to be able to completely re- construct every action which occurred on the database, allowing you to restore your database back to any specific time, provided that you have a full log chain. All entries are kept in the online transaction log until the log is backed up, after which only active transactions will remain in the online log. This means that in order to get information about completed transactions from the log, the log backups will have to be taken into account.

BULK_LOGGED recovery model
When you are using the BULK_LOGGED recovery option, all minimally logged operations are not written to the Log. Minimally logged operations are operations such as SELECT INTO, BULK INSERT and Index operations. Essentially just enough information is logged to be able to undo the transaction, but not enough to redo it. The log is handled in much the same way as the FULL recovery model, and inactive transactions are moved to the log backup when a log backup is taken. Of course no information about bulk transactions are available.

SIMPLE recovery model
The SIMPLE recovery model only logs enough information to allow you to recover your database. All inactive log entries are automatically truncated when a checkpoint occurs. All operations are still being logged, but as soon as a checkpoint occurs the log is automatically truncated, which means that it becomes available for re-use and older log entries can now be over-written.