Up next in 10
Performance Features in SQL Server and Azure SQL by Javier Villegas || SQL Server Virtual Conference
10K views
Oct 30, 2023
We will cover all the latest features in SQL Server and Azure SQL that are intended to improve performance and make workloads more stable. We will cover Query Store, Automatic Query Tuning, and Intelligent Query Processing. About Speaker: Javier Villegas is IT Director (Data and BI) at Mediterranean Shipping Company with more than 20 years of experience working with SQL Server, Including Azure SQL. His specialization is Administration, Performance Tuning, High Availability, and Disaster Recovery. Javier is a Microsoft MVP Data Platform since 2016 and MCT He is a frequent speaker in conferences and events such as SQL Saturdays, PASS Virtual Groups, PASS Marathons, 24 Hours SQL PASS , vOpen , GroupBy ,DataPlatformGeeks and Azure Global Bootcamps. Conference Website: https://www.2020twenty.net/sql-server-virtual-conference/ C# Corner - Community of Software and Data Developers https://www.c-sharpcorner.com #SQL #Bigdata #AzureSQL #conference #sqlserver #csharplive #csharpcorner
View Video Transcript
0:30
So let's start. Today we will be talking about performance features in SQL Server and Azure SQL
0:38
Let's cover a few of those new features, existing features, and hopefully all of you can learn something today to go to your environments and start using it quickly
0:52
So, a little bit about myself. My name is Javier Villegas. I am from Buenos Aires, Argentina
1:01
I work at Mediterranean Shipping Company as IT Director of Data and BI. I am Microsoft MVP and
1:08
Microsoft Certified Trainer, and also a technical speaker which loves to participate in every single
1:15
event that I can, right? On the left side of the screen, you have all my contact information
1:22
Twitter, LinkedIn, email, blog. So feel free to ring me for any question related to this presentation
1:28
or if you just want to do networking. All right. So let's start real quick
1:35
Let's see what we're going to be covering today. Basically, Microsoft is not supporting anymore SQL Server 2008 and R2, right
1:45
And SQL Server 2012, the extended maintenance, we are kind of, you know, soon is going to be also off
1:57
So what Microsoft calls the modern version of SQL Servers are basically 2016, 17 and 19
2:05
So in terms of SQL Server on-premise, let's focus on these three versions
2:13
We also going to be covering performance features in Azure SQL, right
2:19
So this new umbrella called Azure SQL, which covers a couple of things that we will go through the presentation
2:28
Nowadays, right, under this umbrella of Azure SQL, let's do the initial categories or let's say product slash services that Microsoft defined for Azure SQL
2:46
First of all, in Azure, in the bucket of infrastructure as a service, we have virtual machines running same SQL Server version that we saw before, right
2:59
Since 2008 and 2008 R2 up to 2019 in any combination of operating systems, SQL Server version and edition, right
3:15
So this is kind of the same thing that we are used to in on-prem, right
3:23
But running on a virtual machine. Then in the category of platform as a service, right
3:34
We have two flavors. One is Azure SQL Managed Instance, right? This is a managed SQL server service that Microsoft give us in where the only thing we need to do is just connect to the SQL server endpoint with Management Studio, with Visual Studio or any other tool that we use to connect
4:03
This is quite similar to what we know about SQL server. So the footprint in terms of features
4:12
connectivities, and any kind of usage is quite, quite similar, not 100%
4:20
but quite similar to SQL Server. And then we have Azure SQL Database
4:26
which is the modern version of SQL Server, which again, has a lot of things in common
4:33
with SQL Server, but it's more target to a specific scenario, which is called Modern Application, right
4:41
So in this conversation, we won't go deeper on what Azure SQL Database is
4:47
but it's one of the flavors that we have in the Azure SQL family
4:53
Again, now's the day SQL Server runs everywhere. I remember on my beginnings in where the only platform
5:02
that was Windows Server running on Intel AMD processors and SQL Server 6, 6.5, et cetera
5:13
But nowadays, we can say that SQL Server is everywhere. So we have SQL Server running in Windows environment
5:23
environmental servers. Since SQL Server 2017, Microsoft gave us the possibility of running SQL Server in Linux
5:36
major Linux distributions like Suse, Red Hat or Ubuntu. And in parallel, we also have the possibility
5:44
of running SQL Server in containers and also under Kubernetes if we need to orchestrate a bunch of containers
5:53
with SQL Server. Additionally, on the cloud or in this particular case, Azure environment
6:05
we have, as we mentioned before, the virtual machines. We also have managed instance, as we said
6:10
and Azure SQL Database. And then, finally, we also have the possibility
6:15
of running SQL Server on Edge. Basically, this is SQL Server running on ARM devices, right
6:24
Like a Raspberry Pi, in where you have a very common footprint of your SQL Server environment
6:32
Of course, you won't be running your critical workload, your mission critical environment in one of these devices
6:43
But for certain scenarios, specifically on Edge, right, it's a great, great option
6:51
So going deeper to performance, right, We know some of the capabilities and tasks that we may need to consider when we do performance tuning and troubleshooting
7:06
We know that we definitely need to take care about memory, CPU, and I.O
7:13
Those are the three major pillars of SQL Server. we have indexes partition in memory, most recently
7:23
and also we have something that is called intelligent performance We gonna be covering that in a minute There are also certain tasks that we always need to take care
7:36
of, like maintenance, right? So definitely, our SQL Server required us as DBAs
7:44
to schedule and prepare certain maintenance activities that we need to do on a specific environment
7:53
In the Azure SQL world, right? This hasn't changed much, right? As Microsoft called this Azure SQL is just SQL, right
8:04
So all that we learned over the past many, many years applies on Azure SQL, right
8:14
So we need to take care of CPU, memory, and IO, right
8:19
right we have to handle indexes maintenance partitions and you know we have this particular
8:27
thing called intelligent performance that we're going to cover when we need to configure an
8:34
environment for achieving performance more i mean we can talk about any production environment
8:42
we know that there are certain things that we always have to take care of like 10db right there
8:48
are multiple articles and we can discuss for hours about 10 dB, but there are certain rules
8:55
that apply for 10 dB. In terms of Azure SQL, we need to consider that the recovery model
9:07
in Azure SQL is only full, right? So this is something that for certain performance operation
9:14
And I mean, just knowing that we have to deal with that is enough, right
9:21
We don't have simple recovery model. We also have, you know, to deal with files and files groups
9:29
It's a little bit different than what we know on-premise. The whole I.O. thing is a little bit more complicated
9:38
Not complicated. There are more things that we may need to consider, right
9:42
This is not the same storage that we know how to deal with in an on-premise environment
9:51
but definitely there are a bunch of things in common. And of course, MaxDOP, again, we can talk for hours
9:59
Also, resource governor, right? We can define resource governors to, you know, give and assign certain set of resources for
10:07
a particular type of workload, right? In terms of monitoring and troubleshooting, this is kind of all the thing that we always know, right
10:19
But we have some additions in Azure SQL. Definitely, we have the DMVs, right
10:26
The dynamic management views in where, you know, SQL Server gives us a ton of feedback for us to understand what's going on
10:33
and basically to do monitoring, troubleshooting, and also be proactive in certain areas
10:40
to avoid future performance problems, right? In Azure, we have the different type of metrics
10:50
that we can monitor directly through the Azure portal. We also gonna be covering specifically query store
11:00
which is one of the features that I like a lot. and is a game changer, believe me
11:06
So then, you know, we will see this in action in a few minutes
11:13
In terms of DMVs, right, for managed instance and Azure SQL database
11:19
we have all the ones we know, right? But there are certain cases that, I mean, not cases
11:28
There are additional DMVs for each of them, which are specifically for points related
11:36
to SQL Server as a service, right? We have the list in here
11:41
If you all use extended events, something that we definitely should be using heavily
11:49
these days, right? We also have the possibility of configuring extended events sessions in managed instance
11:56
and Azure SQL database. The main difference is that the target, right
12:03
Since this is a managed service, we don't have access to the file system
12:09
the target will be on Azure Blob Storage, right? So definitely we can do and create
12:18
and configure our sessions, right? And the target will be on a Blob Storage
12:24
So let's go deeply to this features that we're gonna be covering
12:32
As I mentioned at the beginning, this is mostly based on modern SQL server versions
12:39
since SQL Server 2016. On 16, SQL Server 2016, Microsoft released this feature
12:46
which was a kind of a game changer, is Query Store. And what is QueryStore
12:56
Basically, will help us to keep track of a bunch of things, right
13:03
Which will allow us at the end to accomplish good performance. And what do I mean with good performance
13:12
We're coming from older SQL Server version and we all know that, you know
13:18
one day you can have your critical report running, right? normally runs for, let's say, 20 minutes
13:29
and early in the morning, you have your output in your desktop
13:34
That particular day, something happened, and that report is running forever. So besides having good performance
13:46
fast execution, and low resource usage, we also need to maintain that performance level over time, right, on a better way
14:01
And this is what Microsoft is targeting these days with this set of features, right
14:07
So we're going to be covering query store, automatic query tuning, adaptive query processing
14:13
which later turns into intelligent query processing, right? We're going to check something about in-memory and also this feature called 10 dB metadata
14:23
So this intelligent performance basically is based on the concept of, you know, while our workload runs, SQL Server collects information about this and give us the possibility of adjusting the performance on the fly, setting specific topics
14:51
topics like for example in Query Store and we going to see or eventually in an automatic way right So what is Query Store A bunch of people define this and I like this definition as the flight recorder
15:11
of our user databases, right? So with QueryStore, which by the way is disabled
15:20
in SQL server on-premise, but is enabled by default in Azure SQL, right
15:29
We can turn it on for a specific user database. And what we're gonna be doing is basically capturing
15:37
all the execution statistic, plan changes, resource usage. And then in 2017, Microsoft also added the possibility
15:50
of keep tracking of the wait stats, right? So what can we do with this
15:58
Okay, we can have a SQL server monitoring all the activity on that particular database
16:05
from a very nice dashboard. We can see every single query, right
16:13
That is running in our server, right? With all the statistics like that they have
16:19
For example, we have one of the report, which is the queries with regressions
16:25
or the top heaviest queries in our system, right? So with that, we can see for each of them
16:33
the amount of resource usage, the amount of time that was running
16:39
usage of reads, writes, et cetera, et cetera. And how this works internally
16:45
Basically, when SQL Server has to compile, you know, we execute the statement
16:52
a store procedure, something in our SQL environment. What we have is SQL Server
17:00
beside the compilation, send a message to the query store. So it's going to store internally
17:05
in this flat recorder, right? All the plan, right? Then it will execute the query, right
17:14
as we normally do and then it will send again another message to query store to keep track of
17:22
all the um execution stats right and where uh does sql store all this information well basically
17:32
within the same uh user database right so from management studio or from t-sql we can enable
17:41
query store and define an amount of a space, certain amount of gigabytes to be allocated
17:53
on the same user database to store query store information. So it's quite simple to enable, quite simple to use it, and definitely give us a lot of
18:11
feedback to do performance, troubleshooting, and tuning. This is what we're going to be seeing from Management Studio
18:21
all these reports. And we're going to see it in action in a minute
18:26
And what is one of the beauties that we have with QueryStore
18:30
For that case that I mentioned that the report is normally running in a certain amount of time
18:36
and suddenly the execution time change, right? One of the most common possibilities
18:45
is that the plan has changed and the new plan is not ideal, I would say
18:51
It's not as good as the previous one. So we have more usage of resources and more execution time
18:59
From management studio, right? We can decide and manually force to use
19:05
I mean, for a particular store procedure or statement, we can manually form to use a previous execution plan
19:14
So then from that point onward, right? SQL server will rely on the plan that you force to use, right
19:24
And eventually come back to the previous execution time and type of, you know, usage
19:34
So there are certain cases in where we need to use QueryStore
19:45
Like, for example, when we do a migration, right? When we do a migration, definitely it's a good thing to see if we have regressions in our workload
19:55
and easily we can detect it with QueryStore. But also, you know, if you definitely have a very busy environment, you know, it's good to keep track of all this activity, these statistics for you to understand what's going on and, you know, eventually tune it and get better resource usage
20:20
So here are a set of best practices in using Query Store
20:26
Definitely important to follow, right? And also know that if you are enabling a query store in a very busy transactional environment
20:38
right, there is a list of things that you have to consider and eventually enabling some
20:45
trace flags, right? So, again, it's as simple as enabling the query store
20:53
But again, that is if you want just to play. But if you're going to be doing it in a busy environment, well, it's good to take a look on these best practices so you can use it in the right way
21:08
So excellent. I love query store. But what happened in that case in where we have the regression in the middle of the night, right
21:21
So nobody goes to our server again until 8 in the morning
21:27
We're going to have this regression, right, during the whole night. And then when the DBA arrives to the office, 8 a.m., it's going to be notified, right
21:39
and manually going to force the execution plan to, you know, be the previous one
21:46
and go back to the original performance. So with that, Microsoft on SQL Server 2017 has released another feature called automatic query tuning, which basically works at the top of query store
22:07
And what can you do with automatic query tuning? Basically, if you have query store enabled
22:15
and you have automatic query tune enabled for this particular user database
22:22
if automatic query tuning detects that one session one procedure or one statement right has a regression
22:35
automatically will set the last known good plan, right? That means that without us doing anything
22:45
right, even if it happened at 2, 3 a.m. in the morning, it will force to use the last known good
22:51
plan right this is this is really awesome and and i use it a lot right so with that you can have
23:01
automatic plan correction uh on the fly right without you uh taking care of forcing the the
23:09
plans right in azure sql database right there is an additional feature which also allows uh the
23:20
automatic query tuning to drop and create indexes on the fly. So if it detects that there is a
23:28
missing index for a particular type of procedure statement, right, if you enable it, right, it can
23:36
create the missing indexes on the fly for you, right, and then eventually it can drop those
23:43
indexes when they are not used anymore. So again, you can consider that or rely on that on Azure SQL data
23:52
So definitely automatic query tuning will help us, especially the DBA, to do this kind
24:00
of task and also to achieve what Microsoft is focusing a lot this day is giving us good
24:09
performance, a stable performance without doing code change. You will see that, you know, till here and from now on, right, I haven't mentioned about
24:22
going to store procedure and rewriting it or adjusting it or doing anything
24:28
No, all this works on our existing code without any changes. So how this automatic query tuning works
24:39
Well, basically continuously learns what is going on using the query store repository
24:48
It adapts the workload by manually forcing to the last known good plan
24:56
Then do a verification that the new plan is in fact working better
25:03
and then, you know, repeat, right? Learning, adapt, and verify. So definitely is something that, you know
25:12
in several cases is quite important to use. So we mentioned this before about the plan regressions, right
25:23
And most probably all of us have seen this in the past, right
25:29
Something running with a particular plan and then suddenly plan change, right
25:35
So with automatic plan correction, what we can have here is the possibility
25:41
of going back to a previous plan. We're gonna see it in action in a minute
25:47
Then also in SQL Server 2017, Microsoft has introduced this new feature
25:55
called adaptive query processing, right? And basically, this was the first approach
26:05
to do something that, to me, is really good. Because, you know, we all know that when SQL Server compiles the plan
26:15
it's going to use that plan, right, as an static plan from the cache, right
26:22
And then eventually it's going to change it for any reason, right
26:26
With this feature, Microsoft, on certain circumstances, will adjust the plan providing certain feedback to use it in the proper way
26:42
So definitely, again, without any code change, we can do certain things like adjusting the performance on the fly
26:53
So what was adaptive query processing back in 2017? This was this super set of features that includes batch mode memory grant feedback, batch mode adaptive join and interleave execution
27:12
Right. So basically, again, the concept is that for certain, you know, operations within the engine, it was executing, adjusting, you know, the plan on the fly to get better performance
27:34
And let's gonna focus in a couple of them. For example, memory grant feedback is one that I like
27:41
and I see in action a lot. For example, let's say that you have this process
27:47
that when SQL Server compile it for the very first time, SQL Server granted certain amount of memory
27:56
let's say 10 megabytes. Very good. Then you execute the query and you ended up using one megabyte instead of 10
28:07
Well, you can say, all right, that is not a problem. I have 10 megabyte granted and I'm only using one
28:14
So my world is gonna run okay. But what about if that particular procedure
28:20
is being executed hundreds of times per minute, right? You're gonna be allocating several chunks of 10 megabytes
28:30
and using only 10%. And that's gonna affect not only that particular workload
28:36
but all the overall performance for the instance. And then you have the other possibility
28:44
If SQL server grants 10 megabytes, but then when the subsequent execution
28:56
it's ended up using 20 megabytes, right? It's going to be spilling to 10 dB
29:03
And we all know what's happened when you do the 10 dB. Performance is terrible or goes down
29:10
So this is something that Microsoft now can address with the memory grant feedback
29:19
And then adaptive join. Again, we're going to see now a new operator
29:24
within the execution plans, right? that basically will choose one type of showing or the other
29:33
depending on the branch of each of the showings to see how many rows are returning, right
29:40
So depending the amount of row returning from each of the branch
29:43
it will use one type of showing or the other, right? Then in SQL Server 2019
29:52
Microsoft added a super set of feature, right? and instead of calling adapting
29:59
Query processing, it just call it intelligent query processing, right? Which, if you see the slide, right, the ones that are in regular letters
30:09
are the ones that were coming from 2017. And the ones in both letters are the ones in SQL Server 2019
30:18
Many more than before, right? We also have the memory grant feedback
30:23
but initially in 2017 was only in batch mode. Now it's also available in row mode, right
30:33
This is in SQL Server 2019, right? Again, the one in regular 2017
30:42
the one in both or actually all of them in 2019. We also have the adaptive choice
30:50
the interleaved execution, right? And then we have one that is really nice, actually
30:57
which is ScalarUDF inlining. We all know that SQL Server historically had a bunch of problems
31:05
dealing with Scalar function, right? Because the execution plan was not considered a heavy thing
31:15
But we all have seen a bunch of developers being so creative
31:20
and adding a ton of business logic within this ScalarUDF inlining fund
31:27
So now we're going to see that with intelligent query processing, Microsoft basically goes and checks and compiles the UDF and then put it back into the main execution plan
31:44
And then another feature really nice, which is approximate count distinct, right
31:50
It's basically gives you the possibility if you have a table with several million rows, right
31:58
And you have to do a select count distinct, right? We know that's definitely just gonna be a lengthy operation
32:06
It's gonna use a ton of resources in terms of memory, right? And you're gonna get the exact value of the count distinct
32:13
Now, in 2019, there is this new common called approximate candestine that will use a fraction of this execution time and resources, right
32:28
And it's going to give you an approximate value. Microsoft said that around the 3% of variation
32:36
Of course, this is not useful for a financial workload, but there are certain workloads
32:44
like for example, imagine that you need to fill up one chart in a Power BI report, right
32:53
Having a difference of 2-3% is not going to be a big deal
32:58
And you're going to get the result quite quickly. We will see it later
33:06
So how do we use all these features that now we know that they are there and we want to use them
33:12
Well, basically, the only thing we need to do is change the compatibility level of our user database to 140 if it is 2017 or 150 for 2019
33:26
right? So you know that Microsoft is focusing now on, on, you know, having everything encapsulated
33:34
on a user database. So you can set, you can have a set of features per user database, also including
33:41
database scope configuration in where you can turn on and off several set or settings for a
33:50
particular user database. Also, in terms of performance features, or actually we can use it
34:00
you know, to help us with the monitoring performance, Microsoft has released lightweight
34:07
query profiling, right? Again, let's consider that we have this workload running forever
34:12
and we have to take a decision. It's running. So do we wait till it finish
34:21
What do we do? We don't know what part of the execution is at, right
34:27
So for example, we don't know if it is a 10% is at half of the execution or about to end
34:33
Knowing that we will have better information to take our decision, right
34:41
Well now in SQL Server 2019 Microsoft has released this lightweight query profiling which basically for every single process running on your server right you going to get the estimated query progress So you going to get you going to
35:00
see if it is at 10%, 50%, 90%. So you will see all the execution progress in an estimated way
35:09
right? So knowing that information, you can take better decision. This is, as I said, a feature which
35:15
is enabled by default in SQL Server 2019. Something very good that Microsoft has done is that they port this feature back to 2017
35:28
and 2016 Service Pack 1, right? So if we have 2017 or 2016 Service Pack 1 onward, right, we can use lightweight query profiling
35:41
As per compatibility, right? Microsoft is keeping it disabled by default, right
35:50
So if you want to use it, you have to enable the trace flag 7412, right
35:55
Again, in 2019, it comes enabled by default. And then, you know, the 10DB metadata feature, right
36:05
feature, right? This is SQL Server 2019, which, you know, give us the possibility of creating
36:17
or when we have this feature enabled, all the metadata for the user objects
36:24
that we store on 10DB, right, will go to memory. This is similar to the in-memory OLTP technology, right
36:35
that we have from many SQL server versions now, but for 10 DB
36:41
That doesn't mean that if we create the sharp table, everything's gonna go to memory
36:46
and it's not gonna be touching the 10 DB, right? No, the metadata of those objects will go into memory
36:56
And with that, for certain kind of workload that requires, let's say we have a store procedure
37:02
that we execute multiple times, right? And within the store procedure, we are creating and dropping objects quite often
37:10
in a high grade, right? You know, for those kinds of workload
37:15
this feature is gonna be really useful because it's not, we won't have the latch
37:20
of dropping and creating objects on the 10 dB with all that means, right
37:25
All that is gonna be in memory, so no latches, right? That doesn't mean that we don't have to tune our 10 DB
37:34
with certain amount of files per core, et cetera. All the things that we know on how to tune 10 DB, right
37:43
All that stays there. So demo time, let's see how all this works, right
37:49
Let's see it in action. So here I have a virtual machine running SQL Server 2019, right
38:02
And we will start with a lightweight query profiling, right? So I will have here include live query statistics
38:14
I will execute this. And when we see the plan, right? Note here that at the top we have the estimated query progress, right
38:28
It's in this case 61%. And this is dynamic. It keeps moving
38:34
So let's execute this other one, right? And you will see how this keeps moving, right
38:47
so this one was was quite fast so let's do it one more time
38:58
you see how the estimated query progress is is moving and also we see it here at the bottom right
39:04
so here and here so this is really nice and good but you can tell me this is great but you know
39:17
I don't run my workload from management studio. I have middle tiers, web servers, application servers that are hitting my server constantly
39:30
All right. For that we have let have I have here one batch file which basically creates a random activity on my SQL server instance so let
39:48
mean it's just executing a bunch of stuff so let's minimize it for a minute so then I go
39:55
to management studio I do right click on my server and here we have the activity monitor
40:03
So when I go to activity monitor for the ones that don't know, activity monitor basically gives you a real time monitoring solution from within Management Studio, which give you in real time processor time, weights, IOPS and batch requests per second
40:23
So you can see it in action. It's going to take a second to populate all this for the very first time. And then you can have details of those processes, etc
40:33
the last one is saying active expensive queries so if we expand here we see all these statements
40:42
that are running against my instance right so we see the heaviest one well this is configuring
40:51
azure so it's doing certain backups remember we have here the workload running so in
41:01
one second we're going to see this activity in here all right so let's wait for the next refresh
41:10
okay here we're going to capture one of these and here know that we see show live execution plan
41:31
So when we go here, we see the plan, we see the execution live
41:38
And again, we have the estimated query progress. So we see all the activity coming from my application server, right
41:52
Here, and we see at which point of the execution is at
41:57
So really, really nice. So let's switch to something else. Then we have a query store, right
42:12
Let's see how query store works. First of all, here I drop and recreate
42:21
a new user database, right? I create a new table with three columns
42:28
C1, C2, and C3. Then I create the store procedure, which basically does a select statement on that table
42:41
And then I did an insert of pseudo random values to these tables
42:51
And finally, I just enable query store. right if we do select here i did it in in advance because this takes a little bit but you know we
43:03
have the user database ready so if i do the select i see you know all the data so let's execute that
43:16
store procedure 20 times right so we execute the store procedure 20 times we go to management studio
43:27
we go to the database that we created here um and under query store we can see top resource consuming queries right so when we got here we see
43:46
our only query, right? This is the only query running on my server
43:53
And here we have the execution plan. We can also put it in a grid
43:58
We see that it was executed 20 times with a minimum duration of 2.8
44:04
and a maximum of 4.48, right? Okay, so let's go back here
44:12
And what I will do is I will create a non-cluster index
44:16
on the column C3, right? This is on table that we have
44:21
And I will execute my store procedure another 20 times. When I go back to QueryStore all right Kvair I think if you would like to wrap in another two or three minutes okay
44:47
so here we see that now we have an additional plan right we can run it one more time with a new
44:59
index and also executed 20 times, right? When we see that and we refresh, we'll see that we have now
45:11
an additional plan, right? So we see all the execution, we see the plan change. Eventually
45:17
for any reason, let's say we want to use this plan, we just go here and we force to use
45:24
this particular plan right so to end up i will do the demo of uh out automatic query tuning right
45:35
here i have a demo which basically is um executing uh certain workload in my user database right so
45:49
So it's executing, let me do a quick change here. Scale
46:05
Okay, so we have here batch requests per second, right? And we have a number of around 100, right
46:16
So what I will do here, let me clear this. I will enter a regression, right
46:23
So now my workload has a regression and the, and it is decreasing, right
46:39
So now what we can do is basically go to query store
46:44
and forced to use the last one. But if instead we just go and do this, right
46:56
For my user database, right? I'm gonna be just doing enabling automatic query tuning
47:03
forcing the last known good plan, right? And that's gonna make the job for us
47:11
On the upcoming execution, if it detects the regression, it will manually fix it
47:18
Finally, let me do this one, right? This one, what we're gonna see
47:26
the intelligent query processing, what we have here, I'm gonna do only one example, the memory grant feedback
47:34
I'm creating, I'm changing the compatibility level, creating a store procedure, which is doing a bunch of joints, right
47:42
And what I'm doing here is executing the store procedure for the very first time with one parameter
47:49
parameter eight, right? No rows return it. And when we go to the execution plan
47:56
we see the warning here that we have an excessive grant, right
48:02
When we run it with a different parameter, so it's going to be using the plan that is in cache, right
48:07
we will see that it's taking several seconds, five seconds, and it returns a certain amount of rows
48:18
And if we go to the plan, we see these warnings everywhere, right
48:24
The spelling of 10 dB, grants, et cetera. Let's see what happens if we execute it one more time
48:31
First of all, it took two seconds instead of six. when we go back to the execution plan
48:39
we don't see those warnings anymore. They are gone, right? And this is because the Intelligent Query Processing
48:46
has adjusted the memory grant feedback on the fly for us. So let me go back to..
49:03
presentation. All right. This is a quote from PAS Summit 2019 that we as Mediterranean shipping company has made on these new features, right
49:15
We use them very heavily
#Business & Industrial
#Computers & Electronics