Millions of reads for a single table used for reports I am currently looking into a query that retrieves lots of records, and it's used for reporting different balances during each month of the year(periods).
The original query joins a few extra tables but the main culprit is this one:

SELECT [GLHistory].[BranchID] AS [BranchID]
      ,[GLHistory].[LedgerID] AS [LedgerID]
      ,[GLHistory].[AccountID] AS [AccountID]
      ,[GLHistory].[SubID] AS [SubID]
      ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
      ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
  FROM [GLHistory] [GLHistory]
         INNER JOIN [FinPeriod] [FinPeriod]
                    ON [FinPeriod].[FinPeriodID] &gt;= [GLHistory].[FinPeriodID]
 WHERE [GLHistory].CompanyID = 7
   AND (   [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13)
 GROUP BY
       [GLHistory].[BranchID]
      ,[GLHistory].[LedgerID]
      ,[GLHistory].[AccountID]
      ,[GLHistory].[SubID]
      ,[FinPeriod].[FinPeriodID]
      ,[GLHistory].CompanyID 


I need to get the information from last financial period and any other entry that exists in my table until the last financial period for an account. 
Not sure what more, or how to structure the query in order to improve it.

Here is a script with the 2 tables schema and some data https://www.dropbox.com/s/swbqqzdey2tnkxz/script.sql?dl=0

And the full query can be found here: https://www.dropbox.com/s/scnav8flqpcrstd/SQLQueryGLB.sql?dl=0

Query plan presented here:queryplan

The output I am looking for is to get all the information for an account from the beginning period where I have an entry in GLHistory to the last period. And FinPeriodId might have 3 rows because its a row for each company

So it would be something like this, hope this time is right. You have an account for which there are values stored in GLHistory. For that account I need to get the information from GLHistory in a period, but also all other values until that period, so the values in GLHistory that are related to that account for each period until last activity period.  That is why I have FinPeriodId >= GLHistory.FinPeriodId.
[FinPeriod] [FinPeriod] : 180 rows

[GLHistory] [GLHistory] :75334 rows after applying those filter at the end of index spool

At Nested loop :Estimated rows 23483000 which is 75334*180 rows.

Final output Estimated rows 2987930 which is way above 251114.
This prove your query is wrong.you have to improve cardianility estimate.

In Findperiod table finperiodid is char(6) and in GLHistory table it is int.
Which is one of reason for poor cardianility estimate.

Make FinPeriodID to INT in both the table SAFELY.In million of record it does matter.

Alter table [FinPeriod]
Alter column [FinPeriodID] int not null


Experiment,

Declare @i int=201706
Declare @j char(6)='201706'
select DATALENGTH(@i),DATALENGTH(@j)


In FinPeriod table change the order of Clustered Index like this CompanyID (ASC), FinPeriodID (ASC)

Now in GLHistory your choice of Clustered index is wrong.


Firstly It will cause lot of index fragmentation.
Secondly  your index  is not being use in sample record of 700.


So i hv change index in GLHistory table in this manner,

CREATE  CLUSTERED INDEX RIIX ON GLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX RIIIX ON GLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )


I  use your query without changing any logic in old index way .I got all index scan .

No need of any covering index.WHY ?Because CLUSTERED Index use in join is able to select those columns.

Now I  drop the index and created index like above.

In your query you will notice I have only change the order of column in join so that it utilise the index.
After Creating index don't forgot to update statistics.

UPDATE STATISTICS [GLHistory] ; 
GO
UPDATE STATISTICS [FinPeriod] ; 
GO

SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].[FinPtdDebit]
    ,[GLHistory].[CuryFinPtdCredit]
    ,[GLHistory].[CuryFinPtdDebit]
    ,[GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    ,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    ,[GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    ,[GLHistoryLast].[CuryFinPtdDebit]
    ,[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
    ,[GLHistoryLast].[YearClosed]
FROM (
    SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM [GLHistory] [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] &gt;= [GLHistory].[FinPeriodID]
    WHERE [GLHistory].CompanyID = 7
        AND ([GLHistory].BranchID = 13)
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN [GLHistory] [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN [GLHistory] [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706


Result : I am getting all index Seek,minor Nested loop .Thats it.

Don't know how it will perform in million of records.

Alternative Solution,

Put [GLHistory]  filtered records in temp table because you are joining same table 3 times.Add all require column in temp table.I have added few only.

    CREATE TABLE #tmpGLHistory (
    id INT identity(1, 1)
    ,BranchID INT
    ,LedgerID INT
    ,AccountID INT
    ,SubID INT
    ,FinPeriodID INT
    ,CompanyID INT
    ,CuryID NVARCHAR(10)
    ,FinPtdCredit DECIMAL(19, 4)
    ,CuryFinPtdCredit DECIMAL(19, 4)
    ,FinBegBalance DECIMAL(19, 4)
    ,CuryFinYtdBalance DECIMAL(19, 4)
    ,CuryFinBegBalance DECIMAL(19, 4)
    )

CREATE  CLUSTERED INDEX TIIX ON #tmpGLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX TIIIX ON #tmpGLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

INSERT INTO #tmpGLHistory
SELECT [BranchID]
    ,LedgerID
    ,AccountID
    ,SubID
    ,FinPeriodID
    ,CompanyID
    ,CuryID
    ,FinPtdCredit
    ,CuryFinPtdCredit
    ,FinBegBalance
    ,CuryFinYtdBalance
    ,CuryFinBegBalance
FROM [GLHistory] [GLHistory] WITH(NOLOCK)
WHERE [GLHistory].CompanyID = 7
    AND (
        [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13
        )




    SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].FinPtdCredit
    ,[GLHistory].[CuryFinPtdCredit]
    --,[GLHistory].[CuryFinPtdDebit]
    --, [GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    --,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    --, [GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    --, [GLHistoryLast].[CuryFinPtdDebit],
    --[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
--, [GLHistoryLast].[YearClosed]     
FROM (
   SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM #tmpGLHistory [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] &gt;= [GLHistory].[FinPeriodID]
    WHERE [FinPeriod].CompanyID = 7
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN #tmpGLHistory [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN #tmpGLHistory [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

--ORDER BY [GLHistoryByPeriod].AccountID asc oFFSET 0 ROWS FETCH NEXT 10000000 ROWS ONLY


DROP TABLE #tmpGLHistory



  Introduce Filter inside Inner sub query


when you can write WHERE [GLHistoryByPeriod].[finPeriodID] = 201706
Outside inner query then why not introduce same filter inside inner subquery so that inner subquery already give you less resultset imprving the query dramatically.

Like , [FinPeriod].[FinPeriodID] = 201706