Tag Info

Hot answers tagged

7

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel. In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning ...


6

Kris, •Do we really think that we will see a measurable performance gain here? If so, how can we quantify it aside from looking at the space used in the query above? That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much ...


5

Most likely, either: The state of the database (e.g. statistics) changed since the cached plan was first compiled, causing a recompilation; or Your SSMS connection uses different settings (e.g. ANSI_NULLS) from those used by the connection that cached the plan (so it could not be reused); or It was an ad-hoc batch and the text of your query did not exactly ...


3

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything. Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached. When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc ...



Only top voted, non community-wiki answers of a minimum length are eligible