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 12»»

Why am I getting deadlocks? Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 1:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:04 AM
Points: 10, Visits: 51
I'm pulling out my hair. The cluster is 2 nodes with dedicated HP blade,s 8 cores, 32gb of RAM, 4x1gb team NIC. We've got 23 databases, some quite small, a few bigger. The top three are 60gb, 40gb and 30gb in size. All our databases are supporting COTS, so we have zero control over what's getting executed on them.

The SAN is 4gb fiber to fiber channel drives. The spindles are dedicated to these servers (the SAN controller is shared).

D: - RAID5, Data
E: - RAID10, Logs (Had to fight hard to get RAID10)
F: - RAID5, Backups

Nightly, full index rebuilds on all databases (we're not a 24 hour shop, phew). Some databases are full recovery, t-log every hour between 6AM and 9PM. Others are simple.

Optimize for ad-hoc queries is enabled, and MAXDOP is set to 4. We have 2 CPU's, 4 cores per, startup log shows two NUMA nodes.

Page life expectancy is huge - ~21,000 right now as I type during our peak usage. Buffer cache hit is over 99%.

Our CPU usage averages around 5%, with peaks to 40-60%.

Here's the wait statistics

WaitType	Wait_S		Resource_S	SignalS	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
LCK_M_S 1291140.62 1291124.89 15.74 79947 41.15 16.1500 16.1498 0.0002
CXPACKET 521297.72 459353.79 61943.9 82026137 16.61 0.0064 0.0056 0.0008
ASYNC_NETWORKIO 398223.35 395635.23 2588.12 27401498 12.69 0.0145 0.0144 0.0001
OLEDB 180090.60 180090.60 0.00 3608235839 5.74 0.0000 0.0000 0.0000
WRITELOG 149349.00 139786.89 9562.11 92876862 4.76 0.0016 0.0015 0.0001
DBMIRROR_SEND 142664.12 141938.88 725.24 4103374 4.55 0.0348 0.0346 0.0002
PAGEIOLATCH_SH 92814.70 92679.55 135.15 5017440 2.96 0.0185 0.0185 0.0000
PAGEIOLATCH_EX 54933.52 54866.74 66.78 6491651 1.75 0.0085 0.0085 0.0000
BACKUPIO 44246.57 44009.32 237.25 12191895 1.41 0.0036 0.0036 0.0000


The LCK_M_S is probably from one COTS where they implemented their own locking mechanism. Actually, it's a pretty big vendor, and we opened a ticket to say hey, you're getting locks, and they said it's working as intended.

I've gone through a lot of indexes as well and deleted indexes that had a large number of updates but were never used, as well as created a few missing indexes to improve performance (I didn't run tuning advisor and then create every index that it told me to :) )

Page splits look good, around 1 per second - the ratio of splits to batch requests averages 0. All the spindle/CPU queues are zero on average. The disk latency on C: is horrible, luckily that only holds program files. The latency on the SAN disks are good, sometimes the RAID5 is a little slower - the average is 5ms read, hits 10ms pretty often in spikes. The write on the log drive is smokin - 1ms average. Disk idle times average 100%.

I'm not sure what's going on with TEMPDB, but I assume the stats are skewed from rebuilding the indexes at night and just thrashing the disks. TEMPDB has 4 files. The average read stall is 3.6ms, average write stall ms is 2800ms. I wish I could reset these in the morning.

Short of getting the developers to change their queries (impossible?), I'm not sure if there's anything else I can do as a DBA...
Post #1407472
Posted Tuesday, January 15, 2013 1:58 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
Deadlocks aren't necessarily due to performance. You can have the fastest performing system in the world and still have deadlocks. Poorly written applications can cause quite a few. See the post below and it might help narrow down your culprit.

http://richardlees.blogspot.com/2011/11/causing-deadlock-in-sql-server.html

older but still good:
http://www.sql-server-performance.com/2006/deadlocks/


---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1407486
Posted Tuesday, January 15, 2013 2:00 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Deadlocks are mostly caused by poorly written queries and inadequate indexing, not hardware, recovery models, fragmentation or memory stats (though they can have an influence, except for recovery model that is)

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)




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 #1407487
Posted Tuesday, January 15, 2013 2:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:04 AM
Points: 10, Visits: 51
Yeah, that's what I fear :) As they are all commercial products, there's nothing I can do as a DBA.
Post #1407488
Posted Tuesday, January 15, 2013 10:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 2:31 PM
Points: 11, Visits: 74
Like the other posters mentioned, it's not the hardware that is causing your problems (more than likely not at least), its the size of your user base vs the quality of the queries. We use SQL Sentry at my office and its really good at providing detailed stats about the deadlocks, the offending procedures and even the code segments all piled into a nicely displayed gui that is easy to navigate.

You can do it without the expensive software, but I totally recommend it. I'm a visual person and it's helped my team pin point a lot of areas for improvement and we operate at about 1600 transactions/s on a low to mid range of our peak.

Only other thought I have is the tempdb, any issues there? every filled it up? is it running on it's own disk? can it?

Sounds like your hands are tied due to the commercial products you're hosting, but if you can trace the problem to the software, you could have a case that you could bring to the support teams of your respective products. Remember, they need your business! Most vendors are quite helpful so long as you present the evidence and show that you're willing to work with them. Good luck!
Post #1407595
Posted Wednesday, January 16, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:04 AM
Points: 10, Visits: 51
Sorry, for some reason SSMS refuses to sort the time descending.

Date,Source,Severity,Message

01/16/2013 09:10:02,spid32s,Unknown,waiter id=processae84bb88 mode=U requestType=wait
01/16/2013 09:10:02,spid32s,Unknown,waiter-list
01/16/2013 09:10:02,spid32s,Unknown,owner id=process770db88 mode=U
01/16/2013 09:10:02,spid32s,Unknown,owner-list
01/16/2013 09:10:02,spid32s,Unknown,keylock hobtid=72057647025356800 dbid=26 objectname=AX_ASSYST.dbo.jptsys_isel indexname=isel1_dx id=lock3d8f8aa80 mode=U associatedObjectId=72057647025356800
01/16/2013 09:10:02,spid32s,Unknown,waiter id=process770db88 mode=U requestType=wait
01/16/2013 09:10:02,spid32s,Unknown,waiter-list
01/16/2013 09:10:02,spid32s,Unknown,owner id=processae84bb88 mode=X
01/16/2013 09:10:02,spid32s,Unknown,owner-list
01/16/2013 09:10:02,spid32s,Unknown,ridlock fileid=7 pageid=26464 dbid=26 objectname=AX_ASSYST.dbo.jptsys_isel id=lock4eba0af80 mode=X associatedObjectId=72057638693961728
01/16/2013 09:10:02,spid32s,Unknown,resource-list
01/16/2013 09:10:02,spid32s,Unknown,(@P1 int<c/>@P2 int<c/>@P3 int<c/>@P4 nvarchar(30)<c/>@P5 nvarchar(100))update jptsys_isel SET display_order =@P1 <c/> display_width =6 <c/> custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )
01/16/2013 09:10:02,spid32s,Unknown,inputbuf
01/16/2013 09:10:02,spid32s,Unknown,unknown
01/16/2013 09:10:02,spid32s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
01/16/2013 09:10:02,spid32s,Unknown,update jptsys_isel SET display_order =@P1 <c/> display_width =6 <c/> custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )
01/16/2013 09:10:02,spid32s,Unknown,frame procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0d
01/16/2013 09:10:02,spid32s,Unknown,executionStack
01/16/2013 09:10:02,spid32s,Unknown,process id=processae84bb88 taskpriority=0 logused=2252 waitresource=KEY: 26:72057647025356800 (a41650c31ce0) waittime=1077 ownerId=1198849374 transactionname=implicit_transaction lasttranstarted=2013-01-16T09:09:58.523 XDES=0x3d51c73c0 lockMode=U schedulerid=1 kpid=4756 status=suspended spid=341 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-16T09:10:00.963 lastbatchcompleted=2013-01-16T09:10:00.880 clientapp=assyst Enterprise Suite hostname=HOST2xxxx hostpid=3680 loginname=yyyy isolationlevel=read uncommitted (1) xactid=1198849374 currentdb=26 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128058
01/16/2013 09:10:02,spid32s,Unknown,(@P1 int<c/>@P2 int<c/>@P3 int<c/>@P4 nvarchar(30)<c/>@P5 nvarchar(100))update jptsys_isel SET display_order =@P1 <c/> display_width =6 <c/> custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )
01/16/2013 09:10:02,spid32s,Unknown,inputbuf
01/16/2013 09:10:02,spid32s,Unknown,unknown
01/16/2013 09:10:02,spid32s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
01/16/2013 09:10:02,spid32s,Unknown,update jptsys_isel SET display_order =@P1 <c/> display_width =6 <c/> custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )
01/16/2013 09:10:02,spid32s,Unknown,frame procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0d
01/16/2013 09:10:02,spid32s,Unknown,executionStack
01/16/2013 09:10:02,spid32s,Unknown,process id=process770db88 taskpriority=0 logused=0 waitresource=RID: 26:7:26464:0 waittime=1130 ownerId=1198846832 transactionname=implicit_transaction lasttranstarted=2013-01-16T09:09:52.350 XDES=0xc9843950 lockMode=U schedulerid=5 kpid=6528 status=suspended spid=479 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-16T09:10:00.910 lastbatchcompleted=2013-01-16T09:10:00.910 clientapp=assyst Enterprise Suite hostname=HOST1xx hostpid=1628 loginname=xxx isolationlevel=read committed (2) xactid=1198846832 currentdb=26 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128058
01/16/2013 09:10:02,spid32s,Unknown,process-list
01/16/2013 09:10:02,spid32s,Unknown,deadlock victim=process770db88
01/16/2013 09:10:02,spid32s,Unknown,deadlock-list
01/16/2013 09:10:02,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000C9843950 Mode: U SPID:479 BatchID:0 ECID:0 TaskProxy0x0000000117EE6540) Value:0x11eac5c0 Cost0/0)
01/16/2013 09:10:02,spid5s,Unknown,Victim Resource Owner:
01/16/2013 09:10:02,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/16/2013 09:10:02,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000003D51C73C0 Mode: U SPID:341 BatchID:0 ECID:0 TaskProxy0x0000000547EC6540) Value:0x1d1da440 Cost0/2252)
01/16/2013 09:10:02,spid5s,Unknown,Requested by:
01/16/2013 09:10:02,spid5s,Unknown,Input Buf: Language Event: (@P1 int<c/>@P2 int<c/>@P3 int<c/>@P4 nvarchar(30)<c/>@P5 nvarchar(100))update jptsys_isel SET display_order =@P1 <c/> display_width =6 <c/> custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )
01/16/2013 09:10:02,spid5s,Unknown,SPID: 479 ECID: 0 Statement Type: UPDATE Line #: 1
01/16/2013 09:10:02,spid5s,Unknown,Owner:0x0000000403B2C840 Mode: U Flg:0x40 Ref:1 Life:00000000 SPID:479 ECID:0 XactLockInfo: 0x00000000C9843990
01/16/2013 09:10:02,spid5s,Unknown,Grant List 2:
01/16/2013 09:10:02,spid5s,Unknown,KEY: 26:72057647025356800 (a41650c31ce0) CleanCnt:2 Mode:U Flags: 0x1
01/16/2013 09:10:02,spid5s,Unknown,Node:2
01/16/2013 09:10:02,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/16/2013 09:10:02,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000C9843950 Mode: U SPID:479 BatchID:0 ECID:0 TaskProxy0x0000000117EE6540) Value:0x11eac5c0 Cost0/0)
01/16/2013 09:10:02,spid5s,Unknown,Requested by:
01/16/2013 09:10:02,spid5s,Unknown,Input Buf: Language Event: (@P1 int<c/>@P2 int<c/>@P3 int<c/>@P4 nvarchar(30)<c/>@P5 nvarchar(100))update jptsys_isel SET display_order =@P1 <c/> display_width =6 <c/> custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )
01/16/2013 09:10:02,spid5s,Unknown,SPID: 341 ECID: 0 Statement Type: UPDATE Line #: 1
01/16/2013 09:10:02,spid5s,Unknown,Owner:0x000000042E671700 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:341 ECID:0 XactLockInfo: 0x00000003D51C7400
01/16/2013 09:10:02,spid5s,Unknown,Grant List 0:
01/16/2013 09:10:02,spid5s,Unknown,RID: 26:7:26464:0 CleanCnt:2 Mode:X Flags: 0x3
01/16/2013 09:10:02,spid5s,Unknown,Node:1
01/16/2013 09:10:02,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/16/2013 09:10:02,spid5s,Unknown,Wait-for graph
01/16/2013 09:10:02,spid5s,Unknown,Deadlock encountered .... Printing deadlock information
Post #1407879
Posted Wednesday, January 16, 2013 9:23 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Edit: nm

For future reference, just traceflag 1222. You have 1204 on as well which resulted in a mess of a mixture of two deadlock graphs that's going to take far more work than necessary to tease apart.



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 #1407922
Posted Wednesday, January 16, 2013 11:24 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Deadlock graph without the mess from traceflag 1204.

deadlock-list
deadlock victim=process770db88
process-list

process id=process770db88 taskpriority=0 logused=0 waitresource=RID: 26:7:26464:0 waittime=1130 ownerId=1198846832 transactionname=implicit_transaction lasttranstarted=2013-01-16T09:09:52.350 XDES=0xc9843950 lockMode=U schedulerid=5 kpid=6528 status=suspended spid=479 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-16T09:10:00.910 lastbatchcompleted=2013-01-16T09:10:00.910 clientapp=assyst Enterprise Suite hostname=HOST1xx hostpid=1628 loginname=xxx isolationlevel=read committed (2) xactid=1198846832 currentdb=26 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128058

executionStack
frame procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0d
update jptsys_isel SET display_order =@P1 , display_width =6 , custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )

frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@P1 int, @P2 int, @P3 int, @P4 nvarchar(30), @P5 nvarchar(100)) update jptsys_isel SET display_order =@P1, display_width =6 , custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )

process id=processae84bb88 taskpriority=0 logused=2252 waitresource=KEY: 26:72057647025356800 (a41650c31ce0) waittime=1077 ownerId=1198849374 transactionname=implicit_transaction lasttranstarted=2013-01-16T09:09:58.523 XDES=0x3d51c73c0 lockMode=U schedulerid=1 kpid=4756 status=suspended spid=341 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-16T09:10:00.963 lastbatchcompleted=2013-01-16T09:10:00.880 clientapp=assyst Enterprise Suite hostname=HOST2xxxx hostpid=3680 loginname=yyyy isolationlevel=read uncommitted (1) xactid=1198849374 currentdb=26 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128058
executionStack

frame procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0d
update jptsys_isel SET display_order =@P1 , display_width =6, custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )

frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@P1 int, @P2 int, @P3 int, @P4 nvarchar(30), @P5 nvarchar(100))update jptsys_isel SET display_order =@P1, display_width =6 , custom_width =@P2 WHERE ( jptsys_isel.assyst_usr_id =@P3 ) AND ( jptsys_isel.isel_name =@P4 ) AND ( jptsys_isel.field_name =@P5 )

resource-list

ridlock fileid=7 pageid=26464 dbid=26 objectname=AX_ASSYST.dbo.jptsys_isel id=lock4eba0af80 mode=X associatedObjectId=72057638693961728
owner-list
owner id=processae84bb88 mode=X
waiter-list
waiter id=process770db88 mode=U requestType=wait

keylock hobtid=72057647025356800 dbid=26 objectname=AX_ASSYST.dbo.jptsys_isel indexname=isel1_dx id=lock3d8f8aa80 mode=U associatedObjectId=72057647025356800
owner-list
owner id=process770db88 mode=U
waiter-list
waiter id=processae84bb88 mode=U requestType=wait



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 #1408014
Posted Wednesday, January 16, 2013 11:32 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
What's the definition of AX_ASSYST.dbo.jptsys_isel and what indexes does it have?


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 #1408016
Posted Wednesday, January 16, 2013 11:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:04 AM
Points: 10, Visits: 51
8755 rows, 2 megs of data, 1 meg of index.

/****** Object:  Table [dbo].[jptsys_isel]    Script Date: 01/16/2013 13:35:07 ******/
CREATE TABLE [dbo].[jptsys_isel](
[jptsys_isel_id] [int] NOT NULL,
[assyst_usr_id] [int] NOT NULL,
[isel_name] [nvarchar](30) NULL,
[join_label] [nvarchar](100) NULL,
[field_name] [nvarchar](100) NOT NULL,
[col_name] [nvarchar](100) NOT NULL,
[display_value] [nvarchar](100) NOT NULL,
[display_order] [int] NULL,
[display_width] [int] NULL,
[item_order] [int] NULL,
[direction] [char](1) NULL,
[sort_alias] [nvarchar](100) NULL,
[custom_width] [int] NULL,
[hib_property] [nvarchar](254) NULL,
[is_derived] [char](1) NOT NULL,
[type] [int] NOT NULL,
CONSTRAINT [isel_pk] PRIMARY KEY NONCLUSTERED
(
[jptsys_isel_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 30) ON [axios_seg_5]
) ON [axios_seg_4]

ALTER TABLE [dbo].[jptsys_isel] ADD CONSTRAINT [isel_pk] PRIMARY KEY NONCLUSTERED
(
[jptsys_isel_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 30) ON [axios_seg_5]
GO

USE [AX_ASSYST]
GO

CREATE NONCLUSTERED INDEX [isel1_dx] ON [dbo].[jptsys_isel]
(
[assyst_usr_id] 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 = 30) ON [axios_seg_6]
GO

USE [AX_ASSYST]
GO


CREATE NONCLUSTERED INDEX [isel2_dx] ON [dbo].[jptsys_isel]
(
[type] 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 [axios_seg_4]
GO






Post #1408026
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse