Percona

OCTOBER 18, 2023

With proprietary databases, customers often get used to having constant technical support from their vendors, especially if the organization they work for lacks in-house expertise. These vendors often provide integrated stacks that include the database, application server, and other components that seamlessly integrate.

Open Source 90

Open Source Database Hardware Strategy 90

SQL Performance

MARCH 29, 2022

Parameter Data Types. As mentioned in the first part of this series , one of the reasons it is better to explicitly parameterize is so you have full control over parameter data types. When SQL Server applies simple parameterization to an ad-hoc statement, it makes a guess about the data type of the replacement parameter.

Cache 90

Cache Servers Database Testing 90

SQL Performance

APRIL 8, 2020

This article is the first in a series about the fundamentals of table expressions in T-SQL. I will mainly focus on four types of named table expressions, which are known in T-SQL as derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). They are all named table expressions.

C++ 143

C++ Azure Database Servers 143

Percona

JULY 15, 2023

Working with hundreds of different customers, I often face similar problems around running queries. One very common problem when trying to optimize a database environment is index usage. A query that cannot use an index is usually a long-running one, consuming more memory or triggering more disk iops. What is an Index?

Virtualization 77

Virtualization Speed Database Efficiency 77

SQL Performance

NOVEMBER 4, 2021

First introduced in SQL Server 2017 Enterprise Edition, an adaptive join enables a runtime transition from a batch mode hash join to a row mode correlated nested loops indexed join (apply) at runtime. For brevity, I’ll refer to a “correlated nested loops indexed join” as an apply throughout the rest of this article.

Servers 98

Servers Design Database Code 98

Percona

JANUARY 4, 2023

Recently I was working with a customer wherein our focus was to carry out a performance audit of their multiple MySQL database nodes. While working, the customer raised two interesting questions: how can he make complete use of the performance schema, and how can he find what he requires? For example stage/sql/altering table.

Performance 98

Performance C++ Monitoring Servers 98

SQL Performance

JULY 8, 2020

I should stress that in practice, there’s no actual process in which SQL Server converts the original query string with the nested logic to a new query string without the nesting. The plan applies a seek in the index idx_nc_orderdate based on the single merged predicate as the seek predicate.

Servers 110

Servers Code Processing Performance 110

SQL Performance

OCTOBER 20, 2022

When SQL Server reads data under locking read committed isolation (the default for non-cloud offerings) and chooses row-level locking granularity, it normally only locks one row at a time. SQL Server solves this problem using lock classes. Locking optimizations. Changing data. Lock classes. Reading data.

Testing 91

Testing Servers Cache Engineering 91

SQL Performance

MARCH 30, 2021

This five-part series takes a deep dive into the way SQL Server row mode parallel plans start up. I added one nonclustered index: CREATE NONCLUSTERED INDEX PP ON dbo. ) ; My test environment is SQL Server 2019 CU9 on a laptop with 8 cores and 16GB of memory allocated to the instance. MonthlyPosts AS. (

Cache 98

Cache Servers Operating System Processing 98

Percona

OCTOBER 10, 2023

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. One convenient way that I’ve found working well is the use of an external POD running the percona-toolkit. Your MySQL connection id is 256 Server version: 8.0.32-24.2 Your MySQL connection id is 256 Server version: 8.0.32-24.2

Database 93

Database Performance C++ Engineering 93

Percona

FEBRUARY 24, 2023

In the Home Dashboard of PMM, a low CPU utilization on any of the database services that are being monitored could mean that the server is inactive or over-provisioned. Marked in red in Figure 1 is a server with less than 30% of CPU usage. For more information on EBS volume types, check the Amazon EBS Volume Types page.

Cloud 117

Cloud Serverless AWS Storage 117

SQL Performance

JUNE 8, 2022

Assigns an initial data type. When the parser allows simple parameterization to continue, SQL Server® increments the Auto-Param Attempts/sec counter of the SQL Statistics object. This tree does not contain SQL language elements. It's frequently possible to write the same logical requirement in different ways.

Cache 54

Cache Servers Database Processing 54

SQL Performance

SEPTEMBER 25, 2019

They come in three varieties: Lazy Table Spool , Lazy Index Spool , and Lazy Row Count Spool. An example plan shape showing a lazy table performance spool is below: The questions I set out to answer in this article are why, how, and when the query optimizer introduces each type of performance spool. Lazy Index Spool.

Performance 104

Performance Cache Servers Education 104

SQL Performance

MAY 20, 2022

The most important things to understand about server-side parameterization are it doesn’t happen all at once and a final decision to parameterize isn’t made until the end of the process. Names of tables and columns haven’t been resolved to database objects yet, and no type information is available. Initial Data Types.

Cache 55

Cache Database Servers Testing 55

SQL Performance

JULY 1, 2021

There was the case where a colleague constantly promoted the deprecated backward compatibility view sys.sysprocesses instead of newer dynamic management views (DMVs), and another case where a different colleague took down a production server using SQL Server Profiler. USE BadIdeas ; GO. CREATE TABLE dbo. CREATE TABLE dbo.

C++ 58

C++ Database Servers Performance 58

SQL Performance

NOVEMBER 13, 2019

Note that needing to assign row numbers based on nondeterministic order is different than needing to assign them based on random order. This article explores different techniques for computing row numbers with nondeterministic order. This can be achieved by either pulling the data preordered from an index, or by sorting the data.

Servers 104

Servers C++ Storage Performance 104

SQL Performance

APRIL 8, 2021

This is the second part of a five-part series taking a deep dive into the way SQL Server row mode parallel plans start up. SQL Server executes a query through a process known as a query scan. The translation process is itself recursive in nature, starting at the root and working its way down the tree.

Storage 94

Storage Transportation Engineering Processing 94

SQL Performance

APRIL 17, 2020

You can use it to change a column's data type, length, precision, scale, nullability, collation…and many other things besides. When we ask SQL Server to change some aspect of a column's definition, it needs to check that the existing data is compatible with the new definition. Improvements in SQL Server 2016.

Servers 63

Servers Database Testing Efficiency 63

Percona

JULY 31, 2023

Previous MySQL versions checked the syntax but did not perform the required work for a constraint check. This is different from what we want either logically or as data. This NULL problem can be avoided by setting the SQL Mode to STRICT. or later, which is another reason to upgrade. Some examples?

Database 73

Database Open Source Servers Code 73

SQL Performance

SEPTEMBER 5, 2019

I briefly mentioned that batch mode data is normalized in my last article Batch Mode Bitmaps in SQL Server. All data in a batch is represented by an eight-byte value in this particular normalized format, regardless of the underlying data type. You can skip the WHERE clauses if you are running SQL Server 2017.

Performance 81

Performance C++ Storage Servers 81

Percona

JULY 1, 2023

What are the differences between Aurora and RDS? Amazon Relational Database Service (Amazon RDS) is a hosted database service that provides multiple database products to choose from, including Aurora, PostgreSQL , MySQL , MariaDB , Oracle, and Microsoft SQL Server. How do I choose which one to use? What is Amazon RDS?

AWS 52

AWS Database Serverless Storage 52

SQL Performance

DECEMBER 9, 2020

At the time of writing, T-SQL doesn’t provide such a built-in tool, but one can always hope and vote for such a tool to be added in the future. In a comment to my article, Marcos Kirchner mentioned that he tested my solution with varying table value constructor cardinalities, and got different execution times for the different cardinalities.

C++ 114

C++ Servers Testing Processing 114

Percona

SEPTEMBER 1, 2023

MySQL server performance can sometimes be perplexing, and if you’ve ever wondered about the role of triggers in influencing your MySQL server’s memory allocation, this post is for you. Row-level Trigger Row-level triggers in MySQL are a type of trigger that responds to individual row changes within a table.

Cache 59

Cache Database Games Healthcare 59

Percona

JUNE 4, 2023

In this blog, we’ll provide a comparison between MariaDB vs. MySQL (including Percona Server for MySQL ). Introduction: MariaDB vs. MySQL The goal of this blog post is to evaluate, at a higher level, MariaDB vs. MySQL vs. Percona Server for MySQL side-by-side to better inform the decision making process. What is MariaDB?

Open Source 52

Open Source Servers Storage Scalability 52

SQL Performance

FEBRUARY 3, 2020

AND BatchID = @BatchID ; -- do the work - copy from Original to Partitioned. This isn't going to work well if. I probably could have devised a better retry/loop routine to make the work less manual here, and shortened up the delay so I knew immediately whether it worked or not, but this was good enough for my needs.

Speed 113

Speed Processing Servers Storage 113

SQL Performance

JANUARY 11, 2021

There are features many of us shy away from, like cursors, triggers, and dynamic SQL. There is no question they each have their use cases, but when we see a trigger with a cursor inside dynamic SQL, it can make us cringe (triple whammy). But, as with cursors, triggers, and dynamic SQL, they have their use cases. Background.

Cache 59

Cache Tuning Database Performance 59

Highly Scalable

MARCH 1, 2012

First, we should note that SQL and relational model in general were designed long time ago to interact with the end user. This user-oriented nature had vast implications: The end user is often interested in aggregated reporting information, not in separate data items, and SQL pays a lot of attention to this aspect. ” .

Database 279

Database Ecommerce Efficiency Engineering 279

Percona

AUGUST 5, 2023

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables but still gets treated as a single table by the SQL layer. If you don’t have well-designed and small columns for this composite primary key, it could enlarge all of your secondary indexes. MySQL version 5.7

Strategy 40

Strategy Storage Database Best Practices 40

SQL Performance

APRIL 14, 2021

I love Paul White ’s work. BatchMe ( col1 INT NOT NULL , INDEX idx_cs CLUSTERED COLUMNSTORE ) ; GO. -- Function definition. To enable batch processing, the solution uses a dummy left join with a false condition between the Nums CTE and a table called dbo.BatchMe, which has a columnstore index. DROP TABLE IF EXISTS dbo.

C++ 112

C++ Servers Testing Code 112

SQL Performance

JULY 1, 2019

The ANY aggregate is not something we can write directly in Transact SQL. The type of one row per group query that is subject to the ANY aggregate problem is the one where we do not care which particular row is selected from each group. The more complex SQL Server 2016+ plan does not use the ANY aggregate at all.

C++ 58

C++ Servers Database Performance 58

SQL Performance

FEBRUARY 10, 2021

I’ll do my testing in tempdb, enabling IO and TIME statistics: SET NOCOUNT ON ; USE tempdb ; SET STATISTICS IO , TIME ON ; The fastest solution from last month applies a join with a dummy table that has a columnstore index to get batch processing. There were no I/O operations involved in those solutions. BatchMe ; GO. CREATE TABLE dbo.

C++ 124

C++ Servers Testing Code 124

SQL Performance

APRIL 5, 2019

The IGNORE_DUP_KEY option for unique indexes specifies how SQL Server responds to an attempt to INSERT duplicate values: It only applies to tables (not views) and only to inserts. Any insert portion of a MERGE statement ignores any IGNORE_DUP_KEY index setting. Clustered unique index inserts. Article Summary.

Storage 53

Storage Servers Engineering Testing 53

SQL Performance

FEBRUARY 16, 2021

Oftentimes, when we write a stored procedure, we want it to behave in different ways based on user input. To be more precise, there are 4 different behaviors for this query: Return the top 10 rows for all customers sorted by OrderDate (the default behavior). Let's look at the following example: CREATE PROCEDURE. GetOrders. (

Cache 54

Cache Efficiency Code Performance 54

SQL Performance

DECEMBER 9, 2020

At the time of writing, T-SQL doesn’t provide such a built-in tool, but one can always hope and vote for such a tool to be added in the future. In a comment to my article, Marcos Kirchner mentioned that he tested my solution with varying table value constructor cardinalities, and got different execution times for the different cardinalities.

C++ 61

C++ Servers Testing Processing 61

The Morning Paper

FEBRUARY 23, 2020

A key challenge for a web of structured data is that it’s harder to see how it can be supported by advertising, so AnyLog needs a different incentive scheme. Note that AnyLog also differs from projects such as DeepDive or Google’s Knowledge Graph. This comes in the form of micropayments. Market participants.

Blockchain 94

Blockchain Internet Internet IoT 94

SQL Performance

MAY 17, 2021

This is the final part of a five-part series taking a deep dive into the way SQL Server row mode parallel plans start executing. Creates the pipes ( CXPipe ) that connect this task to one or more producer side tasks (depending on the type of exchange). Early phase processing is now complete.

C++ 139

C++ Processing Design Availability 139

SQL Performance

SEPTEMBER 2, 2021

SQL Server 2016 brought us STRING_SPLIT , a native function that eliminates the need for many of the custom solutions we’ve needed before. I’ve written several articles about this function (and STRING_AGG, which arrived in SQL Server 2017) since this post was written: Performance Surprises and Assumptions : STRING_SPLIT().

C++ 59

C++ Servers Testing Database 59