Wednesday, March 21, 2012
Index on identity column
I am hoping that someone has come across the following situation and
can give me some insight on their findings. I have a table that logs a
lot of information that is then staged into a data warehouse. This
table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
database inserts get jammed up and bring my server almost to a halt.
The inserts keep coming in but the server just cannot keep up with
heavy load. I have had this same setup do 100 transactions a second
with no problem so the hardware us up to par. After the slow down has
started, I can try rebooting the server, stopping services etc but
will not the fix the problem. The only thing that I have found to work
is to rebuild or defrag the index on the identity column. I have used
both a clustered and non clustered index on this column and the
behaviour is similiar after the 20 - 30 day timeframe. I realize that
as pages get fragmented it greatly slows down insert performance but I
don't see how a clustered index can get that fragmented on an identity
column that it would cause a slowdown as a result of page splitting
which I beleive to be the cause. Taking the index off this column is
not an option because I need to quickly scan this table for data
warehouse population. This table is now at 130,000,000 rows and I plan
to truncate this down every few months as I don't need the
transactional data for very long. So, I guess my question is is there
any advice that you can give me on maybe managing these indexes to
avoid this problem or is a rebuild or defrag my only option? BTW I am
running SQL Server 2000. Thank you.
CoreyHi Corey
What makes you think page splits are the problem? Have you looked at the
Performance Monitor counter Page Splits/second when you are having your
slowdowns and compared it to the page splitting rate when not having
problems. Have you run dbcc showcontig to see what kind of fragmentation you
are getting? Do you ever actually rebuild the index or indexes?
Actually, 20-30 days is a long time to not do any maintenance on an index
used for heavy insert operations. You're lucky you don't have problems more
often. I would suggest rebuilding the index on a weekly basis, with a
fillfactor less than full. I couldn't suggest a particular value for the
fillfactor, that would need to be determined by testing, but you might start
with rebuilding the index with a fillfactor of 75 (or 75% of the current
value, which SQL Server attempts to observe during defragging) and see how
that works.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Corey" <kildenc@.hotmail.com> wrote in message
news:fbb80b9b.0401051445.3bc0c22d@.posting.google.com...
> Hi everyone,
> I am hoping that someone has come across the following situation and
> can give me some insight on their findings. I have a table that logs a
> lot of information that is then staged into a data warehouse. This
> table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
> database inserts get jammed up and bring my server almost to a halt.
> The inserts keep coming in but the server just cannot keep up with
> heavy load. I have had this same setup do 100 transactions a second
> with no problem so the hardware us up to par. After the slow down has
> started, I can try rebooting the server, stopping services etc but
> will not the fix the problem. The only thing that I have found to work
> is to rebuild or defrag the index on the identity column. I have used
> both a clustered and non clustered index on this column and the
> behaviour is similiar after the 20 - 30 day timeframe. I realize that
> as pages get fragmented it greatly slows down insert performance but I
> don't see how a clustered index can get that fragmented on an identity
> column that it would cause a slowdown as a result of page splitting
> which I beleive to be the cause. Taking the index off this column is
> not an option because I need to quickly scan this table for data
> warehouse population. This table is now at 130,000,000 rows and I plan
> to truncate this down every few months as I don't need the
> transactional data for very long. So, I guess my question is is there
> any advice that you can give me on maybe managing these indexes to
> avoid this problem or is a rebuild or defrag my only option? BTW I am
> running SQL Server 2000. Thank you.
> Corey|||What recovery mode are you in for the database? If it is a warehouse you
might be able to use simple recovery which will help you with the log
growing. Also, what other indexes are on the table? I would definitely
recommend keeping a clustered index on that table as heaps reclaim empty
space and if you are deleting and inserting that would cost you as well.
When you get to the point where you can not insert any records what is going
on with performance? As Kalen mentioned knowing if you are having page
splits or blocking/deadlocks will help you to remedy the situation.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Corey" <kildenc@.hotmail.com> wrote in message
news:fbb80b9b.0401051445.3bc0c22d@.posting.google.com...
> Hi everyone,
> I am hoping that someone has come across the following situation and
> can give me some insight on their findings. I have a table that logs a
> lot of information that is then staged into a data warehouse. This
> table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
> database inserts get jammed up and bring my server almost to a halt.
> The inserts keep coming in but the server just cannot keep up with
> heavy load. I have had this same setup do 100 transactions a second
> with no problem so the hardware us up to par. After the slow down has
> started, I can try rebooting the server, stopping services etc but
> will not the fix the problem. The only thing that I have found to work
> is to rebuild or defrag the index on the identity column. I have used
> both a clustered and non clustered index on this column and the
> behaviour is similiar after the 20 - 30 day timeframe. I realize that
> as pages get fragmented it greatly slows down insert performance but I
> don't see how a clustered index can get that fragmented on an identity
> column that it would cause a slowdown as a result of page splitting
> which I beleive to be the cause. Taking the index off this column is
> not an option because I need to quickly scan this table for data
> warehouse population. This table is now at 130,000,000 rows and I plan
> to truncate this down every few months as I don't need the
> transactional data for very long. So, I guess my question is is there
> any advice that you can give me on maybe managing these indexes to
> avoid this problem or is a rebuild or defrag my only option? BTW I am
> running SQL Server 2000. Thank you.
> Corey
Monday, March 19, 2012
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]), WHERE
PROBE([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