How to improve create non-clustered index performance - 04-18-2012 , 12:47 PM
I have an 7*24 OLTP banking system which run a purge job weekly.
The purge job simply:
a) drop all indices from a large table (10G data)
b) delete many records from the large table
c) re-create all indices
The table has 11 non-clustered indices, there's no clustered index.
Application team complained that the purge job takes too long to complete (more than 3 hours) we (DBA) identified that the bottleneck is the step c of the job -- re-create all indices
- Sybase ASE version 15.0.3 ESD #1
- It's a vendor system, vendor doesn't support table partitioning at the moment
- The purge job is encapsulated in vendor's program, so it's not feasible to change the sql to create index in parallel (currently it's created one by one)
- All user tables' lock scheme are DOL
- All indices on that table are non-clustered, so can't use "with sort_data" option
- The @@maxpagesize of the server is 4096
- We've got a 15 G default data cache (10G for 4K pool and 5G for 16k pool)
My question is: how to reduce non-clustered index creation time?