DP-300 Microsoft Azure Database – Identify performance-related issues Part 2

3. 49. identify sessions that cause blocking

In this video, we’re going to have a look at blocking. What is blocking? Well, here we have an update statement. So it’s going to update this table. So where the city is equal to Toronto, it’s then going to say, well, make the city equal to Toronto or N. Now, if I run this, there is implied an implicit begin transaction and a commit transaction. So this is a transaction. And the reason why this is implied is because, let’s say there were a billion rows and it’s got to update a million rows and the power goes off after 500,000 rows. What happens when the power goes back on where the computer is going to go, well, I’ve not been able to fulfill this previous transaction, so I’m going to roll it back.

So that’s what happens when a transaction fails. So we don’t have an in between state where those 500,000 roles have actually been written and another 500,000 have not been. We’re going to say, okay, you have a commit in its entirety or you roll back in its entirety. Now that is called implicit transactions. Explicit transactions are where you actually write begin transaction and then at the end roll back or commit. So I’m going to run this and if I just do a select afterwards, you’ll see that where city used to be, Toronto, it’s now Toronto or N.

Now, this happens in one session, session number 67, session number 69. I’m going to do the reverse. I am going to be looking at sales address and where the city is, toronto or N. All toronto. I’m going to say that the cities are going to be equal to Toronto. So again, it’s got an explicit transaction, so there’s no end and I execute. And here you will not see the result because it can’t get access to this table, because this session has got it. And that is what’s called blocking. So it’s when session one locks a resource and there are all different types of locks. There are roll locks. So locks at an individual role level, a page lock, so that’s 8192 characters or an entire table, and those locks can be escalated.

So session one locks a resource and then session two requests that resource. So blocking is caused by poor transactional design or long running transactions. Now, to view the locks, I’m going to go into a third session and we’ll have a look at select staff from Sys Dmtranlocks. So this returns information about currently active Lock manager resources in SQL Server. And you can see we have got quite a few resources, keys, objects, pages and databases with some sort of log. And as I say, there are varying different types of log. We have got here s for select, x for exclusive, and IX for Intend exclusive. U means update. So we can see these different columns here.

So, as I say, returns information about currently active lock sessions. You can see the session ID which is locking a particular thing. And then to view locking, we kind of look at DM exec requests. Now, we’ve already had a look at this before. It returns information about each request that is executing in SQL Server. So you can see here that session 69 is being blocked by session 67. So this is where we say where blocking session ID is greater than zero, so its status is suspended.

The command is an update. And this table gives a lot more information about each request which is executing SQL Server. But those are the important ones relating to blocking. So in this video, we’ve identified what blocking is. It’s when session one locks a resource and session two requests that resource. So how does this actually end? Well, it ends with one of them saying, I give up. And that is called the deadlock victim.

And so in this case, it will probably be session 69. Or of course, I could just type into here commit transaction that would release all of the blocks for this session. Then session 69 could continue and it would still be in the middle of the transaction. So it will be blocking anything else. In the next video, we’ll look at how we can reduce blocking.

4. Isolation Levels

In the previous video, we had a look at what sessions could cause blocking. So we got session One, who is hogging a resource, who is taking it and not allowing anybody else to have access to it. Which is a shame because session two needs it. Now, there are some things that we can do to help. And suppose I was to say, okay, I am fine with a certain an amount of rough and ready data and I can do that for a particular session using Set Transaction Isolation level. Now, there are lots of different versions of this. So there is the read uncommitted version. Now, that completely ignores the blocking. But there is a problem with this. Let’s say a particular role was being updated.

So this row here, if I ignore all the blockings, then I may have something called a dirty read. So what’s a dirty read. It means I’ll be reading data that has not necessarily been committed, so it may be rolled back. And then if I do the same read again, I would have something different. Now, the alternative, or one of the alternatives is Read Committed. So there’s no dirty reads because you’re not going to be reading statements that have been modified but not committed. However, there is a bit of a question mark as to whether it would block.

There is another thing that we can have a look at called Alta Database. So Alter Database, name of database set Read Committed snapshot to on or off. So if it is off, which is the default on SQL Server, then using Read Committed may block in itself. So if I was doing anything with Read Committed that could block. However, if it is on, then DML statements start generating raw versions. So this is first role version, second role version, third role version, so that if you do something on Read Committed it does not block.

Now, if you want something that does block, then you can have a look at a repeatable read. So what happens there is that you are guaranteed that if you do exactly the same read in the same transaction, you’ll get exactly the same data. And for that reason it does block any updates and that sort of thing. There is also a snapshot. So here the data. Read remains the same until the end of the transaction, but there aren’t any blocks. Now, this is only going to be the case if firstly, the database is not in a recovery state. So in other words, you’re not restoring it from a backup. And secondly, if you have another Alter database here, you’d be setting Allow Snapshot Isolation to be on.

So you have to have allow snapshot isolation first before you can use snapshot. Finally, there’s another one called Serializable, so no dirty reads. So it can’t read statements that have been modified but not committed. However, it does block updates and inserts but not reads. Now, to see what you’re currently using. You can use the DBCC command user options. If I run this, then we can see that the isolation level currently is Read Committed Snapshot.

So we’ve got these five different isolation levels. You might be going, okay, what’s the difference between Repeatable Read and Snapshot if I always get the same results each time? One blocks, one doesn’t block. Well, repeatable reads allow you to read the same data each time. However, suppose somebody inserts new data into what you’re reading. Repeatable read will read that new data, whereas Snapshot won’t.

So you’ll get an expanded version. So let’s just have a look at this in summary, Read Uncommitted allows for dirty reads. It’s not necessarily going to be repeatable reads, and you might get different results each time. And you may get phantom data so that’s data that is inserted while you’re having a transaction Read Committed, you won’t get any dirty reads. You will not read statements that have been modified but not Committed. It may block depending on what Read Committed Snapshot is set to, whether it’s off or on. The default for your SQL database is on and therefore doesn’t block. Repeatable Read is a repeatable read, so it may have phantom data, some new data coming in, but for the existing roles, you get the same results each time and then snapshot the data.

Read remains the same until the end of the transaction. No blocks. Serializable is even more restrictive. You will get the same each and every time until the current transaction completes. And other transactions can’t insert new roles that would fall in what you are currently reading. So for all of these, you just use Set Transaction Isolation level and you put what you want afterwards. And if you’re using Snapshots, then you need to put in Allow Snapshot Isolation. If you’re using Read Committed, then you should know that there is a difference in blocking. If you’ve got Read Committed Snapshot beforehand, on or off, the default is on for your SQL Database and off for SQL Server on premises or virtual machines. And so read committed. May block. And that is how you can reduce blocking.

5. 51. assess performance-related database configuration parameters

In this video we’re going to have a look at performance related, database configuration parameters and database scoped configurations. So we can get these by right and clicking on a database and going to Properties and then on the left hand side going to Options. So autoclose closes. If you’ve got no connections, you cannot enable that in Azure SQL database. Autocross create statistics well, this allows you to generate information about the contents of each column, which is very good for the query optimizer. So it can be useful for deciding whether to use a scan or seek the incremental one. That goes even deeper. And you can see it is off to start with.

While this instead of using Autocrates statistics, you would use auto shrink on and off. It’s set to off to begin with. It’s not recommended. While it is less impactful to database performance to auto shrink, it’s also less effective and then what happens when your database needs to grow again. So I would personally leave all of these alone.

Now, the database scoped configurations, some of these you can see in this box, some of them are not there and there’s actually quite a lot of them. The syntax is alter database scoped configuration, set whatever you’re setting equals on or off. We’ve also got this for secondary here. So that is there if the secondary is georeplicated. So if you have secondary databases. So we’re going to start off with global temporary table auto Drop so this drops global temporary tables. We’re not in use by any session.

So if you’ve created a global temporary table, if you haven’t, it has no effect because you’re not using it. You set it in individual databases in Azure SQL Database and in Tempdb in managed instance and on virtual machines. Last query Plan Stats this enables or disables the actual execution plans being shown in Sys DM exec query Plan Stats legacy Cardinality Estimation which is here. As you can see the query optimizer Cardinality Estimation model. So we’re talking about if a particular value has value, one has got a million rows. If it’s a two, then there are 100 rows in a particular table. So that’s the Cardinality estimation model. It changed in SQL 2014.

This should only be switched on for compatibility purposes, so you’re likely not going to be using it. Max Dot this is interquery parallelism, the maximum number of parallel threads. So if I am getting things from a table, it may be that I don’t have just one read of the table, I have four, and then the computer puts them all together at the end. Having parallel threads should increase query speed.

However, having too high a max stop may cause performance problems when executing multiple queries at the same time, because it could save new queries of resources. If this happens, you can reduce the max top. The default for new Azure SQL databases is eight, which to be honest is best of most typical workloads optimized for ad hoc workloads.

This still is a compiled plan stub. When a batch is compiled for the first time, it has a smaller memory footprint. It’s only when it’s compiled or executed a second time that this stud will be replaced with a full compiled plan. So this is why it’s ad hoc workloads. When it’s just done the once, then there will be a smaller memory footprint for the compiled plan stub. If it happens a second time, it’s no longer ad hoc.

Parameter sniffing that you can see over here. This evaluates store procedures to create an execution plan on subsequent runnings. The computer uses the same execution plan, no need to spend time and CPU evaluating, but it may be suboptimal for certain parameters supports. I have a still procedure and I use a parameter number one. And again, in my example there are a million roles.

Then the execution plan is going to say, okay, there are a million roles, let’s create an execution plan based on that. However, if I run the store procedure a second time and it only has 100 roles because I’m using a parameter number two, then it’s going to be a sub optimal for that particular running.

So this really is only an impact when the Cardinality is quite different for different parameters. So parameters sniffing is there to counteract that problem and Query Optimizer Hot fixes. Now if this is switched on, then regardless of the compatibility level, this enables or disables Query Optimization Hot Fixes that come along. So you could have a compatibility level for SQL Server 2012, but you could have Query Optimization Hot Fixes that were released after this version. Do you want to use them or not?

So this is really only an issue if you choose to change the compatibility level from 2019 or the latest version to something earlier. Now there are many more options that you can see here in the miscellaneous, but these are the main things that you should look for with database scoped configurations and performance related database configuration parameters. These are Auto Create Statistics auto Shrink global Temporary Table, auto Drop, last Query Plan Stats legacy, cardinality Estimation Maxdop optimized for Ad Hoc Workloads, Parameter Sniffing and Query Optimizer Hot Fixes and if you want to review what all of these are, then make sure you use my resources which are near the beginning of this course.

6. 57. configure Intelligent Query Processing (IQP)

Now this video really continues from the previous video when we’re talking about data scopes configurations, because in this video I want to talk about Intelligent Query Processing or IQP. IQP is a suite of new features, improving performance, and really you can see that they are database scope configurations, what we had in the previous video. Now it is supported in Azure SQL Database azure SQL managed instance for compatibility level 150. So if we go on to a database right and click go to Properties, then at the top of the Options page you’ll see the various compatibility levels. Now for SQL Server VM you will need SQL Server 2019 and level 150.

Now, there are seven features in the IQP, some of which are available in a lower level. So some of them are available in SQL Server 2017 and compatibility level 140 or above. Now, I just want to point out the difference between database wide configuration options and server wide. We can see the Server wide here and you can see various things that you can do and you can see the name here and the bigger description here looking at Sys configurations. Now, if you wanted to change any of these in Azure SQL managed instance or Virtual Machine, but not Azure SQL Database, you could use Execute SP underscore Configure and what you are Configuring.

But that does not work in Azure SQL Server. Even in the master database. So the database wide configurations you can see in Systolt database scoped configurations and you can see ones that we were talking about in the previous video, while the IQP ones are sort of interlaced into those. For example, Batch Mode Adaptive joins in the middle and we’ve seen previously optimizer ad hoc workloads. So it’s not one particular section, it’s just individual elements. Now you can disable any of them apart from one of them approx count distinct for all queries in a single database by saying Alter Database Sculpt configuration set and then in brackets the name of the configuration equals on or off. Alternatively, if you just want to do it for one particular query, then you would use an option hint. So select whatever your select statement is and at the end option use Hint and in brackets and in quotation marks the hint. So in this case, disallow batch mode.

So for the rest of the video, I’m going to just take you through these intelligent query processing options and just describe them. So for the exam you need to be able to identify roughly what they do. So the first one is adaptive, joins in batch mod. As you can see from the table, this was first introduced in SQL Server 2017 and you need compatibility level 140 or above. So you need a column store index in the query or a table being referenced in the Join or Batch mode enabled for Rawstar.

And you can see we have a Batch mod on for Rawstar later down so what happens is it selects the join type of the hash join or nested loop join during runtime based on actual input roles. When it’s just scanned the first input it defines a threshold where the small number of roles makes a nested loop join better than a hash join and then decides whether to switch to a nested loop plan. So that is an adaptive join.

So it’s neither one nor the other until it is scanned the first input and on the right hand side you can see what you need. So alter database scope configuration set batch mode adaptive joins equals on and here it is in the option use hint and also where available in SQL Server 2017 as the names changed in 2019. So option use Hint and in brackets in quotation marks disable batch Mode Adaptive Joins so I won’t be paying any more attention to these last three columns. So most of them were introduced in 2019, there’s just a couple more which were introduced in 2017. And Azure Compatibility level 140 approximate count description is an aggregation.

So in other words, you’ve got count distinct and that tells you how many distinct values there are in a particular column. And that’s fine if you got 1000 rows or million rows, but if you got a billion rows, that could take a while to compute. So approx count distinct with underscores between them, that provides an approximate for big data, it decreases memory and performance requirements and it guarantees up to a 2% error rate within a 97% probability.

So you use it where absolute precision is not important but responsiveness is. Next batch. Mode on raw store. This is for data warehouse workloads. This means that queries can work on batches of rows instead of one row at a time when cached.

Now, this happens automatically when the query plan decides it’s appropriate, no changes required. Interleaved Execution which we notice is also called Interleaved Execution TVF. So it uses the cardinality, the actual cardinality of a multistatement table valued function. So TVF on first compilation so it uses the actual cardinality of a multistatement TVF on first compilation rather than a guess. And that guess was 100 rows from SQL Server 2014 and you can imagine 100 rows is probably not the right number for your standard select query. So it uses it when statements are read only, so it doesn’t use it during inserts updates or deletes. Next we have batch mode memory grant feedback and there’s two versions, a batch mod and a raw mod. SQL Server looks at how much memory is allocated to a cached query and then when it runs a second time, allocates the same amount of memory.

So the alternative is, okay, we get this query, I have to guess how much memory I need for it. If I’m right. Great. But if I’ve allocated too much, well, that’s memory wasted. If I’ve allocated too little, then I’ve got to increase and then increase and then increase. So if a query spills to disk, it can add more memory for consecutive executions and if it figures out it’s wasted 50% or more of the memory, then it reduces the memory for the next execution. TSQL scalar UDF in Lining so this is Scalar UDS user defined functions. They often perform badly because they might run multiple times once per row. So you’ve got select star from and the name of a function and it’s joined to another table and therefore it has to call that function once per this other table. So it runs multiple times once per raw. You’re unable to actually work out the performance cost. You’re able to optimize more than one select statement at a time and you don’t have any parallelism in queries which involve UDS.

So scalar UDS are transformed into the equivalent relational expressions. So it’s taken out of the function put into the select query inlined into the query. So it means it’s actually part of the query and this often results in performance gains. Now, it doesn’t work with all UDS, including those which have multiple return statements.

So it works with UDS which have one in, one out. Now, it can be disabled for a specific UDF if you write in that UDF with inline equals off as begin and then you continue. And then finally we’ve got table variable or TV deferred compilation. So this is similar to the interleaved execution TVF but TVF is for table valued functions, whereas these are just for table variables. Again, it does the same thing. It uses the actual cardinality of the table variable encountered first time on the first compilation instead of using a fixed guess.

And you remember the fixed guess for the TVFS were 100 rows. Well, the fixed guess was just one row, so you can see how much that would be off. So these are the intelligent query processing or IQP. So you’ve got adaptive joins on batch mode. Is it a hash join? Is it a nester join? We’ll wait until we scan the first input approximate count distinct.

So that is a new aggregation. That’s the only one you can’t disable. But if you don’t want to use it, don’t use the approx count distinct formula. So it does account distinct but gives you an approximate figure. It’s better performance batch more than row store. So working on batches of rows instead of one row interleaved execution and these are for table valued functions. It looks at the actual cardinality rather than a fixed guess memory grant feedback.

So how much memory did we used last time? Let’s use the same amount this time. Scalar UDF in Lining it takes your user defined functions and puts them into your queries for better performance and table variable deferred compilation. Same thing as the Interlease execution but for table variables. And to use all of those you use alter database scoped configuration set equals on that changes it for the entire database. Or if you want to do it for just one particular query, then you can use an option. Hint.

• Category: Uncategorized