Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzIndex -- Take table creation date into missing index benefit calculation #937

Closed
BlitzErik opened this issue Jun 14, 2017 · 1 comment

Comments

@BlitzErik
Copy link
Contributor

BlitzErik commented Jun 14, 2017

When we judge missing index benefit, we look at the 'magic benefit number' divided by days of uptime. This makes sense as long as the table has been there the whole time, but less sense if it was created more recently.

We collect create_date in #IndexSanity, which is where we grab missing index details from. To avoid grouping awkwardness, I'm just going to grab ISNULL(NULLIF(MAX(DATEDIFF(DAY, t.create_date, SYSDATETIME())), 0), 1) here, and then in WHERE clause, it will look like

WHERE ( @Mode = 4 AND (magic_benefit_number / CASE WHEN create_days < @DaysUptime THEN create_days ELSE @DaysUptime END) >= 100000 ) 
OR (magic_benefit_number / CASE WHEN create_days < @DaysUptime THEN create_days ELSE @DaysUptime END) >= 100000
@BlitzErik BlitzErik added this to the 2017-07 milestone Jun 14, 2017
@BlitzErik BlitzErik self-assigned this Jun 14, 2017
@BlitzErik BlitzErik changed the title sp_BlitzIndex -- Make Days Uptime an input parameter sp_BlitzIndex -- Take table creation date into missing index benefit calculation Jun 16, 2017
@BlitzErik
Copy link
Contributor Author

There are a couple other places where this calculation lives. Right now, those two places (Table mode and Mode 3) only reference the #MissingIndexes temp table. Will need to think of a clever way to make this match across all outputs.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant