Redgate is making an Index recommendation - Confused as to what the index needs to be

JBed

Hi,

I'm using  monitoring to keep an eye on overall health of our SQL servers (3).  But generally the applications that are being monitored are off the shelf and not something we've built in-house.

Today I've been monitoring a new application that the company has gone live with and Redgate Monitor and it has recommended a missing index. see below.

How do I translate this into a
"create index <idxname>
on <tablename> ......" 

?

There's probably an article / link somewhere that I've not found but any advice welcome


Table: [dbname].[dbo].[tablename]; Impact: 80.52

• Column name: [m_type]; Usage: EQUALITY
• Column name: [ms_code]; Usage: EQUALITY
• Column name: [next_level]; Usage: INEQUALITY
• Column name: [level_]; Usage: INCLUDE
• Column name: [description##1]; Usage: INCLUDE
• Column name: [description##2]; Usage: INCLUDE
• Column name: [description##3]; Usage: INCLUDE
• Column name: [item_id]; Usage: INCLUDE
• Column name: [document]; Usage: INCLUDE
• Column name: [type]; Usage: INCLUDE
• Column name: [form_name]; Usage: INCLUDE
• Column name: [edit_authority]; Usage: INCLUDE
• Column name: [spare_2]; Usage: INCLUDE
• Column name: [params##1]; Usage: INCLUDE
• Column name: [params##2]; Usage: INCLUDE
• Column name: [params##3]; Usage: INCLUDE
• Column name: [params##4]; Usage: INCLUDE
• Column name: [params##5]; Usage: INCLUDE
• Column name: [params##6]; Usage: INCLUDE
• Column name: [params##7]; Usage: INCLUDE
• Column name: [params##8]; Usage: INCLUDE
• Column name: [params##9]; Usage: INCLUDE
• Column name: [params##10]; Usage: INCLUDE
• Column name: [params##11]; Usage: INCLUDE
• Column name: [params##12]; Usage: INCLUDE
• Column name: [params##13]; Usage: INCLUDE
• Column name: [params##14]; Usage: INCLUDE
• Column name: [params##15]; Usage: INCLUDE
• Column name: [posttray_name]; Usage: INCLUDE
• Column name: [posttray_priority]; Usage: INCLUDE
• Column name: [prev_item]; Usage: INCLUDE
• Column name: [PROGRESS_RECID]; Usage: INCLUDE

ThomasBE

Hi, first of all, please be aware that the index recommendations done by Red Gate Monitor apply to that specific query.
Always confirm with general index recommendations (like not more than seven indexes on one tables) before implementing.
Not all software vendors support adding own (custom) indexes, always verify with your vendor first.

The code for you index would look like this:

CREATE INDEX IX_NAME_OF_INDEX ON [dbname].[dbo].[tablename] ([m_type], [ms_code])

INCLUDE ([level_], [description##1], [description##2], [description##3], [item_id], 

[document], [type], [form_name], [edit_authority], [spare_2], [params##1], [params##2], 

[params##3], [params##4], [params##5], [params##6], [params##7], [params##8], [params##9], 

[params##10], [params##11], [params##12], [params##13], [params##14], [params##15], [posttray_name], 

[posttray_priority], [prev_item], [PROGRESS_RECID])

WITH (fillfactor = 90, sort_in_tempdb = on);

A few important remarks:
- Fields marked with include are always red from table (disk) whenever a record is found via the index
- options for the indexes in the "WITH" section are generally recommended:
          - fillfactor = 90 : prevents filling up all pages in the index, will cause index to be a bit bigger on disk,                  but less page splits
         - sort_in_tempdb = on : Option only has effect when running on SQL developer or enterprise edition.             The index will be rebuild in the tempdb and will than replace the one in the table. Reducing the lock               time on your table and improving performance during rebuild.

If you have any additional questions, please reach out.