Sql-server – Stored procedure query plan different in Always on server

Assuming ua is associated with the login la and ub is associated with lb.

To make this work you could grant impersonation of lb to la. Then you can create a wrapper of b.dbo.someproc in a like this:

CREATE PROCEDURE dbo.b_someproc
AS
BEGIN
  EXECUTE AS LOGIN='lb';
  EXEC b.dbo.someproc;
  REVERT;
END;

However, impersonation is a very broad permission that you probably do not want to grant. To get around it you can either add user ub to database a or user ua to database b.

If you add ub to a you can write a wrapper like this:

CREATE PROCEDURE dbo.b_someproc
WITH EXECUTE AS 'ub'
AS
BEGIN
  EXEC b.dbo.someproc;
END;

By far the easiest way however is to ad ua to b and just allow it to call the procedure directly.

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes. Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index. The missing index feature isn't always right, so you should always review whether you really need the columns suggested indexed or if you can modify another index to include the columns instead of adding a separate index.

You can use this query from Glenn Berry to look at the missing index dmvs and see what the index advantage, unique_compiles, and user_seeks to help make the decision:

select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as [index_advantage]
    ,migs.last_user_seek
    ,mid.[statement] as [Database.Schema.Table]
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,migs.unique_compiles
    ,migs.user_seeks
    ,migs.avg_total_user_cost
    ,migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs with (nolock)
inner join sys.dm_db_missing_index_groups as mig with (nolock) on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid with (nolock) on mig.index_handle = mid.index_handle
--where mid.[statement] = '[yourdb].[dbo].[OrganisationLocations]'

order by index_advantage desc
option (recompile);