Press "Enter" to skip to content

Category: Performance Tuning

Row-Level Security Performance and Troubleshooting

Published 2023-09-11 by Kevin Feasel

Ben Johnston digs into row-level security:

There are two main areas where RLS can impact performance. The first is the user or authentication lookup. Some kind of lookup must be performed in the access predicate to determine either the user name, group membership, or specific values in the session context. Considering that RLS is non-prescriptive, the lookup isn’t confined to these methods, but they are very easy methods to use and implement and are standard based on implementations I’ve seen.

The second area is the authorization lookup. The authorization lookup, checking if a user has access to particular rows, can have a much bigger impact on performance. This is also in the access predicate. Following the basic rules for performance and keeping lookups simple goes a long way to minimizing the impact of RLS on performance. The goal is to keep performance levels as close as possible to a table without RLS. If indexes and predicates are correct, RLS can improve performance in some situations due to the automatic filtering that happens.

Read on for Ben’s thoughts on the topic.

Comments closed

Reviewing Postgres SQL Statement History

Published 2023-09-08 by Kevin Feasel

Muhammad Ali does some performance testing:

pg_stat_statements is a built-in PostgreSQL extension that keeps track of SQL statements executed by the database server. It records details about query execution counts, total execution time and I/O related info. By capturing this information, database administrators can identify bottlenecks and optimize queries for better overall performance. In this blog, we’ll take a  look at how pg_stat_statements module can be utilized to enhance the performance of your PostgreSQL database.  

Read on to see how you can enable this extension, configure it, and use it to find the worst performers in the bunch.

Comments closed

Manual Stats Updates in SQL Server

Published 2023-09-08 by Kevin Feasel

Matthew McGiffen can’t wait for SQL Server to update those stats:

Having up to date statistics is vital for getting the best performance out of your queries. Even though SQL Server automatically updates statistics in the background for you (When do statistics get updated?), you may find there are times when you want to manage updating them yourself.

Click through to learn several techniques for stats updates.

Comments closed

Value and Hash Encoding in VertiPaq

Published 2023-09-07 by Kevin Feasel

Kristyna Hughes looks at column encodings:

Power BI encoding is a powerful optimizing option that is often overlooked because it’s not visible in neither the Power BI Desktop tool nor in Power BI Service. Natively, the VertiPaq engine in Power BI investigates all columns in the data model and determines how it can store that data most efficiently. To achieve maximum compression, the VertiPaq engine starts by encoding each column which determines the method of compression applied to that column. There are a couple types of encoding – value and hash.

Read on to learn the difference, as well as how to push your columns to use a specific type of encoding.

Comments closed

CPU Threads in SQL Server Backups

Published 2023-08-25 by Kevin Feasel

Andy Yun dives in:

Welcome back to Part 3 of my SQL Server Backup Internals Series.

In Part 1, I introduced the “parts” of a BACKUP Operation and in Part 2, we delved into Backup Buffers. Today, we’re going to talk about what manipulates those Backup Buffers = CPU Threads. This’ll be a longer blog, so go refill your coffee now.

Andy did an outstanding job explaining what reader and writer threads do and how SQL Server picks the numbers of each.

Comments closed

Load Balancing in Postgres Clusters with pg_cirrus

Published 2023-08-23 by Kevin Feasel

Muhammad Ali explains how load balancing works in Postgres:

Load balancing is a critical component of high availability clusters that optimises performance, scalability, and fault tolerance. By evenly distributing database connections across multiple servers, load balancing prevents bottlenecks, efficiently handles increased workloads and improves response time.

In this blog, we will explore how standby nodes contribute to efficient workload distribution and achieving optimal query execution by directing all read/select queries to these standby nodes.

Read on to see how you can use pg_cirrus to perform query load balancing.

Comments closed

Thoughts on Parameter Sensitive Plan Optimization

Published 2023-08-15 by Kevin Feasel

Jared Poche shares some thoughts:

First, I wanted to point out that there are fixes to issues specific to Parameter Sensitive Plan Optimization in SQL Server 2022. The issue itself is documented at the link below.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver16#known-issues

When I heard about the issue, I heard that it could cause SQL Server 2022 to cause stack dumps every 15 minutes. The time interval made me think it was related to the default interval for Query Store, and that seems to be the case.

Jared has several thoughts on the topic as he puts together a talk on PSPO.

Comments closed

Delayed Transaction Durability in tempdb

Published 2023-08-09 by Kevin Feasel

Bob Ward does some digging:

I have found almost every day of my career at Microsoft, I learn something new about Microsoft SQL. It is one of the reasons I enjoy my job. In April of 2023, I was speaking at the MVP Summit in Redmond, Washington. One day I was spending time in a side conversation with MVP Rob Farley discussing some of his “wish list” items for SQL. One of these wish list items was delayed transaction durability for tempdb.

Good news for Rob on that front.

Comments closed

Execution Plans in Postgres

Published 2023-08-08 by Kevin Feasel

Henrietta Dombrovskaya shows us an execution plan in Postgres:

In the last blog (When PostgreSQL Parameter Tuning is not the Answer), we compared several execution plans for a SQL statement as we made changes to parameters and indexes. Still, there was no mention of what an execution plan is, how one can obtain an execution plan for a query, and how to interpret the result. In this blog, we will take a deep dive into this topic.

Read on for the full article.

Comments closed

Azure SQL MI and the WAF: Performance Pillar

Published 2023-07-28 by Kevin Feasel

Niko Neugebauer looks at one of the pillars of the Well-Architected Framework with respect to Azure SQL Managed Instance:

A baseline is a known value against which later measurements and performance can be compared. Baseline helps us define what is a normal database performance and thus comparing against the baseline provides us with insights into any abnormalities. Ideally, one should take performance measurements at regular intervals over time, even when no problems occur, to establish a server performance baseline. Compare each new set of measurements with those taken earlier.

Click through for additional guidance and recommendations.

Comments closed