SQL Spot – Part 5

Losing sight of what are the most relevant updates on SQL Server 2016? Well, there is no need to spend countless hours browsing the internet. We made another our SQL Spot summary of the most interesting updates and changes for all SQL lovers. You might have gotten to know some of the returning topics: SQL Server news and releases, Power BI, DWH, SSIS, SSAS, SSRS, data science, maintenance, security, tuning and learning materials. And, as always, all credits go to our amazing SQL blogging community.

Business, releases, news and trivia

Business

• Everything you need to know about the WannaCry / Wcry / WannaCrypt ransomware
• More on the EU data protection regulation, soon to be succeeded by the GDPR. It defines IT practices for data that are likely to extend worldwide.
• SQL language is 43 years old. Here are 8 reasons why we still use it…
• See how Microsoft leverages scalability, low latency, and flexibility benefits of DocumentDB to innovate and bring business value to their services.
• The Buzz on Power BI Premium. Is IT for you or not?
• Five reasons to run SQL Server 2016 on Windows Server 2016
• Why machine learning can Improve Customer Service
• Individual excellence vs organizational impact: it is important to see and know the difference?
• Top 5 Reasons You Should be Moving Your Business Intelligence Solution to the Cloud

Releases and news

• SQL Server 2017 CTP 2.1 now available.
• SQL Server community-driven enhancements in SQL Server 2017
• Summarizing Microsoft data innovations announced at Build 2017, including Azure Cosmos DB, as well as support for MySQL and PostgreSQL as Platform as a Service (PaaS) offerings through Azure.
• Microsoft Azure SQL Database Management Pack
• Azure Database Migration now available for preview.

Power and Excel BI

General

• Microsoft accelerates modern BI adoption with Power BI Premium Tier. Previously available were two tiers, Power BI Free and Power BI Pro ($10/user/month). The problem with Power BI Pro is that this can add up for large organizations. In addition, performance needs might not be met. Power BI Premium, which is an add-on to Power BI Pro, addresses the concern about cost and scale.
• A closer look at Power BI Report Server

Power BI

• Power BI Desktop May Feature Summary
• Four ways to address new sharing limits in Power BI Free.
• Everything about new Power BI Apps; a mechanism to share the content in Power BI in a way that has security and governance together. Also see using Apps and Workspaces in Power BI
• With the introduction of Power BI Premium, Power BI Embedded and the Power BI service are converging to advance how Power BI content is embedded in apps. How to migrate Power BI Embedded workspace collection content to Power BI
• Connecting to datasets in the Power BI service from Desktop
• Report level measures for live connections to Power BI Service datasets & SSAS tabular models
• Use Quick measures to easily perform common and powerful calculations
• Better use of colors in Power BI (VID)
• The SandDance Custom Visual
• Custom data connectors are one of the biggest things that’s happened to Power BI in a long time
• Power Query – Create a single source query that is referenced by any other query that requires that shared connection. The reference feature let’s you create new query, referencing the original query as the data source in any number of additional queries.
• Decoding M – Exploring the YouTube Data API with Power BI
• Deploying, Securing, and Updating Power BI Reports
• Dynamic Power BI Reports using parameters
• Reusing Datasets imported to the Power BI Service
• One of the most confusing, under-documented and widely-misunderstood features of Power BI and Power Query (or Excel “Get & Transform” or whatever you want to call it) are the data privacy settings.
• Usage metrics for dashboards and reports

Excel BI

• Using Excels Rank functions
• Powerpivot Dynamic TOPN Reports via slicers

M and DAX

• No reall, what is DAX?
• If you use Power BI to connect to a data source such as SQL Server in DirectQuery mode, you’ll find that you cannot use the complete range of DAX functions inside your calculations, unless you enable the “Allow unrestricted measures in DirectQuery mode” option.
• Different techniques to compute a rownumber column in DAX based on a specific ranking, comparing slow and optimized approaches.

DWH

General

• Why Data Warehouse Modernization must be coordinated with other modernization projects

Azure SQL DWH

• Azure SQL Data Warehouse loading patterns and strategies

SSIS

• SQL Server 2016 SSIS Data Flow Buffer Auto Sizing capability benefits data loading on clustered columnstore tables
• Passively Mine SSIS Data Flow Rows Loaded From the SSIS Catalog

SSAS

General

• The differences in the tiers available in Azure Analysis Services (Azure AS), comparing them with the features in SQL Server Analysis Services (SSAS) on-premises
• The May release of Power BI Desktop introduced a long awaited feature: you can create a measure in the report when you have a live connection to Analysis Services or to another Power BI model.
• Schedule Pause/Resume of Azure Analysis Services
• Three ways to provide High Availability on SSAS
• With so many product options to choose from for building a big data solution in the cloud, such as SQL Data Warehouse (SQL DW), Azure Analysis Services (AAS), SQL Database (SQL DB), and Azure Data Lake (ADL), there are various combinations of using the products, each with pros/cons along with differences in cost.

Azure AS

• Building an Azure Analysis Services Model on Top of Azure Blob

Multidimensional

• One of the most common sources of query performance problems with SSAS Multidimensional is unprocessed aggregations and indexes

DAX (SSAS)

• How to use the DAX expression FORMAT to extract the month name from a date.

SSRS

• Multiple SSRS instances, one database engine instance. Installing multiple instances of SSRS
• How to retrieve detailed scheduling information from the ReportServer database

Data science

General

• How AI is used to infer human emotion

Machine learning, AI, Deep learning

• Using Hadoop Data in R for distributed manchine learning: Basics
• Understanding the main concept behind clustering analysis, using it in Power BI and identifying the best number of clusters in the analysis.

Excel

• Quickly gather statistics for your data using Excels Descriptive Statistics

R, Python, JSON,…

• Clustering Concepts, writing R codes inside Power BI
• K-mean clustering In R, writing R codes inside Power BI
• Data Exploration in R, an introduction

Internet of Things

• Microsoft Azure IoT Edge – Extending cloud intelligence to edge devices

Maintenance

Internals

• All on Adaptive Joins: Anatomy Of An Adaptive Join, SARGability and local variables.
• How is a default column value stored, and what if some rows exist when a column is added and then the default value changes?
• How To Use Temporal Tables For Easy Point-In-Time Analysis

Backup and recovery

• Moving data around efficiently in a hybrid cloud environment is critical and challenging.
• Why restoring is slower than backing up?

Storage

• Make sure you have an understanding of your underlying sequential throughput as important operations that are often executed by SQL Server are potentially performance limited by the sequential throughput of the underlying storage subsystem.
• Why a RAID 5 is a terrible idea for storing your transaction log, and why it is also very risky to store your data files on a RAID 5 volume. Follow up on how to calculate your RAID 5 parity information.
• Azure Cosmos DB is Microsoft’s globally distributed, horizontally partitioned, multi-model database service. The service is designed to allow customers to elastically (and independently) scale throughput and storage across any number of geographical regions.
• If you need persistent data disks for Azure IaaS VMs that are supported on both Windows and Linux then you will be interested in Azure Storage Disks

Index

• Automatic index management in Azure SQL database

Health Practices

• The basics of good T-SQL coding Style
• Have a look at these Diagnostic Queries. Make sure to read the doc and watch the information videos before using it.

Infrastructure

• Working with Windows containers and Docker

SQL

• A quick tip on searching stores procedures via T-SQL

Security and quality

• Guide for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform
• Setting up data quality rules within your analytics system is critical to remove data inaccuracies and leave you with the best quality data possible to feed into your reports.
• Easy concepts around security in your Azure SQL Data Warehouse. For lots more detail, see the excellent documentation provided by Microsoft.
• So you’re using encryption in SQL Server, but you’ve discovered that the expiration date of a certificate is coming. What do you do? The process of safely replacing the certificate is called rotating the encryption key
• The concepts of SQL Server temporary object caching
• Microsoft Azure SQL Database provides unparalleled data security in the cloud with Always Encrypted
• Azure provides you firewall per database where you can explicitly specify who has access to the database.
• Power BI and data security: compliance and encryption
• The information that you need about security and protection in the SQL Server Database Engine and Azure SQL Database.

Tuning

Internals

• SQL Server uses internal statistics to estimate how many rows are returned from a specific query. That process is called the cardinality estimation. There are a few limitations that the Cardinality Estimation currently has, and how you can overcome these by applying various techniques.
• Why the query optimizer doesn’t know (or care) what’s in the buffer pool.
• UNION ALL optimization in the SQL Server engine
• SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing
• In SQL Server 2017 there is one more way to force parallel plan.
• How to determine what statistics are used by the optimizer during a plan compilation in SQL Server 2017
• Batch Mode Sort peculiarities. SQL Server 2017: sort, spill, memory and adaptive memory grant feedback
• SQL Server 2017: interleaved MSTVFs Vs Inline Table Valued Functions
• SQL Server 2017: do Adaptive Joins Work with Cross Apply or Exists?
• Troubleshooting CPU Performance on VMware
• Automatic plan correction in SQL Server 2017
• SQL Server 2017: identifying which statistics were in fact used by the query optimizer for a given compilation has become much easier: they are now part of showplan.
• Tracking Lookup Operations in SQL Server
• Performance issues with computed columns

Monitoring

• Although deprecated don’t expect Profiler to go anywhere soon…
• Perfmon counters are great for measuring workload, but choosing which counter to baseline can be confusing. The Transactions/sec counter has big blind spots
• Finding Queries that Cause Wait Stats in SQL Server

Indexing

• SQL Server and the SARGABILITY in integers.
• A few ways to trick SQL Server into using batch mode without really using columnstore indexes
• When non clustered indexes are not perfect

In-memory optimization

• Clarify cardinality estimation for a memory optimized table variable

Health practices

• Don’t use the string concatenation trick in sql predicates index tuning
• Bad Idea: Hinting multiple indexes on the same table.
• An overview of User-defined functions: their virtues, vices and their syntax.

SQL

• One SQL Cheat Code For Amazingly Fast JSON Queries

Career and learning

Thoughts

• Thoughts on speaking, presenting and teaching
• Time to specialize or generalize your knowledge

Courses

• Vids: Learn how SQL Server Data Tools (SSDT) turns Visual Studio into a powerful environment for database development.
• Books: Tabular Modelling In SQL Server Analysis Services. Also advised for DAX in addition to this book.