SQL Performance

AUGUST 4, 2022

To recap: Normalization and decoding promote cached plan reuse. Shell plans optimize for very frequent execution of simple statements, bypassing the parsing, parameter replacement, normalization, and decoding stages. The latter is common for SQL generated by applications and frameworks but also often occurs in dynamic queries.

Servers 61

Servers Cache Database Availability 61

SQL Performance

MAY 12, 2021

In Part 1 , Part 2 , Part 3 and Part 4 I covered pure T-SQL solutions. Before I cover the CLR-based solutions, let’s quickly review the performance of two of the best performing T-SQL solutions. The execution of this function doesn’t produce any logical reads, of course. sp_configure 'clr enabled' , 1 ; EXEC sys.

C++ 111

C++ Servers Database Code 111

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. One thing I want you to bear in mind throughout this piece is an adaptive join always starts executing as a batch mode hash join.

Servers 98

Servers Design Database Code 98

SQL Performance

APRIL 18, 2022

Execution Plans. It’s more complicated than you might expect to tell from the information provided in execution plans if a SQL statement uses simple parameterization. It’s no surprise even highly experienced SQL Server users tend to get this wrong, given the contradictory information often supplied to us.

Cache 58

Cache Servers Database Testing 58

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. Using a SQL analogy, x IN (4, 8) is logically the same as writing x = 4 OR x = 8.

Cache 54

Cache Servers Database Processing 54

Percona

OCTOBER 20, 2023

This is how Citus defines sharding: “Sharding is a technique used in database systems and distributed computing to horizontally partition data across multiple servers or nodes. “Distribute Tables”, URL There are three types of tables in a Citus cluster, each serving a distinct purpose. . 1ubuntu1~20.04.1)

Scalability 94

Scalability Database Open Source Operating System 94

SQL Performance

MARCH 22, 2022

This is the first part of a series about simple parameterization and trivial plans. In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache. It's almost always better to explicitly parameterize statements, rather than relying on the server to do it. Shell Plans.

Cache 61

Cache Servers Database Code 61

SQL Performance

SEPTEMBER 6, 2022

I’ve seen suggestions such as lock escalation isn’t deterministic, or some types of locks don’t count toward the 5000 lock threshold. I happen to be using SQL Server 2019 CU16 but the details I’ll describe haven’t materially changed since partition level lock escalation was added to SQL Server 2008. EXECUTE dbo.

Database 98

Database Storage Testing 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. All of this in attempt to find an optimal plan. Examine the plan for Query 1 shown in Figure 1. Figure 1: Plan for Query 1.

Servers 110

Servers Code Processing Performance 110

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. As mentioned, the solution should support delimiters of the BIGINT type, but you can assume a maximum series cardinality of 4,294,967,296. Figure 1: Plan for base CTE cardinality of 2.

C++ 114

C++ Servers Testing Processing 114

SQL Performance

OCTOBER 7, 2020

The idea is to reuse previously-computed results from part of an execution plan where it is safe to do so. The canonical example of a plan operator that can rewind is the lazy Table Spool. Replaying rows can be cheaper than re-executing the subtree that generated them. Sort types. Uses standard library quick sort.

Servers 106

Servers Cache Azure Performance 106

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 121

Cloud Serverless AWS Storage 121

Dynatrace

AUGUST 12, 2019

With OneAgent installed on an application server, Davis, the Dynatrace AI causation engine, continuously analyzes all database statements within the context of your applications. Dynatrace helps you identify common problems like: N+1 issues – when the same query is executed multiple times. Dynatrace news.

Database 128

Database Metrics Cache Servers 128

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. EXECUTE sys. EXECUTE sys. Names of tables and columns haven’t been resolved to database objects yet, and no type information is available.

Cache 55

Cache Database Servers Testing 55

SQL Performance

SEPTEMBER 25, 2019

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. To be clear, this difference is about execution plan operators , not T-SQL query syntax. Performance Spools.

Performance 104

Performance Cache Servers Education 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. By the end of the first part, we had created execution context zero for the parent task. Iterative Execution. SQL Server executes a query through a process known as a query scan.

Storage 94

Storage Transportation Engineering Processing 94

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. WHERE 1 = ( SELECT 1 ) -- Avoid trivial plan. c1 = @Start + N.

Performance 81

Performance C++ Storage Servers 81

SQL Performance

FEBRUARY 10, 2021

Last month I used the following code to test the function's performance with 100M rows, after enabling the Discard results after execution in SSMS to suppress returning the output rows: SELECT n FROM dbo. The plans for both queries—the one without ordering and the one with ordering are the same, so performance tends to be similar.

C++ 124

C++ Servers Testing Code 124

Percona

JUNE 1, 2023

In this case, we will be analyzing the migration of MariaDB to Percona Server for MySQL 8 in particular. the same does not happen with MariaDB, which from a specific version, we could say in some way, distanced itself from MySQL and Percona Server for MySQL. With all this said, what would be the steps to follow at a general plan level?

Servers 74

Servers Database Testing Open Source 74

Percona

JANUARY 4, 2023

At which execution stage is a query taking time, or how much time will an alter command will take? Instruments are a combination of different sets of components like wait, io, sql, binlog, file, etc. Instruments are a combination of different sets of components like wait, io, sql, binlog, file, etc.

Performance 98

Performance C++ Monitoring Servers 98

SQL Performance

APRIL 14, 2021

Let’s examine the plans for two of the earlier solutions that utilized batch processing. GetNumsAlanCharlieItzikBatch ( 1 , 100000000 ) OPTION ( MAXDOP 1 ) ; The Plan Explorer depiction of the actual plan for this execution is shown in Figure 1. Figure 1: Plan for dbo.GetNumsAlanCharlieItzikBatch function.

C++ 112

C++ Servers Testing Code 112

SQL Performance

OCTOBER 26, 2021

The SQL Server query optimizer makes use of statistics during query compilation to help determine the optimal query plan. Synchronously updating statistics before compilation obviously introduces a delay and makes the query take longer to compile and execute. Introduction. The Problem With Synchronous Statistics Updates.

Servers 55

Servers Metrics Database Performance 55

SQL Performance

OCTOBER 21, 2021

NULL complexities – Part 3, Missing standard features and T-SQL alternatives. [ type ] IN ( N'P' , N'FN' , N'V' ) AND OBJECT_DEFINITION ( o. I always hear “disk space is cheap” when we talk about data type choices, fragmentation, and token values vs. NULL. Query Execution. NULL complexities – Part 2.

Servers 103

Servers Design Speed Code 103

SQL Performance

NOVEMBER 13, 2019

With the former, you just don't care in what order they are assigned, and whether repeated executions of the query keep assigning the same row numbers to the same rows or not. With the latter, you expect repeated executions to keep changing which rows get assigned with which row numbers.

Servers 104

Servers C++ Storage Performance 104

Percona

DECEMBER 14, 2022

In the database world there are essentially two types of database operations: Online Transaction Processing , OLTP : Online transaction processing applications have high throughput and are insert- or update-intensive in database management. Access method: heap Table "public.t2" Column | Type | Collation | Nullable | Default. --+ + --+ -+.

Database 93

Database Analytics Storage Processing 93

SQL Performance

MAY 16, 2019

An ORDER hint is required to ensure rows arrive at the Clustered Index Insert plan operator in target index key order. Without this guarantee, SQL Server might add index rows that are not sorted correctly, which would not be good. The TABLOCK hint is required to enable the RowSetBulk facility as used for heap table bulk loads.

Cache 67

Cache Servers Testing Storage 67

The Morning Paper

FEBRUARY 20, 2020

A stored model / pipeline can then be invoked a bit like a stored procedure by issuing SQL commands. At some later point a SQL query is issued which SELECT s a model and then uses the PREDICT function to generate a prediction from the model given some input data (which is itself of course the result of a query). An INSERT INTO model.

Processing 72

Processing Hardware Database Azure 72

IO River

NOVEMBER 15, 2023

They check who's coming and going based on broad criteria, like the type of vehicle or the road they're using. OWASP provides a baseline defense against the most critical web application security risks, such as SQL injection, cross-site scripting, and more.‍What SQL Injection‍SQL Injection is a classic but ever-relevant threat.

Traffic 52

Traffic Network Logistics Architecture 52

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. Part 1 initialized execution context zero for the parent task, and part 2 created the query scan tree. Branch B Parallel Tasks Start. Branch C (additional parallel tasks).

C++ 139

C++ Processing Design Availability 139

SQL Performance

AUGUST 23, 2019

In traditional row-mode execution plans, SQL Server may introduce a Bitmap operator as part of performing early semi join reduction before a parallel hash or merge join. There have been major improvements since the first appearance of the batch mode execution engine in SQL Server 2012. Background.

Servers 75

Servers Engineering Efficiency Retail 75

SQL Performance

APRIL 23, 2019

In SQL Server 2012, grouped (vector) aggregation was able to use parallel batch-mode execution, but only for the partial (per-thread) aggregate. SQL Server 2014 added the ability to perform parallel batch-mode grouped aggregation within a single Hash Match Aggregate operator. Introduction. Columnstore storage.

Servers 67

Servers Testing Metrics Storage 67

Scalegrid

JANUARY 8, 2024

These enhancements are particularly evident in areas such as: Customer service Marketing Enterprise resource planning (ERP) Supply chain management By processing and analyzing real-time data, AI revolutionizes business operations, increasing their strategic value and efficiency.

Artificial Intelligence 147

Artificial Intelligence Cloud Scalability Analytics 147

SQL Performance

MARCH 5, 2020

A few weeks ago, an interesting question was asked on the #SQLHelp hash tag on Twitter about the impact of execution plans on the ASYNC_NETWORK_IO wait type, and it generated some differing opinions and a lot of good discussion. To summarize: Focusing on ASYNC_NETWORK_IO waits alone as a tuning metric is a mistake. FILLER , t2.

Education 55

Education Servers Testing Tuning 55

Testlodge

APRIL 14, 2021

However, configuration testing is a time-consuming technique, so the team must create a workable plan. The three-tier application should have client, server, and database layer, and each part of it needs to work well across the various platforms. The server layer will be able to work with Windows Server and Ubuntu Server.

Testing 89

Testing Hardware Virtualization Operating System 89

Dynatrace

APRIL 16, 2019

Many of our customers also run their.NET, ASP.NET,NET Core, SQL Server, CRM, SharePoint … applications on AWS and have reached out to us in the past to ask about best practices around optimizing these workloads in order to run more efficiently and more cost effective. 2: Post-Deployment Validation: Are all services updated?

AWS 127

AWS Best Practices Database Traffic 127

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. As mentioned, the solution should support delimiters of the BIGINT type, but you can assume a maximum series cardinality of 4,294,967,296. Figure 1: Plan for base CTE cardinality of 2.

C++ 61

C++ Servers Testing Processing 61

Percona

AUGUST 1, 2023

If you plan to migrate your database from on-prem servers to RDS (either Aurora or MySQL RDS), you usually don’t have much choice but to do so using logical backups such as mysqldump, mysqlpump, mydumper , or similar. Hello friends! or MariaDB 10.X or MariaDB 10.X If you want to create the same lab, you can find it here.

AWS 71

AWS Servers Engineering Strategy 71