How to determine when to Re-Index programmatically?

[Ralph Clark]

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

[Ralph Clark]

So much for Monday and poor keyboarding skills!

Rest of details:

table built with FillFactor of 100%, automatically Updating Statistics, present size on test machine - 110,000 records, operational machine - 1,500,000 records.

My testing shows Scan Density has dropped from 100% (731:731) just after Re-Index to 50% (916:1805) this morning after 7 days (88,000 records at start to 110,000 now). But, neither the Insert timing nor the test query timing have shown any degradtion!?

Am I impatient or am I not understanding cause and effect? My goal is to determine what combination of data and logic could be used to appropriately launch a reindexing of my table to maintain best efficeincy.

Direction, hints, allegations, and barely concealed half-truths gladly accepted.

RC



------------
Ralph Clark at 5/13/2002 8:38:18 AM

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

[Ray Miao]

Do you have cluster index oon the table? If not, reindexing will not defragment data pages.


------------
Ralph Clark at 5/13/2002 8:49:32 AM

So much for Monday and poor keyboarding skills!

Rest of details:

table built with FillFactor of 100%, automatically Updating Statistics, present size on test machine - 110,000 records, operational machine - 1,500,000 records.

My testing shows Scan Density has dropped from 100% (731:731) just after Re-Index to 50% (916:1805) this morning after 7 days (88,000 records at start to 110,000 now). But, neither the Insert timing nor the test query timing have shown any degradtion!?

Am I impatient or am I not understanding cause and effect? My goal is to determine what combination of data and logic could be used to appropriately launch a reindexing of my table to maintain best efficeincy.

Direction, hints, allegations, and barely concealed half-truths gladly accepted.

RC



------------
Ralph Clark at 5/13/2002 8:38:18 AM

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

[Ralph Clark]

Sorry for the fractured Posting (Monday syndrome)

Yes, the table has a clustered index, Autocreate Statistics, AutoUpdate Statistics.

Index is created on machine_id (int) and Date_time (Datetime).


------------
Ray Miao at 5/13/2002 9:32:34 AM

Do you have cluster index oon the table? If not, reindexing will not defragment data pages.


------------
Ralph Clark at 5/13/2002 8:49:32 AM

So much for Monday and poor keyboarding skills!

Rest of details:

table built with FillFactor of 100%, automatically Updating Statistics, present size on test machine - 110,000 records, operational machine - 1,500,000 records.

My testing shows Scan Density has dropped from 100% (731:731) just after Re-Index to 50% (916:1805) this morning after 7 days (88,000 records at start to 110,000 now). But, neither the Insert timing nor the test query timing have shown any degradtion!?

Am I impatient or am I not understanding cause and effect? My goal is to determine what combination of data and logic could be used to appropriately launch a reindexing of my table to maintain best efficeincy.

Direction, hints, allegations, and barely concealed half-truths gladly accepted.

RC



------------
Ralph Clark at 5/13/2002 8:38:18 AM

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.