Are your Azure Fabric pipelines with multiple notebooks running slower than you’d like? Are you paying for more Spark compute time than you should be? The culprit might be a simple setting that’s easy to miss. In this blog post, we’ll dive into the “For pipeline running multiple notebooks” setting in Azure Fabric and explain why enabling it can significantly improve your pipeline’s performance and reduce your costs.

The Challenge: Multiple Notebooks, Multiple Spark Sessions

Imagine a common scenario: a data pipeline that needs to get data from multiple sources, process it, and then use that data for a final task, like generating an email. A typical pipeline for this might look something like this:

This pipeline, for example, has three notebooks running in parallel to fetch different datasets, and a fourth notebook that uses this data to generate an email.

Without a specific setting enabled, each time a new notebook activity starts, it spins up a brand-new Spark application. This process of starting a new Spark session takes time. For a pipeline with four notebook activities like the one above, that’s four separate Spark application startup times. These redundant startups add up, creating a performance bottleneck and extending the overall runtime of your pipeline.

Even worse, each new Spark session consumes its own compute resources, meaning you are being charged for the time it takes for each session to initialize and for the resources it uses, even if the other sessions are idle.

The Solution: Sharing Spark Sessions

This is where the “For pipeline running multiple notebooks” setting comes in. This powerful feature allows multiple notebooks within the same pipeline to share a single Spark application. Instead of each notebook starting its own session, they all connect to and use the same underlying Spark cluster.

To access this setting, you need to go to your workspace‘s settings.

1. Click the Workspace settings icon in the top right of your Azure Fabric workspace.
2. In the menu on the left, navigate to Data Engineering/Science and then Spark settings.
3. Under the High concurrency tab, you will find a toggle labeled For pipeline running multiple notebooks. Make sure to switch this toggle On.
4. Ensure that each notebook uses a common session tag.

When this setting is enabled, all notebooks in your pipeline will share a single, long-running Spark application. The Spark application starts once at the beginning of the pipeline run and remains active until the entire pipeline is complete.

The Benefits: Performance and Cost Gains

Enabling this setting provides two major advantages:

• Improved Performance: The most immediate benefit is a significant reduction in pipeline runtime. By eliminating the overhead of multiple Spark session startups, your notebooks can execute much more quickly. In a parallel pipeline like our example, the three Get Data notebooks can all begin their work immediately without waiting for their own dedicated Spark cluster to spin up.
• Reduced Costs: Since your pipeline is only using one Spark application for its entire duration, you’ll be paying for fewer resources and less time. The cost savings can be substantial, especially for complex pipelines that run frequently or have many notebook activities.

In a world where data pipelines are the backbone of modern data platforms, optimizing their performance is critical. By taking a moment to enable the “For pipeline running multiple notebooks” setting, you can ensure your Microsoft Azure Fabric pipelines are not only faster but also more cost-effective. Don’t let unnecessary Spark session startups slow you down—turn on this setting and watch your pipelines fly!

Comparing this setting in Fabric to AWS’ Glue

In a previous post, I mentioned comparing Fabric with other cloud platforms. Let’s kick that off by discussing how this may look in AWS.

AWS Glue is a serverless ETL service where a “job” is the primary unit of execution. Each Glue job, whether it’s a Spark ETL job or a Python Shell job, is designed to run in its own, isolated environment. A single Glue job can contain complex logic, including the ability to run multiple scripts or stages, but it’s all within the context of that one job’s execution environment and Spark session.

While you don’t have a toggle for sharing sessions across multiple, distinct Glue jobs in the same way as Azure Fabric, there are ways to achieve similar goals:

• Job Chaining/Workflows: You can use AWS Glue Workflows or other orchestration tools like AWS Step Functions to chain together multiple Glue jobs. This allows for a sequence of tasks, where the output of one job becomes the input of the next. However, each job in the workflow will still spin up its own, separate Spark environment.
• Single, Monolithic Job: A more common and efficient approach in Glue is to consolidate your data processing logic into a single Glue job script. Instead of having separate jobs for GetProductData, GetSalesData, and GetCustomerData, you would write a single Python script within one Glue job that performs all three tasks and any subsequent processing. This job would start one Spark session and use it for the entire duration of the script, thus eliminating the overhead of multiple Spark session startups. This approach is the closest conceptual equivalent to what the Fabric setting accomplishes.
• Glue Streaming Jobs: For streaming data, Glue has continuous streaming jobs. These jobs run continuously and maintain a persistent Spark environment, similar to a long-running session. However, this is for streaming use cases, not batch processing with multiple discrete jobs.

In summary, while there isn’t a simple toggle, the recommended practice in AWS Glue to achieve similar performance and cost benefits is to structure your code into a single, comprehensive job rather than using multiple, small jobs that would each incur Spark startup overhead. This is a key difference in how these two platforms are designed and how you would optimize your data processing workflows on each.

Many of us manage databases or applications that contain regulated data that can’t leave a production environment. This means that we need to “clean” the data if it’s going to be used in QA or development work and one common way to de-identify the data is to simply update columns like firstname and lastname with a simple format “firstname” + counter; however, this results in all the data being unique and sequential. Firstname1, firstname2, firstname3, …
This isn’t good for getting like for like results with a production database and can lead to questions we’ve heard before in the workplace like “Why didn’t we catch this in QA?”

Dawn of AI

We have so many new tools at our fingers with prompt AI like ChatGPT being able to give suggestions on email formatting and content, research detail on Azure or other topics, creating stories or even poems. I asked myself, “Can ChatGPT generate data for SQL Server (and other databases)?”

The answer is yes; but, it comes with limitations. Let’s discuss…

Generating Realistic Test Data with AI

At first, I questioned if ChatGPT could create test data for a table schema at all, since it uses a generic model that isn’t specific to SQL code. I prompted “Create test data for my table with the following schema: create table x (id int, firstname varchar(20), lastname varchar(20), lastpurchasedate datetime2(0))” and the result was surprisingly good!!

Next I wondered how fancy I could get and asked for some of the values in date columns to be null. Yet again I was quite pleased with the result.

Knowing that the AI can generate test data I figured, why not create 5000 rows that I could dump into my table and get straight to testing. Let’s push this thing and see what we get. The result was not quite what I wanted. In fact it produced code that creates data with the exact issue described in the opening paragraphs of this post. Non-realistic, sequential test data is not what we’re after.

Why not prompt it to create “realistic data”?

OK This code is good but there’s a big problem with it. It doesn’t actually generate any test data it assumes there is a firstname and lastname table to pull values from. But wait, We can use this code if we generate smaller batches of names using the prompts, right? Let’s see.  How many names can we generate at one time? The answer is 50. I tried 5000, 100, and then 50. Anything after 50 will result in 50 names and a comment saying “Add more data here”, lol. We can work with this though!

Prompt ChatGPT to “generate 50 realistic first names not based on a counter”.

We can use the strength of this AI to give us many diverse names to use by prompting different countries or regions like “US, France, Germany” or “American, French, German, …” names. Just remember that the real limit is 50. You can even prompt the AI to give you 50 names that are not in a list of names, giving you new names to work with. So if you want 100 Australian names, prompt it for the first 50 then prompt it for another 50 but ask it not to include the names in the original 50 you just got.

Now that we have a few hundred first and last names we can use the code we got earlier to generate test data for our application dev and qa work.

Here’s the simple code I came up with. Keep in mind you can use the first names and last names that you generate to randomly assign data in your “de-identification” process as well. This code is just a very simple example to get you thinking of the possibilities.

— random data generation
SET NOCOUNT ON;

— Create a temporary table for German first names
CREATE TABLE #FirstNames (firstname varchar(20));

— Enable random data generation
SET NOCOUNT ON;

— Insert 50 random German first names
INSERT INTO #FirstNames (firstname)
VALUES
(‘Maximilian’), (‘Sophie’), (‘Lukas’), (‘Mia’), (‘Finn’),
(‘Lena’), (‘Leon’), (‘Emma’), (‘Luca’), (‘Hannah’),
(‘Benjamin’), (‘Marie’), (‘David’), (‘Anna’), (‘Elias’),
(‘Laura’), (‘Tim’), (‘Lea’), (‘Paul’), (‘Johanna’),
(‘Julian’), (‘Emilia’), (‘Jan’), (‘Sophia’), (‘Noah’),
(‘Amelie’), (‘Felix’), (‘Mila’), (‘Simon’), (‘Clara’),
(‘Moritz’), (‘Elena’), (‘Jonas’), (‘Victoria’), (‘Oskar’),
(‘Mara’), (‘Nico’), (‘Isabella’), (‘Erik’), (‘Maria’),
(‘Julius’), (‘Fiona’), (‘Tom’), (‘Maja’), (‘Anton’),
(‘Sofia’), (‘Vincent’), (‘Emily’), (‘Jakob’);

— Insert 50 random French first names into the existing temporary table #lastnames
INSERT INTO #FirstNames (firstname)
VALUES
(‘Léa’), (‘Lucas’), (‘Chloé’), (‘Louis’), (‘Emma’),
(‘Gabriel’), (‘Manon’), (‘Jules’), (‘Inès’), (‘Léo’),
(‘Camille’), (‘Hugo’), (‘Sarah’), (‘Arthur’), (‘Alice’),
(‘Raphaël’), (‘Zoé’), (‘Enzo’), (‘Louise’), (‘Nathan’),
(‘Eva’), (‘Théo’), (‘Clara’), (‘Maël’), (‘Charlotte’),
(‘Tom’), (‘Lola’), (‘Antoine’), (‘Julia’), (‘Mathis’),
(‘Lea’), (‘Paul’), (‘Mia’), (‘Nathan’), (‘Emma’),
(‘Alexandre’), (‘Lina’), (‘Matheo’), (‘Lou’), (‘Ethan’),
(‘Jade’), (‘Evan’), (‘Léna’), (‘Noah’), (‘Lilou’),
(‘Louis’), (‘Elena’), (‘Sacha’), (‘Mila’), (‘Leo’);

— Insert 50 random American first names into the existing temporary table #lastnames
INSERT INTO #firstNames (firstname)
VALUES
(‘Emma’), (‘Liam’), (‘Olivia’), (‘Noah’), (‘Ava’),
(‘Isabella’), (‘Sophia’), (‘Jackson’), (‘Lucas’), (‘Mia’),
(‘Oliver’), (‘Ethan’), (‘Aiden’), (‘Harper’), (‘Evelyn’),
(‘Abigail’), (‘Emily’), (‘Amelia’), (‘Benjamin’), (‘Ella’),
(‘Alexander’), (‘Henry’), (‘Jacob’), (‘Michael’), (‘Daniel’),
(‘William’), (‘James’), (‘Elijah’), (‘Avery’), (‘Sofia’),
(‘Grace’), (‘Scarlett’), (‘Jack’), (‘Chloe’), (‘Lily’),
(‘Luna’), (‘Zoe’), (‘Mila’), (‘Stella’), (‘Aria’),
(‘Eleanor’), (‘Hazel’), (‘Violet’), (‘Leo’), (‘Aurora’),
(‘Levi’), (‘Lucy’), (‘Nora’), (‘Ethan’), (‘Ava’);

— Select the generated German first names
SELECT * FROM #FirstNames;

— Create a temporary table for German last names
CREATE TABLE #LastNames (lastname varchar(20));

— Insert 50 random German last names
INSERT INTO #LastNames (lastname)
VALUES
(‘Schmidt’), (‘Müller’), (‘Schneider’), (‘Fischer’), (‘Weber’),
(‘Meyer’), (‘Wagner’), (‘Becker’), (‘Schulz’), (‘Hoffmann’),
(‘Koch’), (‘Bauer’), (‘Richter’), (‘Wolf’), (‘Schröder’),
(‘Schwarz’), (‘Hartmann’), (‘Lange’), (‘Schmitt’), (‘Werner’),
(‘Schmitz’), (‘Krause’), (‘Maier’), (‘Lehmann’), (‘Herrmann’),
(‘Köhler’), (‘Klein’), (‘Walter’), (‘Schuster’), (‘Böhm’),
(‘Seidel’), (‘Lorenz’), (‘Schwarz’), (‘Bergmann’), (‘Engel’),
(‘Vogel’), (‘Hahn’), (‘Kramer’), (‘Frank’), (‘Sommer’),
(‘Kühn’), (‘Barth’), (‘Zimmermann’), (‘Graf’), (‘Peters’),
(‘Krüger’), (‘Jäger’), (‘Gross’), (‘Voigt’);

— Insert 50 random French last names into the existing temporary table #lastnames
INSERT INTO #lastnames (lastname)
VALUES
(‘Martin’), (‘Bernard’), (‘Dubois’), (‘Thomas’), (‘Robert’),
(‘Richard’), (‘Petit’), (‘Durand’), (‘Leroy’), (‘Moreau’),
(‘Simon’), (‘Laurent’), (‘Lefevre’), (‘Michel’), (‘Garcia’),
(‘David’), (‘Bertrand’), (‘Roux’), (‘Vincent’), (‘Fournier’),
(‘Morel’), (‘Girard’), (‘Andre’), (‘Lefevre’), (‘Mercier’),
(‘Dupont’), (‘Lambert’), (‘Boucher’), (‘Colin’), (‘Caron’),
(‘Renaud’), (‘Picard’), (‘Lemoine’), (‘Lecomte’), (‘Leger’),
(‘Dufour’), (‘Perrin’), (‘Marchand’), (‘Leclerc’), (‘Begue’),
(‘Gauthier’), (‘Faure’), (‘Rousseau’), (‘Blanc’), (‘Guerin’),
(‘Bouvier’), (‘Riviere’), (‘Chevalier’), (‘Beaumont’), (‘Lemaire’);

— Insert 50 random American last names into the existing temporary table #lastnames
INSERT INTO #lastnames (lastname)
VALUES
(‘Smith’), (‘Johnson’), (‘Williams’), (‘Jones’), (‘Brown’),
(‘Davis’), (‘Miller’), (‘Wilson’), (‘Moore’), (‘Taylor’),
(‘Anderson’), (‘Thomas’), (‘Jackson’), (‘White’), (‘Harris’),
(‘Martin’), (‘Thompson’), (‘Garcia’), (‘Martinez’), (‘Robinson’),
(‘Clark’), (‘Rodriguez’), (‘Lewis’), (‘Lee’), (‘Walker’),
(‘Hall’), (‘Allen’), (‘Young’), (‘Hernandez’), (‘King’),
(‘Wright’), (‘Lopez’), (‘Hill’), (‘Scott’), (‘Green’),
(‘Adams’), (‘Baker’), (‘Gonzalez’), (‘Nelson’), (‘Carter’),
(‘Mitchell’), (‘Perez’), (‘Roberts’), (‘Turner’), (‘Phillips’),
(‘Campbell’), (‘Parker’), (‘Evans’), (‘Edwards’), (‘Collins’),
(‘Stewart’);

— Select the generated German last names
SELECT * FROM #LastNames;

— Enable random data generation
SET NOCOUNT ON;

create table #x (id int identity(1,1), firstname varchar(25), lastname varchar(25), lastpurchasedate datetime2(3))

— Declare variables
DECLARE @Counter INT = 1;

— Loop to insert 5000 rows
WHILE @Counter <= 5000
BEGIN
INSERT INTO #x (firstname, lastname, lastpurchasedate)
VALUES
(
(SELECT TOP 1 firstname FROM #FirstNames ORDER BY NEWID()),
(SELECT TOP 1 lastname FROM #LastNames ORDER BY NEWID()),
CASE WHEN @Counter % 5 = 0 THEN NULL
ELSE DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % (365*3) /*3 years*/, GETDATE()) END);

— Increment counter
SET @Counter = @Counter + 1;
END;

select * from #x

In one of my next posts, I hope to use Azure’s Open AI resource to implement this concept further. Keep an eye out for that post.

Some time ago I posted about Halloween protection and how using SCHEMABINDING in your Scalar UDFs can eliminate the need for ugly SPOOL operators. You can read more about that here: https://sqltechblog.com/2016/10/31/why-halloween-slowed-your-queries/

Microsoft has a great post on this new feature here:

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

As for the Halloween protection, let’s take a quick look at how this behaves in SQL Server 2019.

As a quick refresher SQL Server 2017 and older behave like this without using SCHEMABINDING.

Note the first query, which uses the scalar UDF, is higher cost due to the SPOOL.

If we run this in SQL Server 2019 using the 150 compatibility mode, what happens?

Woohoo! The SPOOL is gone without having to alter the code and anything that improves performance without code changes is amazing!

Gotchas

As you may have guessed. UDF inlining is quite new and will be improving over time as the feature matures. You may note that there are hotfixes that have been released to fix some bugs related to this new feature.  (https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019)

As of today, SQL Server 2019 CU4 is the latest and it would seem there’s still some work to be done.

The following code block seems to reproduce a bug in the current latest:

CREATE DATABASE ReproBug;
GO
USE ReproBug
GO
CREATE TABLE Contact (id int identity(1,1), fname varchar(50), lname varchar(50));
GO

USE ReproBug
GO
CREATE OR ALTER PROCEDURE [dbo].[proc_InsertContact]
(
     @lastName     VARCHAR(100),
     @firstName     VARCHAR(100)
)
AS
BEGIN
     BEGIN TRAN

    BEGIN TRY
         INSERT INTO Contact (fname, lname) values (@lastName, @firstName);

        COMMIT
         return 0;
     END TRY
     BEGIN CATCH 
         ROLLBACK TRAN
         RETURN dbo.fx_GetErrorNumber(Error_number());
     END CATCH
END
GO

CREATE OR ALTER FUNCTION [dbo].[fx_GetErrorNumber] (@errorCode INT = NULL)
RETURNS INT  AS 
BEGIN

DECLARE @retValue INT
SET @retValue = 500

IF(@errorCode = 2627)
     SET @retValue = 501   

RETURN @retValue
END
GO

exec dbo.[proc_InsertContact] @lastName=’Vader’,@firstName=’Darth’;

Msg 596, Level 21, State 1, Line 43
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 43
A severe error occurred on the current command.  The results, if any, should be discarded.

Is there a work-around?

Yes; you can set the compatibility mode to 140 or change the UDF to disable inlining. Either change the UDF itself with “WITH INLINE = OFF” or update the database to disable inlining “ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;”

This is an issue I’m watching and I’ll follow up on this post when this issue is resolved.

Update! 

SQL Server 2019 CU5 has been released and resolves the issue described above. Thanks go to the team for their hard work on continuously improving the product. You can find SQL Server 2019 CU5 here:

https://support.microsoft.com/en-us/help/4552255/cumulative-update-5-for-sql-server-2019

So today I’m posting about “the Force” as in Forced Parameterization. If you’ve ever queried using the same query but different parameters, you may have noticed that there are multiple plans in cache by default. As an example if you ran:

SELECT ProductID, Name
FROM Production.Product
WHERE isnull(Weight, 5) between 1 and 20

and then ran:

SELECT ProductID, Name
FROM Production.Product
WHERE isnull(Weight, 5) between 20 and 50

This could store two plans because the isnull function doesn’t allow “Simple parameterization” to occur. With that said, You may think it’d be much, MUCH better to write this as “WHERE Weight between 1 and 20 or Weight is null” but this also negates Simple parameterization.

Let’s take a look at the query plans. You can see the query text for each. Note the parameters.

Because the values for query 1 and query 2 are not parameterized, any time the values change a new plan will be cached which consumes more memory in the instance.

How can we fix this? Alter the database setting “PARAMETERIZATION” to  Forced.

Now that we’ve changed from Simple to Forced we should check the query plan again.

Woohoo! All the values have been parameterized. All good right? Not so fast. Everything and I mean EVERYTHING in the database world is “It depends”.

We’ve condensed the query plans to save memory. All should be good but think about queries that could have greatly different result returned. If query 3 returned 2% of the table for one set of values and 80% of the table for another set of values this could be very bad because you’d essentially have a parameter sniffing problem. Be sure to test before blindly changing any settings and May the Fourth Forced be with you.

#SQLServer #AzureSQLDatabase

In this case we will query a single row using the table’s primary key. We’ll start with a transaction and a single select statement with the XLOCK hint.

Next we’ll look at sys.dm_tran_locks to validate that the row is locked exclusively. This can be verified by observing the request_mode column and noting the “X” for exclusive.

Since this is an exclusive lock it “should” guarantee that all other attempts to access this data would be blocked. After all exclusive blocks selects, inserts, updates, and deletes. The only ways around an exclusive is with a dirty read using the NOLOCK table hint.

Finally, execute the same query without xlock from a different session and you’ll see that XLOCK is completely broken! Well not so fast…

The XLOCK table hint can be considered unreliable. This is because the SQL engine can ignore the hint if the data being accessed hasn’t changed since the oldest open transaction. Note that if both of these queries used XLOCK the second query would be blocked.

Situations like this are why I don’t recommend using hints; but, like all things in the database world: It depends.

What if there’s a business need to have an exclusive lock with a SELECT?

First, I’d ask why is the XLOCK needed. If it’s architectural how did we get here? Bad design can lead to needs for bad practice.

If the XLOCK is truly needed then combining it with PAGLOCK will block SELECT statements on the row. This is because the IX (intent lock) for the page on the second query will be blocked by the X lock on the page from the first query.

What is “How’s My Database?” I’d say it’s something that should have been written a long time ago. Query tuning is a difficult task and can take a lot of practice to become good at. My hopes are that this tool will help the community tune their queries easier, raise awareness to certain issues, and train up those that are just starting out.

You can find the tool in the Windows 10 app store. Just search for “How’s my database”.

How’s My Database is a simple Universal Windows App (UWP). Microsoft didn’t include SYSTEM.DATA.SQLCLIENT in UWP until recently with the release of .NET STANDARD 2.0. Since then they’ve added enough features to allow me to create this tool. I’ve also included the Syncfusion treegrid control to aid in displaying a query plan. I’d like to also thank ComponentOne for lending me some tools for this project; however, I didn’t end up using any.

I’d really love if Microsoft could add SYSTEM.DIAGNOSTICS.PERFORMANCECOUNTER to UWP apps so I could truly write the health check or migration app I’ve always wanted to. Sadly I don’t think it’s on any road map.

UWP is limited from accessing the network locally and thank you to Lance McCarthy of Telerik for helping me with a workaround to access local SQL instances from the tool. You simply need to grab the Windows 8 AppContainer Loopback Utility and use it to unblock any UWP app.

Without this tool you won’t be able to connect to a SQL instance on the local machine.

So what can this tool help with? Lot’s of things.

For example, the “Problems with IN” post I wrote sometime back (thanks Joe Sack for teaching me this).

It can tell the difference between a scan forced by a UDF or scalar function as well.

There are actually about 40 things it checks for.

Current limitations are that queries with a cursor or temp table are not analyzed. There’s also a bug where the missing indexes and warnings appear on the wrong node/operator. Since the tool is using estimated plans at the moment, it may not be as accurate.

I’m planning on a few new features in the next month to add feeding the utility a query plan and displaying the original query. I’m also planning on adding history and the ability to execute a query from the tool. Before we get to those we need to fix some known bugs though. I’m hoping that you. Yes! you can help me identify other bugs to make this a great tool for the SQL community.

Please keep in mind that this is a BETA tool and it’s also free. Please use kind words.

If you haven’t registered yet, no problem. The details are here:

http://www.sqlsaturday.com/774/eventhome.aspx

There’s a ton of great speakers and you don’t want to miss this one.

It’s true that in many situations a scalar function is often a performance bottleneck; but, is there a situation where they could be responsibly used?

What if you had a lookup table that almost never changed? Is it worth doing a join on the lookup to get the data you need?

Let’s examine a simple join between a customer address and a state lookup table.

We have the following tables

To join these you’d usually use a very simple inner join to get the state code, which could also be the state name which is longer than the 4 bytes for int. You may also include other metrics on the States table like geo data or capitol. For now let’s pretend like Abbr is much larger than State (int) so it would make sense to dump it to a lookup table.

Here’s what the join query may look like

To test the value of scalar functions we created the the following code (note that it cuts off but you get the point)

Now that we have a function to test with, we want to create a query that returns the same results as our join query. Let’s have a look.

How do these compare with an actual plan?

Wow! The query with the function has much less cost! Incredible results, right? Not so fast! Sometimes cost can be misleading. To perform a true test we’ll need to do a bit more. Let’s turn off the plan and turn on STATISTICS TIME.

206 ms total elapsed time for the join query.

Only 92 ms for the funtion query. That’s good!

Let’s examine the query stats to see how these two compare.

Here we find some unexpected results. The execution time was less for this single run; however, CPU was greater by a significant amount.

We should dig deeper. You may have not noticed in the query plans that the function isn’t represented in the plan at all. Note that this is SQL Server 2017 and the database is running in the latest compatibility level.

To find out more we’re going to look at the function stats DMV.

SELECT db_name(database_id) as DB,
object_name(object_id) as fn,
type_desc,
last_execution_time,
execution_count,
total_elapsed_time,
total_worker_time,
total_logical_reads,
total_physical_reads,
total_logical_writes

FROM sys.dm_exec_function_stats

What we find is the function was executed 192 times. This is because the function must run once for each row in the result and it’s primarily this reason why scalar functions aren’t usually a great choice.

One final test is to run the queries 100 times. To do this, we’re going to use GO 100.

What have we learned? While the function may look great in the query plan it doesn’t stack up to actual runtime metrics. If there’s one thing a DBA should always say it’s “It depends” but in this case it doesn’t and you should instead add this quote to your toolbox, “You should never fully trust cost in query plans”.

The missing index DMVs are great but they’ve always been missing something.

What are they missing you ask? They currently tell you what table they are for but not what query. How do I know if the queries that sponsored this missing index are business critical or not? Wouldn’t it be nice to know what statements caused this “missing index” to appear?

You may or may not know that you can use xquery to query your xml query plans from cache. If you’re already familiar with this concept then this should be really quick to understand. If you’re not, don’t worry. Xquery is really simple.

We can query our cached plans by using the sys.dm_exec_cached_plans DMV. Joining sys.dm_exec_query_plan gives us the XML query plan and we can then query within that XML with the following statement. This example looks for plans that have a missing index for the address table.

Want the full query? Grab it from my TechNet Gallery here:

https://gallery.technet.microsoft.com/Find-statements-for-13e8c2f4

It’s worth a mention that I rewrote a query from a blog post Jonathan Kehayias did on implicit conversions to get the missing indexes. I wonder what fun you’ll come up with by searching the XML plans?

I plan on writing a few posts on creating extensions; but, before we get to those I’d like to share a tip from one my my Co-workers at DBBest.

Recently we were doing a project that heavily focused on query tuning and many tables had various outer joins. My co-worker pointed out that many of these could be converted to an apply rather than a join.

Apply gives you both CROSS and OUTER. Think of CROSS APPLY like an INNER JOIN and OUTER APPLY like an OUTER JOIN.

Let’s compare some code to see how APPLY stacks up.

For this we’ll be comparing a derived table, CTE, and APPLY. We want to get the Amount for the last transaction for a certain range of customers.

First create the demo code:

create table #Users(UserID int not null primary key clustered,
UserName varchar(100),
Password varchar(100),
Fullname varchar(100))
create table #Transactions(TransactionID int identity not null primary key clustered,
UserID int,
Amount int)

;with Numbers as
(select top(100000) ROW_NUMBER() over(order by (select 1)) rn
from master..syscomments a
cross apply master..syscomments b
)
insert into #Users(UserID, UserName, Password, Fullname)
select rn, NEWID(), NEWID(), NEWID()
from Numbers

;with Numbers as
(select top(1000) ROW_NUMBER() over(order by (select 1)) rn
from master..syscomments a
cross apply master..syscomments b
)
insert into #Transactions(UserID, Amount)
select UserID, rn*40
from (select top 90 percent UserID
from #Users) u
cross apply (select top(cast(rand(checksum(newid()))*100 + 1 as int)) rn
from Numbers
where UserID > 0) n

Next, we want to run the queries with STATISTICS IO ON.

select u.UserID, t1.Amount
from #Users u
inner join (select UserID, max(TransactionID) LastiD
from #Transactions
group by UserID) t on t.UserID = u.UserID
inner join #Transactions t1 on TransactionID = LastiD
where u.UserID between 10 and 40

;with Trans as
(select u.UserID, t.Amount, row_number() over(partition by u.UserID order by t.TransactionID desc) rn
from #Users u
inner join #Transactions t on t.UserID = u.UserID
where u.UserID between 10 and 40
)
select UserID, Amount
from Trans
where rn = 1

select u.UserID, t.Amount
from #Users u
cross apply (select top 1 Amount
from #Transactions t
where t.UserID = u.UserID
order by t.TransactionID desc) t
where UserID between 10 and 40

The results are quite surprising indeed.

The query plan shows a much lower cost for the APPLY and the IO reports slightly fewer as well.

What if we look at “real” data. I mean demo data is fine and dandy but it’s often not representative of the real world. I could fill a database with realistic data using dbForge Data Generator but for this scenario I’ll use AdventureWorks2012. HAHA! Yes, I know I said “real-world data” but let’s look at AdventureWorks anyway.

Here’s the code:

*note that some of you may point out that a join isn’t needed at all since CustomerID is in Sales.SalesOrderHeader. I typed this up and then thought about it and let’s say instead you want to use u.AccountNumber. The query results are the same and now you have reason to use the Sales.Customer table.

select u.CustomerID, t1.TotalDue
from Sales.Customer u
inner join (select CustomerID, max(SalesOrderID) as LastSalesOrderID
from Sales.SalesOrderHeader
group by CustomerID) t on t.CustomerID = u.CustomerID
inner join Sales.SalesOrderHeader t1 on t1.SalesOrderID = t.LastSalesOrderID
WHERE u.CustomerID between 11019 and 11174

;with Trans as
(select u.CustomerID, t.TotalDue, row_number() over(partition by u.CustomerID order by t.SalesOrderID desc) as rn
from Sales.Customer u
inner join Sales.SalesOrderHeader t on t.CustomerID = u.CustomerID
WHERE u.CustomerID between 11019 and 11174
)
select CustomerID, TotalDue
from Trans
where rn = 1

select u.CustomerID, t.TotalDue
from Sales.Customer u
cross apply (select top 1 TotalDue
from Sales.SalesOrderHeader t
where t.CustomerID = u.CustomerID
order by t.SalesOrderID desc) t
WHERE u.CustomerID between 11019 and 11174

We’re still looking at a specific group of customers and getting a list of their totaldue for the last transaction. How does it turn out?

This shows that the CTE is actually the lowest cost; but, what if we look at the CPU and IO used with SET STATISTICS?

What we find is that the APPLY uses 1 fewer IO and elapsed time is consistently 0 ms. The CTE has 1 more IO and is consistently greater than 0 ms and the derived table has significantly greater IO.

What does this mean? Two things:

• Don’t always trust cost in a query plan
• APPLY can be used to enhance some queries. Note that your mileage will vary and you should always test before blindly making changes.

A good example of where you probably shouldn’t use apply is when we change the queries to look between certain dates. Date is on the joined table not the customer and this makes a big difference.

select u.CustomerID, t1.TotalDue
from Sales.Customer u
inner join (select CustomerID, max(SalesOrderID) as LastSalesOrderID
from Sales.SalesOrderHeader
where OrderDate between ‘2008-01-01’ and ‘2008-03-01’
group by CustomerID) t on t.CustomerID = u.CustomerID
inner join Sales.SalesOrderHeader t1 on t1.SalesOrderID = t.LastSalesOrderID

;with Trans as
(select u.CustomerID, t.TotalDue, row_number() over(partition by u.CustomerID order by t.SalesOrderID desc) as rn
from Sales.Customer u
inner join Sales.SalesOrderHeader t on t.CustomerID = u.CustomerID
where t.OrderDate between ‘2008-01-01’ and ‘2008-03-01’
)
select CustomerID, TotalDue
from Trans
where rn = 1

select u.CustomerID, t.TotalDue
from Sales.Customer u
cross apply (select top 1 TotalDue
from Sales.SalesOrderHeader t
where t.CustomerID = u.CustomerID
and t.OrderDate between ‘2008-01-01’ and ‘2008-03-01’
order by t.SalesOrderID desc) t

Note that the APPLY in this scenario is dreadful; so, be sure test!

I hope you found this article useful and it adds to your query tuning kit. Have a great weekend!