Recently I worked with a customer who reported a slow running query. Let me simplify this to illustrate the problem.
There are two tables t1 (pk int identity primary key, c1 int, c2 int, c3 int, c4 varchar(50)) and t2 (pk int identity primary key, c1 int, c2 int, c3 int, c4 varchar(50)). Each table has about 10000 rows.
But the following query is very slow.
select COUNT (*) from t1 inner join t2 on t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3 and t1.c4<>t2.c4
This query runs over 30 seconds and consumes over 30 seconds of CPU. The query actually returns 0 rows. With two tables of size of 10,000 each, this seems to be unreasonable.
When investigate CPU consumption by a query, we normally look at a few things. First, we look at how many logical reads this query has done. Secondly, we look at the plan to see how many rows are processed by each operator.
But when we track logical reads via profiler trace (reads column), we see very low logical reads (less than 60). When we look at the plan, the number of rows processes are not that many either. The partial execution plan is shown below.
Read more: CSS SQL Server Engineers