Yes and no.

That is, you can put your procedure in master and call it sp_somename. Then you can invoke it as

EXEC archer..sp_somename

The procedure will then execute in the context...

Thanks for the plans!

I note that the table is partitioned. What is the partitioning column? ClaimID?

It appears that you have mixed up the plans a bit. The one you call...

I will have to admit that SPNs goes over my head (I don't work as a DBA myself, so I can escape them). Maybe someone else can fill in?

You still have a different plan than on SQL 2005.

The plans posted to evernote are difficult to read. The best is if you can attach them as .sqlplan files.

But the root problem appears to...

A fairly nasty query, I would say, that leaves the optimizer between a rock and a hard place.

SQL Server can retrieve orders by recorddate, until it has found 10000 rows...

Lots of pieces missing here. You say that the procedure completes in 13 seconds, and then there are INSERTs going on six hours later.

Should I guess that the procedure you...

We also need the index - and of course, I forgot to mention: the query!

It does not help how many conditions you add to the IF statement. The USE statement is interpreted at compile time, why you always get the error. Here is a...

The problem is that without knowing the tables and indexes, we cannot say much. Judging from the plan on SQL 2005 alone, there is room for improvements.

There are a multitude of possible reasons why the query started to run slower.

But a very common scenario is that you have a query which involves something like "today's orders"....

It would certainly help to see the query, as well as the CREATE TABLE and CREATE INDEX statements for the table.

There is little reason to look at the hardware, when...

The one thing to keep in mind is that once the server has been renamed in Windows, you need to do this:

EXEC sp_dropserver OLDNAME

EXEC sp_addserver NEWNAME, local

And then restart SQL...

Again, one reason you get so little help is that we know so little about what you do. It still not clear to me how you measure the speed.

You mention...

Yes, the UPDATE statements could also be slow, but the INSERT statements may cause page splits etc, why they are more prone to stalls.

No, TVPs to not persist between calls,...