My SQL Query take too long when using a non-clustered index

Question

I have a database table that hold customer details and when they call in we use their telephone to lookup their details, but this usually takes about 2-3 seconds but recently it has been taking 5 seconds with no extra data. If I query the table using the home_phone_no = '441903354676' this returns in sub second. But if it is queried using home_phone_no = '441903354676' or business_phone_no = '441903354676' then this takes 5 seconds.

Now there are some 1.4m customer records. But if anyone can see anything obvious or provide some helpful suggestions this be most welcome.

This is the structure of the table

CREATE TABLE [dbo].[CCDB_ICR]
(
                [bill_account_no] [varchar](10) NOT NULL,
                [reference_id] [varchar](11) NULL,
                [bill_account_status] [varchar](2) NULL,
                [customer_type] [varchar](1) NULL,
                [customer_name] [varchar](56) NULL,
                [property_address] [varchar](69) NULL,
                [outer_post_code] [varchar](4) NULL,
                [inner_post_code] [varchar](3) NULL,
                [customer_move_in_date] [datetime2](7) NULL,
                [customer_move_out_date] [datetime2](7) NULL,
                [debt_flag] [varchar](1) NULL,
                [payment_category_flag] [varchar](2) NULL,
                [payment_plan_flag] [varchar](1) NULL,
                [key_customer_flag] [varchar](1) NULL,
                [home_phone_no] [varchar](12) NULL,
                [business_phone_no] [varchar](12) NULL,
                [contact_type] [varchar](4) NULL,
                [contact_code] [varchar](1) NULL,
                [contact_method] [varchar](1) NULL,
                [contact_date] [date] NULL,
                [contact_time] [varchar](5) NULL,
                [contact_status] [varchar](1) NULL,
                [contact_unit_resp] [varchar](4) NULL,
                [outstanding_balance] [decimal](9, 2) NULL,
                [date_time_inserted] [datetime] NOT NULL,
                [date_time_updated] [datetime] NULL,

    CONSTRAINT [PK_CCDB_ICR] 
        PRIMARY KEY CLUSTERED([bill_account_no] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This is the structure of the index:

CREATE NONCLUSTERED INDEX [NC_Business&Home_Phone$CCDB_ICR] 
    ON [dbo].[CCDB_ICR] ([home_phone_no] ASC, [business_phone_no] ASC)
    INCLUDE ([bill_account_no]) 

Here is the stored procedure

ALTER procedure [dbo].[GET_ACCOUNT_BY_PHONE_NUMBER]
(
@CLI varchar(12),
@RETURN_VALUE int out,
@NUMBER_OF_ACCOUNTS int out,
@ACCOUNT_NUMBER varchar(10) out
)
as
Begin Try
      declare @ret int
      Select @ret=COUNT(*) from CCDB_ICR where home_phone_no=@CLI or business_phone_no=@CLI
      if @ret=0
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=0,
      @ACCOUNT_NUMBER=null
      else if @ret=1
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=1,
      @ACCOUNT_NUMBER=(Select  bill_account_no from CCDB_ICR where home_phone_no=@CLI or business_phone_no=@CLI)
      else if @ret>1
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=@ret ,
      @ACCOUNT_NUMBER=null
end Try

Begin Catch
      select @RETURN_VALUE=-1,
      @NUMBER_OF_ACCOUNTS=null ,
      @ACCOUNT_NUMBER=null
End Catch

Answer 1

The index can not be used for home_phone_no = '441903354676' or business_phone_no = '441903354676', but it could be used for home_phone_no = '441903354676' and business_phone_no = '441903354676'.

It will not be able to use the second column of the index key without a condition for the first column of the index key.

To use the or, you would use separate supporting indexes, e.g.:

create nonclustered index [NC_Business&Home_Phone$CCDB_ICR] 
  on [dbo].[CCDB_ICR] ([home_phone_no] asc);

create nonclustered index [NC_Business&Business_Phone$CCDB_ICR] 
  on [dbo].[CCDB_ICR] ([business_phone_no] asc); 

Also, you do not need to include [bill_account_no] as an included column on your indexes as it is the clustering key, and as such is already implicitly included.

You can simplify your entire procedure down to:

alter procedure [dbo].[get_account_by_phone_number] (
    @cli varchar(12)
  , @return_value int out
  , @number_of_accounts int out
  , @account_number varchar(10) out
) as
begin;
  set nocount, xact_abort on;

  set @return_value = 0;
  set @number_of_accounts = 0;

  select 
      @number_of_accounts = count(*)
    , @account_number = case when count(*)=1 then max(bill_account_no) else null end
  from ccdb_icr 
  where home_phone_no=@cli 
     or business_phone_no=@cli;
end;
go

If you are still experiencing performance issues after creating the appropriate indexes and updating the procedure, then you should try to identify if parameter sniffing is causing the problem.

I would start with this article by Paul White which covers the following:

SQL Server provides a range of query hints and other options to tune the behaviour of parameter sniffing:

• The OPTIMIZE FOR (@parameter = value) query hint builds a reusable plan based on a specific value
• OPTIMIZE FOR (@parameter UNKNOWN) uses average distribution statistics for a particular parameter
• OPTIMIZE FOR UNKNOWN uses average distribution for all parameters (same effect as trace flag 4136)
• The WITH RECOMPILE stored procedure option compiles a fresh procedure plan for every execution
• The OPTION (RECOMPILE) query hint compiles a fresh plan for an individual statement
  ~ Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White

Answer 2

Even with correct indexes (in this case, two separate single-col indexes, one on home_phone_no and other on business_phone_no), when you OR the predicates you will most likely end up with scan.

It is much better to use UNION ALL, perhaps with a CTE to avoid duplicating the whole query:

;with x as (
  -- your query here
)
select * from x where home_phone_no = @home_phone_no
union all
select * from x where business_phone_no = @business_phone_no

Also, avoid the WITH RECOMPILE, it's too big a hammer. Use OPTION(RECOMPILE) instead, and only if really needed (which I assume is not the case here).