You don't need an extended procedure for that. You should also not be considering writing extended procs unless you have a very good grasp of C++. It's waaay easy to...

Set up a profiler trace with the events you want. The default template is mostly good (though you may want to check TextData for either RPC completed or SQLBatch Completed....

You've run into a classic case of parameter sniffing (or more accurately, the lack thereof). See this article[/url] for some details on the cause.

Recommendation: Split into 2 stored procs. Pass...

I don't have the time right now to do a full analysis, but one thing I noticed from the exec plan is that the row estimates are off. Way off.

Estimated...

It's how scalar functions show up in exec plans. Very irritating.

Please post the code of the view, the table and index definitions and the execution plan (in xml formal, zipped and attached to a post)

Change your app so that it does use stored procedures and restrict table access. You can check for key words, but it's not a sure thing. There are ways to...

One example he gave to me a while back on this subject concerned TOP and ROWCOUNT.

UDFs are my pet peeve in execution plans at the moment. I wish there was...

How many rows do the 2 queries return?

I've seen cases where the batch % is way off, but IME it hasn't been the norm. Maybe I'm just working with simpler queries

Generally I've found that odd joins (cross...

SQL Server Performance Tuning Distilled (by Sajal Dam) is a good book. It's for SQL 2000, not 2005, but is still a very worth while read. The principles of good...

Run a checkDB on that database, see if you have any other errors. How old is your most recent backup?

You said the databases were fine after the move off the...

Nope. Even under 16GB you risk starving the kernal of memory.

Personally I would not...

Make sure the app is pointing at the right database.

Make sure that you don't have 2 copies of the procedure in different schemas

Select into isn't limited to coming from a single table. Imagine something like this (example from BOL)

SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS Province, a.PostalCode

INTO dbo.EmployeeAddresses

FROM Person.Contact AS c

JOIN...