Azure SQL DB VS SQL Server For the DBA

Being the start of the new year and with new projects most likely starting again I would like to share with you an article I wrote a while back, but still quite relevant.

Hopefully you find it useful for those wanting to know key differences between Azure SQL Database and a locally installed SQL Server from a DBA perspective and whether or not these key differences can be seen as advantages or disadvantages.

Continue reading

Advertisements

SQL Server Backup To Azure Storage

Once again I would like to share with you an article I wrote about backing up (and restoring) a SQL Server database to Azure Blob Storage. In this article I write about some important concepts and show you the code needed to do this. This includes all the lovely components such as SQL Server credentials, secrets and SAS etc.

Continue reading

Azure SQL Database DTU Versus vCore

I wrote a guest article for an IT consultancy on Azure SQL Database, more specifically DTUs and vCore options. At a high level I discuss what both options are, the differences and what you should select. Hopefully you will find it useful. Please see the link below:

Continue reading

SQL Server I/O

Sometimes I like to see and know what SQL server is doing under certain operations and recently I wrote an extended events script to see what sort of I/O patterns my query was doing (This is a fun post). The important event here is sqlserver.file_read_completed.

I clear cache and let the fun begin.

Continue reading

Azure SQL DW – Query Labels

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

Output:

whatiswhat
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.
justmylabel

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?

 

Azure SQL Data Warehouse and Backups

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?

Continue reading

SQL Server 2017 – SELECT INTO [A SPECIFIC FILEGROUP]

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.

Continue reading