Replacing A Scalar UDF That Processes Variable Numbers Of Rows
46
sql-server-2016
meme
meme
10
This is a fairly literal, but general, interpretation of the underlying recursive algorithm implemented as an inline table-valued function:
Paul White
Discarded alternatives
sql-server
i-one
i-one
Check constraint and index scan
1
sql-server-2019
sabin
sabin
8
Explanation
Paul White
Database using Filestream in Recovering state
18
sql-server
John aka hot2use
John aka hot2use
10
It looks that FILESTREAM initialization happens when RsFx driver is not well initialized yet. The RsFx is file system minifilter driver, it is used by the SQL Server to expose FILESTREAM content via UNC-share.
i-one
Missed opportunity for a seek where everything is NULL
17
sql-server
Mikael Eriksson
Mikael Eriksson
11
This is an issue I see quite often when the 'new' cardinality estimator is used.
Paul White
Query Store Issue or Totally Unrelated
7
sql-server-2016
John aka hot2use
John aka hot2use
5
Since the Query Store change you made succeeded, I think this lock timeout error is unrelated.
Josh Darnell
FORMAT 'G17' vs 'G'
sql-server-2019
Cade Roux
Cade Roux
11
`FORMAT` is implemented in CLR, but the overall question can be answered in T-SQL terms.
Paul White
I have to put the tempdb on the same disk with data and logs, how to mitigate risk?
2
sql-server
James Jenkins
James Jenkins
6
Probably the simplest way would be to turn off autogrowth for all tempdb files, making sure to set them to a reasonable fixed-size based on your workload and available space.
Josh Darnell
Why Does One Constraint Prevent A Sort?
sql-server
meme
meme
11
Essentially it's the same reason
Paul White
Why does adding a TOP allow the SQL Server query optimizer to find a plan that can honor a FORCESEEK hint?
4
sql-server
Joe Obbish
Joe Obbish
14
Although the query text contains an `APPLY`, the optimizer rewrites this to a regular join using the `ApplyHandler` rule early in the compilation process (during simplification). The optimizer prefers joins over applies because it has more transformations for joins[^1](For background on the difference between join and apply, see my article [Apply versus Nested Loops Join](https://www.sql.kiwi/2019/06/apply-versus-nested-loops-join.html)).
Paul White
9
To supplement Paul's answer, ironically, the optimizer is capable of producing apply plan for the original query, if `OUTER APPLY` is used instead of the `CROSS APPLY`
i-one
