SQL Data Warehouse and Non-Clustered Indexes

One performance optimization that is often over looked in SQL Data Warehouse (SQL DW) is the use of non-clustered indexes (NCI) on tables with a clustered columnstore index (CCI). Clustered columnstore enhances the general performance benefits that RDBM systems bring to set based operations. Great value is derived from being able to apply set based…


How many queries and slots are running in my Azure SQL Data Warehouse?

Azure SQL Data Warehouse allows you to manage your workload using resource classes and concurrency slots for query execution. In the previous blog, Checking for Queued Queries, we showed you how to check to see if your query was queued and awaiting execution. That’s great to know why your query hasn’t started executing yet but…


Checking for queued queries in Azure SQL Data Warehouse

Azure SQL Data Warehouse manages query workload through a combination of concurrent query execution slots and resources classes. The SQL Data Warehouse workload manager enables you to manage your workload within your data warehouse that are contending for system resources. SQL Data Warehouse defines a set of concurrency slots for query execution. The system manages…


Getting the full explain plan for long queries

I was recently debugging a large stored procedure (2000+ lines of code) for a customer. I wanted to look at the explain plan for a particularly long SELECT statement in the sproc right in the middle for optimal table design. I opened the statement in SSMS, copied in the text, added the EXPLAIN keyword in…


Creating a SQL Data Warehouse user with a specific resource class

In your data warehouse, you probably have multiple user groupings that perform different tasks: data loading; system tasks; querying; transformation and aggregations. Each of these groups has it’s own characteristics that can impact each other – a large data load might need a lot of memory while a user running a dashboard might not. Azure…


Adding AD Users and Security Groups to Azure SQL Data Warehouse

Azure offers you the ability to federate your corporate Active Directory to the cloud through Azure Active Directory Connect and Federation. The federation to the cloud allows you to authenticate against your Azure SQL Data Warehouse instance using your domain credentials. We often are asked how to create a user or security group in SQL…


Enabling TDE for Azure SQL Data Warehouse

Security is top of mind for everyone these days – securing your identity, securing your data. When you think about how you secure your data in the cloud you need to ensure that you understand how your data is stored and accessed. The Azure SQL Data Warehouse service utilizes Transparent Data Encryption (TDE) to encrypt your database,…

0

Loading Data to SQL Data Warehouse with the .NET SqlBulkCopy class

The Azure SQL Data Warehouse service is built on SQL Server and extends the capabilities of SQL Server for large scale relational data warehouse workloads. By leveraging SQL Server as the core relational component, customers are able to quickly take advantage of the large ecosystem of solutions and partner products to build their data story. Having…


The effects of column level compression

The Azure SQL Data Warehouse service is built on SQL Server and extends the capabilities of SQL Server for large scale relational data warehouse workloads. One of the areas of interest is how to you optimize for databases with terabytes or petabytes of data. One answer is Clustered Columnar indexes (CCI). A columnar index is…


Exporting to a single file in SQL Data Warehouse

Azure SQL Data Warehouse includes PolyBase – a technology that accesses and combines both non-relational and relational data from T-SQL . It allows you to run queries on external data in Azure blob storage and can be used for importing or exporting data. External data is exposed through a concept called an external table (see…