PDA

View Full Version : SQL Server Reindex Via ADO: Redux



stanl
08-13-2008, 07:59 AM
This is a continuation from the last thread about DBCC.....

Assuming I loop through a series of tables and call a function


oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection=oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandTimeOut = 6000
oCmd.CommandText = "ALTER INDEX ALL ON " & cTable & " REBUILD WITH (ONLINE=ON)"
oCmd.Execute
....
While oCmd.State>=4
doevents
Wend


with a few seconds delay after that between each table. I tested this on 5 tables with a log file that records the time each table was used prior to oCmd.Execute

The time between table1 and table2 was 7 minutes

table2 - table3 2 minutes
table3 - table 4 2 seconds
table4 - table5 2 seconds

I know table3 - table4 take 4-6 minutes to reindex if done separately. So the question is "Does SQL Server run each process as a separate thread after a while so the command state returns to 0?" or "Does the command object just fail to execute?" [I find nothing in the errors collection].

Better yet, is there a way to determine when a reindex in done?:dunno

Stan