Thanks for the reply.
"First, please, for clarity's sake, rename that index. Anytime I open a server and see _dta at the start of an index, I almost want to cry."
Can do - sorry.
"I would suggest you capture the wait statistics on the process as it's running in order to understand where your slow-down is coming from."
I captured the following dueing index creation using SP_WHO3.
---------------------------------------------------------------------------------
dd hh ss ss mss: 00 02:08:12.174
sql_text: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1) --?>
reads: 27,293,752
writes: 662
wait info: NULL
blocking session: NULL
TempDB = on.
---------------------------------------------------------------------------------
dd hh ss ss mss: 00 02:11:41.440
sql_text: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1)--?>
reads: 27,905,369
writes: 675
wait info: (26544ms)PREEMPTIVE_OS_WRITEFILEGATHER
blocking session :NULL
TempDB = on.
---------------------------------------------------------------------------------
dd hh ss ss mss: 00 00:01:29.580
sql_test: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1) --?>
reads: 524,440
writes: 5
wait info: (33ms)PAGEIOLATCH_SH:DM2:1(*)
blocking session: NULL
TempDB = off.
-----------------------------------------------------------------------------------
dd hh ss ss mss:00 04:35:09.826
sql_test: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1) --?>
reads: 53,166,762
writes: 4,084
wait info: NULL
blocking session: NULL
TempDB = off.
----------------------------------------------------------------------------------
"I'd think it's either in memory or possibly waiting on I/O within tempdb, but understanding why and where is going to be a big help."
I use Quest Spotlight to monitor blocking and locking performance.
The "WAIT STATISICS" tab is currently showing 75% MISC WAITS.
I will check it again during the index creation and publish the results.
Anything specific to look for ?
"How much memory does the system have? "
96GB
"Is tempdb and your database on the same disk?"
m: data 2.18tb (raid10)
n: logs 553gb (raid1)
t: tempdb 558gb (raid1)
"What's the clustered index on? I can see that you have one, but not the definition."
Clustered index on Primary Key LID (ASC).
NAME: LID
SORT: ASC
DATA TYPE: INT
Size: 4
Idenity: YES
Allow nulls: NO
ALTER TABLE [dbo].[L] ADD CONSTRAINT [L_PK] PRIMARY KEY CLUSTERED
(
[LID] 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) ON [PRIMARY]
GO
"But, at the end of the day, you'll probably have to throw hardware at this problem. You're trying to sort a very large data set. You need memory and lots of fast disks to get it done. "
15x600GB (SAS) disks in a NexSan SaysBoy. (1 hot spare).
Disk performance during daily operations is great.
SQL performance during daily operation is great.
- 18% CPU utilisation
- 64Gb of 96gb used
- cache hit rate is 82%.
- no deadlocking
- low number of blocked processes
- lock waits 2-5 seconds on Object Locks
Maintenance on a large single table:
- can rebuild in 1 - 2 hours but some writes will timeout.
- can delete 3000 rows per min before causing blocking problems (2.8m inserts per day in this table - another table has more but can truncate).
- cannot create new index tbl.L.
We are very heavy write and low read in general during normal operations.
Thanks for any advice.
Scott