5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keysAscending keys since 2005 SP1 from Mark Storey-SmithMark Storey-Smith comment.

Edit 2015-05-07:

Some cases were still estimating 1 row (sometimes). Using unknown appears to help and then the IsNull() can also be removed:

  select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

Edit 2015-05-07:

Some cases were still estimating 1 row (sometimes). Using unknown appears to help and then the IsNull() can also be removed:

  select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

Edit 2015-05-07:

Some cases were still estimating 1 row (sometimes). Using unknown appears to help and then the IsNull() can also be removed:

  select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))
4 added another solution because first was still failing sometimes
source | link

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

Edit 2015-05-07:

Some cases were still estimating 1 row (sometimes). Using unknown appears to help and then the IsNull() can also be removed:

  select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274)

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

Edit 2015-05-07:

Some cases were still estimating 1 row (sometimes). Using unknown appears to help and then the IsNull() can also be removed:

  select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))
3 Added alternative solution and note about 2014.
source | link

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274)

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274)

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

One answer to Q3)

Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

 select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274)

Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

Edit 2014-04-29:

The estimates for "ascending keys" has likely been improved in SQL Server 2014's improved Cardinality Estimator

There is also a traceon() solution for Ascending keys since 2005 SP1 from Mark Storey-Smith comment.

2 added 9 characters in body
source | link
1
source | link