Combine DDL Statements for Performance - by RobNicholson, MCSM

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 794223 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 7/17/2013 3:03:41 AM
Access Restriction Public


Currently if you need to perform DDL operations on a table or index it must be done sequentially inside a batch or parallel in different sessions (locking permitted).  For small operations this is not a big deal but for VLDB running these statements can cause significant delays and necessary overhead.  

I know tables this size are usually partitioned but please ignore this for the example

Example A: a 200TB table requires statistics updates on five statistics (not all).  Currently SQL must go through each UPDATE STATISTICS statement, scan the table/index, compile the stats.  This happens five times (five scans) to gather the stats.  As the table is so large each scan is disfavoured from the buffer pool therefore has to be rescanned each time.  The UPDATE STATISTICS WITH ALL also has the same behaviour. Combining will allow one scan for all operations.

Example B: a 200TB table requires two indexes to be rebuilt or created.  Currently index build 1 will cause a table or index scan then proceed to build 2 it will then perform another table scan of the same table (of which the buffers have been disfavored from the buffer pool) then proceed to perform the rebuild.  If these statements could be combined only one scan would be required saving a lot of time and server resources).  If table had more indexes to maintain/create this would take longer sequentially than combined.  

One potential issue would be the amount of space needed to be reserved to combine operations (particularly with REBUILDs but not so much with STATS updates).  This would be something the DBA would need to plan for.  There would also be difficulty on how the optimizer would handle the operation i.e. updating stats sequentially may want to read an index if run sequentially but when combined it is likely that performing a scan of a wider index or the table may be a better option.  Obviously combining statements would be valid for specific DDL operations and for one object only.
Sign in to post a comment.