asked on

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

Steve Wales

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.

bfuchs

ASKER

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.

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Wales

From the docs:

Create Indexes with Included Columns
Creating Indexes with Included Columns

And an article from sqlmag.com explaining just this topic in a little more detail:

http://sqlmag.com/database-performance-tuning/behind-scenes-sql-server-included-columns-and-covering-indexes

bfuchs

ASKER

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 Wales

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.

bfuchs

ASKER

I See,

Thanks again Steve!