As many of you know, we started our SQLskills SQL101 series a couple of weeks ago… it’s been great fun for the whole team to go back through our most common questions / concerns and set the record straight, per se. We’ve still got a lot of things to discuss but indexing is one of […]
Kimberly L. Tripp
Improving my SQL skills through your questions!
SQLskills SQL101: Indexes on Foreign Keys
Posted on: March 21, 2017 6:41 am
SQLskills SQL101: Indexing Basics
Posted on: March 13, 2017 7:53 pm
SQLskills introduced our new SQL101 recently and well… indexing is something that everyone needs to get right. But, it’s not a simple task. And, as I start to sit down to write a SQL101 post on indexing, I suspect I’m going to struggle keeping it simple? However, there are some core points on which I will focus […]
USE THIS: sp_helpindex
Posted on: February 27, 2017 11:02 pm
Since I’ve rewritten sp_helpindex a few times, I have a few blogs posts in this category. Each time I do an update I’ll make the NEW version titled USE THIS and I’ll change all others to (OLD). The point of this updated version of sp_helpindex is to add details for newer functionality (included columns and […]
Explicitly naming CL key columns in NC indexes – when and why
Posted on: February 9, 2017 4:49 pm
This morning, as I was prepping for my PASS Data Architecture Virtual Chapter presentation on “Why Physical Database Design Matters,” I went to tweet that folks could join the presentation. I stumbled on a discussion that led to Robert Davis (@SQLSoldier) asking this: I was searching to see if you had a blog post on missing […]
Nonclustered indexes require the “lookup” key in the b-tree when?
Posted on: November 20, 2014 11:40 am
I received a great question in email and it’s something I cover in our IEPTO1 (Immersion Event on Performance Tuning, Part 1) so I thought I’d write a post about it… Question: When you have a non-unique key value in a nonclustered index, SQL Server adds the RID / Row Identifier (if the NC is on a […]
(OLD): New SQL Server 2012 rewrite for sp_helpindex
Posted on: September 10, 2013 10:55 am
Since I’ve rewritten sp_helpindex a few times, I have a few blogs posts in this category. Each time I do an update I’ll make the NEW version titled USE THIS. To use my version of sp_helpindex, you need TWO scripts. One script is version-specific and the other works on versions 2005, 2008/R2 and 2012. All […]
The Accidental DBA (Day 20 of 30): Are your indexing strategies working? (aka Indexing DMVs)
Posted on: June 20, 2013 4:37 am
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
Understanding LOB data (2008/2008R2 & 2012)
Posted on: April 3, 2012 12:30 pm
I know it’s been a while since I last blogged here on SQLskills. It’s been a busy few months with Immersion Events, conferences and even a bit of personal travel in there. And, we’ve also been blogging very regularly (weekly) at SQLServerPro here: Kimberly & Paul: SQL Server Questions Answered. And, this is what’s brought […]
Removing duplicate indexes
Posted on: July 15, 2011 8:47 am
SQL Server has a horrible habit of letting bad habits move forward (for the sake of backward compatibility [e.g. autoshrink]). And, I do understand *some* of the reasons for allowing some of these “features/options” to move forward. However, there are a few that frustrate me and I’ve talked about this one quite often. SQL Server […]
How can you tell if an index is REALLY a duplicate?
Posted on: July 14, 2011 11:39 am
There’s a lot of confusion around duplicate indexes and I think a lot of it has to do with how sp_helpindex (or even SSMS) display what’s in an index. What’s actually in the index – and how it’s structured – are not always what they seem. This was the original motivation behind my rewrite of […]
Trusting the tools!
Posted on: June 9, 2011 4:32 am
Well, it's been a crazy few weeks! Paul and I have been booked solid with client engagements, Immersion Events, conferences and other things (like chapter editing for the upcoming SQL Server Deep Dives Volume 2) and so I've been a bit behind with blogging. I have a ToDo list of items to blog about but […]
Indexes: just because you can, doesn’t mean you should!
Posted on: March 28, 2011 9:10 am
I've decided to create a new series of posts – just because you can, doesn't mean you should! These are going to go in a new category called "Just because" and I've already got a few lined up in this series. The first one is one I've spoken about often and one that's near/dear to […]
(OLD): sp_helpindex rewrites
Posted on: February 27, 2011 11:45 am
I realize that I’ve created quite a few posts (and rewrites) around sp_helpindex and recently I’ve been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it’s not so obvious where to get it and/or what to do to install it. To make it easier… I’m just going to do a […]
More considerations for the clustering key – the clustered index debate continues!
Posted on: November 28, 2010 6:52 pm
OK, I've talked about the clustering key many times. Here, I thought I'd bring together one final series of details (and links) to help you really understand why I'm so adamant about DESIGNING a clustering key and not just letting SQL Server pick it for you (for example when it defaults to making the primary […]
TSQL Tuesday – Why DBA skills are important
Posted on: November 2, 2010 3:23 pm
Paul's hosting the T-SQL Tuesday theme this month and the theme revolves around DBA skills. Specifically, "why are DBA skills necessary?" His specific post is here: Invitation to participate in T-SQL Tuesday #12 – Why are DBA skills necessary?. This is a topic near and dear to me and one that I've found myself […]
(OLD): A new and improved sp_helpindex (jokingly sp_helpindex8)
Posted on: May 18, 2010 8:33 pm
NOTE: I’ve released other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options! OK, I first blogged about re-writing sp_helpindex here as sp_helpindex2 (April 2008). Shortly thereafter (Aug 2008), a reader found a bug and when I went digging, so did I (a couple of others). I did […]
Microsoft eLearning Resources – Clinic 10259 (SQL 2008 DBIS)
Posted on: April 20, 2010 1:11 pm
When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft […]
Filtered indexes and filtered stats might become seriously out-of-date
Posted on: September 24, 2009 9:45 am
Time flies when you're having fun, eh? This week Paul and I are in Dublin, Ireland delivering a deep technical "SQL Immersion Event" on Internals, Performance Tuning and Maintenance with our partner ProData (and having a blast – we have a really fun and very interactive group). And, today's an even more interesting day to be in […]
Companion content for Chapter 6 (Index Internals) of SQL Server 2008 Internals
Posted on: April 12, 2009 11:20 pm
Recently, the SQL Server 2008 Internals title was released (and only in the past few days have people actually received their copies)! In fact, I still haven't seen the book in person… soon though! Anyway, Kalen, Paul, Conor, Adam and I worked to create a comprehensive resource on SQL Server 2008 internals and to supplement the written […]
Spring cleaning your indexes – Part II
Posted on: March 27, 2009 9:33 am
I started the series here: http://www.sqlskills.com/blogs/kimberly/post/Spring-cleaning-your-indexes-Part-I.aspx and I want to continue with Part II today by clarifying some great questions/comments that have come up on the series. In Part III, I'll give you a few more ways to get rid of (or consolidate) indexes. And, I think there's still a bit more that Paul and I […]
Spring cleaning your indexes – Part I
Posted on: March 23, 2009 2:14 pm
First and foremost, happy spring! I truly hope we’re on the path to summer (although who would know it here – we’re in Florida for SQL Connections and the weather is a bit chilly and it’s been raining off/on today – I hope this is short term (no, I don’t want to look at the […]
Database Maintenance Best Practices Part II – Setting FILLFACTOR
Posted on: March 13, 2009 9:45 am
When you decide to rebuild or reorganzie an index, you have an option to set something called FILLFACTOR. This is probably the MOST IMPORTANT thing to understand about index maintenance and reducing fragmentation (especially in databases that are prone to it). Unfortunately, we need to dive into some internals to really understand why this is […]
Database Maintenance Best Practices Part I – clarifying ambiguous recommendations for Sharepoint
Posted on: March 12, 2009 4:01 pm
OK, it seems as though there's A LOT of confusion about what steps are required for proper database maintenance. And, it seems as though some recommendations are being given as "quick fixes" without any real recommendation for root cause analysis. I'm not saying that the generalizations are horribly wrong but in many cases they're just […]
Tipping Point Queries – More Questions To Really Test You!
Posted on: March 9, 2009 8:28 am
OK, so this is interesting. I've got a few answers to my last survey (Tipping Point Query #1) and well, there's a good mix of answers (and, yes, some are correct! ;)). Be sure to go back and review that last post so that you can evaluate it and these two tipping point questions completely. […]
Why aren’t those nonclustered indexes being used?
Posted on: March 8, 2009 5:29 pm
Along the same lines of improving database design and getting better performance on SQL Server (which [IMO] DOES take an experienced SQL Server database developer – but, we'll talk more about "whose job this really is" in many more posts and probably even a RunAs – which Richard and I just setup to record on […]
GUIDs as PRIMARY KEYs and/or the clustering key
Posted on: March 5, 2009 1:06 pm
(Be sure to join our community to get our monthly newsletter with exclusive content, advance notice of classes with discount codes, and other SQL Server goodies!) Expanding on the topic of “are you kidding me”… one of the MOST PREVALENT problems I see today is the dreaded “GUIDs as PKs” problem. However, just to be […]
Seriously, are you kidding me?
Posted on: March 4, 2009 3:03 pm
**** UPDATE March 17, 2009 **** Be sure to read the comments. This doesn't appear to be as bad as it seemed…still glad I did the rant though; I learned more about what it's actually doing! :) And, it's still good to have good database design – NO MATTER WHAT! **** OK… today's been a bit of a weird […]
Foreign Keys can reference UNIQUE indexes (without constraints)
Posted on: February 28, 2009 5:37 pm
Something I learned while the SQL Server 2008 Internals book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez – who, unfortunately, does not have a blog or anything…yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index – one without a PRIMARY KEY or […]
Where in the world does time go?
Posted on: February 5, 2009 12:24 am
I know where I go but where the heck does the time go (it seems to move much faster than I)? First off, I hope everyone had a truly lovely holiday season. I know they've long since past for everyone but maybe this reminder will bring back a nice memory from the holidays as it […]
RunAs Radio Interview Posted – “Kim Tripp Indexes Everything”
Posted on: October 3, 2008 6:51 pm
Well… I think I had had too much tea that morning ;-). But, as always, chatting with Richard and Greg was great. Here’s the specific show link: http://www.runasradio.com/default.aspx?showNum=76. Oh, and just for the record, I didn’t come up with that title. But, I do hope that all your [high-priority and important] queries are indexed! Enjoy!kt
(OLD): Updates (fixes) to sp_helpindex2
Posted on: August 24, 2008 7:23 pm
NOTE: I’ve released other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options! I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and […]
Auto update statistics and auto create statistics – should you leave them on and/or turn them on??
Posted on: August 24, 2008 7:12 pm
YES!!! OK, well, I guess I should be more specific because as in most things in SQL Server, the real answer is “it depends”. And for these two options, it depends mostly on your SQL Server version. Since SQL Server 7.0, the way that auto update works, has changed (much so for the better!!). So, […]
SQL Server 2008 fails to send database mail, Sparse columns with filtered indexes, Upcoming Events, and – as usual – catching up (i.e. cookies)!
Posted on: August 24, 2008 5:27 pm
OK, so, I don't blog very often. I don't know what it is… I think it's that I feel like I always need to blog huge posts and the thought of writing my huge post makes me not want to blog… so, sometimes my time-between-blogging (TBB :) is long. I'm going to turn over a […]
Indexes in SQL Server 2005/2008 – Part 2 – Internals
Posted on: June 8, 2008 5:43 pm
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, […]
Indexes in SQL Server 2005/2008 – Best Practices, Part 1
Posted on: April 16, 2008 9:44 am
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 […]
(OLD): sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
Posted on: April 2, 2008 6:22 pm
IMPORTANT NOTE: Always check the sp_helpindex rewrites category for the USE THIS post! 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, query plans, and are you reading Conor’s blog?
Posted on: February 5, 2008 8:06 am
I know that Paul and I recommended that you subscribe to Conor's blog… but have you? He's posted some great details on Partitioning (Part 1 and Part 2) as well as statistics and it always reminds me of how much I can learn from other people's perspectives! And, just to dove-tail on some of his statistics […]
The Clustered Index Debate Continues…
Posted on: February 2, 2007 7:39 pm
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 […]
Chatting with Greg Low from SQLDownUnder.com
Posted on: May 4, 2006 9:31 am
On Wednesday I had the pleasure of chatting with Greg Low – a fellow RD and MVP who is also excited to be focused and working in the SQL Server space. He’s been doing a few podcasts on database technologies and we were finally able to hook up today. He caught me off guard with […]
MSDN Webcast Series Wrap-up Resources
Posted on: November 5, 2005 4:53 pm
Hey there everyone! I know I still owe you a few Q&A entries (for sessions 7, 8 and 9) but I wanted to get this blog entry out there so that you can play a bit with some of the resources. This series was targeted at developers but really helps to "bridge the gap" between […]
MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 5 of 10
Posted on: September 5, 2005 2:41 am
Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development New Features in Indexing and Index Maintenance Best Practices, Part 5 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? Part 5 can be replayed by clicking here. Q: Where can we get the demo […]
MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 4 of 10
Posted on: August 31, 2005 5:41 am
Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development Indexing Best Practices, Part 4 of 10Presented by Kimberly L. Tripp, SQLskills.com For a list of the non-technical Resources related to this webcast, please review the following blog entry:MSDN Webcast Q&A for Resources: Indexing Best Practices, Part 4 of 10 Technical Questions […]
MSDN Webcast Q&A for Resources: Indexing Best Practices, Part 4 of 10
Posted on: August 26, 2005 9:43 pm
Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development Indexing Best Practices, Part 4 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? The webcast will be available for viewing within 24 hours. All “on-demand” webcasts can be found here. Q: Where […]
MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 3 of 10
Posted on: August 26, 2005 7:09 am
MSDN Webcast: A Primer to Proper SQL Server Development Designing Tables that Scale, Best Practices in Data Types and Initial Table Structures, Part 3 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? Here’s the specific replay link. Q: Where can we get the demo scripts? The […]
Getting ready for Part 4, “Best Practices in Indexing” in the MSDN Webcast Series on Effectively Designing a Scalable and Reliable Database
Posted on: August 25, 2005 3:43 pm
Two days isn't enough time! In fact, that's really true. There are so many interesting (well, I think they're interesting) aspects to Indexing that we could in fact do a whole 10-part Series on Indexing and it's likely that we'd still have other things to look at and talk about. So, in preparation for part […]
An interview with theServerside.net has been posted!
Posted on: June 24, 2005 6:09 pm
Many of you in the .NET Community are already familar with theServerSide.NET but many of you in the SQL Server community are not…well, here’s your chance to bridge the gap over to .NET and hear a bunch of great .NET interviews! My interview is more SQL-centric but there are lots of great ones on Development […]
Ever-increasing clustering key – the Clustered Index Debate……….again!
Posted on: June 6, 2005 5:48 pm
As I'm preparing for my Tech*Ed session on Indexing Best Practices in SQL Server 2005, I'm reminded that there are a ton of best practices that really apply to both SQL Server 2000 as well as SQL Server 2005. When it comes to indexing, there are many dependencies on the storage structures. These dependencies are […]
When did SQL Server stop putting indexes on Foreign Key columns?
Posted on: October 25, 2004 11:51 pm
The title is a common question I've received in the past and I thought I'd take a few minutes to explain a bit about keys and indexes… This is by no means a lot of detail regarding relational theory, etc. but there are a few things that we should quickly review to make sure that […]
MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation, Deletes and the “Sliding Window” Scenario and it’s the LAST one!
Posted on: August 27, 2004 3:26 am
People always ask me how I keep up to date with SQL Server and how I could possibly know so many intricate details about SQL Server… Well, I test/play/figure it out until it’s obvious (painfully sometimes and sometimes it’s not obvious). So, this blog entry is based on a specific question that made me want […]
MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation Questions, Part III
Posted on: August 26, 2004 2:45 am
Q: How do I interpret DBCC SHOWCONTIG WITH ALL_INDEXES on index id = 255 on a table with multiple text columns. I’m looking at a copy of last night’s production after running Maintenance Plan Optimizations. Scan density on table and all indexes is 99 – 100%. Logical Fragmentation is 0% for everything except text column […]
MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation Questions, Part II
Posted on: August 23, 2004 5:54 am
Q: Can I automate DBCC DBREINDEX to rebuild all tables on all databases on a server? Actually, yes and no… With stored procedures you’ll have a hard time changing database context without having to use dynamic string execution…. And so this can add a bit of frustration. BUT – it’s your lucky day (and really, […]
MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation Questions, Part I
Posted on: July 26, 2004 5:39 pm
Q: Is there another way to determine fragmentation? I don’t want to use DBCC SHOWCONTIG as it locks objects. Well, this is a good question and unfortunately, there really isn’t a way to generate information such as Scan Density or Average Page Density without running DBCC SHOWCONTIG. However, if you decide that you’re only interested […]
MSDN Webcast Q&A: Index Defrag Best Practices – Index Usage Questions
Posted on: July 26, 2004 5:14 am
Before I launch into the Q&A from this area there are a few other resources with Index Usage Q&A that you should also check out: Review the Q&A from the June 11 Webcast here. Review the Q&A page on SQLskills here. Q: How does SQL server decide what index to use when […]
MSDN Webcast Q&A: Index Defrag Best Practices – Clustering Key Choice
Posted on: July 24, 2004 5:39 am
Q: What about using uniqueidentifier fields for keys in a clustered index, will this affect performance? They are random numbers so they are not monotonically increasing… Ha, I always want to time how long it’s going to take for me to get this question. Seriously, I don’t think I’ve EVER given an indexing lecture (where […]
MSDN Webcast Q&A: Index Defrag Best Practices – General Questions
Posted on: July 24, 2004 5:37 am
Q: Does clustering rely on the operating system for performance? Well, I have to admit I am not entirely sure of the question here… But – I think I understand from where it might originate (well, I’ll try). First – and unfortunately (and also somewhat seriously), I think many people lack creativity in choosing a […]
MSDN Webcast Q&A: Index Defrag Best Practices – Related Resource Questions
Posted on: July 24, 2004 5:35 am
Q: Can I view a recording of this webcast? On demand MSDN webcasts (in general) can be found here. The Index Defragmentation Best Practices session is ready and available here. The prerequisite Indexing Best Practices session (from June 11) is here. Q: Where can we get a print out of the slides? I have […]
MSDN Webcast Q&A – almost ready….
Posted on: July 21, 2004 5:31 am
NOTE: These have NOT yet been completed and there are still a few questions that need to be answered. I will wrap these up shortly and also break them down into separate blog entries so they’re easier to wade through. I will also post the pdf, the webcast link (when it’s available), the demo scripts […]
Upcoming MSDN Webcast – Index Defrag Best Practices
Posted on: July 15, 2004 6:06 am
Abstract: In SQL Server, Performance Tuning can be achieved through many avenues: hardware, software, the database and the client. While there are many pathes that yield gains, finding the imporvements that give the greatest gain with the least impact on your current systems is a must. In SQL Server there is no other area in […]
Database Design considerations – where’s the silver bullet?
Posted on: July 8, 2004 2:16 pm
Well, the person then asked if we could sitdown… I said sure… We sat down at a table and they proceeded to rummage through their bag, pull out a bunch of paper (which turned out to be a single, very, large piece of plotter paper which was folded up) and proceeded to unfold it and […]
MSDN Webcast Archive Available but…
Posted on: June 15, 2004 6:34 pm
OK – I’ve requested that MSDN create a new “support webcast” page that has all of these links added to it (for a more complete archive/reference item) but it’s not yet been completed. For completeness – here are ALL of the resources: The actual archive of the MSDN Webcast is here. The full Q&A in […]
MSDN Webcast SQL Index Tuning Q&A: Index Management/Maintenance Questions
Posted on: June 12, 2004 10:38 pm
Index Management/Maintenance Questions: Q: Is there a tool or method for monitoring a database for awhile to identify indexes that are rarely or never used – that should be considered for removing? I think I mentioned this one late in the presentation but it certainly warrants an answer. Unfortunately, the answer is both yes and […]
MSDN Webcast SQL Index Tuning Q&A: Index Creation Questions
Posted on: June 12, 2004 10:30 pm
Index Creation Questions: Q: In an OLTP server do clustered indexes create a negative performance impact to the constant inserts and updates? The wrong clustered index can for sure… In most environments, having the right clustered index (an index on an ever-increasing key) will be better than a heap (a table without a clustered index) […]
MSDN Webcast SQL Index Tuning Q&A: General Questions
Posted on: June 12, 2004 10:24 pm
General Questions: Q: If you want to know the value of the key prior to your insert statement, how can you use identity? You could insert a “place-holder” row (i.e. a row that uses only defaults and/or just basic information so that you can get the @@identity of the row) and then come back later […]
MSDN Webcast SQL Index Tuning Q&A: Tools
Posted on: June 12, 2004 10:18 pm
Tools Questions: Q: Isn’t there some issue with profiler where it does not recognize DBName but only the DBID? Well, this is a good one – and a frustrating one as well. It is true that Profiler doesn’t always generate a data column value for every type of event. So – if you setup filters […]
MSDN Webcast SQL Index Tuning Q&A: Related Resources
Posted on: June 12, 2004 10:15 pm
Related Resources: Q: Can I view a recording of this webcast? It should be available on www.microsoft.com/usa/webcasts/ondemand shortly. Q: Where can we get the powerpoint file? The powerpoint will be available on the MSDN webcast resource page. I will post the exact link here and on SQLskills.com as soon as the final link is […]
