You can use the “begin_sample” and “end_sample” syntax for sp_sysmon to provide performance results for individual create index commands:
sp_sysmon begin_sample create index ... sp_sysmon end_sample
Sections of the report to check include:
The “Sample Interval,” for the total time taken to create the index
Cache statistics for the cache used by the table
Check the value for “Buffer Grabs” for the 2K and 16K pools to determine the effectiveness of large I/O.
Check the value “Dirty Buffer Grabs,” If this value is nonzero, set the wash size in the pool higher and/or increase the pool size, using sp_poolconfig.
Disk I/O for the disks used by the table and indexes: check the value for “Total Requested I/Os”
