Press "Enter" to skip to content

Month: November 2019

MSDTC and the Firewall

Published 2019-11-25 by Kevin Feasel

Josh Smith shows how you can enable MSDTC in a buttoned-down environment:

This is just a fancy way of saying you need to be better friends with who ever is managing your enterprise firewall. I hadn’t had to touch the DTC until a recent vendor insisted their application wouldn’t work without it (despite their only having a single data store). The MSDTC was developed to coordinate transactions that would span multiple machines and was originally introduced in SQL Server 2000.

In theory it’s not super complicated: just enable the DTC service/communication on the servers in question and turn on some built in firewall rules on the servers right? Almost.

Read on for the full set of instructions.

Comments closed

Partition Switching to Make Table Changes

Published 2019-11-25 by Kevin Feasel

Daniel Hutmacher shows a couple things you can change with near-zero downtime using partition switching:

Look, I’m not saying that you’re the type that would make a change in production while users are working.

But suppose that you would want to add an identity column to dbo.Demo, and change the clustered index to include that identity column, and make the index unique? Because it’s the table’s clustered index, you’re effectively talking about rebuilding the table (remember, the clustered index is the table), which involves reorganizing all of the rows into a new b-tree structure. While SQL Server is busy doing that, nobody will be able to read the contents of the table.

Daniel mentions a read-only table, though you could also do this with a read-write table as long as you have triggers to keep the two tables in sync until go time. That adds to the complexity, but it is an option if you need it.

Comments closed

Columnar File Formats in Hadoop

Published 2019-11-22 by Kevin Feasel

Matthew Rathbone gives us an overview of the benefits behind the ORC and Parquet file formats:

People throw this term around a lot, but I don’t think it is always clear exactly what this means in practice.

The textbook definition is that columnar file formats store data by column, not by row. CSV, TSV, JSON, and Avro, are traditional row-based file formats. Parquet, and ORC file are columnar file formats.

Read on for a comparison and example. In the SQL Server world, think columnstore versus rowstore indexes and you won’t be too far off.

Comments closed

Aggregations in Power BI

Published 2019-11-22 by Kevin Feasel

Shabnam Watson takes us through aggregations in Power BI:

In Power BI, Aggregations start as tables just like any other table in a model. They can be based off a view or table in the source database, or created in Power BI with Power Query. They can be in Import or Direct Query storage mode.

Once in the model, these tables can be configured so that the engine can use them instead of a detail table to answer queries when possible. The process of creating and configuring aggregations in Power BI is significantly easier than the process of creating aggregations in SSAS multidimensional.

Once an aggregation table is configured, it becomes hidden from end users. Report developers and end users don’t know that it exists and don’t need to change anything in how they query the dataset.

This was one of the key benefits to a multidimensional model. Shabnam has an excellent, detailed article here, so give it a read if you are a Power BI developer.

Comments closed

Decomposition Trees in Power BI

Published 2019-11-22 by Kevin Feasel

Tomaz Kastrun takes us through a new visual in Power BI:

Decomposition tree is a data presentation of slicing and dicing of selected metrics based on the attributes of these metrics or with combination of other metrics. Another great aspect of this visual is to analyze the selected variable with many metrics or attributes (dimensions) as the same time.

It’s not the type of visual I’d want to see on a dashboard, but I can see it as quite useful in exploratory data analysis.

Comments closed

Profiling Hive Jobs on Tez

Published 2019-11-21 by Kevin Feasel

Dmitry Tolpeko takes us through Hive query diagnostics:

I was asked to diagnose and tune a long and complex ad-hoc Hive query that spent more than 4 hours on the reduce stage. The fetch from the map tasks and the merge phase completed fairly quickly (within 10 minutes) and the reducers spent most of their time iterating the input rows and performing the aggregations defined by the query – MIN, SUM, COUNT and PERCENTILE_APPROX and others on the specific columns.

After the merge phase a Tez reducer does not output many log records to help you diagnose the performance issues and find the bottlenecks. In this article I will describe how you can profile an already running Tez task without restarting the job.

Click through for the process, as well as the root cause of the problem.

Comments closed

Why the DBA is Important

Published 2019-11-21 by Kevin Feasel

Melody Zacharias takes us through five areas where DBAs are important in the SQL Server 2019 world:

Databases are the beating heart of digital transformation. Businesses increasingly realize that having a unified view gives them a competitive advantage in a world where data is king. The task of breaking down those silos will fall to highly skilled DBAs using cool new technologies such as PolyBase [https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15]. Although it was introduced in SQL Server 2016, PolyBase got a whole lot more interesting in SQL Server 2019 with the ability to query external SQL Server, Oracle, Teradata, and MongoDB using T-SQL. Our world just got a whole lot bigger!

Read on for the full set of reasons. My agreement with this comes with one caveat: DBAs are important insasmuch as they are willing to grow, try new things, and develop skills. If you’re a stodgy type who hasn’t learned a thing since SQL Server 2008, you’ve got a shelf life.

Comments closed

Upgrading SQL Server 2017 Containers to 2019

Published 2019-11-21 by Kevin Feasel

Anthony Nocentino takes us through one of the big changes to SQL Server containers:

When you start up the 2017 container, the SQL Server (sqlservr) process is running as root (uid 0). Any files created by this process will have the user and group ownership of the root user. Now when we come along later and start up a 2019 container, the sqlservr process is running as the user msssql (uid 10001 by default). This new user doesn’t have permission to open the database files and other files used by SQL Server.

Read on to see how Anthony fixed this.

Comments closed