Press "Enter" to skip to content

Category: Performance Tuning

Collation Compatibility And Linked Servers

Greg Low points out an important property which can help linked server performance:

The on-premises versions of SQL Server have the ability to connect one server to another via a mechanism called Linked Servers.

Azure-based SQL Server databases can communicate with each other by a mechanism called External Tables. I’ll write more about External Tables soon.

With Linked Servers though, I often hear people describing performance problems and yet there’s a configuration setting that commonly causes this. In Object Explorer below, you can see I have a Linked Server called PARTNER.

Read on for more.

Comments closed

Azure SQL DB Automatic Tuning FAQ

Arun Sirpal has a self-Q&A session regarding Azure SQL Database’s automatic tuning options:

What are the options?

  1. CREATE INDEX that identifies the indexes that may improve performance of your workload, creates the indexes, and verifies that they improve performance of the queries.

  2. DROP INDEX that identifies redundant and duplicate indexes, and indexes that were not used in the long period of time.

  3. PLAN REGRESSION CORRECTION that identifies SQL queries that are using execution plan that are slower than previous good plan, and uses the last known good plan instead of the regressed plan.

Very useful information.

Comments closed

Replacing DAX PathContains With OR

Chris Koester shows the performance benefits of replacing the PathContains function in DAX with a simple OR operator:

This post shows how you can generate optimized multi-value DAX parameters in SSRS and achieve greater performance compared to the DAX PathContains function. This will be a short post that provides the SSRS expression to convert multiple SSRS parameters into a double-pipe delimited string for use in a DAX query. In other words, the goal is to use the DAX OR operator (||) instead of the PathContains function. I’m assuming the reader has experience with SSRS, so not all steps will be shown.

Read on for the example, which ended up being a 16X performance improvement.

Comments closed

Performance Tuning TVFs With Optional Parameters

Arvind Shyamsundar walks us through a scenario with user-defined functions with optional parameters:

If you notice carefully, the above query is an example of ‘optional parameters’ wherein the same query caters to situations where there are specific values for the parameters as well as other cases where there are none. Due to the implementation of the query (specifically the usage of ISNULL(@paramname, ColName)) what ends up happening is that the query plan thus generated will not leverage any indexes on the table. While this query can be refactored to separate versions for cases where the parameter values are supplied, and where they are not, another viable option is to use OPTION (RECOMPILE) on the statement level. This is an acceptable solution in most cases because the cost of scanning the table is often far higher than the cost of recompiling this query. So here is how we used OPTION RECOMPILE in this case:

Arvind walks us through three separate solutions.  My fourth solution is, don’t use user-defined table-valued functions.

Comments closed

Non-Cost-Based Optimizations In Relational Databases

Lukas Eder has a big article on ten query optimizations that don’t involve looking at statistics or query costs:

This optimisation is really silly, but hey, why not. If users write impossible predicates, then why even execute them? Here are some examples:

-- "Obvious"
SELECT * FROM actor WHERE 1 = 0

-- "Subtle"
SELECT * FROM actor WHERE NULL = NULL

The first query should obviously never return any results, but the same is true for the second one, because while NULL IS NULL yields TRUE, always, NULL = NULL evaluates to NULL, which has the same effect as FALSE according to three-valued logic.

This doesn’t need much explanation, so let’s immediately jump to see which databases optimise this:

I was a bit surprised at how well DB2 did in this set.

Comments closed

Using Query Performance Insight To Find High-IO Queries

Jim Donahoe shows how he used Azure’s Query Performance Insight to eliminate 10 billion logical reads:

To access QPI, you simply need to click on the database you want to work with. Once you click on your database, scroll down in the portal to Query Performance Insight(QPI). Once QPI opens, you will see three options to sort on: CPU, DATA I/O, and LOG I/O.  You can also set the timeframe to view, I set for 24 hours.  Now, I have my timeline of 24 hours, and I am able to view which queries had the highest DATA I/O. I made a list of the top 3 from each category(CPU, DATA I/O, and LOG I/O) and presented it to my client. I presented the number of times it was executed, and the usage it utilized each time(all from the QPI information). The client then sent me 10 queries they wanted tuned and listed them in a prioritized list.

Well, by the end of tuning their 3 highest priority queries, we removed over 10 billion logical reads!  Yep, 10 BILLION! The client was very happy with our results and is currently awaiting the preview Standard Elastic Pools to come out of Preview and become GA. I have provided a few screenshots of an AdventureWorksLT database on my personal instance just to show you how to access QPI, and change metrics.

Click through for a demo.

Comments closed

I/O Latency And Performance Tuning

Andy Galbraith is starting a new toolbox series.  His first post is an introduction and a look at drive latency:

You look at the numbers again, and now you find that disk latency, which had previously been fine, is now completely in the tank during the business day, showing that I/O delays are through the roof.
What happened?
This demonstrates the concept of shifting bottleneck – while CPU use was through the roof, the engine so bogged down that it couldn’t generate that much I/O, but once the CPU issue was resolved queries started moving through more quickly until the next choke point was met at the I/O limit.  Odds are once you resolve the I/O situation, you would find a new bottleneck.
How do you ever defeat a bad guy that constantly moves around and frequently changes form?

Click through for some pointers on disk latency and trying to figure out when it becomes a problem.

Comments closed

How Functions Affect Data Retrieval Performance

Daniel Janik shows one of the many pain points around user-defined functions in SQL Server:

Note that Query 1 uses a function around the column and this causes a scan which increases IO and CPU utilization. This is because every value in the column for the whole table must have the hyphen removed to see if it is a match.

In Query 2, we see a seek. This is because the value is modified instead of the column.

Click through for a few examples.

Comments closed

Rowgroup Elimination In Stored Procedures

Erik Darling notes a parameter sniffing problem when trying to use rowgroup elimination in a stored procedure:

So where are we? Well, we found that Rowgroup Elimination is possible in stored procedures with ColumnStore indexes, but that the cached plan doesn’t change based on feedback from that elimination.

  • Good news: elimination can occur with variables passed in.
  • Bad news: that cached plan sticks with you like belly fat at a desk job

Remember our plan? It used a Stream Aggregate to process the MAX. Stream Aggregates are preferred for small, and/or ordered sets.

Great post, Brent.

Comments closed

Bad Parameter Sniffing Flowchart

Grant Fritchey is asking for input on a new flowchart he has created:

Lots of people are confused by how to deal with bad parameter sniffing when it occurs. In an effort to help with this, I’m going to try to make a decision flow chart to walk you through the process. This is a rough, quite rough, first draft.

I would love to hear any input. For this draft, I won’t address the things I think I’ve left out. I want to see what you think of the decision flow and what you think might need to be included. Click on it to embiggen.

I think it’s a great first step.  I think a decision to add local variables and use them instead of parameters would be useful, particularly in contrast to using RECOMPILE and OPTIMIZE FOR UNKNOWN.

Comments closed