Whitepaper on why database maintenance is important

Today I’m publishing a white paper which I wrote for our Customer Service department to give to our customers. It explains why database maintenance is so important in keeping your database happy and healthy. Continued »

Speaking at the South Bay .NET Users Group

On September 10th, 2009 I’ll be speaking at the South Bay .NET Users Group in Torrance, CA.

I’ll be giving two presentations at the meeting.  First I’ll be giving my “SQL Server Indexing for the Client Developer”, and second I’ll be giving my “What do all Those Undocumented Stored Procedures do?” session.

The meeting starts at 7, but I’m sure people start arriving about 6:30 or so.  This is my first time speaking at this user group, but I’m sure it’ll be a blast.

See you there.

Denny

How I got started in IT

I was tagged by Brent Ozar in his post Starting the SQL Journey where he talked about how he got started as a SQL DBA.  So here’s my story, in all its glory. Continued »

SQL Server 2008 Clustering Article in SQL Server Magazine

For those of you who haven’t check out this months SQL Server Magazine, the cover article was written by yours truly about Clustering SQL Server 2008 on Windows Server 2008.  This is an updated version of the article I did last December on Clustering SQL Server 2005 on Windows Server 2003.

I hope that you find it handy.  (Subscription is required.)

Denny

Difference between an Index and a Primary Key

There seams to be some confusion between what a Primary Key is, and what an Index is and how they are used.

The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null. Because they are unique and not null, these values (or value if your primary key is a single column) can then be used to identify a single row in the table every time. In most if not all database platforms the Primary Key will have an index created on it.

An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are part of the index. When you create an index within the database, you are creating a physical object which is being saved to disk. Using a table which holds employees as an example:

CREATE TABLE dbo.Employee
 (EmployeeId INT PRIMARY KEY,
 LastName VARCHAR(50),
 FirstName VARCHAR(50),
 DepartmentId INT,
 StartDate DATETIME,
 TermDate DATETIME,
 TermReason INT)

The EmployeeId is the Primary Key for our table as that is what we will use to uniquely identify an employee. If we were to search the table based on the last name the database would need to read the entire table from the disk into memory so that we can find the few employees that have the correct last name. Now if we create an index on the LastName column when we run the same query, the database only needs to load the index from the disk into memory, which will be much quicker, and instead of scanning through the entire table looking for matches, because the values in the index are already sorted the database engine can go to the correct location within the index and find the matching records very quickly.

Hopefully this will help sort out some of the confusion.

Denny

Clearing the Windows page file and its effect on server performance

I just published a new article over on SearchSQLServer.com about “Clearing the Windows page file and its effect on server performance“.  In the article I talk about the pros and cons of telling Windows to clear out the page file when the server is rebooted.

Denny

Damn rollback scripts are a pain

Writing release scripts for a database is easy.  What ever new objects you want you create you create, and what ever objects you need to change you change.  Piece of cake.  Rollback scripts however are a bit trickier since you need to know what everything should look like after the script is done. Continued »

Adding column to take taking to long? You’re probably assigning a default.

When you create a column on a database, and you put that column on the right of the table the column shouldn’t be added almost instantly.  However if you are assigning a default value to the column and setting the column to now allow NULL values the operation will take quite a lot longer.  This is because when the operation happens the SQL Server has to write the values to each table.

If you allow the column to have NULL values then the database doesn’t have to write the values to the column so the operation completes very quickly.

Denny

Learn to use the COPY_ONLY flag for one off backups

Taking a full backup when doing major database upgrades is a great idea.  However if you are taking differential backups this one off full backup will break the differential backup chain. Continued »

Auto Close is almost as bad as auto grow

In theory the auto close setting is a great idea. Free up resources for other databases to use when all the users are finished with the database.

In practice, its not so great.  If a single user connects and disconnects over and over (like say a single user using your website or even a few users using your website) every time a user connects the database has to be spun up and the data loaded into memory.  When the user disconnects the data is removed from cache, and the connection to the file is closed.  Then when the next user runs a query the file is opened again, and the data is loaded into cache.

This causes the users queries to run slower as the data must be pulled from disk each time instead of pulled from memory.  Plus there’s the time spent spinning up the IO thread, and opening the file.  And as I said last time, more time is bad.  Plane and simple.

Denny