StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your table has 1,517,820 rows. Of these nearly one third (476,672) contain the value <code>DAV</code> (or more accurately the value <code>DAV </code> as it is of <code>CHAR(8)</code> datatype so is padded out with trailing spaces.</p> <p>In a <code>LIKE</code> comparison trailing spaces in the <code>match_expression</code> are not significant (though they are significant in the <code>pattern</code> itself). </p> <p>Therefore the expression <code>WHERE APPLIC LIKE 'DAV'</code> does in fact match 476,672 rows. Neither of the execution plans estimate anywhere near this however. Though the faster plan (with variables) is three orders of magnitude closer.</p> <pre><code>+-----------------------+-----------+-----------+ | | Slow Plan | Fast Plan | +-----------------------+-----------+-----------+ | Estimated # Rows | 32 | 47,343 | | Memory Grant | 1 MB | 333 MB | | Degree of Parallelism | 1 | 4 | +-----------------------+-----------+-----------+ </code></pre> <p>For the plan with the variables as SQL Server does not do variable sniffing (without e.g. the <code>OPTION (RECOMPILE)</code> hint) it falls back on guesses as to how many rows will match the predicate and comes up with an estimate that around 3.1% of the table will qualify. </p> <h3>Fast Plan</h3> <p><img src="https://i.stack.imgur.com/qUR9b.png" alt="Fast"></p> <p>The plan with the literal value ought to have much better estimates. The screen shot you supplied of the <code>DBCC SHOW_STATISTICS</code> output (after adding another million rows) shows that <code>DAV</code> is definitely in there</p> <p><img src="https://i.stack.imgur.com/TSKV0.png" alt="Stats"></p> <p>Unfortunately it seems that although the trailing space in the column values are not significant for the query result their presence does mess up the cardinality estimates (<a href="https://connect.microsoft.com/SQLServer/feedback/details/763060/statistics-estimation-for-like-on-columns-with-trailing-spaces-can-be-very-wrong#tabs" rel="nofollow noreferrer">Reported as a bug here</a> and currently stated to be fixed in the next version). As a result of this problem it estimates only a handful of rows will be returned and comes up with the following plan.</p> <h3>Slow Plan</h3> <p><img src="https://i.stack.imgur.com/bh1rp.png" alt="Slow"></p> <p>As well as performing half a million key lookups because of the poor cardinality estimates the memory grant is probably no where near adequate for the size of data being sorted resulting in spills to <code>tempdb</code>.</p> <p>There are many work arounds you might consider if you can change the query or table schema.</p> <ul> <li>Using <code>=</code> instead of <code>LIKE</code></li> <li>Changing the <code>WHERE</code> clause to <code>LIKE CAST('DAV' AS CHAR(8))</code></li> <li>Changing the column datatype to <code>VARCHAR(8)</code> (and ensuring all stored values are trimmed).</li> <li>Dropping the current index being seeked (<code>Index_A</code>). You haven't supplied its definition but if it is a single column index on a column with few distinct values its presence may be more of a hindrance than a help (depending on your query workload))</li> <li>Adding a covering index with key column <code>APPLIC</code> (and possibly <code>LOGDH DESC, M8_ID ASC</code> to avoid a sort) and the other referenced columns as <code>INCLUDED</code>.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload