I hate being on call! :w00t:

When I'm on call, I want to retire early.

When not on call, I feel I want to work with SQL to the grave.

But, aside...

Craig Farrell (5/16/2011)

---

You're correct, the second step is unnecessary.

See this article: http://msdn.microsoft.com/en-us/library/ms187317.aspx, in particular in the remarks section for when you have to manually start a population. If WITH...

OK, thank you!

The use of static readonly fields for SAFE/EXTERNAL_ACCESS assemblies is discussed here:

http://www.devx.com/codemag/Article/34918/1954

Hmm, that was a while ago, I'd...

There is also this white paper:

Plan Caching in SQL Server 2008

http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

See "Query Plan Reuse" section on factors affecting plan reuse.

Thank you all for your responses.

There are indeed at least a couple of 'sins' committed by the devs here that are causing recompilations and are likely the culprit.

(Incidentally, the number...

I found this link reporting similar issue:

http://www.sqlservercentral.com/Forums/Topic979929-360-1.aspx#bm979947

Does anyone have a query that looks at a bunch of exec plans and reports on differences?

Ninja's_RGR'us (5/3/2011)

---

Makes no sense. Normally you could get a few plans for different connection settings but this is ridiculous.

Are use sure those are called via RPC and not via...

Here is the code:

SELECT

P.usecounts

,P.cacheobjtype

,H.query_plan

,LEFT([sql].[text], 1000) as [text]

FROM

sys.dm_exec_cached_plans P

CROSS APPLY

sys.dm_exec_query_plan(plan_handle) H

OUTER APPLY

sys.dm_exec_sql_text (p.plan_handle) [sql]

WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName;

Run your sql statement and obtain the *actual* exec plan.

In the exec plan identify the operator(s) with the largest cost, eg. clustered index scan.

Compare actual with estimated number...

I have actually just tested the above scenario, and was able to reproduce one job schedule overwriting another, by simply using the same @schedule_uid.

I deployed a new job on a...

I've experienced this before, but I'm not quite sure why this happens either.

I do have a theory though. In the job script, there is code like in sample below:

...

EXEC @ReturnCode...

Are there any scenarios where having the dll file present is required?

I have had reports from our dev/QA teams that after a certain database is restored from production to the...

opc.three (4/20/2011)

---

Technically you do not need the DLL at all, even in the first place. When you build and deploy a SQL CLR project from Visual Studio the assembly is...