Back To Basics

Back To Basics: Reading an Execution Plan

All to often when helping people look at query performance problems I’ll ask them to look at the execution plan, and see what it says.  Most query performance problems can be resolved simply by looking at the execution plan and seeing where you need to add an index.

All to often I (and others) then get asked, how to I look at the execution plan, and what does it mean?

Continued »

Back To Basics: Clustered vs NonClustered Indexes; what’s the difference?

SQL Server has two basics kinds of indexes. They are clustered and nonclustered indexes. There are some fundamental differences to the two which are key to understanding before you can master index tuning.

Continued »

Back To Basics: What are indexes and what are they used for?

A while back someone posted on the ITKE forum asking what Indexes where, and what they were used for. I put up a quick answer, but I felt that it deserved a more in depth blog post; so here it is.

Continued »

Back To Basics: Getting Data from an XML Document

One of the most popular ways to get data multiple pieces of data in a single parameter from one stored procedure to another, or from a client application to the database is to use XML. This can be done in SQL Server 2000 by using the NTEXT (or TEXT) datatype, and in SQL 2005 using the XML datatype. (In SQL Server 2008 you can use Table variable input parameters.)

Continued »

Back To Basics: Creating Indexes

After you have created your tables, and stored procedures you will have a basic idea of what queries are going to be running against your database.  If you haven’t already done so, its now time to start adding indexes to your database.  The catch with index is that there is such a thing as to much or a good thing.  As you add more and more indexes to your database your INSERT, UPDATE and DELETE commands will being to slow down, as each time you insert a record the insert is committed to the table, as well as to each index on the table. Continued »

Back To Basics: What’s the difference between a Scan and a Seek?

There are a few basic operations which SQL will perform when looking for the data that you need.  Here they are listed in the order of worst to best.

• Table Scan
• Clustered Index Scan
• Index Scan
• Clustered Index Seek
• Index Seek

The basic rule to follow is Scans are bad, Seeks are good.

Continued »

Slide Decks and Sample Code for SoCal Code Camp at USC

Here are the slide decks and sample code from my sessions at this weekends SoCal code camp.

Back To Basics; Getting Back To The Basics of SQL Server
Scaling that database bigger than ever
Storage for the DBA

While the first two are both two part sessions, there is only one download for both halves as they run together.

Denny

SoCal Code Camp Sessions are posted

I and many of the other presenters have begun entering our sessions for the next SoCal Code Camp on on October 25th and 26th at USC in Los Angeles, CA.

The Code Camp is the weekend before PDC, and right near where PDC will be held.  So if you are coming to PDC this year, come a couple of days early and check out what is hoped to be the largest SoCal Code Camp ever.

I’ve got four sessions scheduled, all of which are tagged with “SQL Server“.

• Back To Basics: Getting Back To The Basics of SQL Server (Part 1)
• Back To Basics: Getting Back To The Basics of SQL Server (Part 2)
• Scaling that database bigger than ever (Part 1)
• Scaling that database bigger than ever (Part 2)
• Storage For the DBA

If you are planning on attending the Code Camp, be sure to mark the sessions you want to attend with the interest check box.  That way the Code Camp staff knows how big of a room each session will need.

If you are interested in speaking at the Code Camp, feel free to enter more sessions.  The more sessions we have, the more popular the event will be.

As the date gets closer additional sessions will be entered onto the site.  Be sure to check back often.

I’ll do my best to get the slide decks and sample code posted before the Code Camp starts.  Odds are I’ll get them posted on Saturday morning as I did last time.  If you missed any of my sessions from prior Code Camps, let me know in the comments and I’ll see what I can do about adding those sessions to the new schedule.  See you at the Code Camp.

Denny

Back To Basics: The RESTORE DATABASE Command

The restore database command is what is used to recover a database which has been backed up using the BACKUP DATABASE command.  The syntax of the RESTORE DATABASE command is very similar to the BACKUP DATABASE command in many respects.

 You start with where you are restoring the database from.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'

Then if you need to move the physical files to another location because your disks are laid out differently, or because your folder layout is different you can add a MOVE command for each file you want to move.  For each MOVE command you specify the local file name, and the new physical file name.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

When restoring the database, many people think that you have to create the database first.  You do not.  When restoring a database through the UI (Enterprise Manager, or SQL Server Management Studio) if you create the database first, it will then be selectable in the drop down menu.  Even with using the UI, creating the database first is optional as you can simply type in the name of the new database in the UI.

If you are restoring from a striped database backup then you will need to specify the name of all the members of the strip.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase1.bak',
  DISK=’E:\Backups\MyDatabase2.bak’
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

If you need to restore a differential or log backups after you restore your full backup you will want to place you backup with the NORECOVERY flag. This will tell SQL Server not to complete the recovery process, and to leave the database in an unusable state. This will allow you to continue the restore process. Once the database has been switched into a writable state you won’t be able to restore any transaction logs to the database without restoring from the full backup again.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’,
  NO RECOVERY

Denny

Back to Basics: The BACKUP DATABASE command

Now that you have objects created within your database, and data within the tables you need to secure your database in case of a server failure or accidental data deletion.  This would be done by taking backups of the database.  Backups are taken by using the BACKUP DATABASE command.

The frequency that full backups are taken at should be determined by how much data changes within the databases.  On busier systems full backups should be taken daily.  On systems which are less busy the full backup can be taken less often, every few days, once per week, etc.

The syntax of the BACKUP DATABASE command is fairly basic.  You specify the name of the database to backup, and the destination that you will be backing up to.  If your database name is MyDatabase and your going to backup to a file named MyDatabase.bak on the E drive your BACKUP DATABASE command would look something like this.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak'

This command will backup all tables, views, procedures, functions, etc as well as all the data within the database.  Backing up the database in an online operation meaning that users can be connected to the database, continuing to make changes to the database while the backup database command is running.  The database is backed up in the same state that it was in when the command started.  All changes made during the backup are logged into the database and staged in memory.  Upon completion of the BACKUP DATABASE command these changes are destaged from memory to the physical file during a checkpoint operation.

If you have a full text index, and are using SQL Server 2000 the full text backup is not included as part of the backup which is created by the BACKUP DATABASE command.  After restoring the database you would need to recreate the full text indexes.  This issue is resolved in SQL Server 2005 when the full text indexes are added to the database backup.

If you wish to create two backups of the database so that your backups can survive a failure of the disk you backup the database to you can use the MIRROR TO clause of the BACKUP DATABASE command.  This clause creates two exact duplicate backups of the database.  This option is included in SQL Server 2005 and higher.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak' MIRROR TO DISK='F:\MyDatabase.bak'

If you find that your backups are taking to long do to a large database size and/or slow disk speed on the disk you are backing up to you can strip the database backup across database backups.  This is done by simply specifying additional destinations.

BACKUP DATABASE MyDatabase TO DISK='E:\Mydatabase.bak', DISK='F:\MyDatabase.bak'

Now striping your database can improve your backup performance, but it puts your backups at greater risk for a disk failure as each part of the backup disk is spread across two drives.  If either backup file is lost or damaged the entire backup is useless.  Because of this you can combine the striping for speed and the mirroring for safety.  To do this the number of backup devices specified in the MIRROR clause must be the same as the number of backup devices specified in the initial destination clause.

BACKUP DATABAES MyDatabase TO DISK='E:\MyDatabase.bak', DISK='F:\MyDatabase.bak' MIRROR TO DISK='G:\MyDatabase.bak', DISK='H:\MyDatabase.bak'

You can read up more about detailed backup techniques in the eZine article I wrote a few months back.

Check back in the coming weeks for information about log backups and restoring your database and log backups.

Denny