DP-300 Microsoft Azure Database – Implement performance-related maintenance tasks Part 2

4. 47. manage storage capacity

In this video, we’re going to have a look at how we can manage storage capacity. So the following may apply just to Azure SQL database, by the way, and not as your SQL managed instance. So why might you want more storage capacity? Or you might need to just add more space, or even decrease the maximum capacity to a database elastic pool. Don’t forget, the more that you got allocated, higher the potential cost. Or you might want to change to a different service tier.

Now, there’s some terminology we need to look at. Data space used generally increases with inserts and decreases with deletes, but it is dependent on fragmentation, whether that happens. Now, in contrast to Wave data space used, that’s the amount actually used. You have a data space allocated, so that is the amount which is made available for your data. Now, this can grow automatically. Let’s say you add a billion rows, then the amount used increases, but the amount allocated also increases.

However, if I delete a billion rows, the amount of data space allocated doesn’t decrease automatically after these deletes. So there’s a bit in the middle. So difference between data space allocated and dataspace used is data space allocated but unused. So this can be reclaimed when data files are shrinked. And then there is a data maximum size, so that’s the maximum that data space allocated could be. So let’s have a look at how we can display the allocated space. So, first of all, I need to be in the Master database for this. So let’s change it to Master.

So we can see here that we’ve got Sys resource stats. And if I run that, you can see that I have got 32 megabytes. There’s more columns in this particular table which we haven’t looked at before, but you can see we’ve got 32 megabytes allocated storage. If this was an elastic pool, then I would use Sys elastic pool resource stats. Again, we’ve had a look at that before. Now, if we want to display the maximum size. So here we’re displaying it for DP 300 use database property ex. So this gives the maximum size and database property x. This gives you a property. So in this case, max size in bytes. So I need to change from Master. If I do it in Master, we’ll get the answer null. So if we go to another database, we get this ridiculously big answer.

It just means unlimited. If you want to view the current log size, we can do that using this sys database files. Type one is a log file, so you can see that there is one log file. If I run this without the type one, you can see one log file. We’ve also got things called roles and file stream. Now, I can shrink the transaction log file, which in this case is file ID two. So I would run DBCC shrink file and in brackets, two DBCC, by the way, stands for database console commands. So that will shrink one individual file. So now you can see the result. So this file, this log file occupies 3072 pages pages, 8192 characters, or 8 KB. This is the minimum size number of use pages, or the number of eight kilobyte pages, and the estimated size that it could be shrunk down to. Now I can specify how small I wanted to go, so I could say I want it to go down to five megabytes. So if I want to do that, I’ll put to comma five.

I could also write empty file if I wish to actually empty it. There’s other settings like no Truncate, which moves allocated pages to the front of the file, and truncate only, which releases all free space but doesn’t perform any page movement. But in reality, I would just use the BCC shrink file and then the number. Now it can take a bit of a while to do it, depending on how big the size is. So shrink operation is probably most effective when you’ve actually got a lot of unused space. Like for instance, you’ve dropped a table or you’ve truncated a table.

That means removing all of the data. I wouldn’t do it that often, to be honest. If you want to shrink an entire database, you can also do that with shrink database and then in brackets, the name of the database. Note that the name of the database is not in quote marks. It’s just in this case, DP 300. So this will go through all of the files. We know that there are three of them potentially and will shrink them. However, I wouldn’t do it that often because files need room to grow, and if you’re shrinking everything down, then they’ll just have to grow, and that takes time. So this is how we can manage storage capacity. In the next video, we’ll look at how we can assess the growth of databases and logs and report on database free space.

5. 50, 105. assess growth/fragmentation and report on database free space

In this video we’re going to have a look at the growth of databases and logs and reports on database free space. So to assess growth in a database, we can use Sys Resource Stats. So again, this is something we keep bumping into. So here we can see the start time database name and the storage in Megabytes. Now you need to be in the master database for this within as your SQL database. If you don’t, then you’ll get an invalid object name. For an elastic pool. You would use Sys elastic pool. Resource stats. Now to display the amount of database free space and the amount used, then we can have a look at SP underscore Space Used. So you can see that this database has a database size of 40 megabytes with unallocated space of eight megabytes. If I change it to the DP 300 database, then you can see that there are different statistics. Now, I can display this by file.

So if I use Sys database underscore file, we had a look at that in the previous video. So again, here’s our log file and you can see the current size. These again are measured in pages, each of which are 8 KB. So you have to multiply this by eight to get the number of kilobytes and a max size. So the maximum that is allowed if you want to view the number of pages that have been used, as well as the total free space for a particular database, you can use Sys Dmdb file space usage.

So you can see that we’ve allocated 1808 pages. We’ve got 240 which have not been used, which are unallocated. Now, you can also use the DBCC command SQL Perf and brackets logspace. However, this only reports on transaction logspace statistics, but you can see the percentage of the logspace that is used there.

Now, this is not valid for Azure SQL database, but if I were to go to a VM, so I’m just going to go to my local machine and have a look at a database and right and click and go to reports, standard reports. You can see that we’ve got disk usage. So there I could see more information about my database in a graphical form together with disk space used by data files. However, that’s not able to be used in Azure SQL database. If I tried to do that, then that option is simply not there. Now for the Tempt DB database, then you can use these two, and I’m going to play both of those. So first of all, we’ve got Sys Dmdbsession space usage.

So this shows the number of pages, and we’re using the word internal to represent the Tempdb database number pages allocated and de allocated by each session. And then we’ve got a similar one here for Sys Dmdb taskspace usage. Again, we’re using internal, whereas the previous one uses internal pages for each session. This one uses internal pages allocated and de allocated for each task. So those are two that can be used wave, Temp, DB. But have a look at the internal bit on the right hand side and here are the columns. So it is these two columns which you would be of most interest in looking at tempdb. So in this video we’ve had a look at how to report on database free space.

You can use Exec SP underscore space used. You can use Sys database files to display it by file. You can use Sys Dmdb filespace usage which results in this. So it shows space usage for each data file in a database shown in pages 8. Can also use dbccsql perf brackets log space. And for Tempdb you can also use Sys Dmdb session or task space usage to assess growth in the database. You can use our old friend sys resource stats or Sys elastic pool resource stats.

• Category: Uncategorized