data from _TableStat and _IndexStat

[intergyDev]

Hello, I have built a simple UI that gets data for each table in our database using _TableStat and displays the reads, creates, and deletes since the DB started, and also the numbers since you last ran the tool (basically recentReads, recentCreates, and recentDeletes).

We took it a step further and are showing the Indexes for each table, and getting data from _IndexStat for each Index so we can try to match up the Index with reads when a procedure is run (We use XRef, but for dynamic queries, we need a runtime analysis, not compile time).

We are seeing some interesting results that I was hoping someone might be able to explain. We were expecting that the sum of reads for all Indexes for a table would equal the reads from _TableStat, but it doesnt always seem to be the case. Sometimes we'll see 2 reads for a table, and 0 Index reads. Sometimes we'll see 20 reads, and 170 Index reads.

Can someone provide some knowledge on how to use _IndexStat in conjunction with _TableStat, and if the numbers should match up, or if there is something I am not understanding?

 

[toby.Harman]

There's a couple of suggestions I can offer on this.

I don't think the numbers should add up.

This is because of 2 things.
1. CAN-FIND will read the index but not actually read the record.

2. Sometimes it will be necessary to read more than 1 index block to resolve a particular record. The "depth" of a B-Tree will determine the number of index blocks required to find a record. If the index depth is 3 then the process will require to read the Anchor Block, then more to one of the nodes on the B-Tree and then move to another node to identify the Rowid of the record and then be able to retrieve the record.

I used to have a program which read the number of reads by user and allowed identification of a particular user process by reads - ranked by reads or by buffer misses. That proved extremely useful in identifying the people (if not the actual programs!) where excessive reads too place. Certainly in the case of dynamic queries this may identify a user who has a requirement that has not been satisfied by an index properly.