scott_lotus (4/29/2014)
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 ?
Just what the actual waits are. You already have two that are interesting. PREEMPTIVE_OS_WRITEFILEGATHER is an indication of file growth occurring and your system is waiting on it. So, if you're growing files during this process, it'll certainly slow you down. You'd want to grow those files ahead of time so it's not waiting. You're also seeing PAGEIOLATCH_SH which is flat out an indication you're waiting on the disk for some other process to clear. But those waits seem to be associated with an INSERT query, not your index build. You need to see what the index build itself is waiting on.
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
Tiny thing, unlikely to help overall, but since your PK is on LID, you don't need to have that in the INCLUDE statement. SQL Server will just ignore it anyway since the key for the PK is automatically stored with the nonclustered index.
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
You have a reasonably hefty system. I'd think it could handle what you're adding to it, but you need to identify where the bottleneck is first, then understand why it's a bottleneck. Early, and incomplete, indications are that it's disk related, but you need to get more accurate information about the specific blocking occurring in your index creation script.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning