DP-300 Microsoft Azure Database – Perform Administration by Using T-SQL

1. 101. evaluate database health using DMVs – overall resource usage

Hello. And in this section we will be performing administration by using TSQL. And we’re going to be starting with evaluating your database health by using DMVs. So a couple of these we’ve already had a look at. We’ve had a look at Sysdmdb resource stats. So this returns CPU I O and memory consumption for an Azure SQL database or Azure SQL managed instance. Now, there is one raw for every 15 seconds or so, even if there is no activity. And historical information is retained for about an hour. So you can see here from 355 down to 251. So that’s 64 minutes in my particular case. Now, we’ve also got, if you take away the Dmdb Resource Stats which return CPU usage and storage data for each Azure SQL database, the Dmdb version just gives it on.

One related DMVs are the Sys Server Resource Stats, which give you the data for an Azure SQL managed instance and Sys Elastic Pool Resource Stats which do the same for elastic pools within an SQL database server. Next. I want to look at Sysdm. User DB resource Governance So what this does is it gives actual configuration and capacity settings used by resource governance mechanisms in the current database for Elastic Pool. So we’ve got things like DTU limit, CPU Limit, Min and Max, CPU Min and Max Database size in Megabytes and the default and the file graph and many other things. The next thing to have a look at is Sysdmos Job Object.

Now, what is a job object? A Job object is a Windows construct that implements CPU, memory and I O resource governance at the operating system level. So you can see that we got things like the CPU rate, the memory limit in Megabytes, the read and write operation count. So all of that is there. Now, if you have a look at Sysdmio virtual file stats, now this returns IO statistics, input out statistics for data and for log files. You can see if I put in brackets null comma null, it gives it me for all databases and all files. I could specify a database ID and a file ID in the brackets if I so wished. You can see we have got things like number of reads, number bytes read and written. And then finally for this video, Sys DM OS performance counters. And there are an awful lot of these counters. Just look how far it goes down 3700 different roles here.

So if I say select distinct object name and counter name, then that will reduce the number that we’ve got down to 686. So you can see the sort of information that we’ve got here. And notice we’ve got this area for database replicas, database mirroring and databases. So this returns a raw for each performance counter maintained by the server. And so it doesn’t give a huge number of columns, but the fact that you can drill down and see the current value and current type, current value is probably much more interesting, and you can see it for a particular instance. Name can be quite useful if you want to actually find one particular counter.

So these are your DMVs for evaluating database health. There’s Dmdb Resource Stats, so that gives me a row for every 15 seconds and contains information about CPU, I O and memory consumption. And of course, don’t forget you can get rid of the Dmdb to have it for all databases. DM User DB Resource Governance gives me actual configuration and capacity settings for the resource governance mechanisms. DM OS job object. So job objects or window constructs that implement CPU, memory and I O resource Governance. We have DM I O Virtual File Stats, so that is at a data and log file level. And then DM OS performance counters. They return a role per performance counter maintained by the server.

2. 101. evaluate database health using DMVs – other topics

Now another part of database health is waiting on resources and possible blocking. And if we do the same thing as we’ve done previously, we have session one which has a begin transaction and I’m not going to execute the rollback tran. So this is an explicit transaction that hasn’t finished and I’m going to do the same thing here in session two and these two are going to not be able to finish or one is going to block the other. So what we’ve got in Sysdm OS Weight Stats and in Azure SQL database and managed instance, you can also use Sysdmdb Weight Stats is to have a look at information about the weights encountered by threads.

So you can see the various weight types here, the maximum weight time in milliseconds and you can also see that there are an awful lot of different weight types. In fact, if you have a look at the DMOs weight stats you can see that there are over a thousand. So if I’m going to order it in some sort of order, I’ll probably order it by the weight type if I was just having a general lock or by the max weight time milliseconds or the weight time milliseconds if I wanted to see what particularly was causing the biggest problem. Now, the top weight types include the log rate governor. So this is weights for the Azure SQL database. There’s also a pool log weight governor for elastic pools and instance log governor for mi weights.

There’s a series of weights starting RBIO, which are for hyperscale log governance. And then there is HADR throttle log rate, and that’s for business critical and Georeplication latency. So you’ve got all of those. Most of those include Log rate governor. One includes log rate. Then we’ve got weight types for IO, so input output. So we’re looking at page IOR latch. So these are data file IO issues. We’ve got then Page latch. So these are Tempdb IO issues and then near the bottom we have got write log so that is Transaction Log I or issues. Then we’ve got Memory Grant weight performance issues and that is called Resource underscore Semaphore and that is waiting for memory to become available.

And then we’ve got a couple of weight issues caused by parallelism. So first of all we have got CX packet. So the max degree of parallelism may be too high if you’ve got these weights or maybe that indexes need to be created and that then we have got SOS scheduler yield. So I’ve gone down to that. So you can see I’ve got quite a lot of wait time for that. So that’s high CPU utilization may be caused by missing indexes. This is often seen with CX packet weights. Now if you want to see what is blocking then we can have a look at Sys DM exec requests.

So these are active queries. So by session ID and if I scroll along we can see that there is a column called Blocking Session ID. And if I look for all of those which are nonzero, you can see that Session ID 91 here is suspended because it is being blocked by session 93. We’ve had a look at this earlier. There’s also Sysdm OS waiting tasks. So this is based on the tasks as opposed to based on the sessions. And again it has a column Blocking Session ID, and you can see that there is a lock. And again we have the same thing, session ID 91 being blocked by 93.

So if you are waiting on resources and you’ve got possible blocking, then you might want to have a look at the weight type statistics. So we have got Sys DM OS weight stats, but as I said, there are quite a number of them, 1300 in my case. Sys DM weight stats gives you more limited list. And so this may be a better list to start with and then expand into the other list if you need to. If you’re looking for possible blocking, have a look at Sysdm exec requests if you want a list by Session ID and have a look for DM OS waiting tasks. If you want a list by tasks.

3. 102. evaluate server health using DMVs

Now in this video we’re going to have a quick look at DMV’s relating to server health. And first of all, we’ve got Sys Dot databases, so that shows you the databases. You should note that in the Azure SQL database you do not see msdb, Tempdb or model. So here I’ve just got the Master and my DP 300 database. You’ve also got Sys dot objects. So that shows all of your objects, so quite a number of them. And you’ve got various things that run from that, like Sys table, sys views, Syst indexes, that sort of thing, if you want a narrower list.

So this just shows the name, the object ID and the create date, whether it’s published and that sort of thing. Now, if you want to know what vCores you have, then have a look at Sysdm OS schedulers. Now we have got a status here and you can see visible online and hidden online. So if you want to see what you’ve got access to, then have a look at status equals visible online.

So that shows the vote. So you can see that I have got two V codes and you can see current worker count so that’s the requests and total CPU usage and things like that. And then finally there is a server property and engine edition. And this returns a five for Nazi SQL database, an eight for Managed instance and a variety of figures, 1234 if it’s on Prem and Virtual machine. Either of those can return a figure below five.

So these are some of your DMVs that you can use to evaluate your server health. See what you’ve got, what objects, databases, objects and recalls you are using, and another one for this as well as if you want to see what resources you are using generally, then you can do that from Sys Resource Usage. So here we can see if I go into the Master database, my databases and the SKU and the storage in Megabytes, and we can see that over a period of up to 90 days and it provides an hourly report of the resource usage. So these are some DMVs relating to server health.

4. 103. perform database consistency checks by using DBCC

In this video I’ll be talking about one DBCC database console command, and that one is the check DB. So this checks the logical and physical integrity of all objects in a specific database. So you can see, you can run it as is or you can put in brackets the name of the database and it doesn’t require quotation marks. So you’ll see at the bottom the things that you get as part of the output. So it is quite a long list. So let’s break down what DBCC check DB does. Well, first of all, it checks the consistency of disk based allocation structures, so make sure that everything is consistent. So if I run that so this is just a tiny part of check DB. You can see for instance, this table has got one page in it or 1D page and 23 pages used in two dedicated extents. So what it’s doing isn’t making sure everything is allocated correctly. And at the bottom it says number of extensions.

This many used pages is this many and has found how many allocation and consistency errors. Next, we’re looking at check table. However, unlike the others, I can’t just run it as is. I actually have to give a table name and this one does go in quotation marks. So I can check an individual table like this and in a few seconds it gives me the output. Now this DBCC checks the integrity of all pages and structures in a particular table or index view. So this includes whether data pages are correctly linked, indexes are in the correct sort order, every row in a table has a matching raw in a non clustered index and vice versa and is in the correct partition.

Next, we’re looking at check catalog. And what this does is it checks for catalog consistency using an internal database snapshot to provide transaction consistency to perform these checks. So it performs various consistency checks between system metadata tables. It doesn’t run against tempdb and it doesn’t run against any file stream data. So Blobs for instance, on the file system. Finally, checkdb validates the content of every indexed view in the database and link level consistency between the table metadata and file system directories and files.

So how can we use check DB? So yes, you can just run it as is, but you can also run it with zero in brackets. Well that indicates that the current database should be used. Or you can select a particular database and then afterwards you could have a comma and have no index. So that just detects errors. Only it’s got smaller execution time because it doesn’t do intensive checks for non clustered indexes for user tables. Then we’ve got repair rebuild. This only does repairs which has no chance of data loss. It includes quick repairs such as missing roles in nonclassed indexes, and time consuming repairs such as building an index. But for this one repair reboad, you’d need to be in single user mode beforehand.

So before this you would need to say like alter database, name of database, set single user with rollback immediately, then you can do your DBCC check DB and then afterwards you would alter the database again and you would say set multi user. I’m going to have a look at single user multi user in the next video. Now I should actually put go in between all of these as well. So repair fast, don’t use this is for backwards compatibility reasons only, no repair actions are performed and then repair allow data losses. As it says it repairs any found errors but may cause data loss. So Microsoft recommends that you create copies of the database files beforehand.

Additionally you would need to be in single user mode before that. So you’d need to be running this. But in addition, Microsoft recommends that you also set emergency. So this makes the database read as read only. Logging is disabled and access is limited to sysadmins. Now in addition to all of this we also have with after it. So with there’s only one wave, wave DBCC check catalog and that’s with no info messages. So it suppresses all messages. All the of us allow additional waves. So you would have something like DBCC check DB zero for the current database comma whatever you’re going to be using and then wave all error messages displays or reported error messages per objects extended logic checks. This isn’t used in DBCC check alloc, so this performs logical consistency checks on index views, XML indexes and spatial indexes.

We’ve already had to look at no info messages so it doesn’t display informational messages tablock. This speeds it up but it obtains exclusive locks so reduce concurrency. You might find other processors are blocked estimate only, so no database checks are done, but it displays the amount of tempdb space needed to do it physical only. So again, this is not DBCC check a lock. This limits checking to page structure integrity, record header integrity and the consistency of the database. And then with a lot of these things you’ve also got max DOP. So this is the number of processes it overrides the maximum degree of parallelism in SP underscore configure.

So some best practices. Microsoft recommends begin transaction beforehand so that the user can confirm if they want to accept or reject the results, so they can do a commit transaction or rollback transaction afterwards. When you’re repairing errors, Microsoft recommends restoring from a backup instead of doing all of this.

So it says there are other ways of doing this and after using DBCC check DB then you would need to inspect the referential integrity of the database and for that we would use DBCC check constraints. So this checks the integrity of a constraint or all constraints in a table or all constraints in the database. So we have got check DB so that’s the one that does everything. We then have got check alloc which checks the consistency of allocation structures check table, which checks tables and index views and check catalog, which checks for internal consistency.

5. 106. review database configuration options

In this video we’re going to review some database configuration options and we’ve already had a look at a fair number before. So if I right and click on the database, go to Properties and then on the left hand side go down to Options. So you can see the TSQL syntax and you will need to know this syntax for the DP 300 exam. So first of all, we’ve got autoclose so not using in Azure SQL database. It says web, the database is shut down after the last user exists.

Don’t recommend you use it anyway. Create statistics or autocrate statistics. So whether statistics are created on single columns in query predicates, so we’re talking about where so where x equals something. This helps improve query plans and performance. Auto update statistics. We’ve got two of those query optimizer update statistics when they’re used by query and when they might be out of date. After an insert or update or delete or merge operations, change the data distribution. So maybe where something equals one has a million rows, where something equals two has 1000, but maybe you do an insert and that changes.

So do you want to update it and do you want it to be done asynchronously in other words, not at the same time. So you can add after auto update statistics underscore async auto shrink on or off shrinks, where more than 25% of the file contains unused space. It’s recommended to leave that off because the database will have to grow at some point anyway. Now, going down to the bottom, we’ve got Database Read Only. And you can see, you can set it to read underscore right or Read Only. So if users can only read from the database, they can’t modify it.

And we’ve also got whether restricting access to single user, restricted user or multiuser. So in other words one at a time or restricted user, so that’s DB owner, fixed database roles and DB creator and Sysadmin fixed server roles. So that’s not just one user, that’s any number, but only of those categories and then multi user, all users who have appropriate permissions. And you can see that over there. Now, in terms of other forms of Azure SQL, I’m thinking here virtual machines, you can also change the recovery model. You can have full bulk logged or simple.

So Full uses transaction log backups. Bulk logged only uses that for most transactions, but minimally logs, certain large scale, also known as bulk operations. So if you are inserting a billion rows and you’ve got bulk logged, then it wouldn’t keep track of all those million or billion rows, whereas Full would. Simple only allows for complete full backups. So you want to be able to do differential backups or transaction log backups.

However, Recovery Simple is used for small databases and then right at the top we have the compatibility level and this can be set in Azure SQL database Mi as well as the VM and so you can see the compatibility level goes from 100 to 150 from SQL Server 2008 to 2019. Only set it at a previous compatibility level if you’ve got an actual need to.

You can see here set compatibility underscore level equals and it’s the number in the brackets, not the number of the server. So in this video we’ve had to look at how we can review database configuration options. So they all have alter database name of database set and then whatever the configuration is. And then quite a few of them have got on or off.