January 23, 2013 at 6:32 am
GilaMonster (1/23/2013)
bugg (1/23/2013)
Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.That's a deadlock graph. 2 actually, since you enabled both traceflags 1204 and 1222 resulting an a messed up combination of old style of old and new deadlock graphs
Ohh...I was expecting an actual graph. I will disable the old trace. Still not sure how to eliminate the deadlock.
January 23, 2013 at 6:48 am
All statistics and indexes rebuilt and updated on the order table?
Can you post the execution plans as a SQLPLAN file from the below query, if they exist any more.
select
query_plan
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_query_plan (plan_handle)
where
sql_handle in
(
0x02000000880e2612bdc1c85889b28ed1a83337436c23418e,
0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a,
0x020000007c853201340a85cf70825b9c171de622351e36b1
)
January 23, 2013 at 7:04 am
Can you post the table definition and all index definitions for dbo.order?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2013 at 8:22 am
anthony.green (1/23/2013)
All statistics and indexes rebuilt and updated on the order table?Can you post the execution plans as a SQLPLAN file from the below query, if they exist any more.
select
query_plan
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_query_plan (plan_handle)
where
sql_handle in
(
0x02000000880e2612bdc1c85889b28ed1a83337436c23418e,
0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a,
0x020000007c853201340a85cf70825b9c171de622351e36b1
)
The Indexes are rebuilt every night depending on how fragmented they are. They all seem to be pretty much up to date on the order table.
Unfortunately the execution plans do not exist anymore.
January 23, 2013 at 8:36 am
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
January 23, 2013 at 8:59 am
That's a whole lot of fairly useless indexes. 🙁
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2013 at 9:12 am
GilaMonster (1/23/2013)
That's a whole lot of fairly useless indexes. 🙁
🙁 why do you say that? Could you recommend better ones? thanks
January 23, 2013 at 9:22 am
Single column nonclustered indexes. Great for when all your queries filter on a single column only. Not great otherwise.
http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/
Don't have time to look at the deadlock graph right now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply