0

I've been battling a strange query performance issue. The query is sometimes running slowly from a website(2 - 4 seconds). I run a trace and it seems to be CPU overhead. The trace will show roughly the same amount of CPU time as duration. The thing I can't figure out is that when I copy/paste the query from the trace and run it, it immediately completes and the trace shows it taking roughly 300ms. I can do the same thing over-and-over. Slow on the website, fast in SSMS. I even converted the query into a view and the select is now just directly querying the view, but I'm seeing the same issue.

Another oddity I've noticed is that despite the fact that it's a very small resultset(70ish rows, 13 small columns), the time it takes to run the query correlates with the size of the resultset. So, if I shift a date parameter to return 20 rows rather than 70, my query time also reduces to about a third the time.

Here's another observation: If I take out the last column and add it back it starts working just fine. But, when I go away for the day and come back it runs slow again when I'm back in the office. This has happened multiple times.

I looked up a way to see the execution plan in a trace. It's definitely different when it's slow, but I can't tell why it's slow.

Any reason why it would consistently use a different execution plan for the same query?

Zephryl
  • 343
  • 2
  • 9
  • 1
    Can we close as a duplicate of a blog post? Check your SET settings. http://www.sommarskog.se/query-plan-mysteries.html – Aaron Bertrand Jan 29 '14 at 21:28
  • This is plain SQL query or stored procedure? Do you run it on the same (not copy) database? Did you try to reset execution plan for slow query? – Grzegorz Gierlik Jan 29 '14 at 21:43
  • I tried using the same SET in SSMS. The only difference was arithabort which didn't make the query slow in SSMS. – Zephryl Jan 29 '14 at 22:08
  • The only difference in the sets was the arithabort. Setting this in SSMS didn't make the query slow. It's plain SQL, executed with exec sp_executesql ..., N'@p1 datetime,@p2 datetime,@p3 int,@p4 bit',@p1='2012-10-23 00:00:00',@p2='2014-05-02 00:00:00',@p3=793,@p4=1 I'm not sure how it would be a dupe of the one where they're declaring variables. I'm copy/pasting straight from the trace. The SQL issued is the same then, right? Shows up in the trace the same... – Zephryl Jan 29 '14 at 22:17
  • So you run the same query, with the same arguments (values) on the same database and get different times? Should be impossible :(. Are you sure you don't miss something (running transaction, isolation level, lock of table)? – Grzegorz Gierlik Jan 29 '14 at 22:35
  • @Zephryl - In order to re-use the same cached plan as the application is using various other things need to be the same as well as the `SET` options. Are you logging in with the same credentials? [A full list of the cache keys that would stop you sharing the same plan is here](http://stackoverflow.com/questions/16991584/what-would-cause-parameter-sniffing-one-one-computer-and-not-another/16991971#16991971) – Martin Smith Jan 29 '14 at 23:55
  • But in any event you need to capture the execution plan your application is using (either retrieve it from the cache or use profiler) then look at the parameter compile time vs runtime values. – Martin Smith Jan 29 '14 at 23:58
  • I never figured out why this was happening. I did end up reviewing many of our indexes and combining the FK indexes. Before, most FK's and the IsActive flags were in separate indexes. Combining them seems to have alleviated the problem. – Zephryl May 21 '14 at 20:42

0 Answers0