Author Archives: Clay Lenhart

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.

Advantages of Immutable Data

Posted on February 16, 2008 by Clay Lenhart

I was reading about two of Google’s internal programs, Bigtable and Google File System, and how they handle a large amounts of data so that the processing is distributed (Bigtable) and the data is replicated (Google File System). One thing … Continue reading →

Posted in Architecture | Tagged | 20 Comments

SQL Server Hash Indexes

Posted on February 3, 2008 by Clay Lenhart

There are two problems with indexes on large nvarchar columns: You will likely hit the 900 byte limit in your index Indexing large data isn’t efficient anyway. A neat feature of SQL Server is the CHECKSUM() function which hashes your … Continue reading →

Posted in SQL Server Development | 7 Comments

“Including” Columns in an Index

Posted on February 2, 2008 by Clay Lenhart

A neat feature in SQL Server 2005 is the ability to “include” columns in an index. These included columns are not in the main part of the index, but are additional information in the index. For example, lets say you … Continue reading →

Posted in SQL Server Development | 7 Comments

Finding Used Databases

Posted on January 31, 2008 by Clay Lenhart

It can be difficult to determine which databases you are administering are actually being used. Below is a query that determines the last time an index was used since the last time SQL Server started to help narrow down these … Continue reading →

Posted in SQL Server Administration | 7 Comments

SQL Server RAM Usage

Posted on January 30, 2008 by Clay Lenhart

SQL Server has difficultly managing memory for systems with a large amount of RAM (say 8+ GB), typically found in 64-bit installations. You might see log messages such as: A significant part of sql server process memory has been paged … Continue reading →

Posted in SQL Server Administration | 5 Comments

Defragging Indexes in SQL Server 2005

Posted on January 29, 2008 by Clay Lenhart

There are two ways to optimize your indexes in SQL Server 2005: ALTER INDEX … REORGANIZE ALTER INDEX … REBUILD Which one should you choose? According to BOL, it depends on how fragmented they are. The avg_fragmentation_in_percent column from the … Continue reading →

Posted in SQL Server Administration | 9 Comments

Listing checked-out files in Team System (TFS)

Posted on January 28, 2008 by Clay Lenhart

You can get a complete list of checked out files by using the tf.exe command line tool found in: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE All checked out files in $/MyProject: tf.exe status $/MyProject /user:* /s:http://MyTfsServer:8080 /recursive All files checked out … Continue reading →

Posted in Source Control | 25 Comments

Backing up to a NTFS compressed folder

Posted on January 28, 2008 by Clay Lenhart

It seems like a good idea to backup SQL Server 2005 databases to a compressed folder, however if your database is larger than roughly 30 GB, you will get the following error: BackupMedium::ReportIoError: write failure on backup device ‘G:\Backups\200501220402.BAK’. Operating … Continue reading →

Posted in SQL Server Administration | 32 Comments

The sp_getapplock secret

Posted on January 28, 2008 by Clay Lenhart

sp_getapplock is not very well advertised in SQL Server 2005, however it is a good way to synchronize code in a stored procedure. Before finding out about sp_getapplock, I would SELECT from a table with an exclusive lock, like so: … Continue reading →

Posted in SQL Server Development | 22 Comments

SQL Server Security with EXECUTE AS OWNER

Posted on January 24, 2008 by Clay Lenhart

EXECUTE AS OWNER is a great way to limit the permissions of a SQL Server Login. The general idea is to create your stored procedure with the EXECUTE AS OWNER modifier. Any user who has the permissions to execute the … Continue reading →

Posted in SQL Server Development | 56 Comments