Looking for deadlocks

[jkj2001]

We've got a table on our NT server (version 3.01.992447526) that appears unresponsive. The indexes are fresh from yesterday morning, but when we try to do things like "select count(A_FIELD)" on the table tsql just sits there.

Granted, it's a table with about a million records, but no large text-- the .tbl file is about 650 MB in size. Maybe 35 fields in the table total.

I thought I'd try my luck, and do a reindex on one of our unique, counter fields (using "create unique index.."), but all that's happened in the last half-hour is a 44 byte .btr file has appeared. No temporary files have been created to indicate the indexing is running along, very atypical.

The box itself has 2 GB of Ram, and according to NT's task manager is barely using any of it. My own PC should look so good. The processor utilization floats from between 1% and 10%, so it's not slammed with other things to do. Also, our other texis tables on this machine run just fine.

I've run monitor -k, to see if that might fix things here, but it doesn't appear to have done so. Could you give a few suggestions as to what might be locking things up, maybe a few ideas to try, places to check, that sort of thing? Thanks a bunch.

[John]

If there are no indexes on the table then the count() does need to read in every row from the table, which for a million rows will take a noticeable amount of time.

When creating a regular index it builds as much as it can in RAM, which in this case is probably the entire index, before writing it to disk. If you watch the process size in in task manager you should see it grow.

If you are not using much CPU it sounds as if maybe there is a delay talking to the disks, and it is spending most of the time waiting for data.

[jkj2001]

We do have recent indexes on the fields in question-- they just don't seem to be contributing anything.

Also, and I don't know if this triggers any warning signs, when I dropped an index in this table, everything went away except for its .btr file. Ordinarily that will go along with the _T and other associated files, won't it?

[John]

Metamorph indexes do not help a COUNT, unless there is a where clause that uses the index. Which OS are you on? If a process had the .btr open under NT then it would not be removed, although SYSINDEX should then flag the index as TYPE 'D', and remove it later.