What is Azure SQL Database Serverless? When I hear the term serverless my mind gets confused. How can a database exist without a server? Azure is a cloud platform, is my database just floating in the air? No, I am not really thinking that, but still the word serverless can be hard to understand. So, let’s walk through what it is.

Serverless is a term commonly used for function as a service patterns like Amazon Lambda, or Azure Functions, where you have a piece of code that is called and executed without you deploying any infrastructure. Azure Logic Apps are another version of this—a program that exists to do a thing (say create a user and send them an email) without any notion of what computer it executes on. Serverless in terms of Azure SQL Database is a bit of a misnomer because you still have a logical server to which you connect.

Within the Azure SQL Database service, serverless is a type of deployment in the general-purpose service tier for a single database that allows for autoscaling and auto-pausing. It gives you the ability to manage the resources the service uses, and costs associated with your SQL Database, which in turn can save you a lot of money.

Traditionally, when using Azure SQL Databases, you provision a specific compute tier which provides dedicated resources with fixed costs billed on an hourly rate. This is great when your database has consistent usage patterns, using elastic pools or have average higher utilization. But what if your utilization fluctuates? What if you could save money when utilization is lower? That’s where serverless comes in. When you have those less active workloads serverless will scale back your compute tier and even optionally autopause resources and not charge you for non-use. You can imagine how much this can lower your Azure SQL budget. In addition, serverless costs are based on per second usage verses per hour, so every second you are paused you are saving money.  Note, storage is still billed during the pause time since your data is still consuming storage, which is something to keep in mind. Additionally, you should note that serverless costs more per second than Azure SQL Database does—so if you have a constant workload that is relatively high, your costs may be higher than for a standard database. As always it is important to understand your workload before moving to serverless, but it’s fantastic for most dev and test environments.

If the database id paused it, does not mean your database is not available to your users. Serverless continually monitors for logins and activity at the gateway for your database. When the next activity occurs the database automatically resumes at the proper compute. Autopausing requires two things to occur before a pause is initiated. One there must be zero sessions and two the CPU must equal zero for user workload running in the user pool. To help give you a little control over this there is a performance configuration called autopause delay which allows you to set the evaluation time period of inactivity before an autopause will occur.

What if your workload varies throughout the day therefore causing a wide range of consumption of compute resources? Serverless can help with that too. When setting up a serverless SQL Database you will configure a minimum vCores and a maximum vCores. The SQL Database will automatically operate within this range for scaling on demand. This illustrates an automatic cost saving mechanism without having to manually scale your database each time you need to increase or decrease your compute needs.

This was a very simple explanation of what Azure SQL Database Serverless is. I hope this clears up any confusion you might have had.  Once I realized it just means you can autoscale and are not set to a pre-provisioned compute tier, it was simple to understand.

As you know, I spoke to 12 Chapter Leaders and 4 Regional Mentors yesterday. All are very upset with PASS right now and feel slighted, which as a volunteer myself, I empathize. I have given the feedback, to you.

After talking with them here is where the problem lies IMHO: communication and transparency. We received an email taking away our token of appreciation PASS gives us for all our yearlong hard work that helps to build and amplify this community. In the email we also were asked to please promote and encourage Summit registrations among other things. You cannot take something away and then turn around and ask the leaders for more. It is like cutting off our feet and asking us to run. Of course, there will be backlash, such as leaders considering removing their groups from the PASS umbrellas altogether. Believe me when I say it is not about the money, it’s about the principle.

So how to you fix it?
Communication and transparency. I am talking REAL communication, no more political answers. We, as volunteers and leaders, support PASS and we want it to succeed, but we really need to know the truth. Many comments I hear are in regard to the financials and the perception that it’s C&C, a for profit company that is running the show and using the board\community as puppets to further their agenda. This is further amplified especially since the owner of C&C’s has an executive seat on the BoD. (don’t shoot the messenger, me). It is this perception that needs fixed, until it does the community will continue to be disgruntled and unsupportive.

There is a feeling the community is being swindled for lack of a better word and the community aspect of PASS is lost to a for profit company. I challenge the board and leaders to change this through better communication and transparency. Tell us the numbers, show us a revised budget and yearly cost per member, and/or(?) cost per summit registration. The more we know the more we will help. Do you have a must have break even number of registrants you need, how many do you have now? Tell us, we will help you get there.

We really want PASS to succeed and be there in the future. I urge you to try to fix this.

Perception is key. I am an ally and one of your strongest supporters, I get it believe me. Which is the only reason I have reached out the last few days. I am sincerely hope that has been conveyed.

Under compatibility level 150, in both SQL Server 2019 and Azure SQL Database, you now can use batch mode for CPU-bound analytic type workloads without requiring columnstore indexes. There is no action needed to turn on batch mode aside from being on the proper compatibility mode. You also have the ability to enable it as a database scoped configuration option (as shown below), and you can hint individual queries to either use or not use batch mode (also shown below). If you recall in my earlier blogs on columnstore, it is batch mode in conjunction with page compression that drastically increases query performance. This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server.

What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently.

Just like columnstore this only benefits analytic type workloads or data warehouses, as mentioned above. This is meant for aggregations and joins that process thousands of rows. It will not benefit you when processing singleton lookups. If where clause that does not look up a range of values and is just satisfying predicates, then batch mode does not provide a benefit.

How does the engine know when to use batch mode? According to docs.microsoft.com  the query processor uses heuristics and will make decision based on three checks. An initial check on tables sizes, operators used and cardinality estimates. Then the optimize checks to see if there are cheaper plans it can use. If no alternative better plans are available, the optimizer will choose batch mode. There are some limitations that will prevent the use of batch mode such as, in-memory OLTP tables or for any index other than B-Trees or on-disk heaps. It will also not work for LOB columns including sparse and XML columns.

You can easily decipher when batch mode is used to run query inside the operator’s properties. Let’s see a demo.

To demo I want to first show you a plan NOT using Batch Mode on Row Store, so let’s turn the feature off because as I mentioned earlier it is already enabled for compatibility mode 150 by default. Run the below database scope configuration script to turn it off.

USE AdventureworksDW2016CTP3
GO
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

Now let’s run this query and make sure we capture the execution plan.

SELECT FS.[ProductKey],

       AVG([SalesAmount]),

       [UnitPrice]

FROM [dbo].[FactResellerSalesXL] FS

    JOIN dbo.DimProduct DP

        ON DP.ProductKey = FS.ProductKey

GROUP BY FS.[ProductKey],

            [UnitPrice]

Note the Table Scan. By hovering over it you can see the operator’s properties and see the Actual Execution Mode says ROW and it processed 11,669,653 rows.

Now let’s run it again in Batch. Instead of changing compatibility lets just turn on the feature with an OPTION HINT.

SELECT FS.[ProductKey],

       AVG([SalesAmount]),

       [UnitPrice]

FROM [dbo].[FactResellerSalesXL] FS

    JOIN dbo.DimProduct DP

        ON DP.ProductKey = FS.ProductKey

GROUP BY FS.[ProductKey],

            [UnitPrice]

OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

You can clearly see the optimizer chose to use BATCH mode based on our HINT. In addition, you can see it ran significantly faster at only 405 ms versus 1.119s using row mode. In general, we’ve seen queries that benefit from batch mode running in almost half of what row mode performance is and columnstore in batch mode performance.

Let’s go ahead and change back to the default Batch Mode again for our database just to prove it would have used batch mode without the use of our hint. Run the below and look at the plan.

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

GO

SELECT FS.[ProductKey],

       AVG([SalesAmount]),

       [UnitPrice]

FROM [dbo].[FactResellerSalesXL] FS

    JOIN dbo.DimProduct DP

        ON DP.ProductKey = FS.ProductKey

GROUP BY FS.[ProductKey],

            [UnitPrice]

BINGO! There you have it!

If you are already using compatibility mode 150 you are already taking advantage of this feature and may not even realized. For those that have not made the leap to 2019 I highly recommend it, if only for this little gem which is one of the many reasons why you should upgrade.

The need to transition to virtual speaking engagements is not easy for me. As someone who loves public speaking and delivering in person training, not being able to do so right now is very difficult. I struggle with staring at my screen talking to my camera as a teaching mechanism. I get asked all the time to speak at virtual user group meeting and online conferences but usually kindly decline. Now that virtual events are our only avenue to speak, I am forcing myself out of my comfort zone and trying a few on for size.

There are a several reasons why this is not my first choice in delivering content. First, I LOVE audience engagement. This is tough to accomplish via camera but can be done, it is just not the same. I am very much an extrovert and thrive on seeing attendees absorbing my content and engaging in conversation before and after my talks.

Two, I have a ton of distractions at home and I find it very hard to focus when I speak live. With two teenagers and cats roaming the house I am constantly dreading the possible embarrassing interruptions.

Three, I am worried about Virtual Conference\Computer fatigue. Now that everyone is working from home we find ourselves sitting at our computers for not just work but for outside social engagement such as Virtually Happy Hours, Family Zoom Calls, shopping, getting our news and anything we can amuse ourselves with while we are social distancing. That means we are or will be getting tired of being at our computers, when work is done, we want\need to step away. The motivation to stay there longer to participate in a virtual training event is getting harder and harder to do.

However, with today’s world as it is, I find I need to adjust if I want to continue to speak this year. So I’ve submitted to a couple virtual conferences and last week stepped out of my zone to deliver a live streamed session to 11,000 people at the C# Corner Public Speaking Virtual Conference on this very topic (I start at min 48).  Several other speakers and I talked about the do’s and don’ts of public speaking regardless if you are a new or seasoned speaker.

https://www.c-sharpcorner.com/events/public-speaking-virtual-conference

One thing I’ve notice that the ability to reach a wider audience is a huge benefit of virtual speaking. I am able to deliver my content to others that would not normally be able to attend in person events. As a speaker it is really a great way to teach a broader audience and expand to international speaking. If you are like me and struggle with speaking virtually, I completely understand. I would challenge you to submit to at least one event and give it a try.