RE: Perfromance Issue with the Query...

SSC Guru

Points: 186127

January 17, 2014 at 2:29 am

For testing purposes, comment out the INSERT part of the batch. Run this test batch a couple of times to obtain a performance baseline.

The difference between actual and estimated rows for the index seek (node 9) on table ORDERSTOCKFACTS (index ORDERSTOCKFACTS_I6) suggest that statistics may be out of date. Run

UPDATE STATISTICS ORDERSTOCKFACTS WITH FULLSCAN

then run the test batch a couple of times.

Next, the plan shows a cost of 92% is attributable to a key lookup. Index ORDERSTOCKFACTS_I6 doesn't contain columns QUANSTOCKNKG and QUANCONDNKG, so SQL Server has to use the cluster key in index ORDERSTOCKFACTS_I6 to look these up in the table. Adding these two columns to index ORDERSTOCKFACTS_I6 as INCLUDE columns would eliminate the key lookup at the expense of slightly slower index seeks. With only two columns it's likely to be well worth it. If you're not sure how to add these two columns to the index, post the CREATE INDEX script for ORDERSTOCKFACTS_I6.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden