Last week, I presented a session on Intelligent Query Processing for the first ever Virtual PASS Summit. This summit had a mix of live and pre-recorded session. During the pre-recorded sessions, the speaker could hang out with the attendees in the discussion room and join a virtual chat room at the end. My session was live, so I answered questions a few questions during the session. There were a couple of questions that I couldn’t answer fully during the session, but all the questions were interesting, so I’ll go through them here.
Q: Is the memory grant feedback feature available in 2019?
A: This one is available in 2017 for queries involving at least one columnstore index. In 2019, it’s also available with rowstore queries, in other words, no columnstore index required.
Q: Is the approx distinct count function only in SQL 2019?
A: Yes, it’s a 2019 feature, but you don’t need the database in 2019 compatibility mode to use it. It’s also the only one that requires a code change.
Q: “Even though there’s only one statement, it’s still a multi-statement table valued function.” How?
I showed an example of an inline table valued function and how easy it is to turn it into a multi-statement function. The inline table valued function must have this format which you can get by right-clicking the table valued functions in SSMS and selecting “New inline table valued function…”
CREATE FUNCTION
(
-- Add the parameters for the function here
<@param1, sysname, @p1> ,
<@param2, sysname, @p2>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO
Notice that the RETURNS statement must only say TABLE, and there can be just a SELECT statement in the body. In my example, I added the table definition to the RETURNS and inserted the rows into it in the body. Even though my function had just one statement, it was a multi-line function since it didn’t follow the rules for an inline function.
Q. Is it always better to go for expressions rather than the scalar UDF in SELECT?
A. I’m going to say that it is better most of the time to type out the expression instead of using a UDF. In 2019, Microsoft has added UDF Inlining that takes care of the performance hit some of the time. There is a long list of exceptions in the documentation, so be careful!
Q. On Table variable deferred, if I use option (recompile) during second of third run will fix the estimation?
A. This is something I hadn’t tried, so I didn’t answer during the session. think that if you are going to be adding recompiles, you may as well just use a temp table.
By adding OPTION(RECOMPILE) to the SELECT query using a table variable, you can get an accurate cardinality estimate. This could help in some situations, but I think that the new feature is better since you don’t get the recompiles.
Q. Does this functionality change in SQL 2019 where Table Value Functions defaults to the assumption of 100 rows and not stats are generated?
A. This is the interleaved execution feature added in 2017. The functionality is the same. It doesn’t default to 100, and stats are still not generated. It applies when static data is passed in, not columns.
I ended up drawing a blank during one of the demos in this section. This was the example using a CROSS APPLY with a column from the outer query. What threw me was the information on the operator. If I had just looked at the property tooltip, I would have seen that the estimate was just 100 as expected.
Q. On Memory Grant Feedback, is it possible to fix a minimum memory grant using Query Store or other tool. I am imagining a case where the cardinality changes too much and it is better to do not adjust too much.
A. This is another question that I deferred. During the keynote presentation on Wednesday, Bob Ward and Connor Cunningham announced the ability to add hints to Query Store. Otherwise, you could just add a hint to a query if you wish. Here’s a post from Erin Stellato on the topic.
Q. Is interleaved execution also only available in Enterprise Edition? IS IQP as a whole, only in Enterprise Edition?
A. Interleaved execution is available in other lower editions. Some of the features are Enterprise only, and I’ve found that those typically involve the word “batch” in the name or description.
Thanks to all who attended the session!
Aunt Kathi's SQL Server Home 