Scalegrid

JULY 13, 2020

Oracle Database is a commercial, proprietary multi-model database management system produced by Oracle Corporation, and the largest relational database management system (RDBMS) in the world. While Oracle remains the #1 database on the market, its popularity has steadily declined by over 18% since 2013.

Open Source 244

Open Source Tuning C++ Database 244

SQL Performance

SEPTEMBER 6, 2022

Let’s look at some examples using a fresh database. 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. ALTER DATABASE Escalation SET. DROP TABLE IF EXISTS dbo. ALTER TABLE dbo.

Database 98

Database Storage Testing Servers 98

SQL Performance

AUGUST 1, 2022

Recently I covered some of the T-SQL improvements in SQL Server 2022 here , and Aaron Bertrand covered additional improvements here. Microsoft just announced the release of SQL Server 2022 CTP 2.1. This release includes a number of additional interesting T-SQL improvements, which include: The distinct predicate.

Servers 142

Servers Testing C++ Code 142

SQL Performance

APRIL 13, 2022

It’s based on a question from a public database forum. Use the following code to create a table called T1 and populate it with a small set of sample data: SET NOCOUNT ON ; USE tempdb ; DROP TABLE IF EXISTS dbo. CREATE TABLE dbo. This index can potentially support window functions partitioned by grp and ordered by ord.

C++ 136

C++ Strategy Testing Servers 136

Percona

DECEMBER 14, 2022

This blog is the result of my investigation into column-wise tables. Column-wise tables! When to consider column-wise tables. Consider using the CITUS Columnar Extension under one or more of the following conditions: In regards to table attributes: they are wide with “many” columns. About columnar tables.

Database 93

Database Analytics Storage Processing 93

SQL Performance

SEPTEMBER 8, 2021

This article is the eleventh part in a series about table expressions. So far, I’ve covered derived tables and CTEs, and recently started the coverage of views. In Part 9 I compared views to derived tables and CTEs, and in Part 10 I discussed DDL changes and the implications of using SELECT * in the view’s inner query.

Servers 130

Servers Code Engineering Database 130

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. This month and the next I’m going to cover the physical processing aspects of derived tables. The physical treatment in other database platforms can be quite different.

C++ 109

C++ Database Servers Code 109

Percona

JANUARY 5, 2023

In this blog, I will share a solution to remove this space and remove rows from tables in a few seconds regardless of the size of a table without causing any additional load on the database using table partitions. The first approach that comes to anyone’s mind for deleting the row is using a DELETE query in SQL.

Traffic 60

Traffic Engineering Speed Storage 60

Percona

JULY 5, 2023

Long database service downtime Some queries become slow Applications stop working due to incompatibilities Data loss This is what can happen if MySQL’s major upgrade to 8.0 Now, the first issue with the logical dump restore approach, as somewhat totally expected though, is with system tables. Checking server version.

C++ 119

C++ Database Servers Testing 119

SQL Performance

SEPTEMBER 29, 2021

A common requirement in ETL and various reporting scenarios is to quietly load a SQL Server staging table in the background, so users querying the data aren’t impacted by the writes and vice-versa. Simplified Example of a Staging Table: A Farmer’s Market Analogy. So, what is a staging table in SQL?

Servers 55

Servers Games Database Processing 55

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. This month and the next I’m going to cover the physical processing aspects of derived tables. The physical treatment in other database platforms can be quite different.

C++ 93

C++ Database Servers Code 93

SQL Server According to Bob

SEPTEMBER 9, 2019

Previous posts have discussed partitioning, for example, a partitioned memory object [link] and how a latch maintains the desired access (exclusive, shared, …) [link]. Quick Refresher Over Partitioned Protection/Locking. Acquiring shared access requires only the local partition be acquired (lightweight scalability).

Servers 103

Servers Database Scalability Tuning 103

SQL Performance

AUGUST 14, 2019

This article is the fourth installment in a series about T-SQL bugs, pitfalls and best practices. In my examples I'll use a sample database called TSQLV5. You can find the script that creates and populates this database here , and its ER diagram here. DROP TABLE IF EXISTS dbo. Transactions ; CREATE TABLE dbo.

Best Practices 108

Best Practices Servers Database Azure 108

Percona

JULY 15, 2023

One very common problem when trying to optimize a database environment is index usage. Introduction to MySQL Indexes Indexes in MySQL are database structures used to optimize data retrieval speed and efficiency. They are a roadmap that speeds up the process of finding specific rows in a large table.

Virtualization 78

Virtualization Speed Database Efficiency 78

Percona

JANUARY 5, 2023

In PostgreSQL, vacuuming is a maintenance task that helps to optimize database performance and reclaim space. It involves removing deleted or outdated rows from tables and indexes and updating statistics used by the query planner. When a row is updated or deleted, it is not immediately removed from the table.

Database 107

Database Performance C++ Efficiency 107

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

Percona

AUGUST 5, 2023

So, What is MySQL Partitioning? 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. When partitioning in MySQL, it’s a good idea to find a natural partition key.

Strategy 40

Strategy Storage Database Best Practices 40

Percona

OCTOBER 2, 2023

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually. And did I mention they are open source?! virtual = 2.2G

Cache 101

Cache Database Virtualization Servers 101

SQL Performance

NOVEMBER 13, 2019

This could be across the entire query result, or within partitions. I'll use a table called T1 in my examples. Use the following code to create this table and populate it with sample data: SET NOCOUNT ON ; USE tempdb ; DROP TABLE IF EXISTS dbo. CREATE TABLE dbo. When order matters. ) ; INSERT INTO dbo. . (

Servers 104

Servers C++ Storage Performance 104

Particular Software

AUGUST 16, 2021

How do I move from Azure Tables to Cosmos DB? ?? Cosmos DB is the primary database service provided by Azure. Cosmos DB is a polyglot, NoSQL database that supports multiple APIs, but when people say “Cosmos DB,” they usually mean the Core (SQL) API. But what does it mean for a NoSQL database to have an SQL API?

Azure 52

Azure Serverless Storage Database 52

SQL Performance

FEBRUARY 3, 2020

In part 1 of this series, I tried out a few ways to compress a 1TB table. There I outlined a few of the things I thought might be performance issues, and laid out how I would better partition the destination table for optimal columnstore compression. First, I need to make changes to the batch table I created. BatchQueue.

Speed 113

Speed Processing Servers Storage 113

SQL Performance

APRIL 17, 2020

The ALTER TABLE. It is certainly more convenient than the alternative: Creating a new table and migrating the data each time a change is necessary. 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. Metadata-Only Changes.

Servers 63

Servers Database Testing Efficiency 63

SQL Performance

FEBRUARY 12, 2020

This month I describe powerful standard NULL handling features that have yet to make it to T-SQL, and the workarounds that people currently use. I'll continue using the sample database TSQLV5 like last month in some of my examples. You can find the script that creates and populates this database here , and its ER diagram here.

C++ 120

C++ Code Servers Database 120

SQL Server According to Bob

MAY 19, 2019

SQL Server will ship Azure SQL Database Edge: [link]. With the announcement I can tell you more about one of the things we have been working on; SQL Server running on IoT Edge and Developer machines in under 500MB of memory. The second tenant: Try to make the adjustment beneficial to any SQL Server tenant.

IoT 61

IoT Servers Development Cache 61

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. Like any hash join, an adaptive join reads all rows available on its build input and copies the required data into a hash table.

Servers 98

Servers Design Database Code 98

SQL Performance

SEPTEMBER 9, 2020

This article is the sixth part in a series about table expressions. I describe T-SQL’s support for recursive CTEs, as well as standard elements that are not yet supported by T-SQL. I do provide logically equivalent T-SQL workarounds for the unsupported elements. CREATE TABLE dbo. <table expression> ). <outer

C++ 143

C++ Code Servers Availability 143

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

JULY 1, 2019

The ANY aggregate is not something we can write directly in Transact SQL. The idea is to number the input set of rows using the ROW_NUMBER window function, partitioned by the grouping column or columns. One common approach is to reuse the PARTITION BY column(s) in the ORDER BY clause. This is where the problem might occur.

C++ 58

C++ Servers Database Performance 58

SQL Server According to Bob

SEPTEMBER 9, 2019

To track the login statistical information, enable the sp_configure value ‘common criteria compliance enabled ‘ setting and restart SQL Server. The information flow: Client sends login request to SQL Server. SQL Server creates new session object. LOGIN_STATS (Lock Type By Stat Row) – Partitioned.

Servers 40

Servers Software Engineering Database Engineering 40

SQL Performance

MAY 29, 2019

This post provides new information about the preconditions for minimally logged bulk load when using INSERT.SELECT into indexed tables. It can be activated from SQL Server 2008 to 2014 inclusive using documented trace flag 610. From SQL Server 2016 onward, FastLoadContext is enabled by default ; the trace flag is not required.

Cache 64

Cache Testing Servers Code 64

SQL Performance

MAY 13, 2020

Last month I provided a background to table expressions in T-SQL. I explained the context from relational theory and the SQL standard. I explained how a table in SQL is an attempt to represent a relation from relational theory. The expression can be a query, but doesn't have to be.

C++ 143

C++ Code Design Servers 143

SQL Performance

JANUARY 19, 2021

If you look at the definition for sys.partitions , it is basically what Jake described – a UNION ALL of all the columnstore and rowstore partitions, with THREE explicit references to sys.sysrowsets (abbreviated source here): CREATE VIEW sys. partitions AS. sysrowsets rs OUTER APPLY OpenRowset ( TABLE ALUCOUNT , rs.

Performance 55

Performance Database Systems Efficiency 55

SQL Performance

SEPTEMBER 16, 2021

To finish up my short series of articles on latches, this time I’m going to discuss a few other latches in SQL Server you might see occasionally but don’t merit a complete article by themselves. The post SQL Server Latches – Other Latches To Know About appeared first on SQLPerformance.com. The LOG_MANAGER Latch.

Servers 57

Servers Storage Efficiency Database 57

SQL Performance

APRIL 10, 2019

The ISO/IEC 9075:2016 standard, or SQL:2016 in short, introduces support for a profound concept called Row Pattern Recognition (RPR) in SQL. I posted a suggestion to improve SQL Server by adding support for RPR in T-SQL , but I was quite limited in the space that I had to provide the feedback.

C++ 73

C++ Virtualization Servers Code 73

HammerDB

MARCH 25, 2023

When you list the HammerDB directory you can now see a directory called “scripts” This directory contains CLI scripts to build, run, delete and query TPROC-C and TPROC-H workloads against all databases. If this value is greater or equal to 200 it will also partition the schema. ls -1 agent bin ChangeLog CODE_OF_CONDUCT.md

C++ 83

C++ Virtualization Benchmarking Database 83

SQL Performance

MAY 1, 2019

That said, this article is purely concerned with providing new details about minimal logging when bulk loading traditional (not “memory-optimized”) heap tables using INSERT.SELECT. Tables with a b-tree clustered index will be covered separately in my next post. Heap tables. An Exception to the Rule. TestHeap. (

Servers 47

Servers Database Testing Performance 47

SQL Server According to Bob

DECEMBER 18, 2018

SQL Server relies on Forced-Unit-Access (Fua) I/O subsystem capabilities to provide data durability, detailed in the following documents: SQL Server 2000 I/O Basic and SQL Server I/O Basics, Chapter 2. Be sure to deploy SQL Server 2017 CU6 or newer for best data durability and performance results. “.

Servers 90

Servers Media Cache Storage 90