Lets just get straight to the point, Azure SQL Database across all service tiers gives you the customer a SLA of 99.99% up-time. This means potential unavailability periods shown below.
Using a query label in Azure SQL DW (Data Warehouse) can be a really handy technique to track queries via DMVs. You might want to do this to see what problematic queries are doing under the covers.
Let’s check out an example. First I will show you how things would look without using a query label. I connect to SQL DW and issue the following basic example query.
SELECT sum(p.StandardCost),ps.[EnglishProductSubcategoryName] FROM [dbo].[DimProduct] p JOIN [dbo].[DimProductSubcategory] ps on P.ProductSubcategoryKey = ps.ProductSubcategoryKey GROUP BY p.standardCost, ps.[EnglishProductSubcategoryName] ORDER BY ps.[EnglishProductSubcategoryName]
So what query would you use to see what is going on within this MPP environment? You would normally use sys.dm_pdw_exec_requests and/or sys.dm_pdw_request_steps. Something like:
SELECT r.session_id, r.start_time, r.end_time,rs.location_type, rs.status,rs.command,rs.step_index, rs.operation_type,rs.request_id FROM sys.dm_pdw_exec_requests r JOIN sys.dm_pdw_request_steps rs ON r.request_id = rs.request_id
However you probably would not want to manually go through the output to try and piece the output together, this is where labels are handy. The original code now becomes:
SELECT sum(p.StandardCost),ps.[EnglishProductSubcategoryName] FROM [dbo].[DimProduct] p JOIN [dbo].[DimProductSubcategory] ps on P.ProductSubcategoryKey = ps.ProductSubcategoryKey GROUP BY p.standardCost, ps.[EnglishProductSubcategoryName] ORDER BY ps.[EnglishProductSubcategoryName] OPTION (LABEL = 'QUERY: MySalesCodeSum');
We then use this LABEL within the DMV based query.
SELECT r.session_id, r.start_time, r.end_time,rs.location_type, rs.status,rs.command,rs.step_index, rs.operation_type,rs.request_id FROM sys.dm_pdw_exec_requests r JOIN sys.dm_pdw_request_steps rs ON r.request_id = rs.request_id WHERE r.[label] = 'QUERY: MySalesCodeSum' ORDER BY step_index
Now you will have the output relevant to the original query only.
High level SQL DW is creating temp tables such as:
CREATE TABLE [tempdb].[dbo].[TEMP_ID_16] ([ProductSubcategoryKey] INT, [StandardCost] MONEY ) WITH(DATA_COMPRESSION=PAGE);
DMS does the famous ShuffleMoveOperation then the compute node actually ends up running:
SELECT [T1_1].[col] AS [col], [T1_1].[EnglishProductSubcategoryName] AS [EnglishProductSubcategoryName] FROM (SELECT SUM([T2_2].[StandardCost]) AS [col], [T2_1].[EnglishProductSubcategoryName] AS [EnglishProductSubcategoryName] FROM [tempdb].[dbo].[TEMP_ID_15] AS T2_1 INNER JOIN [tempdb].[dbo].[TEMP_ID_16] AS T2_2 ON ([T2_1].[ProductSubcategoryKey] = [T2_2].[ProductSubcategoryKey]) GROUP BY [T2_2].[StandardCost], [T2_1].[EnglishProductSubcategoryName]) AS T1_1 ORDER BY [T1_1].[EnglishProductSubcategoryName] ASC
Quite cool right?
Just because the cloud movement is strong doesn’t mean the end of “DBA’s”, it does mean a change in skills and no doubt you will (one day) create Azure SQL Data Warehouse (DW) in Azure. If you are from an operational background like me then backups will be on your mind for this product. The question is how are backups done with Azure SQL DW?
The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked?
PsPing tool is part of the sysinternals PsTools download found – ( https://docs.microsoft.com/en-us/sysinternals/downloads/psping)
This is the tool of choice when wanting to find out latency to your Azure SQL Server. In addition to standard ICMP ping functionality, it can report the latency of connecting to TCP ports, the latency of TCP round-trip communication.
Forget about Adaptive Query Processing for a minute, what other feature have I been waiting for? SELECT INTO a specific filegroup, not the default filegroup! I have needed this feature many times in the past. Let’s take a look at it using the WideWorldImportersDW database.
I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code:
Last year I wrote about Azure SQL Database extended events (https://blobeater.blog/2017/02/06/using-extended-events-in-azure/) and gave an example where I was capturing deadlocks via the ring buffer. Ever since then I wanted to do a follow-up post but using Azure storage as the target for my XEL files.
This is more complicated than using the ring buffer as the target and requires a couple of things:
- Azure storage account where you create a dedicated container for the files.
- SAS key.
- Database master key.
- Database scoped credential.
Creating copies of your Azure SQL Database is a common and relatively simple process. You can issue a TSQL statement on the master database such as:
CREATE DATABASE CodeDBcopy AS COPY OF CodeDB
Initially, the main focus of this post was going to be quick and about using the latest version of SSMS (SQL Server Management Studio) to check out execution plans for Azure SQL Data warehouse (DW). I got a little side tracked by a certain operation called – SHUFFLE, because, I like the name.