title: View doesn't perform, but running the same query directly does
body: <p>For PowerBI Usage we have a view on our biggest table. However, it will not run, timing out with <em>PAGEIOLATCH_SH</em>. The Query is as simple as:</p>
  
  <p><code>select * from view where ProgramId = 'ParticularProgram'</code></p>
  
  <p>However, if we run the query within the view, it works without any issues or time-outs. Do you know what could cause this? The original query in the view is:</p>
  
  <p><code>select column 1, column 2 etc from table where ProgramId = 'ParticularProgram'</code></p>
  
  <p>Specifying a where clause with the partition does not change the behaviour and keeps it slow. Query cost of the first one is 99%, when both are ran together. The ProgramId is partitioned in the table, and this Particular Program is part of that Partition.</p>
  
  <p>Many thanks in advance</p>
  
  <p>Please find Attached the Actual query plan for the query that is working:
  <a href="https://i.stack.imgur.com/2GHZx.png" rel="nofollow noreferrer">DirectQueryOnTable</a>, the XML <a href="https://www.brentozar.com/pastetheplan/?id=B1c5SOXeU" rel="nofollow noreferrer">DirextQueryExecutionPlanXML</a> and the corresponding ddl: </p>
  
  <pre><code>/****** Object:  Table [dm].[AggProgramPeriodLoyalty]    Script Date: 1/8/2020 3:47:03 PM ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dm].[AggProgramPeriodLoyalty](
      [AggProgramPeriodLoyaltyKey] [int] IDENTITY(1,1) NOT NULL,
      [ProgramId] [nvarchar](100) NOT NULL,
      [ProgramTitle] [nvarchar](255) NOT NULL,
      [ProgramCurrency] [nvarchar](3) NULL,
      [ProgramPeriodType] [nvarchar](50) NOT NULL,
      [ProgramPeriodTypeLabel] [nvarchar](50) NULL,
      [IsCurrentProgramPeriod] [tinyint] NOT NULL,
      [IsLastCompletedProgramPeriod] [tinyint] NOT NULL,
      [LoyaltyId] [nvarchar](100) NOT NULL,
      [IsRegistered] [int] NOT NULL,
      [IsRegisteredCml] [int] NOT NULL,
      [UsedPlatforms] [nvarchar](100) NULL,
      [DigitalStampsBalanceMutation] [int] NOT NULL,
      [DigitalStampsBalance] [int] NOT NULL,
      [DigitalRedeemPotential] [int] NOT NULL,
      [DigitalParticipationBonusInteractions] [int] NOT NULL,
      [DigitalParticipationBonusInteractionsCml] [int] NOT NULL,
      [DigitalParticipationBonusStamps] [int] NOT NULL,
      [DigitalParticipationBonusStampsCml] [int] NOT NULL,
      [DigitalCollectInteractions] [int] NOT NULL,
      [DigitalCollectInteractionsCml] [int] NOT NULL,
      [IsNewDigitalCollector] [int] NOT NULL,
      [IsDigitalCollector] [int] NOT NULL,
      [IsDigitalCollectorCml] [int] NOT NULL,
      [DigitalCollectedStamps] [int] NOT NULL,
      [DigitalCollectedStampsCml] [int] NOT NULL,
      [TurnoverByDigitalCollectors] [decimal](18, 5) NOT NULL,
      [TurnoverByDigitalCollectorsCml] [decimal](18, 5) NOT NULL,
      [DigitalRedeemInteractions] [int] NOT NULL,
      [DigitalRedeemInteractionsCml] [int] NOT NULL,
      [IsNewDigitalRedeemer] [int] NOT NULL,
      [IsDigitalRedeemer] [int] NOT NULL,
      [IsDigitalRedeemerCml] [int] NOT NULL,
      [DigitalRedeemedStamps] [int] NOT NULL,
      [DigitalRedeemedStampsCml] [int] NOT NULL,
      [DigitalRewards] [int] NOT NULL,
      [DigitalRewardsCml] [int] NOT NULL,
      [TurnoverByDigitalRedeemers] [decimal](18, 5) NOT NULL,
      [TurnoverByDigitalRedeemersCml] [decimal](18, 5) NOT NULL,
      [AdditionalPaymentForDigitalRewards] [decimal](18, 5) NOT NULL,
      [AdditionalPaymentForDigitalRewardsCml] [decimal](18, 5) NOT NULL,
      [DigitalCounterRedeemInteractions] [int] NOT NULL,
      [DigitalCounterRedeemInteractionsCml] [int] NOT NULL,
      [DigitalConvertInteractions] [int] NOT NULL,
      [DigitalConvertInteractionsCml] [int] NOT NULL,
      [DigitalConvertedStamps] [int] NOT NULL,
      [DigitalConvertedStampsCml] [int] NOT NULL,
      [DigitalAddInteractions] [int] NOT NULL,
      [DigitalAddInteractionsCml] [int] NOT NULL,
      [DigitalAddedStamps] [int] NOT NULL,
      [DigitalAddedStampsCml] [int] NOT NULL,
      [DigitalDeductInteractions] [int] NOT NULL,
      [DigitalDeductInteractionsCml] [int] NOT NULL,
      [DigitalDeductedStamps] [int] NOT NULL,
      [DigitalDeductedStampsCml] [int] NOT NULL,
      [DigitalTransferToInteractions] [int] NOT NULL,
      [DigitalTransferToInteractionsCml] [int] NOT NULL,
      [DigitalTransferedToStamps] [int] NOT NULL,
      [DigitalTransferedToStampsCml] [int] NOT NULL,
      [DigitalTransferFromInteractions] [int] NOT NULL,
      [DigitalTransferFromInteractionsCml] [int] NOT NULL,
      [DigitalTransferedFromStamps] [int] NOT NULL,
      [DigitalTransferedFromStampsCml] [int] NOT NULL,
      [DigitalGiftCreatedInteractions] [int] NOT NULL,
      [DigitalGiftCreatedInteractionsCml] [int] NOT NULL,
      [DigitalGiftCreatedStamps] [int] NOT NULL,
      [DigitalGiftCreatedStampsCml] [int] NOT NULL,
      [DigitalGiftRedeemedInteractions] [int] NOT NULL,
      [DigitalGiftRedeemedInteractionsCml] [int] NOT NULL,
      [DigitalGiftRedeemedStamps] [int] NOT NULL,
      [DigitalGiftRedeemedStampsCml] [int] NOT NULL,
      [DigitalGiftExpiredInteractions] [int] NOT NULL,
      [DigitalGiftExpiredInteractionsCml] [int] NOT NULL,
      [DigitalGiftExpiredStamps] [int] NOT NULL,
      [DigitalGiftExpiredStampsCml] [int] NOT NULL,
      [DigitalGiftCancelledInteractions] [int] NOT NULL,
      [DigitalGiftCancelledInteractionsCml] [int] NOT NULL,
      [DigitalGiftCancelledStamps] [int] NOT NULL,
      [DigitalGiftCancelledStampsCml] [int] NOT NULL,
      [CreateDTS] [datetime2](3) NOT NULL,
      [CreateExnId] [int] NOT NULL,
      [UpdateDTS] [datetime2](3) NULL,
      [UpdateExnId] [int] NULL,
      [IsProfileCompleted] [int] NULL,
      [IsProfileCompletedCml] [int] NULL,
      [DigitalRedeemBonusInteractions] [int] NULL,
      [DigitalRedeemBonusInteractionsCml] [int] NULL,
      [DigitalRedeemBonusStamps] [int] NULL,
      [DigitalRedeemBonusStampsCml] [int] NULL,
      [DigitalCounterCollectInteractions] [int] NULL,
      [DigitalCounterCollectInteractionsCml] [int] NULL,
      [DigitalCounterRedeemBonusInteractions] [int] NULL,
      [DigitalCounterRedeemBonusInteractionsCml] [int] NULL,
      [ProgramWeekCombination] [nvarchar](100) NULL,
      [TotalUsedPlatforms] [nvarchar](100) NULL,
   CONSTRAINT [PK_AggProgramPeriodLoyalty] PRIMARY KEY CLUSTERED 
  (
      [AggProgramPeriodLoyaltyKey] ASC,
      [ProgramId] ASC
  )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PS_ByProgramId]([ProgramId]),
   CONSTRAINT [UK_AggProgramPeriodLoyalty] UNIQUE NONCLUSTERED 
  (
      [ProgramId] ASC,
      [ProgramPeriodType] ASC,
      [LoyaltyId] ASC
  )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PS_ByProgramId]([ProgramId])
  ) ON [PS_ByProgramId]([ProgramId])
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedInteractions]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedInteractionsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedStamps]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedStampsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedInteractions]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedInteractionsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedStamps]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedStampsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredInteractions]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredInteractionsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredStamps]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredStampsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledInteractions]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledInteractionsCml]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledStamps]
  GO
  
  ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledStampsCml]
  GO
  </code></pre>
  
  <p>And the estimated query plan for the select statement on the view, that is not performing at all: <a href="https://i.stack.imgur.com/gfThH.png" rel="nofollow noreferrer">QueryOnView</a>, the XML <a href="https://www.brentozar.com/pastetheplan/?id=ByU4rd7gL" rel="nofollow noreferrer">ViewExecutionPlanXML</a></p>
  
  <p>And the view DDL:</p>
  
  <pre><code>CREATE view [dm].[vwAggProgramPeriodLoyalty]
  as
  select [AggProgramPeriodLoyaltyKey] as [Agg Program Period Loyalty Key]
       , upper([ProgramId]) as [Program Id]
       , [ProgramTitle] as [Program Title]
       , [ProgramCurrency] as [Program Currency]
       , [ProgramPeriodType] as [Program Period Type]
       , [ProgramPeriodTypeLabel] as [Program Period Type Label]
       , upper([ProgramWeekCombination]) as [Program Week Combination] 
       , [IsCurrentProgramPeriod] as [Is Current Program Period]
       , [IsLastCompletedProgramPeriod] as [Is Last Completed Program Period]
       , [LoyaltyId] as [Loyalty Id]
       , [IsRegistered] as [Is Registered]
       , [IsRegisteredCml] as [Is Registered Cumulative]
       , [UsedPlatforms] as [Used Platforms]
       , [DigitalStampsBalanceMutation] as [Digital Stamps Balance Mutation]
       , [DigitalStampsBalance] as [Digital Stamps Balance]
       , [DigitalRedeemPotential] as [Digital Redeem Potential]
       , [DigitalParticipationBonusInteractions] as [Digital Participation Bonus Interactions]
       , [DigitalParticipationBonusInteractionsCml] as [Digital Participation Bonus Interactions Cumulative]
       , [DigitalParticipationBonusStamps] as [Digital Participation Bonus Stamps]
       , [DigitalParticipationBonusStampsCml] as [Digital Participation Bonus Stamps Cumulative]
       , [DigitalCollectInteractions] as [Digital Collect Interactions]
       , [DigitalCollectInteractionsCml] as [Digital Collect Interactions Cumulative]
       , [IsNewDigitalCollector] as [Is First Digital Collect]
       , [IsDigitalCollector] as [Is Digital Collector]
       , [IsDigitalCollectorCml] as [Is Digital Collector Cumulative]
       , [DigitalCollectedStamps] as [Digital Collected Stamps]
       , [DigitalCollectedStampsCml] as [Digital Collected Stamps Cumulative]
       , [TurnoverByDigitalCollectors] as [Turnover By Digital Collectors]
       , [TurnoverByDigitalCollectorsCml] as [Turnover By Digital Collectors Cumulative]
       , [DigitalRedeemInteractions] as [Digital Redeem Interactions]
       , [DigitalRedeemInteractionsCml] as [Digital Redeem Interactions Cumulative]
       , [IsNewDigitalRedeemer] as [Is First Digital Redeem]
       , [IsDigitalRedeemer] as [Is Digital Redeemer]
       , [IsDigitalRedeemerCml] as [Is Digital Redeemer Cumulative]
       , [DigitalRedeemedStamps] as [Digital Redeemed Stamps]
       , [DigitalRedeemedStampsCml] as [Digital Redeemed Stamps Cumulative]
       , [DigitalRewards] as [Digital Rewards]
       , [DigitalRewardsCml] as [Digital Rewards Cumulative]
       , [TurnoverByDigitalRedeemers] as [Turnover By Digital Redeemers]
       , [TurnoverByDigitalRedeemersCml] as [Turnover By Digital Redeemers Cumulative]
       , [AdditionalPaymentForDigitalRewards] as [Additional Payment For Digital Rewards]
       , [AdditionalPaymentForDigitalRewardsCml] as [Additional Payment For Digital Rewards Cumulative]
       , [DigitalConvertInteractions] as [Digital Convert Interactions]
       , [DigitalConvertInteractionsCml] as [Digital Convert Interactions Cumulative]
       , [DigitalConvertedStamps] as [Digital Converted Stamps]
       , [DigitalConvertedStampsCml] as [Digital Converted Stamps Cumulative]
       , [DigitalAddInteractions] as [Digital Add Interactions]
       , [DigitalAddInteractionsCml] as [Digital Add Interactions Cumulative]
       , [DigitalAddedStamps] as [Digital Added Stamps]
       , [DigitalAddedStampsCml] as [Digital Added Stamps Cumulative]
       , [DigitalDeductInteractions] as [Digital Deduct Interactions]
       , [DigitalDeductInteractionsCml] as [Digital Deduct Interactions Cumulative]
       , [DigitalDeductedStamps] as [Digital Deducted Stamps]
       , [DigitalDeductedStampsCml] as [Digital Deducted Stamps Cumulative]
       , [DigitalTransferToInteractions] as [Digital Transfer To Interactions]
       , [DigitalTransferToInteractionsCml] as [Digital Transfer To Interactions Cumulative]
       , [DigitalTransferedToStamps] as [Digital Transfered To Stamps]
       , [DigitalTransferedToStampsCml] as [Digital Transfered To Stamps Cumulative]
       , [DigitalTransferFromInteractions] as [Digital Transfer From Interactions]
       , [DigitalTransferFromInteractionsCml] as [Digital Transfer From Interactions Cumulative]
       , [DigitalTransferedFromStamps] as [Digital Transfered From Stamps]
       , [DigitalTransferedFromStampsCml] as [Digital Transfered From Stamps Cumulative]
       , [DigitalGiftCreatedInteractions] as [Digital Gift Created Interactions]            
       , [DigitalGiftCreatedInteractionsCml] as [Digital Gift Created Interactions Cumulative]    
       , [DigitalGiftCreatedStamps] as [Digital Gift Created Stamps]             
       , [DigitalGiftCreatedStampsCml] as [Digital Gift Created Stamps Cumulative]            
       , [DigitalGiftRedeemedInteractions] as [Digital Gift Redeemed Interactions]      
       , [DigitalGiftRedeemedInteractionsCml]  as [Digital Gift Redeemed Interactions Cumulative]     
       , [DigitalGiftRedeemedStamps] as [Digital Gift Redeemed Stamps]            
       , [DigitalGiftRedeemedStampsCml] as [Digital Gift Redeemed Stamps Cumulative]      
       , [DigitalGiftExpiredInteractions] as [Digital Gift Expired Interactions]          
       , [DigitalGiftExpiredInteractionsCml] as [Digital Gift Expired Interactions Cumulative]  
       , [DigitalGiftExpiredStamps] as [Digital Gift Expired Stamps]              
       , [DigitalGiftExpiredStampsCml] as [Digital Gift Expired Stamps Cumulative]        
       , [DigitalGiftCancelledInteractions] as [Digital Gift Cancelled Interactions]     
       , [DigitalGiftCancelledInteractionsCml] as [Digital Gift Cancelled Interactions Cumulative]    
       , [DigitalGiftCancelledStamps] as [Digital Gift Cancelled Stamps]           
       , [DigitalGiftCancelledStampsCml] as [Digital Gift Cancelled Stamps Cumulative]
    from [dm].[AggProgramPeriodLoyalty];
  </code></pre>
  
  <p>Furthermore I added an overview of the time-out: <a href="https://i.stack.imgur.com/X2ajZ.png" rel="nofollow noreferrer">LogOfWaitType</a></p>