This issue is so easy to trace. At least to pinpoint the specific query(s) that takes most of the time. Are you familiar...

Unfortunately if auto-grow...

In short, different parameter values can generate different execution plans.

There is a very good article on that topic: Slow in the Application, Fast in SSMS?

ffarouqi (1/29/2016)

---

I still do not want to go that route, because I have attached very similar code below, it might not be the same catch-all query, and this even after...

ffarouqi (1/29/2016)

---

I did not miss the link, but at this point of time. It seems to be too lengthy of an article to read, and understand that, and no doubt...

Because when you drop procedure you also drop...

It's not a very good practice to drop and re-create procedures. You may want to use this approach instead:

IF OBJECT_ID ('dbo.ProcedureName', 'P') IS NULL

BEGIN

EXECUTE ('CREATE PROCEDURE dbo.ProcedureName...

Your description is totally unclear, but I assume you need something like this:

SELECT

CASE WHEN

(CASE WHEN preMisc1 = 1 AND postMisc1 = 1 THEN 1 ELSE 0 END +

CASE...

NicHopper (1/22/2016)

---

Thank you both for your response, I think I'm going to go with a straight forward SAMPLE by default and let SQL decide based on the table size, but...

Where do you see a "KEY"?

It's an - APPLICATION lock. Caused by sp_getapplock procedure in DB_ID = 7...

GilaMonster (1/21/2016)

---

The keys are in the wrong order. You want filters first, to reduce the rows that need to be read, and the join second (because only nested loop will...

It's actually quite strange why optimizer decides to scan the whole index instead of performing few hundred seeks of the same index. May be statistics is not updated or the...

rarara,

What you are saying makes no sense to me. I would strongly suggest you to review your maintenance tasks, because it feels like you have no idea what you are...

Try "External Data -> Linked Table Manager".

If your query is still in the cache then you might be lucky enough to find it in this DMV sys.dm_exec_query_stats

And then by looking at creation_time, execution_count and total_worker_time columns...