1

Sorry, I may be missing something super-obvious here, but I've googled this and asked some colleagues and am not finding a satisfactory answer.

I've been using sp_BlitzIndex for a few months now along with some of my other regular index tuning tools and have found it to be a super-useful tool to add to my SQL toolbox.

But I do not quite understand the difference between 'High value' and 'High Impact' as in this example below:

'Indexaphobia: High value missing index with High Impact'

At first I thought they meant roughly the same thing -- it was identifying a missing index that would be both highly valuable and have a great impact on query performance.

But that didn't quite make sense to me -- why have two separate terms to indicate what was pretty much the same thing? So a colleague suggested that the term 'impact' was referring to the impact on db write operations. So 'high impact' in this case meant the index might cost more than other indexes when it came to updates and deletes.

But I've read some of Brent's comments, and, if I'm understanding him correctly, I don't think my colleague's interpretation is quite right either.

Can someone please enlighten me on this? I'd really like to understand the difference before testing out some of the suggestions sp_BlitzIndex is making to me. Is 'high impact' good or bad? If it's good, then what's the difference between 'high value' and 'high impact'?

Thanks very much in advance!!

Robert

  • 1
    It’s open source T-SQL - why not just read the script and see what it’s doing? You can do a control-F in there and see where the numbers are coming from, what the thresholds are, etc. You can also click on the links in the details columns to read about the findings on pages on our site. – Brent Ozar Feb 25 '18 at 19:44
  • Yep, good idea, Brent, thanks! Will do. :-) – Robert H Feb 25 '18 at 20:02
  • 1
    So in case anyone else has been wondering about this, this is what I found in the sproc t-sql: SET is_low = CASE WHEN (user_seeks + user_scans) < 10000 OR avg_user_impact < 70. THEN 1 ELSE 0 END; – Robert H Feb 25 '18 at 20:22
  • 5
    OK, great! So you could now answer the question yourself here at Stack - it's perfectly acceptable to answer your own questions when you figure out the answer. People will even upvote you if you do a good job on both the question and the answer. Welcome to the site! – Brent Ozar Feb 25 '18 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.