Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Brestfloda
Starting Member
2 Posts |
Posted - 2013-10-28 : 07:18:24
|
HiI'm having performance issues with a particular query on SQL Server 2008. The query is like this:exec sp_executesql N'select DISTINCT top XXX <8 non-calculated fields selected>from Table1join Table2 on (Table1.P5 = Table2.P5 and ((Table2.P2 = @p2))) where (( Table1.P1 >= p1 and Table1.P3 is not null))order by Table1.P4 asc, Table1.P5 asc',N'@p1 datetime,@p2 uniqueidentifier',@p1='2013-08-26 22:00:00',@p2='E9818E45-C3CE-E111-8A7A-005056BB0042'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... |
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-10-28 : 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.. |
|
|
Brestfloda
Starting Member
2 Posts |
Posted - 2013-10-29 : 05:28:44
|
Hi guys!Thanks for the input, it appears that a simple UPDATE STATISTICS did the trick. |
|
|
|
|
|