Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Deadlocks Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 9:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 40,201, Visits: 36,603
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.


In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels



Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.


Optimise your queries, don't mess with locking hints unless you really understand what you're doing and why and have optimised as much as possible.
You do realise that a row lock hint means more chance of lock escalation (to table), not less?


Nope I did not realize that! I thought it was specific to the row being updated


Depends how many rows the update effects. If it's one row (and the stats estimate that), SQL would probably go for a row lock anyway. If it's several thousand rows, SQL would probably go for page locks, if you put a row lock hint on, SQL will start with row locks, if the number of locks held pass a threshold, it escalates to table locks.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1410108
Posted Tuesday, January 22, 2013 11:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 193, Visits: 684
anthony.green (1/22/2013)
Do you have traceflag 1222 enabled?

If so can you post the deadlock graph from the error log

If not run

DBCC TRACEON (1222, -1)

and then post the graphs once a deadlock has occured.


Hi Anthony

I have this trace on but cant find the deadlock graph anywhere in the errorlog? The deadlock has been recorded but thats all.
Post #1410202
Posted Wednesday, January 23, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
Do you have the deadlock XML in the error log, should be many many lines that looks like this

<TextData><deadlock-list>
<deadlock victim="process53b9288">
<process-list>
<process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410400
Posted Wednesday, January 23, 2013 2:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 193, Visits: 684
anthony.green (1/23/2013)
Do you have the deadlock XML in the error log, should be many many lines that looks like this

<TextData><deadlock-list>
<deadlock victim="process53b9288">
<process-list>
<process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">



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.

01/23/2013 07:25:31,spid23s,Unknown,waiter id=process27317b4c8 mode=X requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=processdb54c8 mode=S
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057605790367744 dbid=4 objectname=dbo.order indexname=IX_status id=lock3d60c8e80 mode=S associatedObjectId=72057605790367744
01/23/2013 07:25:31,spid23s,Unknown,waiter id=processdb54c8 mode=S requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=process27317b4c8 mode=X
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057602492792832 dbid=4 objectname=dbo.order indexname=PK_order id=lock3d27aec80 mode=X associatedObjectId=72057602492792832
01/23/2013 07:25:31,spid23s,Unknown,resource-list
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000880e2612bdc1c85889b28ed1a83337436c23418e
01/23/2013 07:25:31,spid23s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [order] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [order]%%=%%@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=process27317b4c8 taskpriority=0 logused=292 waitresource=KEY: 9:72057605790367744 (2e001d1b37ce) waittime=1840 ownerId=18323630766 transactionname=UPDATE lasttranstarted=2013-01-23T07:25:29.427 XDES=0xe4e2b970 lockMode=X schedulerid=8 kpid=2092 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-23T07:25:29.420 lastbatchcompleted=2013-01-23T07:25:29.420 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630766 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,SELECT count(rsn) As Count FROM order WHERE status<3 AND shipopt <99 AND dateadded <= '2013-01-23 13:00:59'
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000712b952eb475b9b249be45c1513acca8b2b31ed1
01/23/2013 07:25:31,spid23s,Unknown,(@1 tinyint<c/>@2 tinyint<c/>@3 tinyint<c/>@4 varchar(8000))SELECT COUNT([rsn]) [Count] FROM [order] WHERE [status]<@1 AND [shipopt]<@3 AND [dateadded]<=@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000007c853201340a85cf70825b9c171de622351e36b1
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=processdb54c8 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (2c00849cfb3c) waittime=1840 ownerId=18323630729 transactionname=SELECT lasttranstarted=2013-01-23T07:25:29.423 XDES=0x1c384fb80 lockMode=S schedulerid=7 kpid=8128 status=suspended spid=51 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-01-23T07:25:29.423 lastbatchcompleted=2013-01-23T07:25:29.280 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630729 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,process-list
01/23/2013 07:25:31,spid23s,Unknown,deadlock victim=processdb54c8
01/23/2013 07:25:31,spid23s,Unknown,deadlock-list
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy0x000000035B1E3770) Value:0xce952bc0 Cost0/0)
01/23/2013 07:25:31,spid5s,Unknown,Victim Resource Owner:
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000E4E2B970 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxy0x000000024AD44538) Value:0xbd6264c0 Cost0/292)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: No Event:
01/23/2013 07:25:31,spid5s,Unknown,SPID: 51 ECID: 4 Statement Type: SELECT Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x00000001BF286480 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:51 ECID:4 XactLockInfo: 0x00000001C384FBC0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 2:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057605790367744 (2e001d1b37ce) CleanCnt:2 Mode:S Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:2
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy0x000000035B1E3770) Value:0xce952bc0 Cost0/0)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: Language Event: update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x000000008164B2C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000000E4E2B9B0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 1:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057602492792832 (2c00849cfb3c) CleanCnt:2 Mode:X Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:1
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,Wait-for graph
01/23/2013 07:25:31,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

Post #1410421
Posted Wednesday, January 23, 2013 6:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 40,201, Visits: 36,603
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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1410527
Posted Wednesday, January 23, 2013 6:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 193, Visits: 684
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.

Post #1410532
Posted Wednesday, January 23, 2013 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
)





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410537
Posted Wednesday, January 23, 2013 7:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 40,201, Visits: 36,603
Can you post the table definition and all index definitions for dbo.order?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1410552
Posted Wednesday, January 23, 2013 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 193, Visits: 684
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.
Post #1410613
Posted Wednesday, January 23, 2013 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 193, Visits: 684
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



Post #1410625
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse