Sunday, June 08, 2008
Indexes in SQL Server 2005/2008 - Part 2 - Internals

OK, I first posted on some of the limitations to indexes in SQL Server 2005 and 2008 in part one here. Now, I want to dive into index internals for a post (or two). And, I often get the question “who is the best audience for your blog – or, for this post” and well, that’s a bit hard to answer. At SQL Connections in Orlando, I delivered a session titled: Index Internals & Usage and while we (fyi – Paul and I co-chair the SQL Connections portion of “DevConnections”) put it in the "developer-focused track," it was more of a Dev/DBA "hybrid" session with the emphasis on database development and best practices in creating and managing indexes (rather than management/maintenace/operational tuning - which is more for DBAs). Here at TechEd this week, I'm going to focus more on the management/maintenace/operational tuning side with a session called Are your Indexing Strategies Working? I'll also do a complementary blog post for that as well...

Having said that thought, indexes are definitely in a group of topics - very much so related to performance and scalability (index internals, indexing strategies, log maintenance, general database maintenance) which really needs to cross almost all database-related disciplines (dev, admin, ops, etc…). If you work with SQL Server in almost any capacity, you need to get a feel for at least some aspect of indexing for performance.

So, for this post, I’m continuing with some internals. In the first post (in this series), I wrote about limits. Limits/boundaries are interesting to discuss but it's also important to remember that good performance takes a lot more than just staying within the bounds of what’s possible. Creating indexes solely because you can – without reason and only with upper limits in mind – can be even worse than under indexing. So, if you find that you're wanting more about indexes (I have many blog posts that are solely Q&A posts), check out my Indexing category here. Now that you know how many indexes you can create, a better question would be when is it appropriate to create indexes at all?

So, what is “finding the right balance” in indexing? In my opinion, there are three requirements/pre-requisites:

  1. knowing the data
  2. knowing how the users use the data
  3. knowing how the underlying structures and database stores/manipulates and uses indexes

Bringing all of these things together is what I try to do in my workshops, seminars and lectures – in this post, I'll start with a smaller more digestible piece - internals.

Indexes have 2 components: a leaf level and a non-leaf level (or b-tree). The non-leaf level is interesting to understand and discuss (in terms of internals) but simply put, it’s used for navigation to the leaf level (more than anything else). So, we'll start with the leaf level (as does SQL Server - the leaf level is always built first). The leaf level of an index contains something (I’ll explain more coming up) for every row of the table in indexed order (note: I am focusing on traditional indexes in every release from SQL Server 2000 up to and including SQL Server 2008 – with the exception of filtered indexes which I will write about in a later post). Once the leaf level is built, non-leaf level(s) can be built to help navigate to the leaf level but the architecture is rather straightforward. The non-leaf level stores something for every page of the level below – and levels are added (each smaller than the previous because each level only contains one the first entry from every page) until the index gets to a root of one page. While it sounds like this could result in a lot of levels (ie. a tall tree), the limitation on the size of the key (which has a maximum of 900 bytes or 16 columns) helps to keep index trees relatively small. In fact, in the example I’ll show coming up – which has a fairly large (large meaning WIDE) index and has a key definition which is at the maximum size – even the tree size of this example index (at the time the index is created) is only 8 levels high/deep…

To see this tree (and the math used to create it – which is the same thing that SQL Server would go through to create it), we’ll use an example where the leaf level of the index contains 1,000,000 “rows.” I put quotes around “rows” because I don’t want to imply that these have to be data rows – these are really just leaf level rows and I’ll explain more on what leaf level rows can be... The leaf level rows are 4,000 bytes per row (therefore only 2 rows per page) or 500,000 pages. This is not ideal but at least the pages are almost full and we’re not wasting a lot of space – if we had two 3000 byte rows we’d still only fit 2 per page and then we’d have 2,000 bytes of wasted space. Now, as for why these are just “rows” and not specifically data rows is because this leaf level could be the leaf level for a clustered index (therefore data rows) OR these leaf level rows could be rows in a non-clustered index that uses INCLUDE (which was new to SQL Server 2005) to add non-key columns to the leaf level of the index (which therefore creates wider leaf rows (wider than the 900 bytes or 16 column maximum). Again, while this doesn’t currently sound interesting, I’ll explain why this can be beneficial coming up (possibly in another post depending on how long this particular post becomes… J).  

The leaf level of this index would result in a 4 GB structure (and this is only at the time it’s created – if a lot of rows are added and the key is not ever increasing then this structure could become heavily fragmented and therefore much larger/taller). In this case, it’s relatively large (again because of “row” width) and with an index key of 900 bytes you can even see that in this case, the tree would be relatively small and only result in 8 levels – as shown below.

Root page of non-leaf level (Level 7) = 2 rows = 1 page

Intermediate non-leaf level (Level 6) = 15 rows = 2 pages (8 rows per page at 900 bytes)

Intermediate non-leaf level (Level 5) = 122 rows = 15 pages (8 rows per page at 900 bytes)

Intermediate non-leaf level (Level 4) = 977 rows = 122 pages (8 rows per page at 900 bytes)

Intermediate non-leaf level (Level 3) = 7,813 rows = 977 pages (8 rows per page at 900 bytes)

Intermediate non-leaf level (Level 2) = 62,500 rows = 7,813 pages (8 rows per page at 900 bytes)

Intermediate non-leaf level (Level 1) = 500,000 rows = 62,500 pages (8 rows per page at 900 bytes)

Leaf level (Level 0) = 1,000,000 rows = 500,000 pages (2 rows per page)

 

Having said that though, this is NOT a goal. :) In more realistic scenarios [where the key is much smaller and] even when there are more rows, there are fewer levels (3-4 is quite normal). Most importantly, the size of an index (and the number of levels) depends on two things – the width of the key (in terms of the number of bytes) and the number of pages in the leaf level of the indexes. The number of pages in the leaf level of an index depends on the number of rows and the size of the rows (again, in terms of bytes) of the rows in the leaf level.

You can see the size of your index by using one of the following commands:

In SQL Server 2000: DBCC SHOWCONTIG … WITH ALL_LEVELS

In SQL Server 2005/2008: querying the dmv: sys.dm_db_index_physical_levels

To see the syntax of these commands and their output, we’ll use some structures created in the credit sample database. Using credit, you can see exactly how these commands work and how they return the details about every level.

NOTE: you can download a zip of a SQL Server 2000 backup of this database here – and since this is a SQL Server 2000 backup, you can restore this to SQL Server 2000, SQL Server 2005 or SQL Server 2008.

USE credit
go

SELECT *
FROM sys.dm_db_index_physical_stats
    (db_id(), object_id('Charge'), 1, NULL, 'DETAILED')
go

DBCC SHOWCONTIG('charge', 1) WITH ALL_LEVELS, TABLERESULTS
go

Using the DMV or DBCC SHOWCONTIG you can get the same picture of the charge table. Using the detailed (or ALL_LEVELS) parameter, you get the entire structure (all levels) for the clustered index (index_id = 1 is always the clustered index, IF the table is clustered). The reason it returns all levels is that the 'DETAILED' mode has been specified.

The clustered index in this table has 1,600,000 rows (DMV column: record_count or SHOWCONTIG column: rows) and these are stored on 9303 pages (DMV column: page_count or SHOWCONTIG column: pages). If you read to the next level which is level 1 because the leaf level is level 0 (remember index levels always start with the leaf level 0 and then go up to the root), you can see that it's number of "rows" is equal to the number of pages in the leaf level... and this keeps going until you get to a root of 1 page. In this case, the clustered index (which is the widest structure of the table) has a very narrow clustering key (the key is on charge_no which is an int) only has a total of 3 levels even though the table has 1,600,000 rows. Ideally, you should run this on a few of your production tables (in a development/test environment) and you can start to get some insight into how big your structures are. However, a BIG factor that you might see in production is fragmentation. If a particular level (or levels for that matter) are heavily fragmented then each level might be wider and less compact (and therefore less performant). Reviewing the DMV columns avg_fragmentation_in_percent and avg_page_space_used_in_percent, you can get a feel for how full each page is. Poor page density reflects that your pages are not as full as they could be but there are many factors for why this is the case: bad row size, splits due to inserts, splits due to updates of varchar columns or even a poorly chosen fillfactor that has left too much space on the pages. However, page density is only one piece of the puzzle and if your avg_fragmentation_in_percent is very low (0-5%) then I wouldn't be over worried about your pages not being entirely full unless you have the time to possibly re-design tables (eg. vertically partition them) and then rewrite your applications to direct your statements at only the appropriate base table. But, another factor to consider is the rate at which your fragmentation occurs as well as when you can fix that fragmentation. This is a HUGE discussion that requires time... And, I want to get back to index structures for now. However, both Paul and I have blogged quite a bit about rebuilding v. defragging indexes and what those operations do/how, etc. In fact, just today, Paul has blogged a Q&A about myths and misconceptions about index rebuild operations. So, I'll get back to internals for now! :)

You can use LIMITED (which is the default mode), SAMPLED, or DETAILED. All three have excellent uses and all use IS locks (to minimize blocking). Limited gives you a quick overview of fragmentation and mostly describes how intact and in order the levels are. Limited is quite clever in that it only scans the first non-leaf level above the leaf to determine how much fragmentation there is... since the non-leaf level always tracks the first entry (and a pointer to the page) then they know EACH and EVERY page in the leaf level by ONLY reading the non-leaf level (which is [typically] a lot smaller and therefore faster). However, because they don't touch every page and determine page density then they only track how out of order the levels are and not how dense/full the pages are (which is also a form of fragmentation). So, if you want a bit more details, you can use SAMPLED. The SAMPLED mode returns the fragmentation from reading every 100th page of the index (or heap). If the table has less than 80MB used (which is 10,000 pages), every page is read instead (which is a DETAILED scan). The DETAILED mode reads every page of every level to calculate the most accurate picture of your tables fragmentation. This is the best form of analysis but also takes the most time.

If you’re interested in learning a few more of the tips/tricks with using this DMV, check out the following script: Using dm_db_index_physical_stats.zip (2.23 KB)

A favorite tip is that the database in which you want to analyze tables does NOT have to be in 9.0 compatibility mode in order to use this DMV. Don’t get me wrong, you will get errors if you try to use this DMV in a database that’s not in 9.0 compat mode; however, if you are in master (which is set appropriately and cannot be changed) and then use the first parameter to target a non-9.0 compat mode database, then this DMV works great. However, a second "gotcha" is for parameter 2... as long as you don’t use 2-part naming for the objectname (2nd) parameter, everything will work as expected. If you specify object_id('tablename') from master for a table that's in credit then object_id will return NULL. The query will still run but against all tables in credit rather than the one you thought you were targeting. If you want to use this DMV across databases, you will need to supply the database name in the first parameter and then make sure that you use 3-part naming for the second parameter.

Now that you are getting to know some of the structures (in terms of seeing physical structures and internals), where do we go from here? The best route to start “finding the right balance” for performance is to know the data and as well as get some general insight into usage patterns (this is probably the hardest component to know and sometimes you only know exactly what’s going on if you profile what’s actually happening in production – is that too late? To a certain extent yes and to another extent no…there are still many things for which you can plan and other things you can confirm or test once the application is running (i.e. Profiler). All of those things together are going to help to “find the right balance”.

Having said that, and having discussed the general internals of a b-tree (and therefore an index structure), what’s the difference between a clustered and non-clustered index? Well… stay tuned, that will be part 3 in this series. And, then (finally), we'll get to appropriate uses for INCLUDE (which was new for SQL Server 2005) and then appropriate uses for Filtered Indexes (a new feature in SQL Server 2008). Also, somewhere in there I'll post a few tips from my TechEd session so that you can start to determine if your indexing strategies are working??

Thanks for reading!
kt

Conference Resources/Q&A | Events | Indexes | SQL Server 2005 | SQL Server 2008
Sunday, June 08, 2008 9:43:23 AM (Pacific Standard Time, UTC-08:00)  #    Comments [2]  | 
Saturday, June 07, 2008
Time for TechEd ITPro and, it's hot as hell here in Orlando.........

Memorial Day weekend we were in Chicago to celebrate my Father's life. We did a "Celebration of Life" memorial and we had a few drinks (celebratory Meyers, Tonic and lime - which was my Father's favorite drink), we (7 of us) gave a few heartfelt speeches, and a few friends wrote a song (and passed out the words - to which we all sang along) and we grieved... but, in a refreshingly-not-overly-depressing way. I have to admit - it was exactly what I'd want as well. It was a wonderful day filled with memories and friends. After that, we visited with my Mom as well as my Grandmother. Paul blogged a couple of pics (yes, that chair is VERY big!). Then, we were back in Seattle for only one week...back to work...and preparing for TechEd 2008 ITPro week.

As for TechEd being spread over 2 weeks, well... I think it offers some excellent logistical options (smaller size means more possible venues AND/OR it means that they could possibly grow the size for each event). And, for some topics, I think there is a very strong separation between developer and ITPro (admin/ops) but for SQL - I think it's hard to get it perfectly right. I think there's a lot of developers who need to know more about admin/ops just so that they can develop more optimal (and even manageable solutions) and I think that DBAs should have a really good architectural overview of a lot of features to better administer them. So, for SQL, I'd *love* to hear your comments on what you think............

For Paul and I, the decision is relatively simple, we came for this second week for ITPro/Ops. But, we've also spoken at the developer events (and sometimes we even write/present sessions specifically targeted at developers at our SQL Connections shows and/or at User Groups (we just did a local .NET user group in Redmond and the discussion around Indexes became so popular that we're going back in August (for Indexes) and again (tbd) for Disaster Recovery techniques). Basically, developers tend to say...oh, that's why I should x or y or z....... so, maybe next year we'll hit both? Regardless, I'd still like to know what you think? Were you at the Developers event? Do you wish you could be at both? Are you at both?

As for what we're doing - Paul's already blogged it here: http://www.sqlskills.com/blogs/paul/2008/06/05/OffToTechEdUSITProTomorrow.aspx.

But, I thought I'd do a quick recap so that you can get some insight into our week as well as where to find us to come and chat. We'd love to meet you and/or hear your success (or disaster) stories!

Monday

  • Full day pre-con seminar: SQL Server 2008 Overview for DBAs

This is ACTION packed (and a very full day!) and will include giving out the updated SQL Server 2008 HOLs DVD. We weren't really sure we were going to be able to do it... we didn't really burn too many of the CTP6 version of the DVDs, nor were we sure that CTP6 would still be the most relevant. But, it's still excellent to learn on and this time our DVD includes 17 labs:

HOL Lab Filename

Length

Lab Name

 Lab Abstract/Description
Using Policy-based Management.doc 75 minutes SQL Server 2008 Policy-based Management Security, best-practices, proper configuration settings - how do you control these things on one or more server? These hands-on labs show you how to implement and leverage the new policy-based management framework to define and control your business rules and your server's compliance for one or more instances of SQL Server 2008. 
Data Recovery Preventative Techniques.doc 75 minutes SQL Server 2008 Data Recovery and Preventative Techniques Hands-on Lab Can you recover from a dropped table? Can you reconcile tables that have become out of sync due to human error? These hands-on exercises show you how to bring a database back online quickly after a table is dropped as well as how to reconcile the differences between a production environment and a recently restored version of your database - so that you can manually merge the recovered data back into your production database. Once all of the recovery techniques are shown, the last exercise shows how DDL triggers can prevent some of these human errors altogether.
Table and Index Partitioning.doc 75 minutes SQL Server 2008 Table Index & Partitioning Hands-On Lab Table and Index Partitioning allows large tables to be managed more granularly. These hands-on labs show you how to implement and leverage these key features: a partition function, a partition scheme and the sliding window scenario.
Database Mirroring Part I.doc 75 minutes SQL Server 2008 Database Mirroring, Part I Database Mirroring allows you to create a secondary (mirror) database to handle requests either automatically or manually, in the event of a diaster at the principal database. These hands-on labs show you how to implement and leverage as well as when and how to use Database Mirroring. You will setup database mirroring in a High Availability configuration (synchronous mirroring with a witness), see the affects of failover, and see how automatic page repair restores damaged pages in the principal or the mirror.
Peer to Peer Replication.doc 75 minutes SQL Server 2008 Peer-to-Peer Replication Hands-On Lab Replication gives you a scale-out configuration where multiple servers participate in bi-directional transaction replication. Setting up and configuring this topology has a few requirements - many of which are minimized by using the Replication Wizards - but all need to be understood to configure a peer topology correctly. These hands-on exercises show you how to implement a peer topology correctly.
Using Performance Data Collection.doc 75 minutes Performance Data Collection in SQL Server 2008 Performance Data Collection brings together many key tuning features into one cohesive toolset. These hands-on labs show you how to create a Management Data Warehouse, setup and control the collection intervals and analyze the results of system data collection sets.
Instant Initialization.doc 45 minutes SQL Server 2008 Instant Initialization Instant Initialization allows data files of any size to be created instantly - eleminating zero-initialization. These hands-on exercises show you how to configure your server's permissions to leverage instant as well as the security vulnerability created by enabling this feature.
Online Operations.doc 75 minutes SQL Server 2008 Online Operations Hands-On Lab Online Operations are critical to the success of any server that needs to be highly available. These hands-on labs show you how to implement and leverage these key features: online index operations, partial database availability and online piecemeal restore.
Database Development Clients Lab.doc 120 minutes SQL Server 2008: Database Development Hands-On Labs The goal of these hands-on lab materials is to get an understanding of when to use one or more of the advanced features of SQL Server 2008 Database Development. After completing these self-paced labs, you will be able to:
* Set up a Database Project using Visual Studio 2008 Team System Database Edition
* Make changes to the database schema and deploy those changes
* Create and edit a project that uses the LINQ to SQL Object Relational mapper
* Use LINQ to SQL to query and maintain a SQL Server database using the managed classes
* Use LINQ to SQL with stored procedures
* Create and edit a project that uses the ADO.NET Entity Data Model mapper.
* Use the ADO.NET Entity Data Model to model a many-to-many relationship in the database
* Use the ADO.NET Entity Framework classes and LINQ to Entities to query and update a database
* Use Visual Studio 2008 to quickly get an ADO.NET Sync Services application up and running.
* Set up SQL Server 2008 Change Tracking
* Use ADO.NET Sync Services with SQL Server 2008 Change Tracking
Snapshot Isolation.doc 75 minutes SQL Server 2008 Snapshot Isolation Hands-On Lab The goal of these hands-on lab materials is to get an understanding of the appropriate uses of transaction isolation levels as well as how snapshot isolation affects conflicting readers and writers.
Database Mirroring Part II.doc 120 minutes SQL Server 2008 Database Mirroring, Part II Part II of the Database Mirroring HOLs allows you to go through setup, implementation and numerous failover scenarios - step-by-step. While Part I offers quicker setup through SQLCMD scripts, Part II works through the setup process more slowly allowing you to see how things work together. This lab is longer but also goes through changing the mirroring configuration as well as forcing failover. Part I should be completed first and Part II should be completed only if time permits.
Service Oriented Database Architecture.doc 120 minutes SQL Server 2008 Service Oriented Database Architecture Hands-On Lab Manual The goal of these hands-on lab materials is to get an understanding of how and when to use Service Broker in deploying a service-oriented database application.
Database Snapshots.doc 75 minutes SQL Server 2008 Database Snapshots Hands-on Lab The goal of these hands-on lab materials is to get an understanding of how to use the Database Snapshot feature of SQL Server 2008. After completing these self-paced exercises, you will be able to:
* Understand how to create a database snapshot
* Understand how to investigate file sizes and sparse file configuration (using both T-SQL queries and Windows Explorer)
* Understand the benefits and challenges with creating multiple snapshots
* Understand how a database snapshot is created when transactions are in flight as well as when they're not
* Understand how to use database snapshots for testing and reverting databases
* Understand the requirements to drop database snapshots and drop databases that have database snapshots
* Understand how to create a database snapshot on a mirror database
Dynamic Management Views.doc 75 minutes Understanding and Using DMVs Hands-on Lab The goal of these hands-on lab materials is to get an understanding of the more advanced new features of SQL Server 2008 that give access to server information that can be used for performance tuning, server health monitoring, and problem diagnosis. After completing these self-paced labs, you will be able to:
* Determine what DMVs exist, what their input parameters are, how and where their data is stored, and be able to persist DMV data to your own tables.
* Access information from the query plan cache, including determining frequently executed queries and their query plans.
* Access physical statistics information about indexes (e.g. fragmentation).
* Access information about tempdb space utilization.
Resource Governor in Action.doc 45 minutes SQL Server 2008 Resource Governor Hands-on Lab The goal of these hands-on lab materials is to get an understanding of when to use one of the more advanced features of SQL Server 2008: Resource Governor.  After completing these self-paced labs, you will be able to:
* Understand appropriate uses for Resource Governor
* Create Resource Pools
* Create Workload Groups
* Monitor Resource Usage
Understanding Spatial Data.doc 120 minutes SQL Server 2008: Understanding Spatial Data Hands-on Lab The goal of these hands-on lab materials is to get an understanding of one of the more new features of SQL Server 2008:  Spatial Data Support. After completing these self-paced labs, you will be able to:
* Understand what spatial data is
* Understand the different types of spatial data
* Create instances of spatial data
* Investigate the properties of spatial data
* Query the relationships between different instances of spatial data
* Integrate spatial data into a managed code application
* Move spatial data between managed code and the database
* Create spatial data graphically using WPF 
Using SQLCMD.doc 75 minutes Understanding Command-line Management with SQLCMD in SQL Server Hands-on Lab The goal of these hands-on lab materials is to get an understanding of one of the more new features of SQL Server 2008:  Spatial Data Support. After completing these self-paced labs, you will be able to:
* Use SQLCMD with an initialization file, system environment variables and parameters to create customized “master” scripts for automation and administration
* Use SQLCMD and SQL Server Management Studio with the Dedicated Admin Connection for troubleshooting
* Use SQL Server Management Studio to modify and execute SQLCMD mode scripts
* Upgrade databases from SQL Server 2000 using a parameterized script running with SQLCMD

This is a GREAT resource for playing with a lot of these new technologies and it's exciting that we have enough copies to give away to our pre-con attendees! A few of these are featured as HOLs at this year's TechEd as well and some of these can also be found with our prior Jumpstart resources.

Tuesday

  • 13.15 - 14.30 (Room N230) DAT354 Are Your Indexing Strategies Working? (featuring me as speaker/presenter)
  • 15.00 - 16.00 (TechEd Online Stage) Panel: Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy
  • 16.00 - 18.00 DAT track booth (green)

Wednesday

  • 10.15 - 11.30 (Room N220D) DAT375 Corruption Survival Techniques: From Detection to Recovery (featuring Paul as speaker/presenter)
  • 11.30 - 14.45 DAT track booth
  • 15.00 - 16.00 Blogger's Lounge

Thursday

  • 10.15 - 11.30 (Room S230E) DAT363 Essential Database Maintenance (we're co-presenting this one)
  • 11.45 - 13.00 Speader Idol judging (I'll do my best to play Paula and I have hopes that Paul won't play Simon but he does have that British thing going for him)
  • 14.30 - 18.00 DAT track booth

Friday, we fly home... then, we're going to test all of our dive gear and take a little dive vacation at the end of the month. Hopefully, I'll be able to post a couple of nice underwater shots!

Oh, and I've finally tweaked my Indexing post (the one that survived the drive corruption - oh, but as an update to that post... Even though I got that drive repaired, almost all of the jpgs, some of the pdfs and even a few of the Office files were still corrupt. Office opened a few of them and "repaired" them on open (which was really cool) but I did lose the photos I had taken that weekend (well, all of the ones that I removed from my camera's SD card). Anyway, I plan to (well, hope to) post the Indexing post (part 2 to this one) tomorrow!

Cheers,
kt

PS - It's hot as hell here... and the humidity is NASTY!!! But, it beats the SOLID rain that we've been having in Seattle...

Conference Resources/Q&A | Events | Resources | SQL Server 2005 | SQL Server 2008
Saturday, June 07, 2008 5:57:22 PM (Pacific Standard Time, UTC-08:00)  #    Comments [0]  | 
Saturday, May 17, 2008
Something to think about as we approach Memorial Day...

Paul just blogged about all of our scanning here...

Overall, this is a very daunting and time consuming task (scanning and touching up hundreds of images) but it's also very therapeutic. However, I have to admit that I'm sorry that I didn't do a lot of this sooner. I can't believe how much I've enjoyed going through thousands of photos - triaging them down to: scenery that I don't care about, people I can't recognize, and the most important - stuff I really care about.

Once I got the piles down to more manageable sizes, I scanned 5-6 photos at a time (on a very reasonable (< $150) HP flatbed scanner). Once scanning, I've been separating them and then tweaking them in Elements. I've spent many, many, many hours doing it - but I'm really enjoying seeing things I did (many have me in them as a baby/child) and/or things my Father/family has done. It's really something that I wish I had done while my Father was still alive but at least I'm able to do it now. Maybe a good thing to think about as we all approach Memorial Day!

Speaking of Memorial Day - my Grandfather, a Major in the US Air Force, [Clarence] Wayne Tripp (April 3, 1922 - February 22, 1985) was a fighter pilot in WWII and also served in Korea and Vietnam. In going through all of these photos, I found one of my Grandfather that is just WONDERFUL. I thought I'd share:

Cheers to everyone,
kt

Personal
Saturday, May 17, 2008 6:17:23 PM (Pacific Standard Time, UTC-08:00)  #    Comments [2]  | 
Friday, May 09, 2008
When did you last backup your home/personal/less-critical system... is it really less-critical?

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?

Opinions | Personal | Resources
Friday, May 09, 2008 2:11:21 PM (Pacific Standard Time, UTC-08:00)  #    Comments [10]  | 
Sunday, April 27, 2008
Catching up on resources - the Accidental DBA workshops!

OK, we were in Iceland and then Florida for our Accidental DBA workshops and both went really well. People agree that there are quite a few involuntary/accidental DBAs out there and overall, we helped quite a few to see a lot of options for better performance, availability, recovery, and/or just manageability.

So, this is our "resources post". We waited until after the SQL Connections delivery to post these as we figured we might add a few more to the list (as is typical when you deliver content more than once - it's really never the same twice!).

Also, I used a few "interactive" (or build) slides in my presentation - specifically on transaction log backups and the concepts of "clearing the log" which really only clears the inactive portion of the log. To help you visualize this, I've added these slides here: TrippRandal_ClearingTheLog-BuildSlides.zip (647.2 KB).

Finally, we've taken all of the scripts that we demo'ed and placed them on SQLskills on our Past Events page here: http://www.sqlskills.com/pastConferences.asp.

And, if you were there and you think we missed something, feel free to ping me (or Paul!) with an email and we'll make sure to update this resources post (and/or [at least] help you find it what you're looking for!!).

Next stop - Microsoft TechEd ITPro in June (we're back in Orlando again)!
kt

Conference Resources/Q&A | Events | Optimizing Procedural Code | Resources | SQL Server 2005
Sunday, April 27, 2008 5:29:12 PM (Pacific Standard Time, UTC-08:00)  #    Comments [3]  | 
Wednesday, April 16, 2008
Indexes in SQL Server 2005/2008 - Best Practices, Part 1

In my blog post on my new sp_helpindex proc (sp_helpindex2), I mentioned that the indexes in my sample were not necessarily a recommended set of indexes - just a test set of indexes. So... in this post, I thought I'd start a series on indexes, limitations and best practices/uses... Especially, why/how to best choose when to use INCLUDE v. having columns in your key. To start, I thought I'd give some background, as well as limitations that exist in various releases from 2005 to 2008 CTP6 (Feb CTP), plus what's expected in the SQL Server 2008 RTM (ah... I did say "expected" so don't come back and yell at me if/when I'm wrong ;-))

First, let's go through a few rules and limitations and background:

SQL Server 2005

  • 250 total indexes per table: 1 clustered index and up to 249 nonclustered indexes (no, this is not a goal!)
  • The index key can be up to 16 columns OR 900 bytes - whichever comes first...
  • The leaf level is limited just as a table is limited to 1024 columns (and, all column types are acceptable in the leaf level of an index - even LOB columns)
  • Statistics are kept for every index (so, up to 250 index-related stats) and there can also be statistics on columns or sets of columns in addition to the index-related stats. In earlier releases, statistics used index ids and as a result, the number of statistics were limited to 250 total minus the statistics used by indexes... in SQL Server 2005, they changed to having statistics kept/managed separately (use sys.stats to see them). As a result of using sys.stats, you can now have 2000 statistics on a table, in addition to the 250 (total) indexes and their statistics. If you want to test this out (and check it on various versions of SQL Server), use this script to setup a test database, a test table and then use dynamic string execution to loop through (until it errors) with creating nonclustered indexes and statistics: testindexmax.zip (.47 KB)

SQL Server 2008 CTP6

  • So far, it seems as though most of the maximums have not yet been lifted...
  • 250 total indexes per table: 1 clustered index and up to 249 nonclustered indexes (and this number  - for CTP6 - includes filtered indexes AND spatial indexes too!)
  • The index key limit hasn't changed (it can be up to 16 columns OR 900 bytes - whichever comes first)
  • The leaf level is still limited just as a table is limited to 1024 columns (and, all column types are still acceptable in the leaf level of an index)
  • Statistics in CTP6 seem to be limited to only 2000 stats per table...

SQL Server 2008 RTM (expected/target... no guarantees on this one :)

  • 30,000 columns per table (mostly to allow sparse columns)
  • 1,000 total indexes per table: 1 clustered index and up to 999 nonclustered indexes. This is also not a goal BUT, it makes sense because of both sparse columns and filtered indexes. Both Paul and I will try to post some entries about sparse columns and filtered indexes in the coming days...
  • The index key limit won't change
  • The leaf level is will be limited just as a table is limited to 30,000 columns (and, all column types are still acceptable in the leaf level of an index)
  • Statistics are also said to be increasing and likely to 30,000... And, for having extra statistics just sitting around and possibly not being used - well, outside of a minimal amount of disk space taken by the stat blob (which does start to get interesting at 1,000s I suppose), even stats that don't get used don't really create much of a problem. So, I'm OK with this one increasing - even significantly - but I have to admit I'm somewhat nervous about the significan't increase in indexes.........

So... you can have A LOT more indexes in SQL Server 2008 but just because you can - DOES it mean that you should?!

And on that - I'll leave you hanging for my next post where I start to talk about WHY they're increasing this (hint: sparse columns and filtered indexes = more columns/more indexes)....

Have fun,
kt

Indexes | SQL Server 2005 | SQL Server 2008
Wednesday, April 16, 2008 1:44:41 AM (Pacific Standard Time, UTC-08:00)  #    Comments [0]  | 
Wednesday, April 02, 2008
sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex

OK - so this has been frustrating me for many months... when you create indexes with included columns (which was a new feature of SQL Server 2005), they're not shown by sp_helpindex or by DBCC SHOW_STATISTICS. I understand this not showing for statistics because included columns are not factored into the histogram (that's only the high order element which is the first column in the index) OR the density vector (which only shows the densities (or averages) for the left-based subsets of the key). So, why doesn't sp_helpindex show it? Well... I guess it just didn't get updated for SQL 2005. So, in SQL 2008, I was hoping I'd not only see included columns but also filtered indexes... well, neither is there and sp_helpindex is still the same old proc. Don't get me wrong, you can use SSMS to see all of the index properties for a single index (pane, by pane for each property) OR you can run queries to find the included columns for a given index:

SELECT
(CASE ic.key_ordinal WHEN 0 THEN CAST(1 AS tinyint) ELSE ic.key_ordinal END) AS [ID],
clmns.name AS [Name],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N'IsComputed') AS bit) AS [IsComputed],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded]
FROM sys.tables AS tbl
   INNER JOIN sys.indexes AS i 
      ON (i.index_id > 0 AND i.is_hypothetical = 0) AND (i.object_id = tbl.object_id)
   INNER JOIN sys.index_columns AS ic 
      ON (ic.column_id > 0 AND (ic.key_ordinal > 0 OR ic.partition_ordinal = 0 OR ic.is_included_column != 0)) 
         AND (ic.index_id = CAST(i.index_id AS int) AND ic.object_id = i.object_id)
   INNER JOIN sys.columns AS clmns 
   ON clmns.object_id = ic.object_id AND clmns.column_id = ic.column_id
WHERE (i.name =