Wednesday, July 25, 2012

Synchronous Mirroring

There's plenty written about database mirroring already, a cool new feature of SQL which enables you to create a hot standby (read: automatic failover) for a database on your server (read: NOT the whole server)...

One tip: If you are mirroring across any significant distance, and require synchronous mirroring, take lag time into account, as you will nto commit for the 30-90 ms round-trip time it takes to push the change out to the target server.

Free Database Tuneup

We're offering a free, one-hour tuneup of your target database server. In order to take advantage, go to:
to download monitoring software, then email me ( for a trial key, and we'll set it up.
No cost, no obligation, but we do find that most of the folks who take advantage fall in love with the tool or the service...

Jeff Garbus

Data type mismatches causing SQL Server performance issues

I've been talking about this for years, but every once in a while I see a nose rubbed in it, and the results can be dramatic.

The SQL Server optimizer, in some versions (this is SQL 2005 SP3) will not properly resolve statistics if the data types of the declared variables & the compared columns don't match.

The solution: Match up your data types.

In the code sample below, I was looking at a query that was accessing SQL Server from Ruby--on-Rails. The ONLY difference in the code is that in the first example, the data type is being passed in as nvarchar(4000), and in the second exanmple it's varchar(255) the actual data type.

You can see that the relative plan costs are 99% to 1% (actual costs are more like a factor of 10,000).

Code (reproduced with the kind permission of my client):

declare @p0 nvarchar(4000)

SELECT this_.clinicalContactID as clinical1_138_0_,
this_.clinicalContactTypeID as clinical2_138_0_,
this_.socialSecurityNumber as socialSe3_138_0_,
this_.contactNumber as contactN4_138_0_,
this_.firstName as firstName138_0_,
this_.lastName as lastName138_0_,
this_.middleName as middleName138_0_,
this_.dateOfBirth as dateOfBi8_138_0_,
this_.age as age138_0_,
this_.gender as gender138_0_,
this_.raceTypeID as raceTypeID138_0_,
this_.maritalStatusTypeID as marital12_138_0_,
this_.createdOn as createdOn138_0_,
this_.createdBy as createdBy138_0_,
this_.lastUpdated as lastUpd15_138_0_,
this_.lastUpdatedBy as lastUpd16_138_0_
FROM ClinicalContacts this_
WHERE this_.socialSecurityNumber = @p0