I’m a big fan of the sp_help system stored procedures, which is why I’ve been a bit disappointed that the sp_helpindex stored procedure is rather badly flawed. It doesn’t have the included columns from the index. This seems a bit strange to me since included columns first appeared in SQL 2005 (>10 years ago) and are very important when doing index analysis. Because of this I was very excited when I came across a connect item on the subject a little while back.

To me this made it perfect for this month’s T-SQL Tuesday. Brent Ozar (b/t) is hosting this month and want’s us to talk about our favorite Bug & Enhancement Requests.

As I looked at this particular connect item in more detail I was extremely disappointed to find it was “Closed as Won’t fix”. Particularly given the reasoning.

there is an existing workaround already available with at par capabilities.

The idea is that the information is available. You have to query several system views to get to it, but it is there. In fact there are several pieces of free code that use them to provide the information. Kimberly Tripp’s (b/t) version of sp_helpindex (an older version is mentioned in the connect comments) and Brent Ozar Unlimited’s (b/t) sp_blitzindex are two of the more commonly known scripts.

But here is my problem.

The purpose of the sp_help functions is to provide an easy way to access compiled information from various system views. Because the purpose is to make thing easy they are frequently used by all ranges of database professional, from junior to senior. The juniors using these functions do so because for them, writing their own queries to collect the information together is difficult at best. You might argue that a junior DBA can use one of the pre-built scripts found on the internet. Yet many shops don’t allow/discourage outside scripts to be installed on their servers. Not to mention that many junior DBAs may not even know they exist.

On top of that, the lack of information can be misleading. Two indexes may look identical/similar but one has included columns and the other doesn’t. This can make a big difference when reviewing indexes and tuning queries. And again, the people who need the simple functions the most are the ones most likely to be confused/mislead by the lack of information.

So here is my conclusion. Either sp_helpindex should be updated to include the include columns or it should be deprecated. As it stands it’s not overly helpful and can be very misleading. If you agree with me upvote the connect item here. If you disagree feel free to down vote it. You won’t hurt my feelings 🙂