InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 17:43:19 GMT20http://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxAgreed on changing the pk to clustered.Can't tell without lots of analysis, but single column nonclustered indexes are typically not very useful. Maybe check how often those are used?The index isel1_dx should be widened as follows:Index key: (assyst_usr_id, isel_name, field_name)Wed, 16 Jan 2013 13:10:29 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxYeah, unfortunately it's out of my control with the vendor, maybe I can yell at them a bit.I did change a bunch of heaps over, but I figured as this one is 1 megabyte, it shouldn't make a difference. I'll give it a shot and see what happens.Wed, 16 Jan 2013 13:02:22 GMTgreg.cormierhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxAs a start, I suggest changing the primary key ,[isel_pk], to clustered with a fill factor of 80 and changing the index [isel1_dx] to a fillfactor of 80.Also, one of the processes in the dead lock was running with an isolation level of read uncommitted. It is better if you never use that isolation level in a production application.Wed, 16 Jan 2013 12:56:08 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxSorry, yes, ignore the duplicate.I scripted the table def, then scripted each key. I should have skipped the PK.Wed, 16 Jan 2013 12:44:16 GMTgreg.cormierhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspx[quote][b]greg.cormier (1/16/2013)[/b][hr]8755 rows, 2 megs of data, 1 meg of index.[/quote]That table definition can't be right, it has two primary keys defined.Wed, 16 Jan 2013 12:41:21 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspx8755 rows, 2 megs of data, 1 meg of index.[code="sql"]/****** 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]GOUSE [AX_ASSYST]GOCREATE 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]GOUSE [AX_ASSYST]GOCREATE 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[/code]Wed, 16 Jan 2013 11:50:38 GMTgreg.cormierhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxWhat's the definition of AX_ASSYST.dbo.jptsys_isel and what indexes does it have?Wed, 16 Jan 2013 11:32:52 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxDeadlock graph without the mess from traceflag 1204.deadlock-listdeadlock victim=process770db88process-listprocess 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=128058executionStackframe procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0dupdate 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=0x000000000000000000000000000000000000000000000000unknowninputbuf(@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=128058executionStackframe procname=adhoc line=1 stmtstart=120 sqlhandle=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0dupdate 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=0x000000000000000000000000000000000000000000000000unknowninputbuf(@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-listridlock fileid=7 pageid=26464 dbid=26 objectname=AX_ASSYST.dbo.jptsys_isel id=lock4eba0af80 mode=X associatedObjectId=72057638693961728owner-listowner id=processae84bb88 mode=Xwaiter-listwaiter id=process770db88 mode=U requestType=waitkeylock hobtid=72057647025356800 dbid=26 objectname=AX_ASSYST.dbo.jptsys_isel indexname=isel1_dx id=lock3d8f8aa80 mode=U associatedObjectId=72057647025356800owner-listowner id=process770db88 mode=Uwaiter-listwaiter id=processae84bb88 mode=U requestType=waitWed, 16 Jan 2013 11:24:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxEdit: nmFor 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.Wed, 16 Jan 2013 09:23:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxSorry, for some reason SSMS refuses to sort the time descending. [code="plain"]Date,Source,Severity,Message01/16/2013 09:10:02,spid32s,Unknown,waiter id=processae84bb88 mode=U requestType=wait01/16/2013 09:10:02,spid32s,Unknown,waiter-list01/16/2013 09:10:02,spid32s,Unknown,owner id=process770db88 mode=U01/16/2013 09:10:02,spid32s,Unknown,owner-list01/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=7205764702535680001/16/2013 09:10:02,spid32s,Unknown,waiter id=process770db88 mode=U requestType=wait01/16/2013 09:10:02,spid32s,Unknown,waiter-list01/16/2013 09:10:02,spid32s,Unknown,owner id=processae84bb88 mode=X01/16/2013 09:10:02,spid32s,Unknown,owner-list01/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=7205763869396172801/16/2013 09:10:02,spid32s,Unknown,resource-list01/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,inputbuf01/16/2013 09:10:02,spid32s,Unknown,unknown01/16/2013 09:10:02,spid32s,Unknown,frame procname=unknown line=1 sqlhandle=0x00000000000000000000000000000000000000000000000001/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=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0d01/16/2013 09:10:02,spid32s,Unknown,executionStack01/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=12805801/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,inputbuf01/16/2013 09:10:02,spid32s,Unknown,unknown01/16/2013 09:10:02,spid32s,Unknown,frame procname=unknown line=1 sqlhandle=0x00000000000000000000000000000000000000000000000001/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=0x0200000021e00a0101fc6fc2c7d344252ed2da1de7167c0d01/16/2013 09:10:02,spid32s,Unknown,executionStack01/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=12805801/16/2013 09:10:02,spid32s,Unknown,process-list01/16/2013 09:10:02,spid32s,Unknown,deadlock victim=process770db8801/16/2013 09:10:02,spid32s,Unknown,deadlock-list01/16/2013 09:10:02,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000C9843950 Mode: U SPID:479 BatchID:0 ECID:0 TaskProxy:(0x0000000117EE6540) Value:0x11eac5c0 Cost:(0/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 TaskProxy:(0x0000000547EC6540) Value:0x1d1da440 Cost:(0/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 #: 101/16/2013 09:10:02,spid5s,Unknown,Owner:0x0000000403B2C840 Mode: U Flg:0x40 Ref:1 Life:00000000 SPID:479 ECID:0 XactLockInfo: 0x00000000C984399001/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: 0x101/16/2013 09:10:02,spid5s,Unknown,Node:201/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 TaskProxy:(0x0000000117EE6540) Value:0x11eac5c0 Cost:(0/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 #: 101/16/2013 09:10:02,spid5s,Unknown,Owner:0x000000042E671700 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:341 ECID:0 XactLockInfo: 0x00000003D51C740001/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: 0x301/16/2013 09:10:02,spid5s,Unknown,Node:101/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 graph01/16/2013 09:10:02,spid5s,Unknown,Deadlock encountered .... Printing deadlock information[/code]Wed, 16 Jan 2013 08:36:36 GMTgreg.cormierhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxLike 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!Tue, 15 Jan 2013 22:34:01 GMTsqlPirate_Flhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxYeah, that's what I fear :) As they are all commercial products, there's nothing I can do as a DBA. :(Tue, 15 Jan 2013 14:00:40 GMTgreg.cormierhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxDeadlocks 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.[code]DBCC TRACEON(1222,-1)[/code]Tue, 15 Jan 2013 14:00:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxDeadlocks 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.[url]http://richardlees.blogspot.com/2011/11/causing-deadlock-in-sql-server.html[/url]older but still good:[url]http://www.sql-server-performance.com/2006/deadlocks/[/url]Tue, 15 Jan 2013 13:58:36 GMTWant a cool Sighttp://www.sqlservercentral.com/Forums/Topic1407472-391-1.aspxI'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, DataE: - RAID10, Logs (Had to fight hard to get RAID10)F: - RAID5, BackupsNightly, 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[code="plain"]WaitType Wait_S Resource_S SignalS WaitCount Percentage AvgWait_S AvgRes_S AvgSig_SLCK_M_S 1291140.62 1291124.89 15.74 79947 41.15 16.1500 16.1498 0.0002CXPACKET 521297.72 459353.79 61943.9 82026137 16.61 0.0064 0.0056 0.0008ASYNC_NETWORKIO 398223.35 395635.23 2588.12 27401498 12.69 0.0145 0.0144 0.0001OLEDB 180090.60 180090.60 0.00 3608235839 5.74 0.0000 0.0000 0.0000WRITELOG 149349.00 139786.89 9562.11 92876862 4.76 0.0016 0.0015 0.0001DBMIRROR_SEND 142664.12 141938.88 725.24 4103374 4.55 0.0348 0.0346 0.0002PAGEIOLATCH_SH 92814.70 92679.55 135.15 5017440 2.96 0.0185 0.0185 0.0000PAGEIOLATCH_EX 54933.52 54866.74 66.78 6491651 1.75 0.0085 0.0085 0.0000BACKUPIO 44246.57 44009.32 237.25 12191895 1.41 0.0036 0.0036 0.0000[/code]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...Tue, 15 Jan 2013 13:34:54 GMTgreg.cormier