Press "Enter" to skip to content

Category: Performance Tuning

Enabling SQL Server Optimizer Hotfixes

Published 2020-08-03 by Kevin Feasel

John Morehouse takes us through the step-by-step for enabling optimizer hotfixes in SQL Server:

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance.  There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.

Let’s take a look at the three options.

Read on to learn more. There is some potential risk of regression with new optimizer updates, so standard rules around testing apply.

Comments closed

All About The Compute Scalar Operator

Published 2020-07-20 by Kevin Feasel

Hugo Kornelis continues a quest to document query plan operators:

The Compute Scalar operator is used to compute new values based on other columns in the same row. These new values are then added as extra column(s) in the output rows.

The expressions used to compute the new values can only refer to constant values and to columns in the input rows of the Compute Scalar operator. Other than that, there are, to my knowledge, no restrictions. The expressions can vary from very simple to extremely complex. The expressions can even include references to scalar user-defined functions, to CLR user-defined functions, and to built-in CLR functions.

Read on for a good deal of information about the operator.

Comments closed

Performance Tuning for Cloudera’s Operational Database

Published 2020-07-10 by Kevin Feasel

Liliana Kadar, et al, show us the tools we can use to tune Cloudera’s Operatioanl Database:

A query optimizer determined the most efficient way to run a query. Query optimization helps you to reduce the hardware resources required to run a query and also speeds up your query-response time. Cloudera’s Operational Database provides you with various tools such as plan analyzers to make optimal use of your computing resources. 

Cloudera’s OpDB provides various cost-based and rules-based optimizers. You can use different optimizers based on your use cases. OpDB is primarily used for Online Transactional Processing (OLTP) use cases with Apache Phoenix in the OpDB used as a SQL engine. But you can also use Hive and Impala for Online Analytical Processing (OLAP) use cases. 

Read on for recommendations on platform choice as well as indexing and tuning options.

Comments closed

Parallel Processing in Analysis Services

Published 2020-07-10 by Kevin Feasel

Kasper de Jonge takes us through parallel processing of partitions and tables in Analysis Services:

One thing that has come up several times in the last few weeks is a misconception that you cannot process multiple partitions on the same table or tables in parallel as it would cause a lock. This could be true if you try to do the parallelism yourself. Like in SQL Server you need to think about transactions, the AS engine is a transactional system too.

So, the AS engine is definitely capable of loading data in parallel but only if you let him do the puzzling on concurrency. This means you must send processing commands to the AS engine in one transaction so the AS engine can manage the locks itself. There are other benefits of letting AS doing the work like recalculating the calculated items (tables, columns etc) once instead of multiple times which improves processing performance.

Read on for an example.

Comments closed

Keep Parameter Sniffing On

Published 2020-07-01 by Kevin Feasel

Brent Ozar explains why you should keep parameter sniffing on:

What they THINK is going to happen is that SQL Server will do an OPTION(RECOMPILE) on every incoming query, building fresh plans each time. That ain’t how this works at all, and instead, I wish this “feature”‘s name was “Parameter Blindfolding.” Here’s what it really does.

Read on for the explanation. In reality, parameter sniffing is almost always a good thing. It’s when you have major skews in data that you even have to think about parameter sniffing being a problem.

Comments closed

Tips for Optimizing Columnstore Indexes

Published 2020-06-29 by Kevin Feasel

Ed Pollack continues a series on columnstore indexes:

This is worth a second mention: Avoid updates at all costs! Columnstore indexes do not treat updates efficiently. Sometimes they will perform well, especially against smaller tables, but against a large columnstore index, updates can be extremely expensive.

If data must be updated, structure it as a single delete operation followed by a single insert operation. This will take far less time to execute, cause less contention, and consume far fewer system resources.

Read on for several more tips along these lines.

Comments closed

Optimizing Power Query Merges

Published 2020-06-02 by Kevin Feasel

Chris Webb wants to make your joins in Power Query faster:

The first question I decided to investigate was this:

Does the number of columns in a table affect the performance of a merge?

First of all, I created two identical queries called First and Second that connected to the CSV file, used the first row from the file as the headers, and set the data types to all seven columns to Whole Number.

Click through for the answer to this question. Chris promises a series out of this and I would expect there to be enough content for that.

Comments closed

Adaptive Query Execution with Spark SQL

Published 2020-06-01 by Kevin Feasel

Wenchen Fan, Herman von Hoevell, and MaryAnn Xue announce Adaptive Query Execution for Apache Spark 3.0:

Over the years, there’s been an extensive and continuous effort to improve Spark SQL’s query optimizer and planner in order to generate high-quality query execution plans. One of the biggest improvements is the cost-based optimization framework that collects and leverages a variety of data statistics (e.g., row count, number of distinct values, NULL values, max/min values, etc.) to help Spark choose better plans. Examples of these cost-based optimization techniques include choosing the right join type (broadcast hash join vs. sort merge join), selecting the correct build side in a hash-join, or adjusting the join order in a multi-way join. However, outdated statistics and imperfect cardinality estimates can lead to suboptimal query plans. Adaptive Query Execution, new in the upcoming Apache SparkTM 3.0 release and available in the Databricks Runtime 7.0 beta, now looks to tackle such issues by reoptimizing and adjusting query plans based on runtime statistics collected in the process of query execution.

One of the biggest advantages of SQL as a fourth-generation language is that the database engine (whether that be SQL Server, Oracle, or Spark) gets the opportunity to write and re-write the set of operations needed to solve a query to try to find the best path which returns the same result set. These optimizations aren’t perfect, as any query tuner can tell you, but they can go a long way.

Comments closed

dbatools Commands for Performance Tuning

Published 2020-05-22 by Kevin Feasel

John McCormack takes a look at dbatools with an eye on performance tuning:

DBATools is well known in the SQL Server community for the ease at which it allows you to automate certain tasks but did you know that DBATools can help with performance tuning your SQL Server. As my job recently changed to have more of a performance tilt, I wanted to investigate which commands would be useful in helping me with performance tuning. It turned out there are quite a few.

There are some good commands in here.

Comments closed

Tips to Improve Power BI Performance

Published 2020-05-20 by Kevin Feasel

Dan Szepesi has a few tips for improving Power BI performance:

Now that we have talked through the general Power BI system components, let’s talk performance!  The scope of this blog will cover import models (where data is imported to Power BI Desktop and built into a data model) in the Power BI Pro service tier.  Power BI Premium and Direct Query performance tuning will not be included in this blog post, but if there is interest in those areas, please let us know.

In part I of this performance series, we will look at improving performance in your model, the heart of an import Power BI report solution.

Read on for these tips.

Comments closed