If your "reorg" job actually does a rebuild, then that would update stats, which will clear the cache and thus get you new plans that could be optimal for existing...

Given that your query runs in 90 seconds from SSMS I am betting you have a suboptimal plan cached that is being used by the Agent job. The sledge...

+1 on ERStudio. Definitely the tool of choice for database...

sqldba_icon (4/6/2011)

---

Grant Fritchey (4/5/2011)

---

Yeah, local variables is a mechnism for defeating some forms of parameter sniffing. What it means is, instead of using a specific value to arrive at an...

DBANewbie (4/6/2011)

---

Interestingly, i was adding the linked server via SSMS but i have just added it via TSQL script and it has worked correctly.

I have no idea what the problem...

I LOVE that line!! I think it will be my new tag line when I hit clients that use ORMs. :w00t:

Linked servers can be disatrously bad for performance. See what you can do to avoid those. I have helped many clients use replication to eliminate linked server needs....

mail2payan (4/5/2011)

---

Dear Jeff Moden,

I skip this thought of inserting 100rows at a time..:crying:

And I'll be firing the same from a TABLE name as USER_PROBLEM:

---------------------------

ID | USERNAME | PROBLEM

---------------------------

1 |...

Christine, your solution still hits the view umpteen times. The SUM(CASE . . .) solution hits that same data ONE time. It can't possibly be as efficient to...

GilaMonster (1/18/2011)

---

Lee Forst (1/18/2011)

---

Grant, another question if I may. What happens in the case of a proc that has conditional branching? For example, the proc has a major...

1) as an interim fix, does the cache clear out if you run dbcc freeproccache? If so, set up a job to run that every so often to avoid...

They shouldn't be allowed near a SQL Server, at least not a production one. There, I said it! 😀

There really are lots of...

Ninja's_RGR'us (4/4/2011)

---

It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.

Needless to say...

Grant Fritchey (4/4/2011)

---

Grubb (4/4/2011)

---

Thanks, Craig. The problem with that is that the query only runs ~400 ms. Do you know...

get a copy of the awesome (and free) sp_whoisactive from Adam Machanic. It can help you identify blocking chains, among many other things.