Some notes on database snapshots

Overview I recently conducted some tests to double check the exact behavior of database snapshots when: Snapshots are created on volumes which are much smaller than the actual database size Metadata-only operations such as DROP TABLE are executed DDL operations execute on the main database and the snapshot runs out of disk space Here is…


Howto: Have a Clustered instance of SQL Server listen on multiple virtual IP addresses

Recently a colleague asked me if there were any changes specific to Windows Server 2008 which allow SQL Server (clustered instance) to listen on multiple subnets. There was only one change in Windows 2008 to my knowledge (which I have detailed in this post later on) but that had nothing specific to do with SQL…


Concurrency of Full, Differential and Log backups on the same database

Overview Many a time, we are asked by customers about the effect of overlapping backup schedules: for example, whether they can backup the transaction log at the same time that a long-running complete backup of the database is taking place.  Books Online does talk about the concurrency, but does not cover all versions or combinations….


SQL Server setup log locations and more

In order to efficiently troubleshoot problems involving failed setup, we need to use the setup logs. Once you know where the log files are located, you can start troubleshooting. What to look for in the log files is a separate topic in itself, but at least if you know where the log files are, then…


Windows, SQL Server and Multi-Core / Hyper-Threading

Very often we get asked a question about ‘how many processors does my SQL Server 200x Edition really support’. This post hopes to summarize the considerations and actual limits. SQL Licensing is per socket First, let’s take a look at the Books Online topic ‘Maximum Number of Processors Supported by the Editions of SQL Server’….


T-SQL Anti-pattern of the day: ‘all-in-one’ queries

Scenario A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific (‘get me details for for OrderID = NNNN’) and also the occasional reports which ask for all the orders (‘get me all the orders, no questions asked’.) Here is a sample from AdventureWorks which illustrates…


T-SQL Anti-pattern of the day: comparing DATETIME field with date-only literal

Scenario It is a pretty common situation to have transaction date-time stored in a DATETIME field. The problems start with the fact most applications used GETDATE() or some such equivalent at the client side to record the order date-time stamp. So a typical entry for an OrderDate would actually end up with a time component…


T-SQL Anti-pattern of the day: UPPER() and LOWER()

This one is even more common and therefore has a high impact: the usage of the above functions in WHERE clause predicates. I’ve examined each in some detail below. Here’s a typical example I see, based off the AdventureWorks database. Scenario To illustrate the effect of this example, I have added an extra index on…


T-SQL Anti-pattern of the day: Prefix codes stored in (n)varchar fields

In the course of my work as a SQL Server Premier Field Engineer, we tend to see a lot of patterns in code. Some of these patterns do not lend themselves to optimal performance. This post is the first in a series of such ‘how not to code’ tips which would hopefully help some T-SQL…


SQL Worker Thread Stack Sizes

Recently during some internal discussions we had a lot of talk around the stack sizes for various SQL builds. Here is the summary of that talk:   SQL Server Build OS Build Stack Size SQL Server x86 (32-bit) x86 (32-bit) 512 KB SQL Server x86 (32-bit) x64 (64-bit) 768 KB (see notes 1 and 2)…