Index Build
I’ve mentioned the dmv sys.dm_os_waiting_tasks before.
This view you can see the current waiting tasks on your system, most of the time this view is used to spot waits currently experienced, this view can be likened to the sys.dm_os_wait_stats but this is a view with more granular data.
So you can write queries by joining this view to sys.dm_exec_requests in order to detect blocks which is normally very useful on a busy system.
On my system I have a some 20 seconds to perform index maintenance. The window and servers that this apply to is certainly a bit different. On one side I have a system that runs enterprise edition and the other is standard edition. Both systems are running on the same hardware so lets not get into that. Both systems have different tables, some heaps- xml- and varchar(max) etc.
The systems also have different windows for load.
I always like to try and build something relatively simple and I believe this is what I have done. I have written a script that consists of two stored procedures, a job , table and trigger. The one stored proc kicks of an index build and logs it in a table. The table has a trigger that kicks of a job, the job then runs the second stored proc and that’s it.
Here is the details
proc 1 – Index Build Index Simple Build Web
This proc generated a list of indexes to be build for a database specified if the server is “enabled”. Once the list is generated a record is inserted for the index build into a table.
table – Logging and running
The table is used to store the object id, and the index id also the @@spid, this is used to kill the process if required which we will get to. The DB id is not included since I only reindex one database per server, this is by design. You can easily modify this to get the db id. Once the record is generated for the index build the insert into the table will fire a trigger. The trigger will kick of the job.
The job is just used as a asynchronous mechanism to process procedure 2.
proc 2 – Kill index
Once the job is kicked off. The spid and start time is looked up from the table. The proc 2 monitors the execution of proc 1 which is doing the index build. It has the duration by means of lookup from the table and it also has the spid. If the index build is blocking any other processes which in my situation times out after 20 seconds. Then the index build is killed by means of some dynamic SQL using the spid to kill the index build. The index build will then wait and retry in a minute since it can be made to use a job schedule.
There is a simple conditional in the proc 1 that depending on the version will do an online or offline build. There is also some logic that changes the index build to a defrag if the index build has been killed for more than a certain number of times.
That’s it.
Muller Jannie 