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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance problem

Author  Topic 

Brestfloda
Starting Member

2 Posts

Posted - 2013-10-28 : 07:18:24
Hi

I'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
Table1
join 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..
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -