table index question

Hi Experts,
Just wondering why do I get a message like the following when clicking on display estimated execution plan

The Query Processor estimates that implementing the following index could improve the query cost by 11.7409%.
*/

/*
USE [HomeCare]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[EmployeesUnitstbl] ([Unit])
INCLUDE ([EmployeesID])
GO
*/

Open in new window


while I do have an index on both fields and another one of the EmployeesID field?

See attached.
Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
If I'm reading your question correctly, you say you have two indexes:

EmployeeID
EmployeeID + Unit

The database engine is saying you could benefit by having an index on Unit (with EmployeeID included in the index data for ease of fetch).

(This is not the same as EmployeeID + Unit as an index)

Are you performing a query doing comparisons on the Unit column ?

If so, neither of your existing indexes cover that search.
bfuchsAuthor Commented:
Hi,
This is not the same as EmployeeID + Unit as an index
this is what I'm trying to understand, what is the meaning of include, as I always did index on both columns that usually are filtered together.
Steve WalesSenior Database AdministratorCommented:
SQL Server allows you to "include" columns that are not part of the index key into the index to save on IO's.  The columns in the index itself should be kept as minimal as possible to keep the key itself small.  But if you have a couple of columns that are used frequently, sometimes it may be beneficial to "include" just those columns in the index as well.

Normally, you search an index, it grabs the key, then does another I/O to fetch the data row.  That's 2 I/O operations.

If a couple of frequently accessed columns are also included in the index, it can find the rows you want on the index and the non-key column or two you need are also stored with the index - saving you an I/O.

If you want lots and lots of columns, this isn't practical.

Let me see if I can find the mention of included columns in the documentation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Steve WalesSenior Database AdministratorCommented:
bfuchsAuthor Commented:
Thanks Steve!

BTW, is this enough of a reason to add the index and follow blindly what the execution plan suggests, or I should investigate deeper in the table/queries usage?

And what does the 11.xx represents in the message, is it referring to 11% faster or something else?

Thanks,
Ben
Steve WalesSenior Database AdministratorCommented:
Every time the optimizer does something with a query there's a "Cost" associated with that action.  I admit to not fully understanding how this cost is calculated.  The 11% is what the optimizer estimates as a savings to that "Cost" if this index were to exist.

I don't know that I would blindly create every index that the optimizer recommends or you may find yourself with dozens of indexes.

You may want to do some analysis and work out overall  how often this would be used and is the 11% savings on your searches worth the extra overhead every time this table is updated.

Remember that while extra indexes are great for searches, there is a cost to be paid in maintaining these indexes (every insert or update now requires an extra I/O to update the new index).

Is the table heavily updated and not searched a lot ?  The trade off may not be worth it.
Large table, heavily queried, relatively stable table ?  Trade off may very well be worth it.
bfuchsAuthor Commented:
I See,

Thanks again Steve!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.