Performance bug: NOLOCK scans involving off-row LOB data

Here's an interesting bug that was just recently fixed, and someone actually hit it today, so it's time to blog it. Bottom line is that if you're doing NOLOCK scans of tables involving LOB data, the perfmance might suck. 2005 SP3 CU3 has the bug fix, but unfortunately the KB article was written by someone […]

Blog series on computer memory architectures

(Thanks to David Baffaleuf for pointing this out to me) Ever wanted to know a bit deeper how memory works in your laptop or servers? This comprehensive series of blog posts will give you a great overview. It's very clearly written and goes very deep. At times it talks about how things show up in […]

Why isn’t there automatic in SQL Server?

I was in a discussion earlier today, and it's one I've had lots of times in the past – both inside and outside Microsoft and the SQL team: why doesn't SQL Server do automatic <defrag, index creation, refactoring, de/normalization, backups, CHECKDBs, etc>? Some of these were considered while I was still on the team, and […]

How to find me on Twitter

People are saying they can't find me – that's a Twitter issue with indexing. Here's the direct link: and Kimberly's at

Ok, I caved – I’m on Twitter

After being mercilessly pressurized by many people (and I blame Jason Massie the most), I've joined Twitter and I'll be commenting lots on it. Why did I finally cave? Well, I've been seeing lots of little things in forums and newsgroups that don't merit a full blog post but are worth letting people know about. […]

Weekly survey: what kind of backups do you take?

In this week's survey I'm interested in what kind of backups you take, and also what recovery model you mostly use. If you have multiple database with different strategies, by all means respond multiple times. The more responses the better! I'll report on the survey results some time over the weekend of 5/2/2009. A couple […]

Physical database layout vs. database size

A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn't disappointed, with over 1000 responses! Here are […]

MS announces support for ‘lock pages in memory’ for Standard Edition

Finally! All those customers out there who are frustrated by not being able to turn on the 'lock pages in memory' option to protect the SQL working set on Standard Edition – the wait is nearly over. Bob Ward (Principal Escalation Engineer in CSS, and a good friend of mine) announced at PASS Europe that […]

Misconceptions around TF 1118

[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]

Early morning bird-watching in India

In this trip to India (teaching for MS again) we're in and out, with no time for sight-seeing, but there's always time for bird-watching! I got up early this morning and got some photos of some excellent birds. I've included a couple here, and the rest you can check out in the album on Facebook […]

FILESTREAM garbage collection

In my previous posts on FILESTREAM I discussed the directory structure of the FILESTREAM data containerĀ and how to map the directories to database tables and columns. In this post I’m going to explain how and when the FILESTREAM garbage collection process works as that doesn’t seem to be documented anywhere (even in the FILESTREAM whitepaper […]

Why could restoring a log-shipping log backup be slow?

A short post to start the day (in India) and then at lunchtime I'll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was […]

Inside the Storage Engine: More on the circular nature of the log

  Every so often (well, ok, very frequently) my internal list of ‘I must blog about that’ gets too big and I have an almost uncontrollable urge. Luckily, these urges end up with me blogging several times, otherwise who knows what kind of trouble I’d get into? :-) First up today is some pretty deepĀ internals […]

Weekly survey: still running last week’s survey

Last week I kicked off a survey about the physical layout of your databases, based on database size. I'm running it for two weeks and will report on the results next Friday. One thing I missed was 'multiple filegroups over multiple drives/arrays/LUNs for any reason' in the > 1TB category – not enough options on […]

Are we too obsessed with rankings?

This feels like a bit of a strange post for me to make, but I want to make it anyway, and don't take it the wrong way… I've recently started following SQLBatman's blog and I was astounded that he had to post a follow-up to his list of SQL blogger rankings (disclaimer: including ours – […]

SQL 2005 SP2 maintenance plan bug masking corruption

Not much to do here in Tokyo while waiting for the Bangkok flight except read and blog, and I've already done enough reading for one day on the flight here – so that leaves blogging! Here's an interesting case that I got involved in on SQLServerCentral (here's the original thread, linked here with permission of […]

Updated whitepaper: Troubleshooting Performance Problems in SQL Server 2008

Microsoft's popular performance troubleshooting whitepaper has been updated for SQL Server 2008. You can download Troubleshooting Performance Problems in SQL Server 2008 at I've also added it to our whitepaper-links collection page at

New research paper: Migrating Server Storage to SSDs: Analysis of Tradeoffs

At the last few conferences I've presented at, there have been questions about using SSDs (Solid-State Drives) for enterprise storage and whether that will change some of the database maintenance practices. My answer to that is "I don't know" (ha – bet you thought I was going to say "It depends!") because adoption of SSDs […]

Weekly survey: does size really matter – or is it what you do with it?

This week's survey is a little more complicated. I'm interested in the physical layout of your databases. I've got four surveys, for a variety of database sizes. Please vote multiple times in each survey, as you see fit – and by all means forward this link to your friends/clients/etc or re-blog/tweet it. I'm going to report […]

Importance of proper transaction log size management

Last week's survey was on how *you* manage the size of your transaction log (see here for the survey). Here are the results as of 4/10/2009 – the most popular survey yet: In my opinion (guarantee that I'm going to say stuff that some of you won't agree with!), there are only two valid answers in […]

SQL Server 2008 SP1 has been released

See for the download, release notes, etc

Off-topic: What would Facebook be like in real-life?

This is a hilarious video of what Facebook would be in real-life – well worth a couple of minutes for a lunch-time laugh. Enjoy!

Weekly survey: transaction log size management

In this week's survey, I'm interested in how *you* manage the size of your transaction log. I'll report on the results around 4/10/2009. Thanks! (No comments please… wait till the results post next week) 

Importance of choosing the right LOB storage technique

Last week’s survey was on how you should store large-value character data in SQL 2005+ (see here for the survey). Here are the result as of 4/3/2009 – and I think my favorite answer is starting to catch-on: My favorite answer is, of course, it depends! For all those who didn’t answer ‘it depends’, your […]

TechNet Magazine: feature article on Common SQL Server Security Issues and Solutions

The May 2009 TechNet Magazine is now available online, and it's the annual security issue. In there is an article I wrote highlighting 10 common security issues (and solutions) you should worry about if you're not a security-savvy DBA. It covers: Physical security Network security Attach surface minimzation Service accounts Restricting use of administrator privileges […]

Kimberly and Paul awarded MVP status again

Not an April Fool (although SQL Server Central has a great one) – Kimberly and I were both re-awarded MVP status for another year – cool! Don't expect any blog posts this week from either of us – we're on vacation with the kids down on St. Pete beach in Florida. Back to work next […]