Find Missing and Unused indexes with SqlDbAid

SQL Server's Dynamic Management Views (DMV) give you access to a lot of useful information.

The web is full of good scripts about SQL Server indexes, but if you prefer a friendly interface may be you could just use SqlDbAid application.

Lets create a test case.

Create and fill the following table

CREATE TABLE [dbo].[missing_idx]
(
[row_id] [bigint] NOT NULL,
[object_id] [int] NOT NULL,
[name] [nvarchar](128) NOT NULL,
[type] [char](2) NOT NULL,
CONSTRAINT [PK_missing_idx] PRIMARY KEY CLUSTERED ([row_id])
)

INSERT INTO dbo.missing_idx
SELECT TOP 20000 ROW_NUMBER() OVER (
                                    ORDER BY O.object_id) row_id,
                                                          O.object_id,
                                                          O.name,
                                                          O.type
FROM sys.all_objects O CROSS
JOIN sys.all_objects O1

then we could try to extract some data accessing the table using object_id column (no index there).

SELECT object_id,
       name,
       TYPE
FROM dbo.missing_idx
WHERE object_id IN
    (SELECT TOP 1 object_id
     FROM dbo.missing_idx)

Now run SqlDbAid, connect to your server and select the database where you created the table.

Select "Missing Indexes" from Tools --> Indexes menu.

You should see the following report:

Double click the row to get the create script:

CREATE NONCLUSTERED INDEX [IX_AT_dbo_missing_idx_1870364392] ON
[dbo].[missing_idx] ([object_id]) INCLUDE ([name], [type])

To find unused indexes from SqlDbAid select "Indexes Status" from Tools --> Indexes menu.

You should see the following report:

The read_vs_write column is the sum of all reads (scan, seek and lookup) minus the writes.

Look also to the date columns to know when was the last read.