Index tuning question

I am tuning some indexes and seeing some issues would like to take your advice

On 1 table there are 3 indexes

dbo.Address.IX_Address_ProfileId 
[1 KEY] ProfileId {int 4}
Reads: 0 Writes:10,519

dbo.Address.IX_Address 
[2 KEYS] ProfileId {int 4}, InstanceId {int 4}
Reads: 0 Writes:10,523

dbo.Address.IX_Address_profile_instance_addresstype
[3 KEYS] ProfileId {int 4}, InstanceId {int 4}, AddressType {int 4}
Reads: 149677 (53,247 seek) Writes:10,523

1- Do i really need the first 2 indexes, or should i drop them?

2- there are queries running that use condition where profileid = xxxx and other use condition where profileid = xxxx and InstanceID=xxxxxx. Why the
optimizer choose the 3rd index not the 1st or 2nd?

Also i am running a query that get the Lock wait on each index. If i am getting these counts, what should i do to tune this index?

Row lock waits: 484; total duration: 59 minutes; avg duration: 7 seconds; 
Page lock waits: 5; total duration: 11 seconds; avg duration: 2 seconds; 
Lock escalation attempts: 36,949; Actual Escalations: 0.

table structure is

TABLE [dbo].[Address](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressType] [int] NULL,
[isPreferredAddress] [bit] NULL,
[StreetAddress1] [nvarchar](255) NULL,
[StreetAddress2] [nvarchar](255) NULL,
[City] [nvarchar](50) NULL,
[State_Id] [int] NOT NULL,
[Zip] [varchar](20) NULL,
[Country_Id] [int] NOT NULL,
[CurrentUntil] [date] NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[ProfileId] [int] NOT NULL,
[InstanceId] [int] NOT NULL,
[County_id] [int] NULL,
 CONSTRAINT [PK__Address__3214EC075E4BE276] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
 )

this is an example (this query created by hibernate so looks strange)

```
(@P0 bigint)select addresses0_.ProfileId as Profile15_109_1_
, addresses0_.Id as Id1_20_1_
, addresses0_.Id as Id1_20_0_
, addresses0_.AddressType as AddressT2_20_0_
, addresses0_.City as City3_20_0_
, addresses0_.Country

Answer to question 1:

From what you posted you can drop the first two indexes as the third will cover all of the queries you mention and the query optimizer see that as well when it builds the query plan (based on the plan you posted.)

Answer to question 2:

It's always using the third index because it has more data already in the index with the two additional index keys (InstanceId and AddressType). This keeps SQL from needing to pull InstanceId and AddressType from the primary key (the key lookup part of the execution plan) to satisfy the query.

What I would suggest is drop the first two indexes and rebuild the third with include columns to cover the other columns being requested in the query

Create index IX_Address_profile_instance_addresstype 
on dbo.address  (ProfileId, InstanceId, AddressType) 
include() 
with (drop_existing=on,sort_in_tempdb=on)

This should help with the queries and should remove the key lookup from the query plan.

See if the locks drop off after these changes and if they don't we can dig in a bit deeper.

StackExchange Database Administrators Q#111702, answer score: 6