So, about a week ago, I had the honor of being your host for T-SQL Tuesday for the month of September. And an amazing 22 bloggers responded to that invitation. I want to thank each of the bloggers for the time they put into their contribution. We really had some high quality contributions this month.
And another thanks to Adam Machanic for letting me host this month.
Here are the participating blog posts:
1. Brad Schulz Little Known Index Facts
First to go live with his post was Brad Schulz, awesome as ever. He talks about Fill Factor percents and Unique Indexes. He shows his knack for writing in a way that after having read his post, you’ll never forget what he explained.
What Struck Me: Before he gets into the topic at hand, he throws his support behind team indexes (and takes a few shots at team indices) a must read by itself, I pointed my family at the post and heard out-loud laughs.
First to go live with his post was Brad Schulz, awesome as ever. He talks about Fill Factor percents and Unique Indexes. He shows his knack for writing in a way that after having read his post, you’ll never forget what he explained.
What Struck Me: Before he gets into the topic at hand, he throws his support behind team indexes (and takes a few shots at team indices) a must read by itself, I pointed my family at the post and heard out-loud laughs.
2. Rob Farley (@Rob_Farley) Table? No such thing…
Rob Farley explains how there is no such thing as a table. Essentially he explains how a clustered index is the table. (Good on ya Rob).
What Struck Me: Spoofing the Matrix:
“Don’t try to look up information in the table. That’s impossible. Only try to realize the truth.”
“What’s that?”
“There is no table.”
Rob Farley explains how there is no such thing as a table. Essentially he explains how a clustered index is the table. (Good on ya Rob).
What Struck Me: Spoofing the Matrix:
“Don’t try to look up information in the table. That’s impossible. Only try to realize the truth.”
“What’s that?”
“There is no table.”
3. Pinal Dave (@pinaldave) Disabled Index and Index Levels and B-Tree
Pinal Dave tackles the question “What will be the status of the B-Tree structure when index is disabled?”
What Struck Me: He investigates and explains the answer thoroughly. He also includes a personal touch with his post when he talks about a mentor Vinod Kumar.
Pinal Dave tackles the question “What will be the status of the B-Tree structure when index is disabled?”
What Struck Me: He investigates and explains the answer thoroughly. He also includes a personal touch with his post when he talks about a mentor Vinod Kumar.
4. Noel McKinney (@NoelMcKinney) Constraints & Unused Indexes
Noel (team indexes) explains how an unused index (an index with few reads & writes) can still be useful.
What Struck Me: I like Noel’s writing style. His articles make me feel like he’s sitting right beside me explaining something at my computer. Maybe it’s his first-person narrative or the fact that he throws in his own experiences. Whichever it is it works.
Noel (team indexes) explains how an unused index (an index with few reads & writes) can still be useful.
What Struck Me: I like Noel’s writing style. His articles make me feel like he’s sitting right beside me explaining something at my computer. Maybe it’s his first-person narrative or the fact that he throws in his own experiences. Whichever it is it works.
5. Nicholas Cain (@SirSQL) Applying Indexes To Replicated Tables
Any time I read from someone who has tackled replication in a successful way, I’m immediately impressed. Nicholas explains how to extend a replication solution so that the subscriber has indexes that the publisher doesn’t.
What Struck Me: A must read for any one who uses replication (or who is considering replication)
Any time I read from someone who has tackled replication in a successful way, I’m immediately impressed. Nicholas explains how to extend a replication solution so that the subscriber has indexes that the publisher doesn’t.
What Struck Me: A must read for any one who uses replication (or who is considering replication)
6. Richard Douglas (@SQLRich) Unused Indexes (Indices)
For the first time, Richard Douglas (Team Indices) contributes a post for T-Sql Tuesday. He explains how indexes are not all rainbows and unicorns all the time. He shines light on problem indices that don’t do anyone any good: Unused Indices.
What Struck Me: With a working vocabulary that includes words like “amended” and “gracious”, Richard gives “credence” to the thought of switching to Team Indices.
For the first time, Richard Douglas (Team Indices) contributes a post for T-Sql Tuesday. He explains how indexes are not all rainbows and unicorns all the time. He shines light on problem indices that don’t do anyone any good: Unused Indices.
What Struck Me: With a working vocabulary that includes words like “amended” and “gracious”, Richard gives “credence” to the thought of switching to Team Indices.
7. Erin Stellato (@erinstellato) Desperately Seeking Seeks
Erin Stellato explains the steps she takes in her search for a seek (while simultaneously reminding us of 80’s movies).
What Struck Me: What I like about Erin’s take is that she includes in her explanation an Oracle solution. Keeping other platforms in mind keeps everyone honest.
Erin Stellato explains the steps she takes in her search for a seek (while simultaneously reminding us of 80’s movies).
What Struck Me: What I like about Erin’s take is that she includes in her explanation an Oracle solution. Keeping other platforms in mind keeps everyone honest.
8. Michael J Swart (@MJSwart) Guts Of A Clustered Index
A post by yours truly with fancy Visio pictures. I try to explain what physically happens when a clustered index grows.
What Struck Me: A frisbee.
A post by yours truly with fancy Visio pictures. I try to explain what physically happens when a clustered index grows.
What Struck Me: A frisbee.
9. Bob Pusateri (@SQLBob) Potty Chairs And Duplicate Indexes
Provides an embellished script that has been making the blog rounds to report on redundant indexes. Good job Bob!
What Struck Me: A gold painted potty chair! I can’t describe it properly, you just have to see for yourself.
Provides an embellished script that has been making the blog rounds to report on redundant indexes. Good job Bob!
What Struck Me: A gold painted potty chair! I can’t describe it properly, you just have to see for yourself.
11. Jen McCown (@MidnightDBA) Brief Intro To Indexes and INCLUDE
Jen pulls her post from the archives to give an introduction to indexes.
What Struck Me: She explains how INCLUDED columns in an index are only included at the leaf levels! This is something I didn’t know until now. It reminds me of something mentioned by Brad Schulz’s post where Unique Indexes can also do without columns in the index nodes.
Jen pulls her post from the archives to give an introduction to indexes.
What Struck Me: She explains how INCLUDED columns in an index are only included at the leaf levels! This is something I didn’t know until now. It reminds me of something mentioned by Brad Schulz’s post where Unique Indexes can also do without columns in the index nodes.
12. Gail Shaw (@SQLintheWILD) One wide index or multiple narrow indexes?
Gail writes an authoritative post on indexing strategy. Hers is one of the few contribution that addresses the question “Which index is best?”
What Struck Me: I like her conclusion about the so-called “strategy” of having single-column indexes on each column of the table. And her post is a must read for anyone who isn’t clear on this point.
Gail writes an authoritative post on indexing strategy. Hers is one of the few contribution that addresses the question “Which index is best?”
What Struck Me: I like her conclusion about the so-called “strategy” of having single-column indexes on each column of the table. And her post is a must read for anyone who isn’t clear on this point.
13. Tom LaRock (@SQLRockstar) Big Mac Index
The SQL Rockstar explains indexes using a Big Mac analogy it’s an analogy I guarantee no-one has considered before (making this post legendary).
What Struck Me: A Microsoft Connect Item on the Big Mac Index. Wow.
The SQL Rockstar explains indexes using a Big Mac analogy it’s an analogy I guarantee no-one has considered before (making this post legendary).
What Struck Me: A Microsoft Connect Item on the Big Mac Index. Wow.
14. Diane McNurlan (@SQLDevGal) Top 10 Worst Indexing Practices
Another member of team indexes, Diane covered important index topics in a clear way that it can almost be used as a checklist (for creating indexes or querying them).
What Struck Me: Diane is a very competent writer and although I’ve got a bunch of other posts to read for this round up I found myself going through more of her articles. And subscribing to her blog’s feed. Us database developers have to stick together.
Another member of team indexes, Diane covered important index topics in a clear way that it can almost be used as a checklist (for creating indexes or querying them).
What Struck Me: Diane is a very competent writer and although I’ve got a bunch of other posts to read for this round up I found myself going through more of her articles. And subscribing to her blog’s feed. Us database developers have to stick together.
15. AJ Mendo (@SQLAJ) Indexes 101
In AJ’s own words, he talks “about what indexes are, why we can benefit from their use and some of the costs associated with using indexes.”
What Struck Me: Along with the standard clustered vs. non-clustered, AJ gives a brief intro to some of the lesser known kinds of indexes: Fulltext, Spatial, XML etc…
In AJ’s own words, he talks “about what indexes are, why we can benefit from their use and some of the costs associated with using indexes.”
What Struck Me: Along with the standard clustered vs. non-clustered, AJ gives a brief intro to some of the lesser known kinds of indexes: Fulltext, Spatial, XML etc…
16. Jason Strate (@StrateSQL) An Index On Indexing
Just like the cheesy 80’s sitcom clip show, Jason gives a list of his series on Index analysis. It’s a good sign though, only the best 80’s sitcoms ever got clip shows (Joanie Loves Chachi notwithstanding).
What Struck Me: In fact in my own “cheesy clip show” post last year I ran out of clips and ended up linking to Jason’s site.
Just like the cheesy 80’s sitcom clip show, Jason gives a list of his series on Index analysis. It’s a good sign though, only the best 80’s sitcoms ever got clip shows (Joanie Loves Chachi notwithstanding).
What Struck Me: In fact in my own “cheesy clip show” post last year I ran out of clips and ended up linking to Jason’s site.
17. Dave Levy (@Dave_Levy) How is Fill Factor Impacting My Indexes?
Dave queries some DMVs to help you analyze how effective your fill factor settings are.
What Struck Me: Okay, I have to call this one out. If you’re a dba, and you read only one index post this month make it mine, but if you have time to read two read Dave Levy’s. His fill factor script belongs in every DBA’s toolkit alongside queries that analyze things like missing index or fragmentation levels.
Dave queries some DMVs to help you analyze how effective your fill factor settings are.
What Struck Me: Okay, I have to call this one out. If you’re a dba, and you read only one index post this month make it mine, but if you have time to read two read Dave Levy’s. His fill factor script belongs in every DBA’s toolkit alongside queries that analyze things like missing index or fragmentation levels.
18. Jeremy Carter Cut Your Index Bloat
Jeremy explains the process he follows to identify and remove unused indexes.
What Struck Me: Jeremy is probably the most underexposed blogger on this list. There are a ton of MVP participants this month include honest-to-God book authors! Jeremy holds his own quite easily alongside them.
Jeremy explains the process he follows to identify and remove unused indexes.
What Struck Me: Jeremy is probably the most underexposed blogger on this list. There are a ton of MVP participants this month include honest-to-God book authors! Jeremy holds his own quite easily alongside them.
19. Andy Lohn (@SQLQuill) Partitioned Indexes and Data Types
Andy tackles index partitioning in this post. Specifically, he explains two methods of moving from non-partitioned indexes to partitioned indexes.
What Struck Me: I like that this is not a hypothetical situation. Andy and his colleagues are facing this problem as we speak (or as I write). And they are testing to determine which method is faster. The results are not in yet making his post a bit of a cliff-hanger.
Andy tackles index partitioning in this post. Specifically, he explains two methods of moving from non-partitioned indexes to partitioned indexes.
What Struck Me: I like that this is not a hypothetical situation. Andy and his colleagues are facing this problem as we speak (or as I write). And they are testing to determine which method is faster. The results are not in yet making his post a bit of a cliff-hanger.
20. Robert L Davis (@SQLSoldier) To Be or Not To Be (a B-tree)
Robert (aka SQL Soldier) explains how XML indexes are structured.
What Struck Me: Oh the things you can do using the dedicated administrator connection!!
Robert (aka SQL Soldier) explains how XML indexes are structured.
What Struck Me: Oh the things you can do using the dedicated administrator connection!!
21. Aaron Nelson (@SQLvariant) Picture The Indexes
A great analogy for indexes is the phone book. Aaron Nelson runs with it.
What Struck Me: The post and comments stretch that metaphor about as far as it can go.
A great analogy for indexes is the phone book. Aaron Nelson runs with it.
What Struck Me: The post and comments stretch that metaphor about as far as it can go.
22. Jason Brimhall (@sqlrnnr) Indexes And Blobs
Ending this month’s T-SQL Tuesday, Jason (team indexes) refurbishes an old script of his to report on BLOB index columns.
What Struck Me: He mentions something that I’m surprised never got covered by anyone else this month: Blob’s make lousy indexes.
Ending this month’s T-SQL Tuesday, Jason (team indexes) refurbishes an old script of his to report on BLOB index columns.
What Struck Me: He mentions something that I’m surprised never got covered by anyone else this month: Blob’s make lousy indexes.
Encore!
(Update 9/20/2010) One late addition (sorry I missed it, there was no comment at the invite post).
23. Steve Jones (@WayOutWest) Remembering To Index
In Steve’s own words, he reminds us that “Indexing is important, but you can overdo it.”
What Struck Me: He wrote about a third-party db he came across which include the most over-indexed table I’ve ever seen in my life.
In Steve’s own words, he reminds us that “Indexing is important, but you can overdo it.”
What Struck Me: He wrote about a third-party db he came across which include the most over-indexed table I’ve ever seen in my life.
[…] 09/20/2010: Find the roundup here) September already! Summer holidays are over and that means no more lazing around. The second […]
Pingback by Invitation to Participate in T-SQL Tuesday #10 - Indexes | Michael J. Swart — September 20, 2010 @ 8:47 am
I know that I will be reading this blog 100s time and sending people as link.
The best of the best collection on the subject.
Comment by Pinal Dave — September 20, 2010 @ 8:59 am
Thanks Pinal! I had fun doing this. There was a lot of reading but it was fun to do!
Comment by Michael J. Swart — September 20, 2010 @ 9:30 am
Excellent work – nice round up. Way to go the extra mile with the avitars/logos/icons. I needed that intermission as well. Thanks.
Comment by Andy Lohn — September 20, 2010 @ 11:20 am
Thanks Andy.
I wanted to do the job right. It’s good to get feedback like yours.
Comment by Michael J. Swart — September 20, 2010 @ 1:01 pm
Excellent roundup… very well presented… and with an intermission to boot! The only thing missing was refreshments. 8^)
You’ve set the bar high for future hosts.
I’m still laughing about the frisbee… good one!
–Brad
Comment by Brad Schulz — September 20, 2010 @ 1:39 pm
Thanks Brad,
refreshments… hmmm I’ll have to work out how to provide those in the future, in the mean time, root around under the cushions of your couch for some stray popcorn.
Comment by Michael J. Swart — September 20, 2010 @ 1:59 pm
It’s not every day I get to learn about prostitutes (horology), Big Macs and potty chairs in one venue. Reminds me of my younger days. Thanks for the great articles, sort of like a smorgasboard or indexes.
Comment by Dave Schutz — September 21, 2010 @ 11:49 am
Thanks Dave… It almost makes me curious to find out about your younger days. ALMOST!.
Comment by Michael J. Swart — September 22, 2010 @ 4:01 pm
[…] T-SQL Tuesday #10 Round up, An index of participants from Michael J. Swart […]
Pingback by Weekly Challenges – 9/24/10 « SQL Feather and Quill — September 26, 2010 @ 11:20 pm
Hi Michael…
After re-reading the roundup, I’m not sure that the blurb on Rob Farley’s contribution said enough.
I don’t believe he was simply saying that “essentially, the clustered index is the table”… It was way deeper than that… He was saying that when approaching a query you should think (i.e. approach it) in terms of indexes (clustered or nonclustered… heck, even XML indexes) rather than just thinking in terms of tables. We should have some idea in mind as to how we think the optimizer would most likely approach our queries. If it chooses something other that what we predicted, then we should find out why and we might learn something. In short, we should “be” the optimizer… we should think like the optimizer… and consider all the physical manifestations of the data… ESPECIALLY the indexes. By doing this, we become better query tuners (and query writers).
Just my $0.02.
–Brad
Comment by Brad Schulz — September 28, 2010 @ 8:49 pm
You’re right Brad, I’ll explain what was going on in my brain when I read Rob’s post.
He started with the quick argument that the clustered index is the table. Which makes sense (a clustered index is mostly just the ordering of the table). And then after some skimming I read the bit that mentioned how heaps can be thought of as a kind of unordered index (in their own fashion).
And so I took the message as the informative: “Tables can be thought of as indexes.” rather than the advice “Think of your tables in terms of indexes.
The distinction is subtle and I missed it. Thanks for the insight Brad. I suppose – could have used the excuse that these posts are difficult to sum up in a line or two 🙂
Comment by Michael J. Swart — September 28, 2010 @ 9:58 pm
Hi Brad,
We have lot to learn from you! I continuously do.
Kind Regards,
Pinal
Comment by Pinal Dave — September 28, 2010 @ 10:08 pm
@Michael: No problem… I just wanted to give Rob some credit… After all, at least 3-4 people in this T-SQL Tuesday quoted him or cited his work (me included) in their posts. I commend you for your work in putting together this month’s list… it was one of the best months I think. It’s a lot of work to read those posts and summarize them… why do you think I haven’t volunteered yet to host? 8^)
@Pinal: Thank you for your kind words… I just wanted to make the point that we have a lot to learn from Rob!
Best…
–Brad
Comment by Brad Schulz — September 29, 2010 @ 1:43 am
Shush now guys – you’re embarrassing me.
But yes – my point wasn’t at all that tables are CIXs. It was that we should think in terms of NCIXs if anything, and just consider a CIX as just another index.
Good round-up though. I do like the “what struck me” theme.
Rob
Comment by Rob Farley — September 29, 2010 @ 3:35 am
I am finally getting around to looking at this round from September. Thanks for hosting
Comment by Jason Brimhall — October 4, 2010 @ 10:40 pm
Bringing up the rear again eh Jason? :-).
Did you know the next invite is already out? Sankar Reddy will be doing the honors this month.
Comment by Michael J. Swart — October 5, 2010 @ 8:55 am
Yup – I saw that. Maybe I’ll get mine in earlier this time 😉
Comment by Jason Brimhall — October 5, 2010 @ 10:51 am
[…] of Adam Machanic’s (Blog | @AdamMachanic) T-SQL Tuesday (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, and 12) events. Read all of these roundups and linked posts for some great SQL Server […]
Pingback by What are you thankful for? | Strate SQL — November 21, 2010 @ 2:22 pm
[…] our blogs and email messages. He was good enough to submit an article on indexes when I hosted TSQL Tuesday in September last […]
Pingback by Pinal Dave: Blogger, MVP and now Interviewee | Michael J. Swart — February 9, 2011 @ 12:03 pm
[…] and roundup from Michael J. […]
Pingback by T-SQL Tuesday #010 – Indexes – T-SQL Tuesday — March 2, 2017 @ 5:10 pm
[…] Invitation and roundup. […]
Pingback by T-SQL Tuesday Participation Over the Years | SQL RNNR — July 2, 2019 @ 3:32 pm