Restoring Azure SQL Datawarehouse

Microsoft recently added snapshots to the Azure SQL datawarehouse recovery arsenal. What does it do? A snapshot is like a point-in-time marker, which allows you to restore a datawarehouse to a state recorded at the marker. Restore points are useful in more than just the traditional bcp scenarios, such as disaster recovery. Restore points can … Continue reading Restoring Azure SQL Datawarehouse

Azure DW, The Peril of AUTO_CREATE_STATISTICS

Last year the Azure DW team announced the availability of added another hotly requested feature, auto creation of statistics. Auto creating statistics has many known benefits which impacts DevOps, as database deployment pipelines don’t have to include the statistics creating queried, DBA’s who don’t have to worry about the lack of statistics on objects and … Continue reading Azure DW, The Peril of AUTO_CREATE_STATISTICS

Azure Data warehouse Performance Tuning 102

In this post we explore data warehouse performance, taking into account the new Gen2 tier. A key goal when optimising request (data warehouse speak for query) performance is to reduce any costly DMS operation, specifically shufflemove and broadcastmove. Performing the basic steps outlined in part 1, will in most cases reduce the impact of the costly operations. … Continue reading Azure Data warehouse Performance Tuning 102

Capture Telemetry – Azure SQL Data Warehouse

A short article about capturing transient telemetry. Most DMVs in the Azure SQL data warehouse are transient, which means data stored in the table is not persisted usually after: A Pause / Resume / Scale event After a max rollover threshold has been reached (10000 rows) For example, the data in the sys.dm_pdw_request_steps dmv – … Continue reading Capture Telemetry – Azure SQL Data Warehouse

Upgrading The Azure Data warehouse To Gen2

The Data warehouse has recently been upgraded to the next version called Gen2. The good news is that the upgrade process is relatively seamless. Upgrading is supposed to be a one step process but I have decided to add 2 extra steps: Backup / Take a snapshot Upgrade the data warehouse Rebuild all indexes Backup / … Continue reading Upgrading The Azure Data warehouse To Gen2

Azure Parallel Data Warehouse (PDW) Errors #1: cancelled transactions

Microsoft’s Azure data warehouse is a cloud hosted PaaS offering. Which implies compute and storage resources are managed by Microsoft / Azure fabric. Scheduled and unscheduled maintenance activities occur which are supposed to be transparent, i.e. should not impact the platform. This is a false assumption. On some occasions maintenance will impact the platform. For … Continue reading Azure Parallel Data Warehouse (PDW) Errors #1: cancelled transactions