No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.
I did a simple test to rule out index fragmentation... Although the tables involved were not particularly fragmented, I defragged them and ran the queries again. No difference. As you said, shouldn't be an issue anyway with smallish tables. The durations are elapsed time as reporting in the 'duration' column of profiler trace output and also in query analzyer - bottom right.
I just remembered that I should test the query on our DR server, which is basically the same server and configuration. It ran much quicker on the DR box... Quicker as one might have expected than on a DL380 test server. However, as I said, on the production server it runs 9 or 10 times slower than on a DL380 test server.
Yes, all is identical. I tried another test with interesting results. I created a test database on the production server and restored the database in question to it. This gave me the same database on the same production server but just with a different name. The act of restoring it has significantly improved query peformance. I do think this is an indexing issue. Part of the problem was that I wasn't doing an update usage command regularly enough. This seemed to screw up my maintenance procedure because it simply wasn't picking up fragmentation issues that should have been reported by dbcc showcontig. I'll see how it goes tomorrow.
I don't know but restoring has some effect on something. Wish I knew! Last night I amended my db mainteance processes for the affected database by ensuring that nearly all indexes were rebuilt (DBCC DBREINDEX) but not much improvement this morning. Something is screwing performance and, so far, a restore is the only thing that actually fixes it.
I don't think it's an indexing issue but something in the act of restoring a database has had an effect. IPStateTransitionHistories isn't indexed because it isn't... It's become an issue as the number of rows in the table has increased. The developer responsible for this will put an index on it now but this is not a cause of the problem because the exact same tables, without an index, are used in test and the query completes in a fraction of the time. ChangeNotes is indexed appropriately and isn't an issue here.