Have you tried this? Is it not working?

e.orig_dept_id = @orig_dept_id where @ @orig_dept_id = 2 this is supposed to what you are trying to do..

Why you want to integrate the orig_dept_id with the c.funded_Date?

Cant you do something like below?

Declare @year int

Set @year = 2014

Declare @orig_dept_id int

Set...

Let me ask you a question. What is the state of the second database? If I understood you correctly, then it is that your mirror is not connected to witness....

Or if you are using SQL Server 2012/2014 Enterprises Edition, you can have multiple readable replicas with Always on.

You need to put relevant where clause for "all the funded contracts that are in Service which is in the tlkorigdept table".

Is the server added as linked server? Looks like it is not.

Look at the following url and add linked server first.

http://msdn.microsoft.com/en-us/library/ff772782.aspx

Then the way to execute stored proc on linked server.

http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

Also check if Instant file Initialization is enabled.

When you say pretty much same it means identical or few things are different?

Do they have same # of processor & same amount of memory?

How much memory is allocated to...

Have you checked the execution plan for those procs?

What are the wait type and last wait type you see for them?

Some more info will help better.

Just keep in mind that Always ON is Enterprises only feature where as Mirroring is available for standard version also and work very well for HA purpose.

No. Back up does not defrag the DB.

Your QA is better because it is less busy than prod.

Is this issue between publisher and distributor or distributor or subscriber? Please check the error logs for distribution and log reader agent and see if you find anything?

Also just to...

If the default trace is enabled then you can get some information out of it.

If you see slow sql in SQL server, it means there is something off in SQL. As Gail suggested it may be very well parameter sniffing issue. But again without...

When sql server is showing the signs of slowness have you checked to see if you run the same sql on query analyzer, how much time time it takes?

Also look...