Creating an Index based on Execution Plan

Question

I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the performance.

But as I am looking at the execution plan to see where I could possible gain more performance, I noticing this - SQL is basically having to read 5 times the number of records that are being returned here from this one particular table and Plan Explorer is telling me this is where 24% of the query cost is coming from.

But when I look at the index that is mentioned, these are the columns that are included in the index ( no columns are included ) :

So my question is - as I try to understand what exactly I am looking at here - is there anything I can do to the existing index to help performance here?

I tried to use Brent Ozar's Paste the Plan but the XML was too large for that and since the TSQL itself is about 800 lines, I have created a folder in my Google Drive that contains the .sql file, .sqlplan file and a .pesession file ( for Sentry One Plan Explorer ).

Link to files.

Answer 1

You may find this answer to be helpful. The index contains all needed columns for the query so it can be used by the query. However, the order of the columns does not allow for as much filtering as possible using the index keys. About 14.3 million rows are located in the index using an index seek but almost 12 million of those rows are discarded after the predicate filter is applied. It's also worth mentioning that the table only has 10.7 million rows. Using nested loops and an index to read 14 million rows from a table with 10.7 million rows may not be the best choice. With that said, you can make the index seek more efficient by changing the index key order to the following:

1. PolicyNumber
2. CovPerilId
3. TransactionId
4. All other columns

Changing the key order of the index may impact other queries in positive or negative ways. I can't say if it's the right decision to make overall for the server's workload.

I noticed a few other things while looking at your query plan:

• About 164 seconds (38% of total execution time) was spent during query execution waiting to send results to the client. Returning almost a million rows to the SSMS results grid can take a while. Be sure to run your query in a way most similar to what the application does to get the most accurate tuning results. You may find it useful to discard to query result set in SSMS while doing performance testing.
• About 42 seconds (10% of total execution time) was spent waiting on CPU. The query will complete faster if it can be changed to use less CPU, if other queries on the server can be tuned to use less CPU, or if the total core count of the server is increased.
• There are other index seeks in the plan where more rows are read from the index than are present in the corresponding table. '[AfterShock].[dbo].[LineOfCoverage].[LineOfCoverage11]' in node 53 is one example. A different index or join type may improve performance of the query.
• There are some poor cardinality estimates that lead to tempdb spills. Explaining why is outside the scope of this Q&A. The query is fairly complex so perhaps splitting it into multiple queries that use temp tables will result in better performance.

Answer 2

Transaction Id in the index is not the leading key column in both the table it seems.

You are probably filtering with transaction id in where clause which is an inequality predicate i.e <= or > = .

Therefore you might prefer creating an index with transaction Id as leading key column to see if that helps in avoiding the spills