Gathering Data About Your v12 Azure SQL Database

One of the beautiful things about SQL Server has been the ability to more or less install it and let it run. Well, it’s a beauty until it becomes abject horror. Azure SQL Database takes the beautiful aspects of this and expands on them some since you no longer have to worry so much about the server you’re running on. You do still need to worry about what you’re doing to your database and how it’s interacting with the system. The good news, most of the tools you’re used to working with are available within Azure SQL Database, with a few exceptions.

Dynamic Management Views

I won’t spent much time on this other than to bring it up. You can go and look at one of my previous articles where I outline all the various information available through DMVs within an Azure SQL Database. I just want bring them to your attention again because they are such a vital part of understanding what your system is doing and how it is working. Nothing changes within Azure SQL Database, the DMVs are just as vital.

DBCC

There have been a large number of additions to the abilities and functionality exposed through the DBCC commands with the release of v12. In v11 and earlier, because it was a platform as a service, it didn’t seem like there was much need for very many of these commands, but over time, as you were troubleshooting a performance problem or just worried about the current status of your database, you, well, I, wished I had some of the control that I was used to in the earthed version of SQL Server.

There were a lot of the standard commands that worked just fine. For example, we’ve always been able to run DBCC SHOW_STATISTICS because that deals directly with tables and structures within a database and doesn’t relate to the database or the server that it’s sitting on. Because remember, prior to v12, while you were working with a database, your primary control was around the objects within the database, not the database itself. So that meant that really interesting stuff that you might want to know about your system like, how much of the transaction log are you using, available through DBCC SQLPERF would just return an error:

Msg 40518, Level 16, State 1, Line 3DBCC command 'SQLPERF' is not supported in this version of SQL Server.

I’m sure you won’t be shocked to discover that this was a pain the bottom sometimes. Now, I can run this command:

DBCC SQLPERF(LOGSPACE);

And the results come back:

No, I don’t use a GUID for my database names generally. That’s the underlying structure within the SQL Server instance on which my databases reside. There is no way to convert this. It’s actually an artifact of the service tier I have chosen. Since I’m not on the Premium tier, I share a server with others. If I was on the Premium tier, I would see my databases as actual file names here. If I did want to know which of these files was associated with my database, I could shift to the Premium tier and then shift back. Yeah, I know. I said this stuff was functional. I never said you’re going to love all of it.

It’s probably worth noting, you can’t reset your OS wait statistics using this command like you can with the earthed product.

You also have the capability to run full blown consistency checks on your databases. Just as with the other DBCC command, SQLPERF, if you attempt to run CHECKDB, you’ll receive an error. However, if I run this while on a v12 database:

DBCC CHECKDB();

Then I get a full output on the current state of my database. You really shouldn’t need to do this. As part of the Platform as a Service (PaaS) offering of Azure SQL Database, Microsoft is regularly running consistency checks for you. Regardless, the output should look fairly familiar:

There are other DBCC commands that are vital within a database. For example, DBCC SHOW_STATISTICS. You can run this command:

DBCC SHOW_STATISTICS('Person.Address','PK_Address_AddressID');

The output is perfectly ordinary information about the statistics of the table:

This information includes the header, the density graph and the histogram. After all, Azure SQL Database is just SQL Server, so you have to be able to run commands that you truly need for maintaining and monitoring the status of the objects within your database.

It’s always a good idea to take a look at the documented T-SQL support for Azure SQL Database. It’s to the point where they don’t list what’s supported, rather what isn’t. And there are a few things that you might miss.

Unsupported Statements

Because it is a platform as a service, some functions won’t be available. For example, let’s say you’re doing some testing and you want to clear out the buffer cache so you can see how the query you’re working on behaves. You’d normally do this:

DBCC DROPCLEANBUFFERS();

Even on a v12 Azure SQL Database, this will be your reward:

Msg 40518, Level 16, State 1, Line 13DBCC command 'DROPCLEANBUFFERS' is not supported in this version of SQL Server.

I’m actually OK with that. I usually don’t worry overly much about the amount of time to load things into cache or compile plans for most data loads. But, speaking of compiling plans, what happens if you’re dealing with some bad parameter sniffing and you decide to run a pretty standard query like this:

DBCC FREEPROCCACHE();

You’re going to another error message:

Msg 2571, Level 14, State 9, Line 14User 'dbo' does not have permission to run DBCC freeproccache.

Which means, you can’t even do the correct thing and run a query like this:

DECLARE @PlanHandle VARBINARY(64)SELECT @PlanHandle = deqs.plan_handleFROM sys.dm_exec_query_stats AS deqsCROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS destWHERE dest.text LIKE 'CREATE PROCEDURE dbo.MergeSalesOrderDetail%'IF @PlanHandle IS NOT NULL    BEGIN    DBCC FREEPROCCACHE(@PlanHandle);    ENDGO

That’s one small issue that I do have with all the tools available to me. I’d really like to be able to pull plans out of cache as needed.

Summary

You have a vast amount of information and control at your fingertips thanks to the v12 upgrade. Yeah, some of it is less than perfect in every way. For the most part, everything you’ll need to manage a database and the objects within that database will be exposed to you. The Platform as a Service model means that most, if not all, of the standard pieces of information about the server you would normally collect, you just won’t need when working in Azure SQL Database.