Have you ever written something and then lost it... for whatever reason: your own stupidity (come on we've all accidentally done something at some point where we lost data or a spreadsheet or a document or something...), the software eats it (this might be self-inflicted but I've been in apps that just hang and that's it... there's nothing you can do except power off), or....whatever.

Well, during those times... have you ever thought - I'd do almost anything to get that data (and time) back?

Before I go any further - yes, backups are good. No, let me be clearer... Backups are an absolute requirement of ANY environment!!! 

Even personal/home environments should have something to protect the data. Something to consider is even offsite copies. Burn a DVD or two of your precious family photos and then swap DVDs with a friend... there's your simple/cheap/offsite data recovery. We all focus on critical data when a business depends on it - what about the personal stuff... Paul and I always talk about database backups and this post is not really targeting enterprise systems or even business critical systems... Really, there's NOTHING THAT SHOULD EVER REPLACE A GOOD DISASTER RECOVERY STRATEGY...

Having said that though, I have an interesting story (with a data-saved/happy ending)!

I was on a plane flying back from SQL Connections (I was actually flying from Tampa after having visited family in St. Pete) and well, disaster struck again (yes, this has NOT been a good year for hard drives for me as this was my 3rd and MOST catastrophic disaster so far...).

Anyway, Paul and I were "discussing" what I STILL think is a bug in a SQL function (ok, I'll get to that in a later post) and I had been feverishly completing a multi-page blog post AND some index examples/metadata queries, etc. when my laptop slipped off of my crappy airplane table (I was in an exit row so I had a table that came out of an arm chair and folded - it was very wobbly) and crashed to the floor (and, the irony of being in an exit row... had I been in a regular seat there wouldn't have been room for the laptop to have fallen to the ground :).

To my surprise, I picked it up and still saw the screen I was working on. I typed in another line... and then got a little dialog that said "windows hard error" or something... I don't even remember now. The only thing I could do is power it off. My laptop was dead. Very dead. I [expect] that I had had a major head crash when my laptop hit the floor because it's likely the disks were still spinning. However, I still did not know this at the time.

I rebooted and received "Error 2100 - Hard drive initialization error"... so, when we landed in Denver, I was off to one of the small stores to buy an eyeglasses kit (I needed a screwdriver :) and then I went to find a spot to do some laptop surgery. The good news is that I've had so many disasters over the years that almost none really freak me out anymore (this is probably the 10th drive I've had personally fail) and, I always carry at least one spare laptop... But after the terrible time I had March in India, I actually had 2 spare laptops on this trip (yes, airline security hates me even more now - oh, and Paul travels with 2... so, we go through security with 5 laptops... we get some interesting looks!).

Anyway, I took the take the drive out, loaded it into my secondary drive bay (if you travel a lot - having this second bay that can hold a primary/secondary drive is INVALUABLE as these secondary drive bay usually use the same setup (Serial ATA in my case) as the boot drive). So, if your boot drive doesn't boot, you *might* be able to still read and/or save data by using the secondary drive. So, on a second machine, I gave it a try to see if it would spin. No dice. I even tried my third machine (my primary was XP, my secondary is XP and my backup/backup is Vista... I thought... well, maybe?) Ah, I thought wrong. My drive would never spin again. So, on my flight from Denver to Seattle, I was not overly pleased (this is an understatement to be honest - just ask Paul) because I was at least a couple of days from having done a backup AND I was even more frustrated about having lost the detailed indexing post I was working on - and even losing the code that was on screen just THAT day.

We finally got home (which seemed like a much-longer-than-normal trip ;) and I got online thinking/hoping - is there ANYTHING I can do... And, I stumbled on a reference to a possibly out of date BIOS version and the needed update which also happens to generate this SAME error. And, being hopeful (and opportunistically forgetting the drop/crash/thump which led me to this problem), I *attempted* the BIOS update and well, it didn't recognize that a drive was attached. OK, that was my last hope. Hours lost. Let's move over to my backup laptop and shift everything I do have backed up over........ which I did and I was up and running that morning. Yes, I had lost a few things and yes I was pretty frustrated but, I wasn't totally down. It wasn't as bad as it could have been without any backups...... but, I was still annoyed.

Then, I thought... is there any other option(s)? It's been at least 10 years since I sent a drive into a drive recovery place. So, I thought this needed some research. I wanted to see what it might take to (and/or IF I could) recover data. About 10 years ago, I had a single drive of a RAID 0 array fail and the disk recovery place couldn't recover any of it (well, I think they could get 128kb out it and it was going to be 800 bucks). But, that was 10 years agao. Have things changed??! Hmmm... what could they do?

Anyway, I got in touch with Drive Solutions, Inc. and they gave me the rundown of what it would take to get data back. I wasn't sure if it was really going to be worth it (especially for the costs) but I still wanted to go through the process (for multiple reasons - some of it was for the data but some of it was for this post - and to remind people of what's possible (myself included)) given that we talk about the importance of backups and the UNLIKELY potential for data recovery off of damaged disks...

The long story short (ah, too late :) is that they can do amazing things these days (NO GUARANTEES THOUGH!!!) and they can completely rebuild the drives in a clean room - replacing drive heads, etc. Once complete, IF they get anything back, they'll give you a directory structure of what they've recovered and different options for getting it back to you (DVDs of just critical directories (there's a cost for each DVD after the first one or two) OR you can purchase a new comparable drive and they'll copy it over). The whole process took about a week (and this was for expedited service - which was also an extra charge). However, and amazingly, they recovered everything (well, I've only done a bit of spelunking but so far, so good)...

And, here's the coolest part, I was working on some SQL files at the exact time of the disaster and well, I went to the \Documents and Settings\username\My Documents\SQL Server Management Studio\Backup Files\ directory and found a directory of Solution1. In it were 3 files with similarly ugly names (~AutoRecover.~vsC.sql, ~AutoRecover.~vsC.sql~RF93d469.TMP, etc...) and the AutoRecover file was 2KB (the others were 0KB). Anyway, I opened up the file and viola! I actually recovered the .sql script I was working on at the time I dropped my laptop (well, I still blame this on Paul cause he made me lean over to talk to him and this ultimately pushed my laptop off the crappy airline table ;) ;) ;) :).

Needless to say, I am pretty amazed at what they can do now... but, I'm certainly not going to rely on that AND, it wasn't cheap!!!

So... what did I learn??!

1) First and foremost, data recovery is NEVER GUARANTEED. (yes, ok, we all knew that. However, I think we just need to say it out loud a few times :).
2) Even if they can recover some of the data, data recovery is not lightning fast. Even with the more expensive expedited service it takes time to ship (overnight), get the drive into a clean room, rebuild it from parts of an exactly matching (including BIOS/firmware) drive, test it, copy it to another drive, ship it back (overnight). Maybe you can find a place that's local, that would help but, it's still time...
3) It's expensive... expect about $10-15 per GB. And, I guess that some will think that's a crazy amount... Again, I had multiple motivating factors - one of which was also related to some pictures I had taken over the weekend with family in FL, some was for data, some was even better to understand this overall process... I expect that got about 10 hours back plus some photos and, I'm pretty impressed with the overall process (in general).

More than anything, I'm going to get even better at daily full system backups when I'm on the road (scheduled/overnight to an external drive) and I might even copy critical stuff to something like Windows Live Skydrive (or something like that). And, while on a plane, I might keep a small/simple USB stick handy if I do something that I really don't want to lose while on a long plane ride. I think new technologies like "mesh" and "cloud" are really interesting and definitely the direction to better performance AND *possibly* minimizing data loss but you're always at risk if there's only one copy. All of this might seem crazy but well...... I've been called worse ).

So, just to wrap things up, I'll be doing a bit of final tweaking on my indexing blog post + my indexing demo scripts from Connections + my metadata script that I was working on at the time of the laptop disaster (which is also why it's been a while since my last post) and I'll be leveraging some of my favorite tools (Beyond Compare) to determine all of the differences between my recovered data/directories and the system I rebuilt from a backup (especially now that it has also changed over the course of this week since I moved over to my backup laptop). And, I've now ordered a new harddrive for my primary laptop. Sadly, I'm getting good at laptop rebuilding.

Thanks for reading,
kt

PS - When did you last backup your home/personal/less-critical system... is it really less-critical?

I had a customer ask me about the debate of skipping SQL Server 2005 entirely and just moving straight to SQL Server 2008... I haven't thought much about this as most of my direct customers have moved already BUT, I know there are a few out there that have not yet moved.

So, I definitely need to spend some time on SQL Server 2008 upgrade requirements, restrictions, etc. - and that's my plan in the not-too-distant-future BUT, I haven't thought much about it so far.

Your thoughts???

Cheers,
kt

While at SQL Server Connections in Orlando, Stephen Wynkoop of SSWUG stole some morning time for an interview (morning time is not my best but we did get a lovely "I got my mug on SSWUG tv" mug so that made it OK :) :). We (Paul and I) had a great time chatting about Diaster Recovery, Backup/Restore, general best practices and well - games (specifically - the VERY addictive game of Blokus). Here's the interview link: http://www.sswug.org/columnists/editorial.asp?id=1135.

Enjoy!
kt

PS - If any of you pick up (and become completely addicted to) Blokus, let us know! It's great for 2 to 4 players and extremely fun when a 5 year old "wild card" sits in and throws moves that you just can't understand (but later come to really frustrate you :) :).

Last week while Paul and I were in the UK delivering a one day seminar on Crucial Database Maintenance Techniques, we met David McMahon from the Next Generation User Group. They're doing some exciting things in the UK and even for the wider community - for example - podcasts. Paul and I were interviewed for one and it's ready for download here.

Enjoy!
kt

This is a bit of a rare post for me as most of my posts are technical. I guess it's a bit of a change in attitude, as a few things (from my personal life) have come to light recently. (and, I thank Richard and Carl for this :) :).

Anyway, I'm sitting in Tokyo/Narita airport waiting to board my final flight home to Seattle. This is after a trip which has brought me over every longitude possible - in only 8 days. It sounds "glamorous" as many people always tell me "how lucky I am that I get to see the world" and while many trips allow me to see some amazing things AND I absolutely LOVE what I do... this trip really wasn't all that much fun. The long story short is that my Father became very ill where he was vacationing. It really didn't sound all that good when we heard (which was Wednesday - in London - where Paul and I had just completed a one-day workshop on Database Maintenance on Tuesday). Anyway, we thought that even if he were to make it, we knew he couldn't do the return travel alone. I was a bit of a basket case and in the short period of time we had to make a decision the only logical one was to get to him to help him get home (and for Paul to help me through it). Fortunately, for me, everyone (Miracle, Paul, Tony Rogerson, Tony's wife Alex, etc...) were extremely supportive and everyone just said they'd take care of what we left behind in our wake of crazy travels. And... well, that's where we are now (on our way home). We're amazingly happy to say that he's getting his strength back (slowly) and that we're making our way back home today (which is why we're in Narita). In the end, we (Paul and I) had to miss the OpenWorld event in Lalandia (to which we were truly looking forward) but I can't say that we regret the decision in any way.

Having said all of that, we (Miracle, Paul and I) are looking at options to give the OpenWorld attendees something special for our not having been there. We're looking at options that might include a special LiveMeeting presentation with an open Q&A as well as options for later in the year when both Paul and I are back in Europe (which is at least once or twice more this year).

So, while I'm not so fond of lots of [my] personal [life] posts (I certainly don't mind other people's posts :), we hope that you'll accept our sincerest apologies for missing the event. But - we have to admit - without Paul there, there must have been more beer.

Cheers! And, thanks for reading......
kt

OK - I feel like I know a fair amount about SQL Server but sometimes I also feel like I don't :) :) I'm continuously amazed at how big a product SQL Server is... today was one of those days when I felt "I don't"!

I've been wanting to know more and more about the new features in the tools and the direction in which the tools are headed so... I setup a meeting with Paul Mestemaker (it helps that I live in Redmond and I'm working on some SP2 resources for the team :); it was a great meeting. Some exciting new features and some great new directions in which the tools are headed. I like the way they're thinking and I especially like the options that are now in place to discover, use and customize the "reports" feature within Management Studio (just to name one!). What I learned (that was the highest on my "I didn't know that" list) is that quite a few gems are released as part of the Feature Pack for SQL Server. The Feature Pack "is a collection of standalone install packages that provide additional value for SQL Server 2005. It has been updated for SP2." From that description, it doesn't leap out at me as exciting AND I often know about many of these tools through other channels - but usually it's just "a tool at a time". The thing that's nice about the FP page is that it seems to be a nice and central, single location for ALL of the "add-ons" for SQL Server. It includes things like the Upgrade Advisor (which I typically point people to individually on it's main page) and will include (once it ships) things like the BPA (SQL Server 2005 Best Practices Analyzer) BUT it also includes things like a standalone download for SQLCMD so that you don't need to install all of the tools if you just want this lightweight client for automation. Additionally, it includes the SQL Server 2000 DTS Designer Components if you want to edit/modify/maintain DTS packages in 2005 before you rewrite/convert them to SSIS.

So - the point is that there's lots of great stuff out there and sometimes it just takes another person, a blog entry, or a few minutes hitting a company site to see what's new. I'd strongly suggest that you and your team pick a morning - maybe once or twice a month (and round robin who brings the coffee/doughnuts :)) to just browse around and see if there's anything new on your hardware, software and other supporting sites - especially those that don't already have a blog/rss feed (or other form for notifications). No one needs to know everything but knowing where to look can really make all the difference when you do need something (or when you have a concern/problem). And browsing a few sites (occasionally) might make the difference in applying a hotfix/patch before something becomes a big problem. Staying current with hotfixes, service packs, bios updates, firmware updates, etc. is difficult so make it a team effort.

Speaking of service packs, here's the primary page for SQL Server 2005 SP2: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
And don't forget the Books Online Update as it is NOT installed when you update an instance to SP2: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Also, be sure to watch the SQL Server Manageability Blog (aka Paul Mestemaker's blog) moving forward as he'll have the first news about BPA and many other tips/tricks with regard to the tools.

Finally, (and this is great timing too), Paul Randal - prolific author at the SQL Server Storage Engine Blog - blogged about all of the active "SQL Server Product Team"-related blogs here.

And........ if that doesn't keep you busy, I'm not sure what will! :)

Instant Initialization technical details are next and then I'll get back to the Clustered Index Debate. Thanks for reading!
kt

Well, I've promised to blog more and I'm really going to try to do so. This morning I got the perfect question/comment (in email) to respond to and after working through a response that was taking me upwards of 3 hours (you'll learn later why I have 3 "spare" hours :)......... I figured that it was time to turn the response into a blog post. ;)

Background: The Clustered Index Debate
In the years since the storage engine was re-architected (SQL Server 7.0+) there's been constant debate on how to appropriately choose the clustered index for your tables. I've generally recommended an ever-increasing key to use as a clustered index and many find that counterintuitive. The primary reason people feel it's counterintuitive is that it creates a hotspot of activity. [If "hotspot" is not a familar term - a hotspot is solely an active place within your table.] Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there. In fact (and probably even more counterintuitive), the opposite is true. Hotspots (specifically hot PAGES not hot ROWS) can be very beneficial because they; minimize the number of pages needed in cache, improve the likelihood of the required page already being in cache and in general, they minimize the overall amount of cache required. So, this is why many of us have changed our recommendation on where to create the clustering key in 7.0+. Instead of focusing on range queries we now focus on placing the clustering key on an ever-increasing key. In earlier releases, focusing on range queries for the clustered index reduced hotspots for insert/update and this in fact was the PRIMARY motivation to choose them, NOT range query performance! But - there are even MORE reasons to choose an ever-increasing key and they are based on internals as well. These internals are based on the significant changes made in the storage engine for 7.0+. For a quick start on these, I went through them in the Blog entry here.

And, today's email is not uncommon. This is the basis for the title clustered index debate. In general, there are still a lot of questions related to creating clustered indexes to improve "range query" performance. Don't get me wrong, there's definitely a benefit in performance for some range queries but the first thing to remember is that you get only one CL index per table (therefore only one type of range query can benefit). In the real world, t's not likely that you want to see your data exactly in the same way all the time. Therefore it's very challenging to come up with the "right clustered" index if you're using range queries as your strategy. Even worse, the affect of choosing the clustering key to improve range queries causes problems for modifications against that table (INSERTs/DELETEs and UPDATEs). So.............. this is what started my day today. A great email from a reader that brought up these points. The question/comment (modified to hit only the highlights and to protect their identity :) was this:

The most important characteristic for a Clustered Index key is to satisfy range queries. More often than not, if a sufficient range of data will be scanned, the Optimizer will choose the Clustered Index over all others due to the excessive cost of Bookmark Lookup operations. As such, the table KEY is a more suitable clustered index candidate than any surrogate (few every query a database by range of surrogate keys).  [kt note: this second sentence is not entirely true... SQL Server will certainly choose a clustered index over non-clustered that require table scans but there are A LOT of algorithms that SQL Server can use instead of either of these and my examples later show this... non-clustered covering seekable indexes, non-clustered scanable indexes, index-intersection, etc. ] 

Now, when the default behavior for SQL Server was designed such that the PRIMARY KEY was chosen as the default clustered index, it was exactly for this reason.  It is the business key.  It would satisfy uniqueness (by definition of logical KEY).  And, it is well suited for a wide variety of range scans.  However, this is when the PRIMARY KEY is defined on the Business Key of the data.

But, when you introduce the usage of surrogate keys (i.e., IDENTITY) as a physical implementation, and thus transfer the PRIMARY KEY definition to it, two things must be considered.  First, the Business Key this IDENTITY will be a proxy for must still exist as it is still apart of the logical design.  As part of the physical design, the logical key needs to be implemented as a physical constraint to maintain logical uniqueness.  Second, just because a proxy has been defined does not make it a natural candidate for the clustered index.  The business key still maintains this distinction.

What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits.  However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list.  Page Splits are managed by proper FILLFACTOR not increasing INSERTS.  Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.

Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting.

In short, the argument runs shallow.

Luckily, this email arrived with perfect timing for me as I'm sitting in a "bootcamp" event on Always On technologies and I'm not speaking this morning (my colleague Bob Beauchemin is doing lectures on Scale Out technologies: Scalable Shared Databases, Service Broker, DPVs, etc.). Anyway, in addition to listening to Bob, I've decided to continue the blog series on "the clustered index debate". The first and most important point to stress is that minimizing page splits is NOT the only reason nor is it the most important. In fact, the most important factors in choosing a clustered index are that it's unique, narrow and static (ever-increasing has other benefits to minimizing splits).

The Clustered Index Debate Continued
First, there are many angles to look at wrt to "the clustered index debate" and it's not until all of the issues are reviewed, that this strategy (a monotonically increasing key) becomes obvious. So, I think it will probably take a couple of blog posts to really prove this. I'll start up this debate again here...... When you look at a general purpose table (which is most) where the table has ALL DML (S/I/D/U) then you are best off with an ever-increasing key (again, you have to look at the overall impact of all operations against the table - not just select... because I/D/U will also impact select in the long term). So, I'll break this down into each DML operation here. If you don't look at the overall impact, then large tables can end up having a tremendous number of problems once they're put into production. I've certainly heard this concern/debate before (and most people are skeptical at first glance) but when you look at the situation overall, you'll find that "finding the right balance" includes not just looking at range queries. In fact, here's a quick list of the things/tests/numbers/scenarios that help to prove my strategy:

• Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing. I have some simple numbers but I'm thinking about creating a much larger/complex scenario and publishing those. Simple/quick tests on a laptop are not always as "exciting". But - this is a well documented issue (IAM/PFS lookups) and poor performance on a heap is also referenced in this KB: PRB: Poor Performance on a Heap. note: this KB is quite dated and I don't actually agree with everything in this article however, the general concern of poor performance for inserts is still true on SQL Server 2005.
• Updates are often faster (when the row needs to be relocated) and for the same reason (IAM/PFS lookups) BUT there are many types of updates and not all updates cause records to be relocated. Here are a few things to think about wrt to updates:
  • Updates that are completely in-place (some examples are where the update is updating a fixed-width column OR to variable-width columns where the row size doesn't change, etc.). These types of updates don't really care.
  • Updates that cause record relocation (where the row size changes) are definitely better by having a clustering key because the record relocation (which will be handled by a split) is defined by the clustering key
  • Updates to the clustering key are the WORST (in this case) which is one of the key reasons for having a cl key that is static (so we have to keep this in mind when we choose a clustering key).
• Deletes aren't nearly as big of a concern BUT deletes in heaps create more gaps and more gaps creates more work in PFS/IAM lookups and while this helps to reduce wasted space, it still requires the time to find the space........ hence the slowed performance of Inserts/Updates. I've also written some blog entries that cover very interesting test cases for large scale deletes and why you'd want to consider partitioning to optimize for the "sliding window scenario" in this blog entry: MSDN Webcast Q&A: Index Defrag Best Practices - Fragmentation, Deletes and the “Sliding Window” Scenario and it's the LAST one!.
• Selects.............. now this is the hardest one to go through in just a couple of bullets (ah, I guess this will lead to another one or two posts :) BUT I'll start by saying that the best way to tune the vast majority of range queries is through non-clustered [covering] indexes. But, it's also important for me to stress that I do NOT advocate covering every query (it's impossible to do). What's important to realize in terms of covering is that SQL Server 7.0 and up continues to include internal algorithms to improve performance when you don't have the "perfect" non-clustered covering seekable index and instead still gives better performance than going to the base table (or performing bookmark lookups - as mentioned in the mail...and I completely agree that these [bookmark lookups] can be evil!). To start this discussion, I'll give one of my favorite examples of a large-scale aggregate. The absolute best way to improve the performance is through an indexed view but the data can be gathered through many other algorithms - ideally through a non-clustered covering index that is in order by the group by and that includes the column(s) being aggregated. For example, take this query:

SELECT c.member_no AS MemberNo,
 sum(c.charge_amt) AS TotalSales
FROM dbo.charge AS c
GROUP BY c.member_no

On a charge table of 1.6 million rows here are the performance numbers to handle this aggregation:

• Clustered table scan (CL PK on Charge_no) with a hash aggregate = 2.813 seconds
• Index scan (non-clustered covering but NOT in order of the group by) with a hash aggregate = 1.436 seconds
• Index scan (non-clustered covering in order of the group by) with a hash aggregate = .966 seconds
• Indexed view = .406 seconds

Now this was a pretty small table (narrow rows and only 1.6 million rows) AND I didn't have any concurrent activity. The concurrent activity would have caused this to be even slower for hash aggregates, etc. Regardless, it proves the point (at least generally). Now, if I wanted to improve this range query then I'd have to cluster on the member_no column (and this is an ideal example because I often hear people say that clustering on a foreign key column helps to improve range/join queries - which can be true as well)......... But - this strategy has a few problems in addition to a few benefits (and we have to look at everything to be sure of our choice/decision). First, member_no is not unique (in the charge table) so SQL Server has to "uniquify" the rows. The process of "uniquification" impacts both time (on insert) and space (the rows will be wider to store each duplicate row's uniqufier). Also, theoretically it could change (in this case that's not true). Anyway, the time it takes for the clustered index is 2.406 seconds which is better than the clustered on the PK (of course) but if I were to also start modifying the rows (which creates splits) or even just insert 15% more rows........ then my table would become fragmented. At that point, the query performance should get worse in the table clustered by member_no table and it will continue to get even worse in the table clustered by charge_no (because of the worktable created in tempdb by the hash aggregate) BUT it won't be all that much worse in the non-clustered index examples (especially the covering index that's in the order of the group by - because this doesn't require a worktable).........

• CL on member_no = 4.906 seconds
• CL on charge_no = 6.173 seconds
• Index scan (non-clustered covering but NOT in order of the group by) with a hash aggregate = 3.906 seconds
• Index scan (non-clustered covering in order of the group by) with a hash aggregate = 1.250 seconds
• Indexed view = .516 seconds

This is a great start to furthering the clustered index debate but I do have to admit that it's a counterintuitive and difficult issue to tackle because often isolated tests lead you to different conclusions. In this case though, the non-clustered indexes are better for this range query and the indexed view is the best (but I wouldn't consider the Indexed unless this were more of a read focused database rather than read/write). [and - of course, that statement warrants yet another blog post :)]

So, depending on the tests that you do - especially if you focus only on selects and you don't have modifications (i.e. fragmentation) - then they will make "creating the clustered index for range queries" appear to be best. Again, I'm not just saying this to prevent fragmentation, I'm saying this because I wouldn't use the clustered index OR a non-clustered index with bookmark lookups to handle this query. I'd consider a non-clustered covering that's seekable OR even a non-clustered covering that's scanable before I'd even choose the clustered (and that's what the optimizer would prefer as well). In the end it's really a bit of an art and a science to "finding the right balance" of indexing.

Oh - and if you arbitrarily add a column to use for clustering (maybe not as the primary key) that can help but many would prefer to use actual data... which means [potentially] creating your primary key with a new identity [or similar] column and this can impact your business logic (absolutely). I'm certain that certain tests can show that range queries are faster and it's absolutely correct that business application/usage can be a concern but when you look at the big picture (and the impact on I/D/U) then the benefits of the monotonically increasing key significantly outweigh these concerns. Simply put, a small/narrow key can help join performance and an ever increasing key can also help lookups for rows! (yes, definitely more coming)

Happy Friday! Have a great weekend. I'll try to continue more threads on this debate shortly!
kt

Ok - a strange title indeed but it's been a strange couple of months. It all started with a "much needed" vacation and I thought that would get me through the persistent "cold" that I was having all through my travels. Anyway, vacation didn't help and I came back to find that what I had was a sinus infection. Janaury has been filled with antibiotics, sleep and well......still a lingering cough even though the month is over today. The long story short is that I'm starting to come out of it and I promise to start blogging a lot more frequently starting now. In fact, I have 3 or 4 entries in the queue that I'm plotting for upcoming posts.

To get you started - there are some great and NEW resources that were posted just this week by some of my SQLskills colleagues. If you read their blogs then you've probably already seen this but for completeness, I'm going to post them here:

Bob Beauchemin's Blog Entry: http://www.sqlskills.com/blogs/bobb/2007/01/30/TheFirstOfMyScaleoutWhitepapersIsAvailable.aspx
Bob's Whitepaper: Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005

Liz Vitt's Blog Entry: http://www.sqlskills.com/blogs/liz/2007/01/30/AnalysisServicesPerformanceTuningWhitepaperHasArrived.aspx  
Liz's Whitepaper: Analysis Services 2005 Performance Guide

Ok - there's my first post of 2007 and NOT my last. More to come. Thanks for reading and HAPPY NEW YEAR (at least I got that in January :),
Kimberly

OK, it's been a heck of a long time since I blogged... and for that I apologize. I'm also WAY overdue in my posting my demo scripts from a TON of conferences BUT... now everything has been posted. Check out the past events page on SQLskills and you can find the demo scripts that you're looking for......lots of fun stuff and TONS of scripts to play with and test.

Now - as for the reason(s).... many are business and for that I blame the following (yes, 17 flights [yes, one boarding pass is missing] over ONE 5 week trip with 7 events and 5 continent changes):

The other reason(s) are personal...suffice it to say that the last 6 months have been some life changing times for me and what I'm finding (or trying to find) is that ever important balance between work and life. During this holiday season (and always), I wish you and your loved ones well and I hope that you too can find (and cherish) what's most important to you.

So, you won't see anything else from me for this year but I do hope to be better (and more frequent) with blogging in the New Year and I especially hope to see you again at an upcoming conference. Let me leave you with the most exciting picture I've witnessed this year...it was during my one day of site seeing in Cape Town - where I went cage diving with Great White Sharks (and got horribly sea sick - which is rare for me) but where I was able to witness these amazing and powerful creatures....

Have a happy and safe holiday season!
kt

There are SO many new features in the updated DasBlog that I don't even know where to start. Here's just a quick list of the things I really love:

1) Scrolling through the entries - if you click a specific entry's title, you will get a "last blog entry" | main | "next blog entry" section above the entry's title and you can scroll through the blog entries. It's really a nice way of reading through a series of blog entries.

2) the SUPER cool complete archive by category - here's a link: http://www.sqlskills.com/blogs/kimberly/Archives.aspx

And - those are just a couple of my favorite new features with 1.9 but you can read more about it from the source - Scott Hanselman - here. Definitely worth the upgrade, definitely worth the consideration to move from some other blog engines (depending on how much you blog) OR consider creating your own blog (here's why)........

Enjoy,
kt

I am asked often.....

• How can I get into presenting?
• How do I get into external consulting?
• How can I do something similar to what you do?

It's a challenge to work for yourself...

• There are periods of feast and periods of famine
• There are [way too many] times when the only person you can blame is yourself ;)
• You need to do current work, work on getting future work and do the admin for past work (billing/receivables/paying subcontractors)
• No one pays you when you are sick or when you want to vacation or when you just want to veg... and you really need to schedule all of that way in advance (which is challenging)

BUT - it's also very rewarding...

• If you plan ahead - you can take time for yourself and you don't have to deal with a corporate limitation in vacation days
• If the work is light you can take the morning to mess about and blog ;)
• You feel really good when things go well

So...what's my point?

If you've been thinking that you'd like to expand your horizons and possibly get out and do something on your own, I'd *really* suggest NOT doing it without planning ahead. First, you should plan to have some cash reserves (the first 6-12 months are the hardest as you'll have a lot of setup costs (hardware, network, accounting and legal fees, etc.) AND it might take time to get customers and even more time to get paid). Second, you should already know from where your business will come. What I mean by this one is that you should start trying to figure out who might hire you before you take the leap. I'm not suggesting that you try to steal customers...not at all. You just want to think a lot about where you will get business before you have 0 income. And - that really leads me to the reason for this post. One of the best ways to get business is to be desirable. In this industry that means many things:

(1) Get really good with a technology that you like... which #2 will help you know if you are good or not AND whether or not you really do like it.

(2) Make a name for yourself - create a blog, write magazine articles, post on other people's blogs, answer newsgroup postings...start to learn more but also get more involved. Do a bit of research before posting and even test and/or create sample code. And present at conferences - this is a bit challenging BUT it can be done. Some conferences are community based (like SQLPASS) and they look for new and exciting presentations from the field... Others expect a lot more prior history and sometimes have a smaller number of speakers in general BUT, TechEd ITForum (week of Nov 13 in Barcelona) is doing something that's pretty interesting called "Speaker Idol" and this might be the break you need?

OK, so Speaker Idol is really the reason for why I've posted this blog entry. I wasn't really sure what the heck it was when I first bumped into it (accidentally actually - after someone asked me to verify something totally unrelated). Anyway, it's a competition for attendees of TechEd ITForum to submit a presentation that will be voted on as "best presentation" and the winner will get free attendance to next year's TechEd ITForum (and a VERY cool item to put on your blog, resume, etc.).

(3) Research the whole process of starting a business in your neck of the woods A LOT more before you actually do it!!! I truly wish you well if you do!

So, check out TechEd ITForum's Speaker Idol and have fun! It's certainly an interesting way to jump start the process if you've been thinking about this but just hadn't made the leap. GOOD LUCK!

Cheers,
kt

Hey there everyone - Been a LONG time since I last blogged (sorry!)... key reason (fyi) is that I'm trying to find the ever-challenging work/life balance during the best months of the year (here in Seattle July/Aug are GREAT! months - September is almost always good too). Anyway, it's been a few weeks and I thought I'd catch you up... It all started with some travel (of course) and I was in Chicago for a SQLskills Immersion event and then off to London for another event with SQL Server FAQ (aka Tony Rogerson). I returned during the CRAZY travel restrictions and had to check two laptops (sigh) BUT they both made it back without damage after I purchased kitchen towels, bed sheets, a blanket and a duvet (and two new [cheap] suitcases) in which to pack them. When I got back, I relaxed! I've been up to see the Athabasca Glacier and Johnston Canyon and just this week I took off a day to do the "Lotus Experience" Advanced Driving Course at Pacific Raceways/ProFormance Racing School. I've always LOVED driving so driving 700 miles to Johnston Canyon didn't bother me at all (especially with great company and fantastic tunes - have you heard the latest Snow Patrol?) and the driving course in the Lotus was not only great fun but VERY informational. I've taken the Advanced Driving course before and I think EVERYONE should consider continuing their education in driving - things like Collision Avoidance, Advanced Braking (understanding proper driving under ABS), "high eyes" and so many other things...just make you safer on the road. OK, enough about all that fun/practical stuff.....let's get back to SQL.

Some great new resources are out:

• Books Online Updates are about every month or two now - are you keeping up? Here's the July Update!
• The SQL Server 2005 Failover Clustering Resource Whitepaper whose primary author is the the primary author of the SQL Server 2000 High Availability book that I also had a small part in... the author is Avenade consultant Allan Hirt. He's currently working on the update to our 2000 book (for 2005) and it's slated for release toward the end of this year. Stay tuned on the book, I'll post a link when it's released.
• A whitepaper on Physical Database Storage Design
• And - along the lines of what my colleague Bob Beauchemin discusses in many of his classes - SODA (Service Oriented Database Architectures). Here's the paper that started it all titled Service Oriented Database Architecture: App Server-Lite? by Dave Campbell and here's a more recent paper titled How SQL Server 2005 Enables Service-Oriented Database Architectures by Don Kiely.

And - it's now time to start enjoying Labor Day weekend, there ain't no labor going on here this weekend. Enjoy - and check out those links next week.

Cheers everyone,
kt

OK - so, lots of exciting things going on. I've just completed my first two day partner event in Zurich (first of two in Switzerland). Today I head over to Geneva by train so that I can deliver the second two day partner event there. The partner I've been working with in Switzerland is Trivadis and it's always been fun coming over here and working with them (this is my third trip to Switzerland since Mar last year and second with Trivadis)! Trivadis is home to one of my fellow RDs Meinrad Weiss and he's always great to chat with and very clever too! In fact, I have a blog post coming based on something we chatted about during class (it's a cool trick... stay tuned). But, regardless, one of the things that came up at the end of the day was the type of training that I did this week... (well, really the type of training that we do at SQLskills). The discussion ended up talking about the complete learning/training cycle and how do you choose from ALL of the different choices??! First, (if we're talking about SQL Server), it takes a layered approach and a variety of different techniques, technologies and even a lot of planning/practice/coordination among staff, etc. to keep a system running WELL. In the end, it takes a combination of things (and probably training) to make that happen. As a result and based on all of these choices, we talked about training in three primary buckets...

Conferences
Conferences are great when you want get a wide variety of topics covered. They're great when you want to network with large groups on a variety of levels. They're great when you want to see a variety of products and how they work and/or what they do. Conferences really are great when you want to go wide more than deep. Don't get me wrong, I like doing 300-400 level sessions at conferences (and there are a lot of other speakers that do as well) BUT, in only a 60-75-90 minute session, you can only go so deep. You can do two sessions or three sessions in a topic but sometimes that's hard to coordinate and to be honest, there's always so much going on that you tend to lose people that are getting distracted by other tempting sessions so I'm not all that fond of going beyond two for a conference. Having said that, you can certainly hit a lot of best practices and technical depth BUT I think a conference is BEST at giving you breadth and there's nothing wrong with that! In fact, some of the best conferences I've been to in a long time were TechEd in Boston and SQL Connections in Orlando... lots of great sessions and I got some great ideas of different architectures, etc. and I also learned about some great upcoming products, etc.

Training
Depending on the vendor there are lots of options in this bucket. Probably the most obvious is MOC (Microsoft Official Curriculum). MOC has been around for a long time... in fact, that's where it all started for me! Back in '90 I got my MCP in something (hmmm...DOS?) and then shortly thereafter got my MCP in OS/2, LAN Manager and SQL Server and in '91, I became an MCT (all of this while I was working for a small ATEC in Chicago called LAN\mind). Then, I went to work for Microsoft as a trainer for Microsoft University (the building 0 of Microsoft even though by that time Microsoft was a lot larger and had moved to it's main campus in Redmond). It was a GREAT place to work. Both of these training centers (LAN\mind and then Microsoft) were a BLAST to work at and I learned an amazing amount with my team members. It seems like all of the trainers had great fun working together...one of my best friends actually rollerbladed through my class once (those were the good old days!). My point, is that this kind of training is a lot more focused... it allows you to focus for 3-4-5 days on one area of the product and it gives you some hands-on. This can be exactly what you need. The only problem is that you're somewhat at the mercy of the instructor (well, this is always true!) and you have to hope you get someone who has really worked with the product and/or customers and really knows depth (I’ve heard EVERYTHING in terms of comments – both good and bad about MOC and most of the time it’s about the instructor). To add to this, there are some companies that “host” MOC training but don’t even really have instructors on site (which is also good and bad). In fact, I’ve received a few emails that are clearly being blasted at a ton of people (I guess MCTs) and the mails are saying things like “Is anyone available NEXT week to teach course XYX” and that just completely blows me away. Don’t get me wrong I used to jump in when possible and say “yes” I’m available and I have to think that it’s still a good class BUT… it just seems like the RANGE of quality is the most widely varying here and that’s the part that can be disappointing. With regard to the courses, these are generally good. However, I do think that MOST of the courses try to cover too many topics. Basically, each class tends to focus on a discipline - like DBA or Dev or BI and while that can be good...sometimes you need more AND sometimes certain topics really hit at least two and possibly all three of those areas. That's where I *think* (well, I hope!), that I (we) come in with "Immersion" events.

Immersion Events
Other companies have things which are similar but my thoughts on Immersion Events are that some areas just need time and incredible depth to really understand. I always make fun of this old Chinese proverb but it's so true: give a man a fish and he eats for a day, teach a man to fish and he eats for a lifetime. The point is that anyone can tell you a command and how to implement something... but if I can really tell you why something works the way that it does and show you how you can see it and troubleshoot it, then you're going to be able to use that and leverage that when other things go right/wrong. I believe heavily in internals and the lower levels of how pages, extents, files, filegroups, data v. log, locking... if you start to get a feel for how all of these things are interrelated, you can make better architectural decisions AND you’ll know what the other impacts are of a variety of decisions. For example, transaction design (esp. long running transactions) impacts the types and lengths of time that locks are held (ok, that’s a “well duh”) but poorly written transactions not only cause blocking (which in turn, affects performance) but can also impact the size of your transaction log and whether or not the log can be cleared. This can end up not only causing performance problems but can impact recovery and even some of your secondary systems (or cluster) down the road in terms of restart recovery (because a long UNDO can take time and in SQL Server 2000 and SQL Server 2005 (in 2005 it’s ONLY the non-Enterprise Engine editions), users cannot access a database until UNDO has completed. (sidenote: the SQL Server 2005 Enterprise Engine editions (Developer, Enterprise Eval and Enterprise) have Fast Restart which allows users to access the database after REDO has completed and while UNDO is processing. The good news is that data integrity is maintained because the records being processed by UNDO are locked and inaccessible (unless lock hints are used).) Finally, if you’re waitinf for UNDO, this can lead to the perception of potentially A LOT of downtime. And – all of this could be because of a poorly written application. So, my point is that knowing the impacts of something from end to end and really diving into how things work – that’s what some classes do. That’s what Immersion Events do. We take one area and we really try to focus on a subset of features (new features/critical features) and then we just try to go deep. We’ll often hit on best practices in other areas and other options but we really try to go deep in the more critical areas. The other thing that we do with Immersion Events is that we try to extend the courses with other types of learning – group troubleshooting, guest speakers, hands-on, *interesting* demos and then we end the day with BYOP (“Bring Your Own Problem”). BYOP sessions are unstructured sessions that typically run from 4:30 - 6pm after an 8:30am-4:30pm course day. BYOP is designed so that you can apply what you’ve learned during the class day to your own systems and challenges. If it’s a tuning class, we strongly urge you to bring your laptops and external drives – loaded with database backups, sample applications/workloads and Profiler Traces – so that you have real world data/problems for real world (and specifically YOUR WORLD J) tuning scenarios. If it’s an architectural class (like Bob’s classes in SODA), then we want you to come with your ideas and current architectures so that we can see if other features and/or alternatives could provide better x, y, z (insert performance, flexibility, control, etc…). The BYOP sessions begin with group discussions – focusing on similar systems and/or challenges and then the groups will move into deep troubleshooting discussion groups from there. Basically, the aim of an Immersion Event is to have an intense and focused event where not only do you come back with a few new/critical features to leverage but you may have even solved some of your most frustrating challenges.

OK, this blog entry really wasn’t planned to be this long but it’s a discussion that I got into last night that started to motivate this. At the end of my partner event at Trivadis (not quite a full blown Immersion Event but *very* close – certainly in terms of technical depth but just not the really long days and BYOP), they were really pleased that we had just spent two full days on nothing but Indexing (Internals to Statistics to Advanced Indexing Strategies to Maintenance). They told me that they felt like they were ready to tackle a few problems and a couple folks said that they already knew what they were going to do to fix a thing or two. One person came to me telling me that they really didn’t think that I could tell them WHY one of their more complex problems “worked” the way that it did but after the module on statistics he said he knew exactly what was going on. And – I love when I get mail from folks after class… I just chatted with someone from my last class who told me that they had a query that was taking 15 mins (over a 2.8 billion row table) and now (after some of my Advanced Indexing Strategies) the query is taking 9 seconds. Can’t complain about that, eh? J

Anyway, learning can be fun AND there are lots of ways to learn. I think the best way to a better system is to combine all of the above (and don’t forget webcasts, blogs, whitepapers, Hands-On Labs, etc.). The basic point is that you should attend some conferences to get a wider variety of topics and architectural options, attend some training to get started with a variety of technologies and then attend Immersion Events to really get the depth and focus to solve some of your more complex challenges. And – you can certainly substitute webcasts, blogs, whitepapers and a few other options for some of these training options but sometimes you really need to get away and “Immerse Yourself” in the technology!

And – OK, I have another reason for blogging on this topic today… we’ve (SQLskills) just announced our new Immersion Event lineup for summer/fall 2006. We’re hitting three cities in the US (Chicago[land], Redmond, and New York City) and we’re targeting some intense classes around Performance Tuning, Service Oriented Database Architectures, Tuning BI (IS/AS/RS) Environments AND Always On Technologies. They’re going to be great fun, in-depth, hands-on courses with long days so that we can all really focus and “get it done”. And we’re still adding some guest speakers (Gert (aka Data Dude) is joining us in New York and it looks like the famous “SQL Apprentice” will join us in Chicago for some BYOP on SQL – details to be posted shortly). Check out the complete lineup of events here.

Let me know your thoughts on training in general and/or in specific. Really, I think all types are good and all have their time/place. I just happen to like the deep “Immersion” ones more than anything!

Hope to see you there!
kt

So, I've now spent the last couple of hours playing with Database Mail and HTML formatted messages being sent to the SQLskills subscribers. It's been a fun learning experience as I think I've found a bug with the email account name length...let's just put it this way - don't be too descriptive with your account names.

Outside of that - it's amazing how well queue based email works. The old mapi based mail would take a LONG time to complete the batch mail processing but now - with queue based mail it's done in seconds.

The best part is that I'm on a flight over the Atlantic right now...on my way to Frankfurt. I think this is the MOST productive flight I've ever had!

Have a great weekend,
kt

What a GREAT week here in Boston. I'm just about to pack up and go home (kind of). Right now it's early and I think I'm still feeling really good from last night's fun. The big party at Fenway, dinner on the town, walking around the city, etc... It was a beautiful spring (almost summer) night last night and the stars were out. We couldn't have asked for a better day (weather-wise) to end the week. There's still one day left at TechEd but this is when a few of us start heading out. I'm going to Chicago for the night to hang out with my Father (don't forget it's Father's Day this weekend) and then on Sat I head back to Seattle. I want to get back fairly soon as I'm only home for a bit under a week before I head to Switzerland to deliver a couple of master classes with my partner Trivadis.

But - part of why I'm writing this is that this was one of the best weeks I can remember at TechEd. Everyone seemed to be enjoying themselves and for once (in a long time) I really liked the layout of the venue. Eventhough TechEd is a MONSTER conference (12K people), the venue was easy to navigate and (most of) the sessions never seemed too full. Even the Expo hall didn't feel overly crowded. Don't get me wrong - there were a lot of people in there a few times BUT, it always seemed manageable. And - even when sessions did fill, there were overflow rooms setup and capacity seemed to be managed well. So, I do feel like I walked quite a bit this year but it felt reasonable (compared to Orlando last year, this was a dream).

And - the WAY more fun part of TechEd (for me) is meeting people. I've meet quite a few customers - many of you - who read my blog and/or have attended some of my webcasts and it's GREAT to put faces with the names.

So, that was my week and now it's done (and if you didn't make it - check out Virtual TechEd). In my next few posts, I'll try to find some time to chat a lot more about the technology that I saw and the technology that I demoed. In fact, all of my demo scripts and sample code will be "tweaked" and posted over the weekend and then I have to move on to planning some of our (SQLskills) Immersion Events... At least I don't have to plan an event the size of TechEd! Our events are 5-days, hands-on, and intense technical events where we dive deep into one technology (and you can count the number of people with 2 digits instead of 5). In fact, I think that's the combination I like. Go to conferences for depth in small chunks and over a wide variety of topics and technologies (and futures too, so that's always fun/helpful). Go to training/"immersion events" for deep, technically-focused, hands-on courses to really become fluent in a specific area. I'll post all of the final details when we're all set to open for registrations (next week). But, if you think you might want to attend - subscribe on SQLskills and you'll get an early mail to let you know the details. Subscribe here!

Have a great weekend and HAPPY FATHER'S DAY!
kt

OK - today's session was quite fun... lots of demos and quite a few "tie-ins" where I tried to bring together many things that we've touched on in our series. And - that's really the point of the series - creating a reliable, robust, scalable and available environment takes MANY different features. You really need to architect a complete solution in order to handle the many potential problems that may occur. And, unfortunately, it's a never ending process; you're never done and you're never going to get everything (sorry!). You will need to re-evaluate, monitor, and manage your system as long as it runs to keep it reliable, available and fast. Something will come up...someday...that you didn't think about, evaluate and/or prevent. But, then you'll know and then you'll put something into place to keep it from happening again.

So - to tie back into some of the other sessions and resources, here is a list of everything to date in the series as well as a few specific references I made during the session.

Demo Scripts are here: 20060512_TechNetWebcast-Part10.zip (25.46 KB) (updated on Sat, May 13 at 2:55 PDT)
Credit Database zip is here. NOTE: This is a 48MB zip which expands to a 175MB backup and restores to a 700 MB database (with a lot of free space for testing, etc.).

TechNet Webcast Series

Session 1: A Fast-Paced Feature Overview and Series Introduction (Level 200)
   Presenter: Kimberly L. Tripp, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 2: Security (Level 200)
   Presenter: Bob Beauchemin, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 3: Understanding Installation Options and Initial Configuration (Level 200)
   Presenter: Kimberly L. Tripp, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 4: Upgrade Considerations and Migration Paths (Level 200)
   Presenter: Kimberly L. Tripp, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 5: Effective Use of the New Management Tools (Level 200)
   Presenter: Kimberly L. Tripp, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 6: New Application Design Patterns for Scalability and Availability and the Operational Implications of Service Broker (Level 200)   
   Presenter: Bob Beauchemin, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 7: Technologies and Features to Improve Availability (Level 200)
   Presenter: Kimberly L. Tripp, SQLskills.com, 
   Session's corresponding blog entry, here. 

Session 8: Implementing Database Mirroring, Part 1 of 2 (Level 200)
   Presenter: Mark Wistrom, SQL Server Program Manager - Microsoft Corp., 
   Session's corresponding blog entry,