You are here: Home > indexes
Archive | indexes RSS feed for this section

The Magic of the Self-Assigned Lab (SQLSkills Immersion Training Days 2 and 3)

by on February 24, 2011

The duck has obtained scuba gear-- it can go to new depths on its own.

Today, more blog from SQLSkills Immersion Training on Internals and Performance in Dallas, TX. For more about the training, see my prior post.

Where We’ve Been

We’ve moved through most of Module 8. We’ve been through the land of the transaction log, locking and blocking, isolation and versioning, table design and partitioning, and many neighborhoods of index internals.

A Sample Case

I love learning about how the transaction log works. I know a bit about how the log operates, and as a DBA I keep this knowledge pretty fresh. So I was looking forward to yesterday’s session on transaction logging quite a bit.

This week, I’ve learned a lot about the internals of how the log works with crash recovery, as well as how log buffers work in general. (And I mean a lot. Pages and pages of notes.)

Looking over my notes, what makes me happy is how much I was able to note down and absorb much more than I have been in trainings previously. This is attributable to three things:

  • A lot of time devoted to the topic;
  • Plenty of room for questions (and there were lots!) with lots of rich data in the answer;
  • A good context for the information.

I’m really proud that I know enough now that I’m able to understand and note this level of detail. That’s a great feeling.

The magic of the Self Assigned Lab

When I’m in a great session or a good conversation and I learn something that works differently than I thought, or something very interesting, I made a note to myself with a “To Do”. These are basically self-assigned lab assignments: I’ve learned from blogging and presentations that I learn a ton by setting up my own scenarios and working to show that something works (and look at how it works), or the opposite.

I already have enough self-driven labs to keep me learning, and blogging about the best parts, for several months. Here are a few:

  1. ToDo: find an automation opportunity with DBCC PAGE WITH TABLERESULTS
  2. ToDo: create a scenario where you can’t get rid of a secondary filegroup without unusual operations.
  3. ToDo: Look at/experiment with transaction savepoints

There are lots more good ones, but I’m hoarding quite a few of them. (I have 41 at the time of this writing.)

That’s the magic of great training– not only do you pick up a lot and you receive pre-designed labs you can learn from, but you also find paths you hadn’t imagined to explore and create tools on your own. And you’re inspired to go there.

Comments { 7 }

Index Usage Statistics with ColumnList and Index Size

by on April 25, 2009

As an add on to my last post, here is what I currently do use to track index usage. This shows usage, columns in the index, and index size on disk. The size can be quite useful to know when evaluating how much an index is worth– typically if this index is large then you’re paying a fair amount on the inserts.

If it’s not easy to tell the data types from your column names, that is a modification you’d definitely want to make. Remember that indexes that have a uniqueidentifier at the head are much more likely to cause page splits and be more work to maintain, so those indexes are more “expensive”. (In my current system I do have the luxury of a consistent naming convention where it’s fairly easy to tell the datatypes in indexed columns, so I haven’t added the datatype to the column list.)

The data represents usage since last sql server restart. I am about to start working on a project to automate collection and storage for review over a longer period of time. It’s been on the list for a while and is finally just about in the top priority spot.

It would be useful if the data in the index statistics/missing index DMVs could be cleared every time the data is stored off, as can be done with wait statistics. If you think so too, please lend your support for feedback item 264140, “Clearing dm_db_missing_index_*” at connect.microsoft.com.
Continue Reading →

Comments { 0 }

Everything About Your Indexes (well, almost)

by on April 21, 2009

I am going to post my monstrously big index query.

Why? Because it’s AWESOME. No really, it actually is awesome. At least, if you like that sort of thing. I use some variant of this almost daily, and I tweak it fairly regularly to suit the needs of whatever I’m working on. So it’s a work in progress, but I find it constantly valuable.

Awesome? Oh Really? Why?
This query describes the size, basic definition, location, number of rows, partition status, and enabled/disabled status for all clustered and nonclustered indexes in a database. I typically sort them by descending size, since my primary usage is when a drive space alert fires, or when someone asks one of the million “how much space would it take if we wanted to [x]?” questions.

When you are working with a database which has many indexes that are partitioned over multiple filegroups, which are spread out over multiple drives, this can be very useful when a reindex fails due to a file filling up. Or when you want to estimate how much free space you need to main in a given filegroup in order to be able to reindex the indexes using it.

And it’s also just a really useful big picture reference for what you’ve got, where it is, and how much room it’s taking up.

And it’s *fast*.

Well, Indexes aren’t Everything.
That depends who you ask. ;)

And I’m actually cheating, because I include index id 0, which is actually no index at all. As an extra bonus for the introductory offer, you get HEAPS for free in this query. Yes, you heard it here first.

But I don’t have index usage in this. At least not yet! I’m considering working in a rating since last sql start, but that’s for another day.
Continue Reading →

Comments { 0 }