_IndexStat and _TableStat query

[Cringer]

Can someone tell me what the Reads section of _IndexStat and _TableStat actually mean? Our production database was restarted on Saturday and it's showing us as having around 5,000,000,000 reads of one table since then. This seems a little excessive for this table and my boss is trying to establish if this actually relates to the number of physical reads of this table or if other things might be included in there?

If I compare this to the _UserTableStat data then the number of reads seems to be a lot lower.

 

[TomBascom]

For _TableStat it is the total number or records read since the db started.

For _IndexStat it is the number of index entries read. This could be much larger than record reads if you don't have good indexes.

 

[Cringer]

Thanks - that's what I thought. My boss is Adam Ant. No, sorry, he's adamant it can't be. There are only 500k records in the table and TableStat matches IndexStat or there abouts. Time to go hunting the dodgy code methinks!!

 

[cj_brandt]

If you have at least 10.1C and you are on UNIX, you can monitor the _UserTableStats and when a user's stats increases rapidly then fire a kill -s SIGUSR1 <pid> and that will show the code being run. Makes hunting down that problem code a lot easier.
You can also do this on windows using the _connect table and the Client Request Statement Cache fields, there is a KB about it. Getting to the KB now may be the hardest part.

 

[Rob Fitzpatrick]

This is where ProTop will really help you. You can look at the table and index stats for the whole DB (_TableStat and _IndexStat), to see when the big reads start happening, and on which tables. Then you can drill into individual users (_UserTableStat and _UserIndexStat) to see who is responsible for the reads, then you can either check their call stack (in protrace.<PID>), as cj said, or check in with the user to see what they're up to.

 

[Cringer]

The protrace PID part is the missing link. I have an application that allows me to see who is making the big requests, just no way of tracking that back at the time when they're happening. I'll give that a go.

 

[cj_brandt]

You can also monitor the _UserIO table. When DBAccess for a single user goes over a certain threshold - you can generate several protrace files.