Index not being used when it should be

Hello.

I have a situation where I have two tables setup.

**ordering_header_table

- order_glue (PK)

- order_id

- flag_state

....

* index(s)

1)

- order_glue (PK)

2)

- order_id asc,

- flag_state asc,

- order_glue asc

** shipping_table

- order_glue (PK)

- country

* indexes(s)

1)

- order_glue (PK)

My query is as follows:

select h.order_id, h.order_glue, h.flag_state, s.country from ordering_header_table as h

inner join shipping_table as s on h.order_glue = s.order_glue where h.order_id > 0 and h.flag_state = 0

When I run the above query, it takes between 90 to 120 seconds to run. The odd part is, when I display the estimated execution plan for their query, it is NOT using my index. It is doing a table scan instead of using my index. Any idea why this would happen?

Thanks,

Chris

Could you run the code below and post back the results? This will give us some idea of the distribution of your data and will give a clue as to why your index is being ignored.

Chris

Code Snippet

SELECT SUM(CASE WHEN h.order_id > 0 THEN 1 ELSE 0 END), h.flag_state, COUNT(*)

FROM ordering_header_table h

GROUP BY h.flag_state

|||

Here are the results of the query you suggested. It will always be the case that 99.99% of the results will have the same flag state of 3.

HIts Flag State

0 0
0 1
1117882 3
1 10

|||

In that case I would reverse the first two columns in your index:

From:

- order_id asc,

- flag_state asc,

- order_glue asc

To:

- flag_state asc,

- order_id asc,

- order_glue asc

Chris

|||

I just made the changes you suggested and and still doing a table scan and not using the index.

Chris

|||

OK, could you execute SET SHOWPLAN_TEXT ON, then run the query and post back the results?

Also it would be helpful if you could post the CREATE script for each of the tables involved in the query.

Chris

|||

Below is a copy of both full table and the full query I am executing along with the showplan_text. Thanks for you help in advance!

select
h.ship_method,
h.order_id,
h.order_glue,
h.access_id,
h.flag_state,
h.date_created,
h.date_commit,
h.account_rep,
h.class_code,
h.user_identity,
s.adrs_country_lov,
h.currency_code,
h.vid_number,
h.order_model,
h.commit_language,
h.ip_address
from
ordering_header_table as h inner join SHIPPING_TABLE as s on h.salesorder_glue = s.salesorder_glue
where
h.salesorder_id > 0 and h.flag_state = 0

CREATE TABLE [dbo].[ORDERING_HEADER_TABLE](
[ORDER_GLUE] [int] IDENTITY(11,1) NOT NULL,
[ORDER_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ORDER_ID] DEFAULT (0),
[IP_ADDRESS] [nvarchar](16) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_IP_ADDRESS] DEFAULT ('0.0.0.0'),
[ACCESS_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ACCESS_ID] DEFAULT (0),
[DATE_CREATED] [datetime] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_DATE_CREATED] DEFAULT (getdate()),
[CUSTOMER_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_CUSTOMER_ID] DEFAULT (0),
[ACCOUNT_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ACCOUNT_ID] DEFAULT (0),
[FLAG_STATE] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_STATE] DEFAULT (0),
[PURCHASE_ORDER] [nvarchar](21) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_PURCHASE_ORDER] DEFAULT (''),
[SHIP_METHOD] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_SHIP_METHOD] DEFAULT ((-1)),
[BACKORDER_SHIP_METHOD] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_BACKORDER_SHIP_METHOD] DEFAULT ((-1)),
[BACKORDER_HOLD_DAYS] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_BACKORDER_HOLD_DAYS] DEFAULT ((-1)),
[PAYMENT_TYPE] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_PAYMENT_TYPE] DEFAULT (5),
[DATE_COMMIT] [datetime] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_DATE_COMMIT] DEFAULT ('1-1-1975'),
[DATE_REVIEW] [datetime] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_DATE_REVIEW] DEFAULT ('1-1-1975'),
[FLAG_TAX_EXEMPT] [char](1) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_TAX_EXEMPT] DEFAULT ('F'),
[FLAG_DISCOUNT] [char](1) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_DISCOUNT] DEFAULT ('F'),
[FLAG_SHIP_CONTROL] [nvarchar](1) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_SHIP_CONTROL] DEFAULT (''),
[ROOT_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ROOT_ID] DEFAULT (0),
[ACCOUNT_REP] [nvarchar](5) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ACCOUNT_REP] DEFAULT ('00'),
[Class_Code] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_Class_Code] DEFAULT (99),
[REFERENCE_ID] [nvarchar](20) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_REFERENCE_ID] DEFAULT (''),
[USER_IDENTITY] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_USER_IDENTITY] DEFAULT (0),
[CURRENCY_CODE] [int] NOT NULL CONSTRAINT [DF__so_header__CURRE__47DBAE45] DEFAULT (1),
[CS_CURRENCY] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_CS_CURRENCY] DEFAULT (0),
[AR_CURRENCY] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_AR_CURRENCY] DEFAULT (0),
[ORDER_MODEL] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ORDER_MODEL] DEFAULT (0),
[VID_NUMBER] [nvarchar](26) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_VID_NUMBER] DEFAULT (''),
[SHIPPER_ACCOUNT_NUMBER] [nvarchar](10) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_SHIPPER_ACCOUNT_NUMBER] DEFAULT (''),
[COMMIT_LANGUAGE] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_COMMIT_LANGUAGE] DEFAULT (0),
[SHIP_OPTIONS] [int] NOT NULL DEFAULT (0),
[Site_Code] [int] NOT NULL CONSTRAINT [DF__so_header__Site18D771F0] DEFAULT (1),
[SESSION_ID] [int] NOT NULL DEFAULT (0)
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE] ON [dbo].[ORDERING_HEADER_TABLE]
(
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_CUSTOMER_ID')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_CUSTOMER_ID] ON [dbo].[ORDERING_HEADER_TABLE]
(
[CUSTOMER_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_FLAG_STATE_DATE_CREATED_CUSTOMER_ID_ORDER_MODEL')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_FLAG_STATE_DATE_CREATED_CUSTOMER_ID_ORDER_MODEL] ON [dbo].[ORDERING_HEADER_TABLE]
(
[FLAG_STATE] ASC,
[DATE_CREATED] ASC,
[CUSTOMER_ID] ASC,
[ORDER_MODEL] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_MODEL_DATE_COMMIT')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_MODEL_DATE_COMMIT] ON [dbo].[ORDERING_HEADER_TABLE]
(
[FLAG_STATE] ASC,
[ORDER_MODEL] ASC,
[DATE_COMMIT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE] ON [dbo].[ORDERING_HEADER_TABLE]
(
[FLAG_STATE] ASC,
[ORDER_ID] ASC,
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_IP_ADDRESS')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_IP_ADDRESS] ON [dbo].[ORDERING_HEADER_TABLE]
(
[IP_ADDRESS] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_ORDER_MODEL_FLAG_STATE_ORDER_GLUE')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_ORDER_MODEL_FLAG_STATE_ORDER_GLUE] ON [dbo].[ORDERING_HEADER_TABLE]
(
[ORDER_MODEL] ASC,
[FLAG_STATE] ASC,
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_ORDER_ID')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_ORDER_ID] ON [dbo].[ORDERING_HEADER_TABLE]
(
[ORDER_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_VID_NUMBER_ORDER_MODEL')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_VID_NUMBER_ORDER_MODEL] ON [dbo].[ORDERING_HEADER_TABLE]
(
[VID_NUMBER] ASC,
[ORDER_MODEL] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SHIPPING_TABLE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SHIPPING_TABLE](
[ORDER_GLUE] [int] NOT NULL,
[Adrs_City] [nvarchar](31) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_City] DEFAULT (''),
[Adrs_Company] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Company] DEFAULT (''),
[Adrs_Country] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Country] DEFAULT (''),
[Adrs_Country_LOV] [nvarchar](2) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Country_LOV] DEFAULT ('00'),
[Adrs_First_Name] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_First_Name] DEFAULT (''),
[Adrs_Last_Name] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Last_Name] DEFAULT (''),
[Adrs_Line_1] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Line_1] DEFAULT (''),
[Adrs_line_2] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_line_2] DEFAULT (''),
[Adrs_line_3] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_line_3] DEFAULT (''),
[Adrs_Mail_stop] [nvarchar](17) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Mail_stop] DEFAULT (''),
[Adrs_State_Code] [nvarchar](6) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_State_Code] DEFAULT (''),
[Adrs_Zip_Code] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Zip_Code] DEFAULT (''),
[Adrs_Zip_Plus] [nvarchar](5) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Zip_Plus] DEFAULT (''),
[Telephone] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Telephone] DEFAULT (''),
[Email] [nvarchar](65) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Email] DEFAULT (''),
[Fax] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Fax] DEFAULT (''),
[Adrs_Foreign_Territory] [nvarchar](30) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Foreign_Territory] DEFAULT (''),
[Flag_Domestic_Adrs] [int] NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Flag_Domestic_Adrs] DEFAULT (1),
[DATE_CREATED] [datetime] NULL CONSTRAINT [DF_SHIPPING_TABLE_DATE_CREATED] DEFAULT (getdate()),
[FLAG_US_TERRITORY] [int] NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_FLAG_US_TERRITORY] DEFAULT (0),
[Source_Code] [int] NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Source_Code] DEFAULT (0),
[VAT_Number] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_VID_Number] DEFAULT (''),
[WebSite] [nvarchar](128) NOT NULL DEFAULT (''),
[geocode] [int] NOT NULL DEFAULT (0),
[flag_geocode_choice] [int] NOT NULL DEFAULT (0),
CONSTRAINT [PK_SHIPPING_TABLE] PRIMARY KEY NONCLUSTERED
(
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH.[SALESORDER_GLUE])=(.[SalesOrder_Glue]))
|--Bitmap(HASH.[SALESORDER_GLUE]), DEFINE[Bitmap1008]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[SALESORDER_GLUE]))
| |--Table Scan(OBJECT[DKC_SQL_SALES].[dbo].[SO_HEADER_TABLE] AS ), WHERE[DKC_SQL_SALES].[dbo].[SO_HEADER_TABLE].[FLAG_STATE] as .[FLAG_STATE]=(0) AND [DKC_SQL_SALES].[dbo].[SO_HEADER_TABLE].[SALESORDER_ID] as .[SALESORDER_ID]>(0)))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[SalesOrder_Glue]), WHEREPROBE([Bitmap1008])=TRUE) [IN ROW])
|--Table Scan(OBJECT[DKC_SQL_SALES].[dbo].[SO_SHIPPING_TABLE] AS ))

|||

First of all, the query you provided will not work with the table DDL scripts that you have included as the ORDERING_HEADER_TABLE table contains no 'salesorder_id' column.

Secondly, the plan text you provided refers to a 'SO_SHIPPING_TABLE' and a 'SO_HEADER_TABLE', neither of which you have provided the DDL scripts for and neither of which are referenced in the query you provided.

Please could you provide the correct table and index DDL scripts and a query that will run against those tables? It is difficult to troubleshoot a problem when you don't have correct information to work with.

Thanks

Chris

|||

Sorry about that. Below is the correct information.

Code Snippet

select
h.ship_method,
h.order_id,
h.order_glue,
h.access_id,
h.flag_state,
h.date_created,
h.date_commit,
h.account_rep,
h.class_code,
h.user_identity,
s.adrs_country_lov,
h.currency_code,
h.vid_number,
h.order_model,
h.commit_language,
h.ip_address
from
ordering_header_table as h inner join SHIPPING_TABLE as s on h.order_glue = s.order_glue
where
h.order_id > 0 and h.flag_state = 0

CREATE TABLE [dbo].[ORDERING_HEADER_TABLE](
[ORDER_GLUE] [int] IDENTITY(11,1) NOT NULL,
[ORDER_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ORDER_ID] DEFAULT (0),
[IP_ADDRESS] [nvarchar](16) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_IP_ADDRESS] DEFAULT ('0.0.0.0'),
[ACCESS_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ACCESS_ID] DEFAULT (0),
[DATE_CREATED] [datetime] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_DATE_CREATED] DEFAULT (getdate()),
[CUSTOMER_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_CUSTOMER_ID] DEFAULT (0),
[ACCOUNT_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ACCOUNT_ID] DEFAULT (0),
[FLAG_STATE] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_STATE] DEFAULT (0),
[PURCHASE_ORDER] [nvarchar](21) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_PURCHASE_ORDER] DEFAULT (''),
[SHIP_METHOD] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_SHIP_METHOD] DEFAULT ((-1)),
[BACKORDER_SHIP_METHOD] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_BACKORDER_SHIP_METHOD] DEFAULT ((-1)),
[BACKORDER_HOLD_DAYS] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_BACKORDER_HOLD_DAYS] DEFAULT ((-1)),
[PAYMENT_TYPE] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_PAYMENT_TYPE] DEFAULT (5),
[DATE_COMMIT] [datetime] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_DATE_COMMIT] DEFAULT ('1-1-1975'),
[DATE_REVIEW] [datetime] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_DATE_REVIEW] DEFAULT ('1-1-1975'),
[FLAG_TAX_EXEMPT] [char](1) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_TAX_EXEMPT] DEFAULT ('F'),
[FLAG_DISCOUNT] [char](1) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_DISCOUNT] DEFAULT ('F'),
[FLAG_SHIP_CONTROL] [nvarchar](1) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_FLAG_SHIP_CONTROL] DEFAULT (''),
[ROOT_ID] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ROOT_ID] DEFAULT (0),
[ACCOUNT_REP] [nvarchar](5) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ACCOUNT_REP] DEFAULT ('00'),
[Class_Code] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_Class_Code] DEFAULT (99),
[REFERENCE_ID] [nvarchar](20) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_REFERENCE_ID] DEFAULT (''),
[USER_IDENTITY] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_USER_IDENTITY] DEFAULT (0),
[CURRENCY_CODE] [int] NOT NULL CONSTRAINT [DF__ORDERING_HEADER__CURRE__47DBAE45] DEFAULT (1),
[CS_CURRENCY] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_CS_CURRENCY] DEFAULT (0),
[AR_CURRENCY] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_AR_CURRENCY] DEFAULT (0),
[ORDER_MODEL] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_ORDER_MODEL] DEFAULT (0),
[VID_NUMBER] [nvarchar](26) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_VID_NUMBER] DEFAULT (''),
[SHIPPER_ACCOUNT_NUMBER] [nvarchar](10) NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_SHIPPER_ACCOUNT_NUMBER] DEFAULT (''),
[COMMIT_LANGUAGE] [int] NOT NULL CONSTRAINT [DF_ORDERING_HEADER_TABLE_COMMIT_LANGUAGE] DEFAULT (0),
[SHIP_OPTIONS] [int] NOT NULL DEFAULT (0),
[Site_Code] [int] NOT NULL CONSTRAINT [DF__ORDERING_HEADER__Site18D771F0] DEFAULT (1),
[SESSION_ID] [int] NOT NULL DEFAULT (0)
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE] ON [dbo].[ORDERING_HEADER_TABLE]
(
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_CUSTOMER_ID')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_CUSTOMER_ID] ON [dbo].[ORDERING_HEADER_TABLE]
(
[CUSTOMER_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_FLAG_STATE_DATE_CREATED_CUSTOMER_ID_ORDER_MODEL')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_FLAG_STATE_DATE_CREATED_CUSTOMER_ID_ORDER_MODEL] ON [dbo].[ORDERING_HEADER_TABLE]
(
[FLAG_STATE] ASC,
[DATE_CREATED] ASC,
[CUSTOMER_ID] ASC,
[ORDER_MODEL] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_MODEL_DATE_COMMIT')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_MODEL_DATE_COMMIT] ON [dbo].[ORDERING_HEADER_TABLE]
(
[FLAG_STATE] ASC,
[ORDER_MODEL] ASC,
[DATE_COMMIT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE] ON [dbo].[ORDERING_HEADER_TABLE]
(
[FLAG_STATE] ASC,
[ORDER_ID] ASC,
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_IP_ADDRESS')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_IP_ADDRESS] ON [dbo].[ORDERING_HEADER_TABLE]
(
[IP_ADDRESS] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_ORDER_MODEL_FLAG_STATE_ORDER_GLUE')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_ORDER_MODEL_FLAG_STATE_ORDER_GLUE] ON [dbo].[ORDERING_HEADER_TABLE]
(
[ORDER_MODEL] ASC,
[FLAG_STATE] ASC,
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_ORDER_ID')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_ORDER_ID] ON [dbo].[ORDERING_HEADER_TABLE]
(
[ORDER_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERING_HEADER_TABLE]') AND name = N'IX_ORDERING_HEADER_TABLE_VID_NUMBER_ORDER_MODEL')
CREATE NONCLUSTERED INDEX [IX_ORDERING_HEADER_TABLE_VID_NUMBER_ORDER_MODEL] ON [dbo].[ORDERING_HEADER_TABLE]
(
[VID_NUMBER] ASC,
[ORDER_MODEL] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SHIPPING_TABLE](
[ORDER_GLUE] [int] NOT NULL,
[Adrs_City] [nvarchar](31) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_City] DEFAULT (''),
[Adrs_Company] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Company] DEFAULT (''),
[Adrs_Country] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Country] DEFAULT (''),
[Adrs_Country_LOV] [nvarchar](2) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Country_LOV] DEFAULT ('00'),
[Adrs_First_Name] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_First_Name] DEFAULT (''),
[Adrs_Last_Name] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Last_Name] DEFAULT (''),
[Adrs_Line_1] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Line_1] DEFAULT (''),
[Adrs_line_2] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_line_2] DEFAULT (''),
[Adrs_line_3] [nvarchar](40) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_line_3] DEFAULT (''),
[Adrs_Mail_stop] [nvarchar](17) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Mail_stop] DEFAULT (''),
[Adrs_State_Code] [nvarchar](6) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_State_Code] DEFAULT (''),
[Adrs_Zip_Code] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Zip_Code] DEFAULT (''),
[Adrs_Zip_Plus] [nvarchar](5) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Zip_Plus] DEFAULT (''),
[Telephone] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Telephone] DEFAULT (''),
[Email] [nvarchar](65) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Email] DEFAULT (''),
[Fax] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Fax] DEFAULT (''),
[Adrs_Foreign_Territory] [nvarchar](30) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Adrs_Foreign_Territory] DEFAULT (''),
[Flag_Domestic_Adrs] [int] NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Flag_Domestic_Adrs] DEFAULT (1),
[DATE_CREATED] [datetime] NULL CONSTRAINT [DF_SHIPPING_TABLE_DATE_CREATED] DEFAULT (getdate()),
[FLAG_US_TERRITORY] [int] NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_FLAG_US_TERRITORY] DEFAULT (0),
[Source_Code] [int] NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_Source_Code] DEFAULT (0),
[VAT_Number] [nvarchar](20) NOT NULL CONSTRAINT [DF_SHIPPING_TABLE_VID_Number] DEFAULT (''),
[WebSite] [nvarchar](128) NOT NULL DEFAULT (''),
[geocode] [int] NOT NULL DEFAULT (0),
[flag_geocode_choice] [int] NOT NULL DEFAULT (0),
CONSTRAINT [PK_SHIPPING_TABLE] PRIMARY KEY NONCLUSTERED
(
[ORDER_GLUE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go

|||

I've used the DDL you supplied and populated the tables with ~1.5 million rows based on the data distribution you provided earlier in this thread.

When I use:

SELECT h.ship_method,

h.order_id,

h.order_glue,

h.access_id,

h.flag_state,

h.date_created,

h.date_commit,

h.account_rep,

h.class_code,

h.user_identity,

s.adrs_country_lov,

h.currency_code,

h.vid_number,

h.order_model,

h.commit_language,

h.ip_address

FROM ordering_header_table AS h

INNER JOIN SHIPPING_TABLE AS s ON h.order_glue = s.order_glue

WHERE h.order_id > 0

AND h.flag_state = 0

I get the following plan, as expected:

Code Snippet

|--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([tempdb].[dbo].[SHIPPING_TABLE] AS [s]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([h].[ORDER_GLUE]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[ORDERING_HEADER_TABLE] AS [h]))
| |--Index Seek(OBJECT:([tempdb].[dbo].[ORDERING_HEADER_TABLE].[IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE] AS [h]), SEEK:([h].[FLAG_STATE]=0 AND [h].[ORDER_ID] > 0) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[SHIPPING_TABLE].[PK_SHIPPING_TABLE] AS [s]), SEEK:([s].[ORDER_GLUE]=[h].[ORDER_GLUE]) ORDERED FORWARD)

If I specify:

WHERE h.order_id > 0

AND h.flag_state = 3

I get the following, again as expected:

Code Snippet

|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([h].[ORDER_GLUE])=([s].[ORDER_GLUE]))
|--Bitmap(HASH:([h].[ORDER_GLUE]), DEFINE:([Bitmap1002]))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([h].[ORDER_GLUE]))
| |--Table Scan(OBJECT:([tempdb].[dbo].[ORDERING_HEADER_TABLE] AS [h]), WHERE:([h].[FLAG_STATE]=3 AND [h].[ORDER_ID]>0))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([s].[ORDER_GLUE]), WHERE:(PROBE([Bitmap1002])=TRUE))
|--Table Scan(OBJECT:([tempdb].[dbo].[SHIPPING_TABLE] AS [s]))

Does your [IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE] index definitely exist, and is it enabled? Have you recently updated the statistics?

As a last resort, try adding a table hint to see if you can force the use of the index, see below.

Chris

Code Snippet

SELECT h.ship_method,

h.order_id,

h.order_glue,

h.access_id,

h.flag_state,

h.date_created,

h.date_commit,

h.account_rep,

h.class_code,

h.user_identity,

s.adrs_country_lov,

h.currency_code,

h.vid_number,

h.order_model,

h.commit_language,

h.ip_address

FROM ordering_header_table AS h WITH (INDEX ([IX_ORDERING_HEADER_TABLE_FLAG_STATE_ORDER_ID_ORDER_GLUE]))

INNER JOIN SHIPPING_TABLE AS s ON h.order_glue = s.order_glue

WHERE h.order_id > 0

AND h.flag_state = 0

GO

|||

I made two changes and everything seems to be working excellent now!

1) I change my query to force using the index as your suggected above

2) I added on index on the shipping table with order_glue and adrs_country_lov

With these two changes, my query respone time went to approximatelly 90 seconds to 2 seconds

Thanks for you help,

Chris