Quick blog post to celebrate a bit of a milestone and round out the month of May – no blogging tomorrow. Earlier today I was making a backup of all the content on my blog because I want to make sure I have a secure copy of it if multiple failures happen with site hoster [...]
Paul S. Randal
In Recovery...
Blog post 350 on this blog – vive la communite!
Posted on: May 30, 2009 6:50 pm
Weekly survey: what’s the most important thing when performance tuning?
Posted on: May 30, 2009 6:38 pm
This week's survey is going to provide some fun debating topics. I'd like to know what the number 1 thing is that YOU go after when performance tuning – what do you think is most important? (Assuming you're not just randomly walking up to a server to do some perf tuning – there's an actual [...]
Importance of defining and measuring SLAs
Posted on: May 30, 2009 4:00 pm
In last week's survey I asked you two things, as a precursor to a whitepaper I'm writing for Microsoft The first question was what is your maximum allowable downtime SLA (either for 24×7 operation or not). See here for the survey. Here are the results as of 5/30/09. The Other values were all about not having [...]
Investigating locking and deadlocking with %%lockres%%
Posted on: May 30, 2009 3:41 pm
I've just read a very good, very deep, and very interesting blog post by James Rowland-Jones. In the post, James investigates some locking issues using a variety of means and explains about the undocumented %%lockres%% function with you can use to figure out what the wait resource will be for individual table rows (basically the [...]
We all do stupid things when we’re young
Posted on: May 29, 2009 2:41 pm
I've just been posting a few links on Facebook and began reminiscing about my days at Edinburgh University. I spent a lot of my 3rd and 4th years there running the Tardis unix cluster, a fantastic experience which fed directly into my addiction for putting things together, taking things apart, and generally dinking about with [...]
SQL Server 2008 Data Compression whitepaper published
Posted on: May 29, 2009 12:59 pm
The SQLCAT team have published another excellent whitepaper – this time the long-awaited one on the SQL Server 2008 data compression feature. Thirteen people inside and outside Microsoft (including me) provided technical reviews and the authors (Sanjay Mishra along with Marcel van der Holst, Peter Carlin, and Sunil Agarwal) did a great job. I remember [...]
Misconceptions around the log and log backups: how to convince yourself
Posted on: May 29, 2009 11:09 am
There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior [...]
Quick joke
Posted on: May 29, 2009 8:32 am
Saw this on Twitter (anonymous original author): A SQL query walks into a bar. It goes up to two tables and says “Mind if I JOIN you?” :-)
Paul and Kimberly will be at PASS in November
Posted on: May 28, 2009 2:55 pm
Cool news – the pre/post-con workshops and spotlight sessions acceptance emails came out this week and we're both going to be there. We've booked our hotel all week even though we live 15 miles away in Redmond so we can be party animals too :-) I could write a long post about why PASS is [...]
Weekly survey: maximum allowable downtime – target and actual
Posted on: May 25, 2009 1:58 pm
This week's survey is inspired by this morning's Kimberly+Paul hot-tub conversation around data-dependent routing vs. network load balancing, which then turned to SLAs. Yes, we lead an exciting life . This survey is a *four*-parter. Part 1 is for each of your databases, which survey option is closest to your target maximum allowable downtime SLA (Service-Level [...]
Importance of testing your disaster recovery plan
Posted on: May 25, 2009 1:00 pm
In last week’s survey I asked whether you’re ever tested your disaster recovery plan, and if so, what happened? (See here for the survey). Here are the results as of 5/25/09: The ‘other’ responses are: 2 x “restored to test env regularly. don’t know if sla would be met.” 2 x “test it regularly, most [...]
New script: how much data will the next log backup include?
Posted on: May 24, 2009 2:32 pm
About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. [...]
Garden flower close-up photos
Posted on: May 23, 2009 5:47 pm
I was reading through some gardening books this morning trying to identify a plant we had last year that died over the winter (I was successful – it was a Tibouchina Urvilleana (Princess Flower)) and thought I'd break out the 50mm and 100mm macro lenses and shoot some close-ups myself. Here's an example: I've uploaded [...]
An example of a nasty cluster key
Posted on: May 20, 2009 12:45 pm
I'm teaching a class this week on database maintenance, for DBAs inside Microsoft. One of the things we're discussing today is index fragmentation and how poor cluster key choice can lead to page splits, poor performance, index fragmentation, and so on – not just in the clustered index, but also in nonclustered indexes. One of [...]
Weekly survey: have you ever tested your disaster recovery plan?
Posted on: May 17, 2009 5:38 pm
This week's survey is inspired from many stories I saw on the forums and Twitter this week – mostly bad, one good (someone I'm following is spending the weekend testing their disaster recovery plan – cool!). I'd like to know whether you're ever tested your disaster recovery plan, and if so, what happened? I'll report [...]
Importance of choosing the right architecture and SQL Server Edition
Posted on: May 17, 2009 4:40 pm
Last week's survey was another two-fold one – when you buy new servers, what architecture to you predominantly buy, and why?; when you buy new servers, which Edition of SQL Server do you predominantly buy, and why? Here are the results as of 5/17/2009. For the first survey, the 'other' values were basically that [...]
Misconceptions around database repair
Posted on: May 15, 2009 5:02 pm
This week's been a busy one on the forums and Twitter, with lots of interesting problems people are hitting. One of the things I've noticed is that there are lot's of misconceptions about running repair, so to round out Friday I'm going to run through a list of them for you. Here are the misconceptions, [...]
Clustered or nonclustered index on a random GUID?
Posted on: May 14, 2009 9:34 am
Quickie this morning to start the day off. I saw a question on a forum: if I *have* to use a GUID and *must* have a primary key, should I make the primary key clustered or nonclustered? Now, I'm not getting into the whole GUID vs. bigint identifier, or random GUID vs. GUID generated by [...]
Cool way to visualize word distribution in text – wordle
Posted on: May 13, 2009 5:13 pm
Go to www.wordle.net/create and enter a URL. Here's what it thinks of my current blog homepage: Cool!
Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?
Posted on: May 13, 2009 4:29 pm
I've just been setting up some of our new hardware, and wanted to do some background reading to ensure I use the correct disk partition offset, RAID stripe size, and NTFS allocation unit size to enable the best possible performance for the volumes I'm creating. You may not of heard about this (or your disk [...]
How Twitter and social networking changed my life…
Posted on: May 13, 2009 9:59 am
A sad, tear-jerky story about how a socially-inept, stay-at-home shut-in, SQL expert gets a new lease of life and finds he really does have friends out in the big world after all. LOL. Not. I'm definitely not socially-inept – managed to persuade Kimberly to marry me, right? Well, that was really down to the classic pick-up [...]
A sad tale of mis-steps and corruption (from today)
Posted on: May 12, 2009 8:36 pm
This is a true story, and unfolded over the last few days. It's deliberately written this way, I'm not trying to be patronizing – just illustrating the mistakes people can make if they don't know what not to do. Once upon a time (well, a few days ago), there was a security person who had access [...]
How expensive are page splits in terms of transaction log?
Posted on: May 12, 2009 5:50 pm
Page splits are always thought of as expensive, but just how bad are they? In this post I want to create an example to show how much more transaction log is created when a page in an index has to split. I'm going to use the sys.dm_tran_database_transactions DMV to show how much more transaction log [...]
How to get all the corruption messages from CHECKDB
Posted on: May 12, 2009 4:56 pm
Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes [...]
Weekly survey: what architecture do you buy? And which SQL Server edition?
Posted on: May 10, 2009 3:57 pm
This week's survey is another two-parter, part 1 of which suggested by Mike Walsh on Twitter: When you buy new servers, what archictecture do you predominantly buy, and why? When you buy new servers, which Edition of SQL Server do you predominantly buy, and why? I know the surveys below don't have a 'whatever suits [...]
Importance of having a manageable environment
Posted on: May 10, 2009 11:22 am
Last week's survey was two-fold – what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009. As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of [...]
OT: Excellent funny videos
Posted on: May 7, 2009 12:59 pm
Here are a couple of videos I came across today that are very funny. The second one had me in tears laughing. Let's just say that it could easily be Kimberly singing the second one – she'a bit of a toast fanatic. 1) "Cannon PI" – start of a new spoof video series from Microsoft: http://www.youtube.com/watch?v=cCApcSq1ke0 [...]
What is FSAgent.exe and why does it have lots of waits?
Posted on: May 6, 2009 4:21 pm
Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is [...]
Misconceptions around adding columns to a table
Posted on: May 6, 2009 3:38 pm
This is a quick follow-on from my Misconceptions around null bitmap size post. The null bitmap is *always* present in a data record (i.e. records in a heap or the leaf-level of a clustered index) except when all columns are defined as SPARSE in SQL Server 2008 onwards, but is optional in index records if all [...]
Misconceptions around FILESTREAM storage
Posted on: May 6, 2009 1:47 pm
This short post is prompted by a question that came in through Twitter – I *knew* it was worth joining and spending time on it (http://twitter.com/PaulRandal). The (paraphrased) question is "can FILESTREAM data be stored remotely?". This has been confusing people, and neither FILESTREAM BOL nor my FILESTREAM whitepaper (see here) explicitly answer the question. [...]
Why did the Windows 7 RC download failure happen?
Posted on: May 5, 2009 11:57 am
5/11/09: Little bit of a rewrite today as it seems some people are taking what I'm saying the wrong way. Over on Ed Bott's blog, last week he showed some inside info about the notorious "SQL Server problem" that caused the Windows 7 RC downloads to be so slow. The reason given was that SQL [...]
Misconceptions around corruptions: can they disappear?
Posted on: May 5, 2009 8:30 am
This has come up a few times now, most recently in an email question this morning - subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what's going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no [...]
Do transactions rollback when DBCC CHECKDB runs?
Posted on: May 4, 2009 4:04 pm
Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another [...]
Misconceptions around null bitmap size
Posted on: May 4, 2009 2:47 pm
I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this [...]
Weekly survey: what’s your largest database, and how many do you manage?
Posted on: May 2, 2009 7:58 am
Jason Massie posted an interesting statistic yesterday – Facebook has 1.5 petabytes of image storage, and it grows by 25TB daily – I wonder how they store and manage it? In this week's survey, I'm interested in two things: what's the largest single SQL Server database in your company and how many SQL Server databases [...]
Importance of having the right backups
Posted on: May 2, 2009 6:32 am
Last week's survey was on what kind of backups you take, along with the recovery model used (see here for the survey). Here are the results as of 5/2/2009. The 'other' responses were combinations of the other answers. This survey is a bit of a pre-cursor to my article on Understanding SQL Server Backups [...]
Photos: awesome statues in the Bangkok Imperial Palace
Posted on: May 1, 2009 8:43 am
In one of our visits to Bangkok this year, we toured the Imperial Palace. I uploaded a bunch of photos to Facebook of statues that caught my eye. Check them out at http://www.facebook.com/album.php?aid=2023831&id=1293146061&l=e17b5abc80. Here's an example for you.
TechNet Magazine: June 2009 SQL Q&A column
Posted on: May 1, 2009 8:08 am
The June edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Combining index and statistics maintenance How some Enterprise-only features can prevent a database restoring on lower editions Why database mirroring can switch between SYNCHRONIZED and SYNCHRONIZING Use a [...]
