GilaMonster (1/23/2013)
Can you post the table definition and all index definitions for dbo.order?
Just spotted there are 2 indexes on session_id I will drop one of them.
CREATE TABLE [dbo].[order](
[rsn] [bigint] IDENTITY(1,1) NOT NULL,
[sessionid] [nvarchar](50) NOT NULL,
[order] [int] NOT NULL,
[perc] [decimal](18, 8) NOT NULL,
[userid] [bigint] NOT NULL,
[hashtotal] [decimal](18, 2) NOT NULL,
[dateadded] [datetime] NOT NULL,
[shipped] [tinyint] NOT NULL,
[status] [int] NOT NULL,
[suspectorder] [tinyint] NOT NULL,
[shippingweight] [decimal](18, 2) NOT NULL,
[discount] [decimal](18, 2) NOT NULL,
[shiptotal] [decimal](18, 2) NOT NULL,
[shipopt] [int] NOT NULL,
[rd] [tinyint] NOT NULL,
[giftwrap] [tinyint] NOT NULL,
[bay] [int] NOT NULL,
[instock] [tinyint] NOT NULL,
[beenupdated] [tinyint] NOT NULL,
[shipdate] [datetime] NULL,
[vatamount] [decimal](18, 2) NOT NULL,
[prodname] [nvarchar](250) NOT NULL,
[source] [char](2) NOT NULL,
[externalsessionid] [nvarchar](70) NOT NULL,
[postalrsn] [bigint] NOT NULL,
[printref] [nvarchar](50) NOT NULL,
[sdref] [nvarchar](20) NOT NULL,
[oos] [tinyint] NOT NULL,
[sdreflabel] [nvarchar](20) NOT NULL,
[bin] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[rsn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IX_dateadded] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_dateadded] ON [dbo].[order]
(
[dateadded] ASC
)
INCLUDE ( [shiptotal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_gw] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_gw] ON [dbo].[order]
(
[giftwrap] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_oh] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_oh] ON [dbo].[order]
(
[order] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_printeref] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_printeref] ON [dbo].[order]
(
[printref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_rd] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_rd] ON [dbo].[order]
(
[rd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_sess] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_sess] ON [dbo].[order]
(
[sessionid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_sessionid] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[order]
(
[sessionid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_shipopt] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_shipopt] ON [dbo].[order]
(
[shipopt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_status] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_status] ON [dbo].[order]
(
[status] ASC
)
INCLUDE ( [printref]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_suspect] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_suspect] ON [dbo].[order]
(
[suspectorder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sessionid] DEFAULT ('') FOR [sessionid]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_order] DEFAULT ((0)) FOR [order]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_perc] DEFAULT ((0)) FOR [perc]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_userid] DEFAULT ((0)) FOR [userid]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_hashtotal] DEFAULT ((0)) FOR [hashtotal]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_dateadded] DEFAULT (getdate()) FOR [dateadded]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipped] DEFAULT ((0)) FOR [shipped]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipping] DEFAULT ((0)) FOR [status]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_suspectorder] DEFAULT ((0)) FOR [suspectorder]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shippingweight] DEFAULT ((0)) FOR [shippingweight]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_discount] DEFAULT ((0)) FOR [discount]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shiptotal] DEFAULT ((0)) FOR [shiptotal]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipopt] DEFAULT ((0)) FOR [shipopt]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_rd] DEFAULT ((0)) FOR [rd]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_giftwrap] DEFAULT ((0)) FOR [giftwrap]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_bay] DEFAULT ((0)) FOR [bay]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_instock] DEFAULT ((0)) FOR [instock]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_beenupdated] DEFAULT ((0)) FOR [beenupdated]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_vatamount] DEFAULT ((0)) FOR [vatamount]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_prodname] DEFAULT ('') FOR [prodname]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_source] DEFAULT ('IC') FOR [source]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_externalsessionid] DEFAULT ('') FOR [externalsessionid]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_postalrsn] DEFAULT ((0)) FOR [postalrsn]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_printref] DEFAULT ('') FOR [printref]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sdref] DEFAULT ('') FOR [sdref]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_oos] DEFAULT ((0)) FOR [oos]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sdreflabel] DEFAULT ('') FOR [sdreflabel]
GO
ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_bin] DEFAULT ('') FOR [bin]
GO