Posted - 10/28/2013 : 07:18:24
I'm having performance issues with a particular query on SQL Server 2008. The query is like this:
exec sp_executesql N'
DISTINCT top XXX
<8 non-calculated fields selected>
join Table2 on (Table1.P5 = Table2.P5 and ((Table2.P2 = @p2)))
(( Table1.P1 >= p1 and Table1.P3 is not null))
The query plan generated when run in SSMS is this:
Table1 contains just over 100.000 rows and Table2 contains just over 3.1 million rows. Certainly nothing major. All indexes are recently reorganized.
What my problem is, is that the query is very frequently run and more often than not CPU usage (as seen in sql server profiler) is > 1000 ms, sometimes more than 20.000 ms!
What i've tried so far (all to no effect):
- I've taken about 20 of the querys straight from sql server profiler and run them in SSMS, they all complete very quickly (< 3 seconds total)
- DBCC FREEPROCCACHE to clear the plan
- Reorganized all indexes with fragmentation > 5% (no index of any size was more than 30% fragmented)
- Disk latency as seen in perfmon looks to be ok
I'm baffled and my usual "toolbox" is all used up. Any help is appreciated...
Constraint Violating Yak Guru
Posted - 10/28/2013 : 18:04:17
| I'm not sure which table in the plan is "table1" and which is "table2". Moreover, your query does not contain a 3rd table but the plan does.
Also, I can't see the predicates or seek predicates in the plan since I can't cursor over the index seek to make that pop up :-) .
Can you post the xml version of the query plan instead?
And align the table names: either all table1/table2/etc. or all activitypartybase/etc..