SQL Query taking long to execute especially with offset and fetch for pagination
Using OFFSET...FETCH likely introduces a row goal (in the same way that TOP does so - see Setting and Identifying Row Goals in Execution Plans by Paul White for more information on that subject).  This is why you see such a difference without that part of the query - the lack of a row goal changes the plan entirely.

The row goal leads to SQL Server choosing the series of nested loops joins in the execution plan you shared.  This would normally be fine, but my guess it that the joins are more restrictive than the estimates indicate, and thus it takes a lot longer than expected to get the 10 rows requested.

You can test this theory by adding OPTION (QUERYTRACEON 4138) to the end of the query and running it again.



As far as a proper solution - as Dan Guzman suggested, you could try creating a narrow index on fsams.CTB_SUBSIDY_CARD_HEADER:

CREATE NONCLUSTERED INDEX IX_PaymentLotNo
ON FSAMS.CTB_SUBSIDY_CARD_HEADER (PaymentLotNo)
INCLUDE (OwnerName, TECHID, Id, TotalSubsidy)


Also, don't use SELECT * for your performance-related testing, as it's definitely going to change the final plan that you get (as compared with the column list of the actual query).

There is an implicit conversion going on that could be throwing the query's estimates off - LotNumber is stored as a string in the CTB_SUBSIDY_CARD_HEADER table, which is being converted to an int before being joined to PaymentLotNo in CTB_SUBSIDY_APPLICATION_HISTORY.  You should ALTER these columns to have matching data types if possible.



Side note: you're running SQL Server 2014 RTM, which is out of support.  You should upgrade to the latest service pack.  Also, consider enabling TF 4199 at that time, as the issue you're experiencing could be related to an optimizer bug that has since been corrected.