Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

2 Posts

Posted - 10/28/2013 :  07:18:24  Show Profile  Reply with Quote

I'm having performance issues with a particular query on SQL Server 2008. The query is like this:

exec sp_executesql N'
<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))
order by
Table1.P4 asc,
Table1.P5 asc',N'
@p1 datetime,
@p2 uniqueidentifier',
@p1='2013-08-26 22:00:00',

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...

Aged Yak Warrior

550 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

Starting Member

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  
 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.02 seconds. Powered By: Snitz Forums 2000