a. Comment on the current situation of query and specify the major and minor bottlenecks b. Suggest a solution for improving the performance of the query by eliminating the major bottleneck c. Draw/show the estimated execution plan after your suggestion is applied d. Calculate estimated number of pages reads (8060 bytes are usable in a page) and speed up factor

For below SQL query, propose performance tuning suggestions (use Adventure Works2019 Database). Result set should be exactly same after performance tuning operations since customer insist on result set remains unchanged. SELECT ProductID MAX (Quantity) AS Total Quantity MIN (TransactionId) AS MinTransactionId FROM Production. TransactionHistory WHERE TransactionType 'P' GROUP BY ProductID ORDER BY ProductID DESC In Production. Transaction History table there are 113443 rows and there is only clustered index with the TransactionID its index key column (PK_TransactionHistory_TransactionID). as TransactionID (PK, int, not null) ProductID (FK, int, not null) ReferenceOrderID (int, not null) ReferenceOrderLineID (int, not null) TransactionDate (datetime, not null) Transaction Type (nchar(1), not null) 1 Quantity (int, not null) ActualCost (money, not null) ModifiedDate (datetime, not null) Statistics in message tab: Table 'TransactionHistory'. Scan count 1, logical reads 792. And actual execution plan for the query is SELECT Cost: 0% Sort Cost: 2 Hash Match (Aggregate) Cost: 14 Clustered Index Scan (Clustered) [Transaction History]. [PK_Transactio... Cost: 84 Sort Hash Match Clustered Index Scan (Clustered) Sort the input. Use each row from the top input to build a hash table, Scanning a clustered index, entirely or only a range. Physical Operation Sort and each row from the bottom input to probe into the Actual Number of Rows Actual Number of Rows 211 hash table, outputting all matching rows. 7866 Estimated Row Size 19 B Estimated Row Size 19 B Output List Physical Operation Hash Match Predicate [AdventureWorks).[Production). Logical Operation Aggregate [AdventureWorks). [Production). [Transaction History]. [Transaction History].ProductID; Expr1003; Expr1004 Actual Number of Rows 211 [Transaction Type]=N'P' Order By Estimated Row Size 19 B [AdventureWorks). [Production). Object Actual Rebinds 0 [Transaction History].ProductID Descending Actual Rewinds [AdventureWorks). [Production).[Transaction History). 0 [PK_Transaction History TransactionD] Node ID Output List Output List [AdventureWorks). [Production). [AdventureWorks].[Production) (Transaction History].ProductID; Expr1003; Expr1004 [Transaction History]. TransactionID; [AdventureWorks). [Production].[Transaction History].ProductD; [AdventureWorks]. [Production].[Transaction History] Quantity 1 For below SQL query, propose performance tuning suggestions (use Adventure Works2019 Database). Result set should be exactly same after performance tuning operations since customer insist on result set remains unchanged. SELECT ProductID MAX (Quantity) AS Total Quantity MIN (TransactionId) AS MinTransactionId FROM Production. TransactionHistory WHERE TransactionType 'P' GROUP BY ProductID ORDER BY ProductID DESC In Production. Transaction History table there are 113443 rows and there is only clustered index with the TransactionID its index key column (PK_TransactionHistory_TransactionID). as TransactionID (PK, int, not null) ProductID (FK, int, not null) ReferenceOrderID (int, not null) ReferenceOrderLineID (int, not null) TransactionDate (datetime, not null) Transaction Type (nchar(1), not null) 1 Quantity (int, not null) ActualCost (money, not null) ModifiedDate (datetime, not null) Statistics in message tab: Table 'TransactionHistory'. Scan count 1, logical reads 792. And actual execution plan for the query is SELECT Cost: 0% Sort Cost: 2 Hash Match (Aggregate) Cost: 14 Clustered Index Scan (Clustered) [Transaction History]. [PK_Transactio... Cost: 84 Sort Hash Match Clustered Index Scan (Clustered) Sort the input. Use each row from the top input to build a hash table, Scanning a clustered index, entirely or only a range. Physical Operation Sort and each row from the bottom input to probe into the Actual Number of Rows Actual Number of Rows 211 hash table, outputting all matching rows. 7866 Estimated Row Size 19 B Estimated Row Size 19 B Output List Physical Operation Hash Match Predicate [AdventureWorks).[Production). Logical Operation Aggregate [AdventureWorks). [Production). [Transaction History]. [Transaction History].ProductID; Expr1003; Expr1004 Actual Number of Rows 211 [Transaction Type]=N'P' Order By Estimated Row Size 19 B [AdventureWorks). [Production). Object Actual Rebinds 0 [Transaction History].ProductID Descending Actual Rewinds [AdventureWorks). [Production).[Transaction History). 0 [PK_Transaction History TransactionD] Node ID Output List Output List [AdventureWorks). [Production). [AdventureWorks].[Production) (Transaction History].ProductID; Expr1003; Expr1004 [Transaction History]. TransactionID; [AdventureWorks). [Production].[Transaction History].ProductD; [AdventureWorks]. [Production].[Transaction History] Quantity 1