SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Brestfloda
Starting Member

Denmark
2 Posts

Posted - 10/28/2013 :  07:18:24  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
425 Posts

Posted - 10/28/2013 :  18:04:17  Show Profile  Reply with Quote
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

Denmark
2 Posts

Posted - 10/29/2013 :  05:28:44  Show Profile  Reply with Quote
Hi guys!

Thanks for the input, it appears that a simple UPDATE STATISTICS did the trick.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000