I have some 3rd party software that is doing so often the same update query, on 10 million rows table with 1 GB data
(@P1 bigint,@P2 bigint,@P3 bigint,@P4 bigint,@P5 bigint) UPDATE top(1000) hsi.hspendingitems SET status = @P1 WHERE handlertype = @P2 AND status in (@P3,@P4,@P5)
Initial Results (without modifications)
Query 1 plan:
Query IO statistics
Table 'hspendingitems'. Scan count 9, logical reads 1686, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I then added an index:
CREATE NONCLUSTERED INDEX [ix_test_dba] ON [hsi].[hspendingitems] ( [handlertype] ASC, [status] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [DBSpace8]
Results with New Index
Query 2 plan: https://www.brentozar.com/pastetheplan/?id=S1yjulAS-
Table 'hspendingitems'. Scan count 1, logical reads 10253, physical reads 20, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Is it better for SQL Server to do less scans but more logical reads?
I noticed that the cost plan is also different:
Initial Cost 102.937 vs with Index 0.0232853 ?
Vendor is aware to add that index, what do you think I should push vendor for adding that index?