8

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_Id as Country_4_20_0_
, addresses0_.County_id as County_i5_20_0_
, addresses0_.CreatedDate as CreatedD6_20_0_
, addresses0_.CurrentUntil as CurrentU7_20_0_
, addresses0_.InstanceId as Instance8_20_0_
, addresses0_.isPreferredAddress as isPrefer9_20_0_
, addresses0_.ProfileId as Profile15_20_0_
, addresses0_.State_Id as State_I10_20_0_
, addresses0_.StreetAddress1 as StreetA11_20_0_
, addresses0_.StreetAddress2 as StreetA12_20_0_
, addresses0_.UpdatedDate as Updated13_20_0_
, addresses0_.Zip as Zip14_20_0_ 
from dbo.Address addresses0_ 
where addresses0_.ProfileId=@P0 

enter image description here

(@P0 bigint,@P1 bigint)
select addressdmo0_.Id as Id1_20_
, addressdmo0_.AddressType as AddressT2_20_
, addressdmo0_.City as City3_20_
, addressdmo0_.Country_Id as Country_4_20_
, addressdmo0_.County_id as County_i5_20_
, addressdmo0_.CreatedDate as CreatedD6_20_
, addressdmo0_.CurrentUntil as CurrentU7_20_
, addressdmo0_.InstanceId as Instance8_20_
, addressdmo0_.isPreferredAddress as isPrefer9_20_
, addressdmo0_.ProfileId as Profile15_20_
, addressdmo0_.State_Id as State_I10_20_
, addressdmo0_.StreetAddress1 as StreetA11_20_
, addressdmo0_.StreetAddress2 as StreetA12_20_
, addressdmo0_.UpdatedDate as Updated13_20_
, addressdmo0_.Zip as Zip14_20_ 
from dbo.Address addressdmo0_ 
left outer join dbo.Profile profiledmo1_ 
on addressdmo0_.ProfileId=profiledmo1_.Id 
where profiledmo1_.Id=@P0 and addressdmo0_.InstanceId=@P1

enter image description here

  • Any chance you could add in what the full table structure is, what the clustering key is and then what other columns are being included in the queries that are looking for profileid = xxxx and the query with profilerid = xxxx and instanceid = xxxx. There is a lot of "it depends" in these answers and having that information would definitely help to explain what it depends on. – mskinner Aug 20 '15 at 14:34
  • More information about the data would be helpful. For example, if statistics have been updated on the table, how many records are in the table along with uniqueness and so forth. – Glen Swan Aug 20 '15 at 15:09
  • @GlenSwan, there are 567644 record in this table. Statistics got updated twice a week. Tuesday and Saturday – sebeid Aug 20 '15 at 15:16
  • 2
    Please do your own research about feature comparisons. While there is some overlap, Failover Clusters and Availability Groups have different features and meet different requirements, so you can't really ask generically which one is better. You need to compare the features of each with your actual business needs. Also, licensing / cost questions are not on-topic here. Please read this meta post in full. – Aaron Bertrand Sep 11 '15 at 20:49
6

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(<put in the remaining columns comma delimited>) 
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.

  • i did what you recommend and there is no more key lookup . i am monitoring locks .. i still need some clarification for these counts 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. – sebeid Aug 20 '15 at 17:05
  • @sebeid when inserts/updates/deletes are done on the Address table are other tables also being modified by the same batch request? If so, does the batch explicitly begin a transaction for all of the modification statements the in batch and only commit or rollback at the end of the batch? – Aaron Aug 20 '15 at 19:42
  • i don't know , from where can i get this information. Thanks – sebeid Aug 20 '15 at 19:53
  • 2
    @sebeid if everything is a generated statement from hibernate I would ask your developers as that might be the simplest approach, otherwise you will need to trace or setup an extended event to try and trap the calls so you can see what is all coming across in the batch statement. Extended Events Profiler Example – Aaron Aug 20 '15 at 19:58
3

Not the stated question but may get better query plans with better queries
You are killing the left outer with the where
where profiledmo1_.Id=@P0 turns that into a join

On indexes just the first two

select addressdmo0_.Id as Id1_20_
     , ...
     , addressdmo0_.Zip as Zip14_20_ 
  from dbo.Address addressdmo0_ 
  join dbo.Profile profiledmo1_ 
    on addressdmo0_.ProfileId = profiledmo1_.Id 
   and profiledmo1_.Id = @P0 
   and addressdmo0_.InstanceId = @P1

all that join does is make sure it is in Profile but you are not reporting anything from profile
and how is that not ?

select addressdmo0_.Id as Id1_20_
     , ...
     , addressdmo0_.Zip as Zip14_20_ 
  from dbo.Address addressdmo0_ 
 where addressdmo0_.ProfileId  = @P0 
   and addressdmo0_.InstanceId = @P1
2

It seems as if you could drop index 1 and 2 as index 3 includes all information (columns) you need. It might be possible that another index makes sense as a clustered index to represent the primary key.

With this limited information we can just guess. If you need more hints please post more detailed information as your complete table structure (tables, index, keys,...), your queries and the execution plan.

  • 1
    Or a clustered index that doesn't represent the primary key. While these two things are often tightly associated with one another, and while a primary key is created as clustered by default, they are not (and do not have to be) the same thing. – Aaron Bertrand Aug 20 '15 at 15:03
  • That's true of course. :-) – Josh Alvo Aug 21 '15 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.