SQL Performance

AUGUST 4, 2022

[ This series: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 ]. In part 5, I described how the early phases of query compilation affect simple parameterization. To recap: Normalization and decoding promote cached plan reuse. OPTION ( KEEP PLAN ) ; -- New. Users AS U.

Servers 61

Servers Cache Database Availability 61

SQL Performance

JUNE 8, 2022

[ This series: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 ]. In part 4, I described the role the parser plays turning a statement with constant values into a parameterized prepared statement. This tree does not contain SQL language elements. Maximizing Parameterized Plan Reuse.

Cache 54

Cache Servers Database Processing 54

SQL Performance

JULY 13, 2022

The same goes specifically for handling T-SQL querying tasks in terms of supported language elements. T-SQL, the dialect, supports a subset of the features from standard SQL. This could also happen if you need to migrate code written for another database platform with a different dialect of SQL to T-SQL.

C++ 112

C++ Code Azure Database 112

SQL Performance

JULY 8, 2020

This article is the fourth part in a series on table expressions. In Part 1 and Part 2 I covered the conceptual treatment of derived tables. In Part 3 I started covering optimization considerations of derived tables. All of this in attempt to find an optimal plan. Examine the plan for Query 1 shown in Figure 1.

Servers 110

Servers Code Processing Performance 110

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. Execution shuts down with a final recursive Close call.

Storage 94

Storage Transportation Engineering Processing 94

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

APRIL 20, 2021

This is the third in 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. The plan looks ‘normal’ in all respects. Parent task.

C++ 98

C++ Processing Servers Cache 98

SQL Performance

OCTOBER 7, 2020

Rewinds are specific to operators on the inner side of a nested loops join or apply. 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. Row Count Spool. Remote Query.

Servers 106

Servers Cache Azure Performance 106

SQL Performance

MAY 6, 2021

This is the fourth part in 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 C execution details.

C++ 95

C++ Azure Servers Processing 95

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. CQScanProfileNew::Open (profiler for node 4).

C++ 139

C++ Processing Design Availability 139

SQL Performance

MAY 20, 2022

[ This series: Part 1 | Part 2 | Part 3 | Part 4 ]. 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. The Compilation Process.

Cache 55

Cache Database Servers Testing 55

SQL Performance

MARCH 8, 2021

SQL Server 2014 SP2 and later produce runtime (“actual”) execution plans that can include elapsed time and CPU usage for each execution plan operator (see KB3170113 and this blog post by Pedro Lopes). Batch Mode Serial Execution. Compatibility level 150 is used exclusively. TheYear , CA.

Servers 95

Servers Tuning Architecture Processing 95

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

DECEMBER 9, 2020

In Part 5 of my series on table expressions I provided the following solution for generating a series of numbers using CTEs, a table value constructor and cross joins: DECLARE @low AS BIGINT = 1001 , @high AS BIGINT = 1010 ; WITH. Make sure that you enable Discard results after execution in your SSMS Query Options dialog under Grid, Results.

C++ 114

C++ Servers Testing Processing 114

SQL Performance

APRIL 14, 2021

This is the fourth part in a series about solutions to the number series generator challenge. Let’s examine the plans for two of the earlier solutions that utilized batch processing. In Part 1 I covered the function dbo.GetNumsAlanCharlieItzikBatch, which combined ideas from Alan, Charlie and myself. I love Paul White ’s work.

C++ 112

C++ Servers Testing Code 112

SQL Performance

MARCH 10, 2021

This is the third part in a series about solutions to the number series generator challenge. In Part 1 I covered solutions that generate the rows on the fly. In Part 2 I covered solutions that query a physical base table that you prepopulate with rows.

C++ 134

C++ Testing Code Servers 134

SQL Performance

JUNE 10, 2020

In Part 1 and Part 2 of this series, I covered the logical, or conceptual, aspects of named table expressions in general, and derived tables specifically. Recall from Part 1 the physical data independence principle of relational theory. Does it get persisted on disk, and how does SQL Server handle memory for it?

C++ 109

C++ Database Servers Code 109

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. Figure 4: Amazon RDS Read Replicas Which option is better?

Cloud 120

Cloud Serverless AWS Storage 120

Percona

JANUARY 4, 2023

Instruments are the main part of the performance schema. Which IO operation is causing MySQL to slow down? At which execution stage is a query taking time, or how much time will an alter command will take? At which execution stage is a query taking time, or how much time will an alter command will take?

Performance 98

Performance C++ Monitoring Servers 98

SQL Performance

FEBRUARY 10, 2021

This is the second part in a series about solutions to the number series generator challenge. There were no I/O operations involved in those solutions. You can observe the highest waits by looking at the properties page of the root node of the actual query plan, or run an extended events session with wait info.

C++ 124

C++ Servers Testing Code 124

Percona

DECEMBER 14, 2022

CAVEAT: You cannot perform UPDATE, DELETE operations on a columnar table. 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. OLTP vs OLAP.

Database 93

Database Analytics Storage Processing 93

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. Just before we get started, I want to stress an important point: There are two distinct types of nested loop join in execution plans.

Performance 104

Performance Cache Servers Education 104

SQL Performance

JUNE 10, 2020

In Part 1 and Part 2 of this series, I covered the logical, or conceptual, aspects of named table expressions in general, and derived tables specifically. Recall from Part 1 the physical data independence principle of relational theory. Does it get persisted on disk, and how does SQL Server handle memory for it?

C++ 93

C++ Database Servers Code 93

SQL Performance

OCTOBER 14, 2020

This article is the 7th part of a series about named table expressions. In Part 5 and Part 6 I covered the conceptual aspects of common table expressions (CTEs). In Part 4 of the series, which focused on optimization of derived tables, I described a process of unnesting/substitution of table expressions.

C++ 52

C++ Servers Code Efficiency 52

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. If it can internally guarantee ordering in other ways, the Sort may be omitted.

Cache 67

Cache Servers Testing Storage 67

SQL Performance

MARCH 17, 2020

Back in 2014, I wrote an article called Performance Tuning the Whole Query Plan. This follow-up post revisits the question for SQL Server 2019, using a larger number of rows. It has 32GB RAM, with 24GB available to the SQL Server 2019 instance. All the operators in this plan use row mode execution.

Servers 86

Servers Tuning Database Efficiency 86

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. For EKS, install the OneAgent Operator.

AWS 126

AWS Best Practices Database Traffic 126

SQL Performance

JANUARY 13, 2021

It made me realize how great it is to be part of a community, and that amazing things can be achieved when a group of smart people joins forces. ORDER BY rownum ; I used the following code to test the function with "Discard results after execution" enabled in SSMS: SELECT n FROM dbo. The responses were overwhelming.

C++ 113

C++ Code Testing Processing 113

SQL Performance

NOVEMBER 11, 2020

This article is the eighth part in a series about table expressions. Run it for any amount of time that you like, but eventually you will need to stop the code execution. It's interesting to try and identify the cases where you have a single versus multiple evaluations of the underlying expression in the query execution plan.

C++ 122

C++ Code Storage Processing 122

SQL Performance

OCTOBER 4, 2022

In part one of this series , I explained how the lock manager maintains a count of held locks per transaction , and individual access methods keep a separate lock count per HoBt for each plan operator performing data access. Test 3—20,000 rows at DOP 4. Now run the test with 20,000 rows: EXECUTE dbo. Parallelism.

Testing 56

Testing Processing Servers Engineering 56

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

SQL Performance

DECEMBER 11, 2019

NULL handling is one of the trickier aspects of data modeling and data manipulation with SQL. Even among people who do have a good grasp of relational theory and SQL, you will hear very strong opinions both in favor and against using NULLs in your database. In SQL, a NULL is a marker, or a placeholder, for a missing value.

Best Practices 117

Best Practices C++ Database Code 117

SQL Performance

AUGUST 14, 2019

This article is the fourth installment in a series about T-SQL bugs, pitfalls and best practices. There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. Figure 1: Plan for Query 1, row-mode processing.

Best Practices 108

Best Practices Servers Database Azure 108

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.

Servers 67

Servers Testing Metrics Storage 67

SQL Performance

DECEMBER 9, 2020

In Part 5 of my series on table expressions I provided the following solution for generating a series of numbers using CTEs, a table value constructor and cross joins: DECLARE @low AS BIGINT = 1001 , @high AS BIGINT = 1010 ; WITH. Make sure that you enable Discard results after execution in your SSMS Query Options dialog under Grid, Results.

C++ 61

C++ Servers Testing Processing 61

SQL Performance

SEPTEMBER 12, 2019

This article is the fifth part in a series about T-SQL bugs, pitfalls and best practices. When people want to pivot data using T-SQL, they either use a standard solution with a grouped query and CASE expressions, or the proprietary PIVOT table operator. Figure 1: Plan for pivot query with implicit grouping.

Best Practices 103

Best Practices C++ Code Design 103

SQL Performance

AUGUST 12, 2020

This article is the fifth part in a series about table expressions. In Part 1 I provided the background to table expressions. In Part 2 , Part 3 , and Part 4 , I covered both the logical and the optimization aspects of derived tables. T-SQL supports it starting with SQL Server 2005.

C++ 140

C++ Code Design Database 140