July 21, 2021 at 4:01 pm
If you can, review the query plan to verify that those indexes are actually being used. Without a query plan, we can't tell for sure.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 21, 2021 at 4:12 pm
ScottPletcher wrote:I suggest creating the following indexes to support the query. If you'd like to adjust other indexes on the tables also, just let me know.
CREATE UNIQUE NONCLUSTERED INDEX ncx_Loc_Bin_no ON dbo.wsPKG ( Loc, Bin_no, PKG_No ) INCLUDE ( PKG_ID, Status ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_Item_no ON dbo.wsPKGLin ( Item_no, ID ) INCLUDE ( PKG_ID, PKGLin_ID ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_ser_lot_no ON dbo.wsPKGSL ( ser_lot_no, ID ) INCLUDE ( PKGLin_ID, Qty ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];There are already 4 indexes with the pair of columns item_no, loc at the beginning.
If my query returns max count(*) not more than 10 then 3 of those indexes should be dropped, only the shortest one should remain, it will do the job all right.
Do you mean on the IMLSMST_SQL table? I didn't add any indexes on that table, because (1) it already had an index that could be used for lookup (the clus index in fact, IIRC), and (2) at least as importantly, that table has only 270 rows with an avg row length of roughly 1553 bytes, so it's not critical for tuning. Whereas the wsPKG and wsPGKLin tables each have about 2.5M rows and take about 1GB each, so covering indexes on those will be very useful if we can develop them.
Yes, several indexes on the IMLSMST_SQL table can be safely removed, and many other indexes on many other tables can be consolidated and some removed as well. But, as I stated to the OP -- see the quote above -- whether or not to pursue that process is up to the OP, it's their tables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 21, 2021 at 4:15 pm
After rewriting the query and adding new indexes:
Total Cpu - 1 min 7 sec -> .488 sec
Total Logical Reads - Over 78M -> 150K.
Very impressive improvements. Thanks guys !!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply