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

List the name of the duplicate index when checking for same #807

Open
MrBlueSky opened this issue Oct 21, 2020 · 1 comment
Open

List the name of the duplicate index when checking for same #807

MrBlueSky opened this issue Oct 21, 2020 · 1 comment

Comments

@MrBlueSky
Copy link

@MrBlueSky MrBlueSky commented Oct 21, 2020

Feature Request

The duplicate index check doesn't list the indices that were identified as duplicates. Limits the usefulness of the check since you need a separate tool or query to find out the actual index names.

@MikeyBronowski
Copy link
Contributor

@MikeyBronowski MikeyBronowski commented Nov 25, 2020

@MrBlueSky not sure what version do you have, but the latest gives this error message:
Executing script C:\Users\micha\OneDrive\GIT\dbachecks\checks\Database.Tests.ps1

  Describing Duplicate Index

    Context Testing duplicate indexes on localhost,1433
      [-] Database Mikey should return 0 duplicate indexes on localhost,1433 1.22s
        Expected 0, because Duplicate indexes waste disk space and cost you extra IO, CPU, and Memory, but got 4.
        61:     @(Find-DbaDbDuplicateIndex -SqlInstance $Instance -Database $Database).Count | Should -Be 0 -Because "Duplicate indexes waste disk space and cost you extra IO, CPU, and Memory"
        at Assert-DatabaseDuplicateIndex, C:\Users\micha\OneDrive\GIT\dbachecks\internal\assertions\Database.Assertions.ps1: line 61
        at <ScriptBlock>, C:\Users\micha\OneDrive\GIT\dbachecks\checks\Database.Tests.ps1: line 308

As you may notice there is this bit
Find-DbaDbDuplicateIndex -SqlInstance $Instance -Database $Database

If I adjust it to my environment like that for example:
Find-DbaDbDuplicateIndex -SqlInstance $sql -Database Mikey | FT

i am going to get all four duplicates mentioned above:

DatabaseName TableName  IndexName           KeyColumns                   IncludedColumns IndexType    IndexSizeMB CompressionDescription RowCount IsDisabled
------------ ---------  ---------           ----------                   --------------- ---------    ----------- ---------------------- -------- ----------
Mikey        dbo.Table1 CI_col1             col1 ASC                                     CLUSTERED       0.000000 NONE                          0      False
Mikey        dbo.Table1 NI_col1             col1 ASC                                     NONCLUSTERED    0.000000 NONE                          0      False
Mikey        dbo.Table1 NI_col1_col2_col3   col1 ASC, col2 ASC, col3 ASC                 NONCLUSTERED    0.000000 NONE                          0      False
Mikey        dbo.Table1 NI_col1_col2_col3_2 col1 ASC, col2 ASC, col3 ASC                 NONCLUSTERED    0.000000 NONE                          0      False

Find-DbaDbDuplicateIndex is part of the dbatools anyway, so no need to get additional tools (see https://docs.dbatools.io/#Find-DbaDbDuplicateIndex).

Would it work for you?

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

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.