Why an inner join creates an index scan versus an index

Question

I have two tables

Account: AccountUID bigint (Primary Key) AccountID bigint Version smallint CustomerName varchar(50) 

Note: AccountID and CurrentVersion are also part of the unique

 AccountStatus: AccountID bigint (Primary Key) CurrentVersion smallint Filter1 varchar(10) Filter2 varchar(10)) 

To keep things simple, I currently have 100 lines, since each AccountID has only one version. I have values ​​in the Filter1 and Filter2 tables, so when I do the following, I get 10 entries:

 SELECT * FROM AccountStatus acs WHERE acs.Filter1=SomeValue1 and acs.Filter2=SomeValue2 

Due to the fact that I have an index that has Filter1 and Filter2 in it, the actual execution plan shows an index search with only 10 rows selected in the value "Actual Rows".

When I join the Account table as follows, I get the same 10 records:

 SELECT acs.* FROM AccountStatus acs INNER JOIN Account a ON acs.AccountID=a.AccountID AND acs.CurrentVersion=a.Version WHERE acs.Filter1=SomeValue1 and acs.Filter2=SomeValue2 

However, when I look at the Actual Execution Plan, I still see the Index Seek in the AccountStatus table, as before, with 10 actual rows. However, above, I see an index index that includes the AccountID and Version the Account table. In addition, this “action” shows 100 in actual rows.

Below is detailed information about the indices used:

 CREATE NONCLUSTERED INDEX [IX_Find] ON [dbo].[AccountStatus] ( [Filter1] ASC, [Filter2] ASC ) INCLUDE ( [AccountID], [CurrentVersion] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] CREATE UNIQUE NONCLUSTERED INDEX [IX_Account_Status] ON [dbo].[Account] ( [AccountID] ASC, [Version] ASC ) INCLUDE ( [AccountUID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

While this is not too much for 100-line performance, it bothers me more when I get a million or several million lines. It will become very inefficient.

In any case, so that this type of query does not look at all the rows in the index in the Account table?

Any help would be greatly appreciated.

Answer 1

Execution plans are based on statistics on tables and indexed columns. With only 100 lines, he will have a different execution plan than with 10 m lines. If you are concerned that you are not using this index, you need to increase the amount of data and update the statistics before taking a look at the execution plan again. The engine is pretty smart and very often chooses the fastest version of the execution plan.

I assume that it matches filter 1 for enough rows, even though it doesn’t take a lot of time to perform a search scan using these few rows, so the engine just uses the scan.

Answer 2

The way I determine if an index is needed or not is one of two ways. The first executes the query using the Display Estimated Execution Plan. Then, if the execution plan indicates that the index is missing (green), right-click on the green missing index message and select "Missing index data" ... This will open a new window with code to insert the missing index, which can help with this query . Just uncomment the bottom of the code and replace with the name of the new index and execute the code.

You may need to repeat this process several times for any missing indexes that might help this code.

The second method is a longer-term approach to database performance.

PINAL DAVE posted a series of scripts on his blog http://blog.sqlauthority.com/ that help identify missing indexes as well as unused and duplicate indexes. I run them on a regular basis to determine which indexes help and which actually hurt performance.

Add missing indexes and remove unused or duplicate indexes.