Sql-server – How to determine which SQL Server user or database overuses server resources

High CPU in SQL Server is very often caused by poor indexing

Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily

Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing

That sounds like it is using an inappropriate query plan. There are two obvious paths this query could take and it is presumably deciding between them depending on the values of @row_offset and @row_count: for a small offset it is probably better to scan SomeCol,SomeOtherCol in order and apply the complex filtering clauses (in the WHERE and JOIN ON clauses) to each row until enough have been found to satisfy @row_count. For many other circumstances this would be a terribly bad approach.

To be more specific we'd need details on those other tables, the indexes/keys, and the sort of size/distribution of each table's contents.

What I suspect is happening is a cached query plan is being used when that plan is inappropriate for the current input values. When you run DBCC FREEPROCCACHE the cache is purged so it is forced to create a new plan and use that.

You can try the RECOMPILE hint by appending OPTION(RECOMPILE) to direct SQL and WITH RECOMPILE on the stored procedure definition if it is a procedure rather than an ad-hoc query - this tells SQL Server not to bother checking the procedure cache and always generate a fresh plan. This of course means that you lose the benefit on occasions where using a cached plan instead of having to reassess stats and replan would have been faster - this may be an acceptable tradeoff but obviously you need to benchmark and make sure you are not degrading overall performance more than is acceptable (if so you'll need a different strategy)).