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 [...]
Paul S. Randal
In Recovery...
Performance bug: NOLOCK scans involving off-row LOB data
Posted on: April 30, 2009 5:34 pm
Blog series on computer memory architectures
Posted on: April 30, 2009 4:44 pm
(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?
Posted on: April 29, 2009 2:26 pm
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
Posted on: April 29, 2009 10:47 am
People are saying they can't find me – that's a Twitter issue with indexing. Here's the direct link: http://twitter.com/PaulRandal and Kimberly's at http://twitter.com/KimberlyLTripp
Ok, I caved – I’m on Twitter
Posted on: April 29, 2009 9:59 am
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?
Posted on: April 27, 2009 2:44 am
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
Posted on: April 27, 2009 12:21 am
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
Posted on: April 26, 2009 8:33 pm
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
Posted on: April 23, 2009 9:19 pm
There's been a recent flurry of confusion and misconceptions about trace flag 1118 in SQL Server 2008. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It's used to help alleviate allocation bitmap contention in tempdb under a heavy load [...]
Early morning bird-watching in India
Posted on: April 23, 2009 7:32 am
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
Posted on: April 21, 2009 9:29 pm
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?
Posted on: April 21, 2009 5:48 pm
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
Posted on: April 21, 2009 12:42 am
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
Posted on: April 17, 2009 3:35 pm
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?
Posted on: April 16, 2009 8:28 pm
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
Posted on: April 15, 2009 11:54 pm
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
Posted on: April 15, 2009 11:49 pm
Microsoft's popular performance troubleshooting whitepaper has been updated for SQL Server 2008. You can download Troubleshooting Performance Problems in SQL Server 2008 at http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx. I've also added it to our whitepaper-links collection page at http://www.sqlskills.com/whitepapers.asp.
New research paper: Migrating Server Storage to SSDs: Analysis of Tradeoffs
Posted on: April 13, 2009 5:14 pm
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?
Posted on: April 10, 2009 3:05 pm
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
Posted on: April 10, 2009 12:21 pm
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
Posted on: April 7, 2009 7:17 pm
See http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19 for the download, release notes, etc
Off-topic: What would Facebook be like in real-life?
Posted on: April 7, 2009 12:54 pm
This is a hilarious video of what Facebook would be in real-life – well worth a couple of minutes for a lunch-time laugh. http://www.youtube.com/watch?v=LrFdOz1Mj8Q Enjoy!
Weekly survey: transaction log size management
Posted on: April 3, 2009 9:59 am
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
Posted on: April 3, 2009 8:46 am
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
Posted on: April 3, 2009 7:35 am
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
Posted on: April 1, 2009 7:21 am
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 [...]
