DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server TempDb – what’s it for?

Posted on December 13, 2011 Written by Andy Hayes 3 Comments

SQL Server TempDB is a system database, automatically created when you install SQL Server.

sql server tempdb

So what is it used for? Well a few things actually but first I have to tell you that Microsoft didn’t name tempdb because they couldn’t think of a suitable name. It is a temporary database which is re-created every time the SQL Server service is started and at a higher level, it could be considered to be the page file for sql server.

Lets look at when SQL Server TempDB gets used

Global and local temporary tables are created in here. So for example if you write a create table statement starting like this global temporary table:

CREATE TABLE ##Table....

or this local temporary table…

CREATE TABLE #Table....

When  you execute the create table script, the temporary table will be created in tempdb.

Other objects created in tempdb would be temporary tables, temporary stored procedures, table variables, cursors and internal objects created by the database engine. These would typically be work tables created to store intermediate result sets for spools or sorting. So you might want to create an index using tempdb and use SORT_IN_TEMPDB in your CREATE INDEX statement to store those result sets. You would typically do this because you want to take the load off the main database files whilst the index is being created and hope to speed up the creation of the index in the process. There are disk space considerations to bear in mind with that though and I’ll discuss that in another post.

Finally, its other function is to maintain multiple versions of rows.

The following features make use of tempdb for row versioning

  • Online index operations
  • MARS – (Multiple Active Result Sets)
  • Snapshot Isolation and Read-Committed Snapshot Isolation
  • Triggers

SQL Server TempDB – what can’t you do?

  • You can’t drop it
  • You can’t back it up
  • You can’t change its recovery model from simple
  • You can’t persist user created objects in the database without hassle
  • You can’t create multiple filegroups, only the PRIMARY filegroup is available

So you now know that it is quite a busy little (sometimes large, very large in fact) database on your SQL Server and you’re probably thinking that if tempdb is so busy, what are the performance implications on your SQL Server instance of a busy tempdb? Well I’m not going to talk about that here but instead I will talk about it here in my post about tempdb best practices

Filed Under: All Articles, SQL Server Administration Tagged With: tempdb

The importance of the foreign key constraint

Posted on December 4, 2011 Written by Andy Hayes 8 Comments

The foreign key constraint is an important aspect of database design. This article explains why.

Foreign key constraint advantages

The purpose of the foreign key constraint is to enforce referential integrity but there are also performance benefits to be had by including them in your database design.

Firstly lets look at an example of how they are used in database design.

So here are my two tables.

CREATE TABLE Accounts
(
ID INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(100)
)
GO

CREATE TABLE Orders
(
ID INT PRIMARY KEY IDENTITY(1,1),
OrderDate DATETIME DEFAULT(GETDATE()),
AccountID INT NOT NULL CONSTRAINT FKAccountID REFERENCES Accounts(ID)
)
GO

Now we’ll insert some data.

INSERT INTO Accounts(Name)
VALUES('Test Company 1'),('Test Company 2');

INSERT INTO Orders(AccountID)
VALUES
(1),(2),(1),(2),(1),(2),(1),(2),(1),(2),(1),(2)
,(1),(2),(1),(2),(1),(2),(1),(2),(1),(2),(1),(2);

Let’s have a quick look at the data.

SELECT * FROM Accounts;

SELECT TOP (5) * FROM Orders;
ID          Name
----------- ---------------
1           Test Company 1
2           Test Company 2

(2 row(s) affected)
ID          OrderDate               AccountID
----------- ----------------------- -----------
1           2011-12-04 11:03:08.533 1
2           2011-12-04 11:03:08.533 2
3           2011-12-04 11:03:08.533 1
4           2011-12-04 11:03:08.533 2
5           2011-12-04 11:03:08.533 1

(5 row(s) affected)

So we have inserted rows into table “Orders” which relate to “Accounts” by the AccountID and ID columns respectively. No problems with that. What happens if we try and insert a new row into “Orders” for an account which does not exist in table “Accounts”?

INSERT INTO Orders(AccountID)
VALUES(3);

We get an error.

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint “FK__Orders__AccountI__0AD2A005”. The conflict occurred in database “DBADiaries”, table “dbo.Accounts”, column ‘ID’.
The statement has been terminated.

So the foreign key constraint is doing its job and only allowing recognized account ids to be added to the “Orders” table.

Now lets say for whatever reason someone attempted to remove a row from table “Accounts” which had related records in table “Orders”

DELETE Accounts WHERE ID = 2;

We get an error.

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint “FK__Orders__AccountI__0AD2A005”.
The conflict occurred in database “DBADiaries”, table “dbo.Orders”, column ‘AccountID’.
The statement has been terminated.

Cascading deletes are turned off in this instance so as well as stopping bad data getting into the table, the foreign key constraint is preventing data from being deleted which in this case is exactly what I need it to do.

Foreign key constraint performance benefits

How can a foreign key constraint benefit performance? Well let’s have a look at this simple example using the tables previously created.

Activate “Include Actual Execution Plan” in Management Studio using either Ctrl + M or the button on the toolbar. Run a simple query checking for records in table “Orders” which relate to a row in table “Accounts” and then check the execution plan

SELECT *
FROM Orders O
WHERE EXISTS (SELECT * FROM Accounts A WHERE A.ID = O.AccountID);

Execution plan output:

foreign key constraint

Now we will remove the foreign key constraint

ALTER TABLE Orders DROP CONSTRAINT FKAccountID;

Re run the preceeding SQL statement and check the execution plan again and it has changed.

foreign key constraint

So why is it different? The optimizer has to now execute the EXISTS part of the query because it cannot be sure whether table “Accounts” has any valid references. Having the foreign key in there meant that the optimizer could trust it and therefore by definition it did not have to check table “Accounts” when returning all rows from “Orders”. This is because a valid reference in “Accounts” must exist for a row to be stored in “Orders”

Could a foreign key constraint become untrusted?

The answer is yes.

For example you might decide to disable a foreign key when loading in large amounts of data. It is easier to batch insert consistent rows of data into a database without foreign keys enabled.

An untrusted foreign key would mean that the second execution plan would be used for the query which will not perform as fast as the first. If you had tables with lots of rows in, this could make a massive difference to performance.

For the purposes of this explanation, I have added the FKAccountID foreign key constraint and I ran this statement:

ALTER TABLE Orders NOCHECK CONSTRAINT FKAccountID;

So how do you tell whether your foreign key is trusted? Run this query:

SELECT Name, Is_Not_Trusted
FROM sys.foreign_keys
WHERE Name = 'FKAccountID'

Which outputs this information.

Name                 Is_Not_Trusted
-------------------- --------------
FKAccountID          1

(1 row(s) affected)

To correct this run this SQL:

ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FKAccountID;

You could also look for all untrusted foreign keys in your database as part of a performance tuning exercise.

So a foreign key constraint has advantages and should be part of your design to ensure that you have a consistent database and to help ensure that the database performs optimally.

Filed Under: All Articles, MySQL Administration, SQL Server Administration Tagged With: performance, sql server

TempDB best practices

Posted on November 30, 2011 Written by Andy Hayes 9 Comments

Continuing on from my post describing what SQL Server TempDB is used for, I wanted to write about tempdb best practices and how on a busy system, modifications to the default setup of tempdb and its placement can boost performance.

How to optimize tempdb

  • Place tempdb on a separate disk to user databases. See my post on how to move tempdb.
  • Place tempdb on the fastest IO subsystem possible.
  • Size tempdb accordingly and configure autogrow. This is especially important if your system cannot tolerate performance degradation. If you size the database too small with autogrow enabled, tempdb will automatically grow according to the size increments you have set up. Imagine you have set it to grow in increments of 10Mb and you have a busy workload requiring tempdb to be used. If tempdb needs to expand a number of times to accommodate load, these sizing operations can generate lots of IO. Better to try and set your tempdb to be based on a staging server workload where you have witnessed how large it can grow.
  • Create multiple data files for tempdb, but how many? Allocate 1 data file per physical or virtual CPU core.
  • Keep tempdb data files equally sized and have autogrow increments configured equally across data files.
  • If your SAN administrator can provide it, split the multiple data files over different LUNs as opposed to holding everything on one LUN.
  • Configure the data files to be of equal size.

Please note that there will be many systems out there which have had no modifications done at all to tempdb and they run fine.

Whether you choose to apply all or some of these methods will largely depend on your budget and your knowledge of the system based on projected or analysed workload. I would recommend that for any system, that you try and size tempdb accordingly however.

Filed Under: All Articles, SQL Server Administration Tagged With: performance, tempdb

How to move tempdb

Posted on November 29, 2011 Written by Andy Hayes 2 Comments

How to move tempdb ? So in this post, I show you what is involved to do this and what you shouldn’t try and do.

This is how to move tempdb

You will need to run run some T-SQL and restart the sql server service to complete the operation.

The logical file name values will need to be obtained and there are a couple of ways to do that.

You can either view the logical file names by accessing the database properties and clicking on “Files” or you can run a script. See screen shots below.

how to move tempdb

You can also run this T-SQL:

SELECT name, physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

On my machine, the results were shown in Management Studio as below and here I am interested in the values in the “name” column. You will need these for the next script.

how to move tempdb

Once you have the logical file names for tempdb, you can run the following script after you have edited it with suitable values for your system. In this example, I am going to set the new path to be another folder on the C: drive of my test server but it is likely that you would be moving tempdb to another drive if this were a real scenario. Whatever path you choose, ensure that the account which the sql server service is running under has permission to read and write to it.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf');
GO

Note that the destination path must exist otherwise the script will fail to run.

If the script completes successfully, you will see the following message:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now restart your sql server service.

Move tempdb – don’ts

Don’t try and move tempdb using a backup and restore method as you will receive an error

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Don’t try and detach tempdb using sp_detach_db @dbname=’tempdb’ as you will again see an error

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

I hope you have found this information useful on how to move tempdb and if you would like to watch a video on this topic, you can do so right here.

Filed Under: All Articles, SQL Server Administration Tagged With: tempdb, video

  • « Previous Page
  • 1
  • …
  • 4
  • 5
  • 6

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close