HammerDB

OCTOBER 23, 2023

HammerDB uses stored procedures to achieve maximum throughput when benchmarking your database. HammerDB has always used stored procedures as a design decision because the original benchmark was implemented as close as possible to the example workload in the TPC-C specification that uses stored procedures.

Benchmarking 62

Benchmarking Database Network C++ 62

Percona

JANUARY 3, 2023

I thought it was easy that we can use quotes or backtick symbols to specify the table to drop. sec) mysql> Drop table `#Tableau_01__Test`; Query OK, 0 rows affected (0.01 In this blog post, I will showcase an issue we faced when dropping a table and how it was resolved. The following example recreates the problem.

Database 77

Database Servers Storage Systems 77

Percona

JUNE 1, 2023

Hello friends, on certain occasions, some clients, for whatever reason, ask us to migrate a supposedly “equivalent” brand of an engine in terms of belonging to the MySQL family or ecosystem. In this case, we will be analyzing the migration of MariaDB to Percona Server for MySQL 8 in particular. Simple, right?

Servers 75

Servers Database Testing Open Source 75

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. I will be using the 50GB Stack Overflow 2013 database , but any large table with a low number of distinct values would do. Test Environment.

Servers 86

Servers Tuning Database Efficiency 86

SQL Performance

JUNE 10, 2020

The relational model and the standard querying language that is based on it are supposed to deal only with the conceptual aspects of the data and leave the physical implementation details like storage, optimization, access and processing of the data to the database platform (the implementation ).

C++ 109

C++ Database Servers Code 109

SQL Performance

APRIL 13, 2022

The challenge itself isn’t complicated to handle when using well-known techniques, which primarily employ window functions. 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. And I found such a solution. The challenge.

C++ 136

C++ Strategy Testing Servers 136

Percona

FEBRUARY 20, 2023

Introduction/TLDR: We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools.

Monitoring 84

Monitoring Servers Code Systems 84

SQL Performance

JUNE 10, 2020

The relational model and the standard querying language that is based on it are supposed to deal only with the conceptual aspects of the data and leave the physical implementation details like storage, optimization, access and processing of the data to the database platform (the implementation ).

C++ 93

C++ Database Servers Code 93

SQL Performance

AUGUST 11, 2021

Often the input is time series data stored in a table where the rows represent measurements taken at regular time intervals. For example, the measurements could be temperature and humidity readings taken every 5 minutes, and you want to group the data using hourly buckets and compute aggregates like average per hour. DATE_BUCKET.

Azure 139

Azure Code Database Servers 139

SQL Performance

APRIL 13, 2020

In my last post , I started to outline the process I go through when tuning queries – specifically when I discover that I need to add a new index, or modify an existing one. Up to this point we've identified the problematic query, the index I need, what indexes currently exist on the table, and whether or not those indexes are being used.

Tuning 137

Tuning Cache Metrics Testing 137

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 my examples I'll use a sample database called TSQLV5.

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. In my examples I'll use a sample database called TSQLV5. 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.

Best Practices 108

Best Practices Servers Database Azure 108

SQL Performance

OCTOBER 21, 2021

NULL complexities – Part 3, Missing standard features and T-SQL alternatives. Well, I’ve seen this in current systems where they use some arbitrary-sounding date in views to act as a magic filter, only presenting rows where the value can be trusted. I’m sorry, but that’s not a good reason. NULL complexities – Part 2. How about -1?

Servers 103

Servers Design Speed Code 103

Percona

DECEMBER 15, 2022

In this multi-part blog series, we will start with an overview of using JSON in a relational database. And if you try to put in a string to that column of integers the server will refuse to accept that row of data. So many folks were surprised when relational databases started adding a JSON data type to their products. Why not JSON?

Database 92

Database Servers Processing Efficiency 92

The Morning Paper

FEBRUARY 23, 2020

The Web provides decentralised publishing and direct access to unstructured data ( searching / querying that data has turned out to be a pretty centralised affair in practice though). Micropayments require an environment of trust, and in a decentralised context that leads us to blockchains. This comes in the form of micropayments.

Blockchain 94

Blockchain Internet Internet IoT 94

SQL Performance

FEBRUARY 22, 2021

I would like to share a couple of recent examples that help illustrate how using deprecated SQL Server features continues to bite us. The official documentation for sys.sysprocesses warns: This SQL Server 2000 system table is included as a view for backward compatibility. A recent example. sysprocesses.

Latency 57

Latency Servers Monitoring Systems 57

Percona

AUGUST 17, 2023

What is PostgreSQL used for? It’s used for data management (shocker), application development, and data analytics. In another survey, using multiple metrics for overall popularity, PostgreSQL ranks as the fourth most popular database management system ( DB-Engines, March 2023 ). Is PostgreSQL enterprise-ready?

Open Source 80

Open Source Healthcare Social Media Database 80

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. So, what is a staging table in SQL? So, what is a staging table in SQL? Refreshing Tables in SQL Server.

Servers 55

Servers Games Database Processing 55

SQL Performance

AUGUST 2, 2019

I have been making the same recommendations about tempdb since I started working with SQL Server over 15 years ago, when I was working with customers running version 2000. From the customer side, this has been the limit of what can be done*, until SQL Server 2019. I have SQL Server 2019 CTP 3.2

Servers 95

Servers C++ Testing Systems 95

SQL Performance

AUGUST 27, 2021

Continuing my series of articles on latches, this time I’m going to discuss the APPEND_ONLY_STORAGE_INSERT_POINT latch and show how it can be a major bottleneck for heavy update workloads where either form of snapshot isolation is being used. The current record is copied into the version store. The record is changed.

Database 56

Database Storage Availability Servers 56

Smashing Magazine

SEPTEMBER 28, 2022

There are millions of sites, and you are in close competition with every one of those Google search query results. The resource loading waterfall is a cascade of files downloaded from the network server to the client to load your website from start to finish. On your first try, you can use it as a benchmark for optimizations later.

Performance 125

Performance Cache Servers Social Media 125

SQL Performance

JULY 1, 2021

I've recently discussed a few features that Microsoft advises against using, and that I think you should forget exist, too. My latest run-in with things best forgotten is a new stored procedure with an ntext parameter. My latest run-in with things best forgotten is a new stored procedure with an ntext parameter.

C++ 58

C++ Database Servers Performance 58

SQL Performance

OCTOBER 20, 2022

The examples so far in this series have all used repeatable read isolation to acquire shared locks on qualifying rows and hold them to the end of the transaction. This was convenient for the demos but repeatable read isolation isn’t common in everyday use. SQL Server solves this problem using lock classes.

Testing 91

Testing Servers Cache Engineering 91

The Morning Paper

MARCH 10, 2020

Or in the case of key-value stores, what you benchmark. So if you want to design a system that will offer good real-world performance, it’s really useful to have benchmarks that accurately represent real-world workloads. That matters if your store reads data in (multi-key) blocks. FAST’20. RocksDB essentials.

Benchmarking 59

Benchmarking Storage Cache Open Source 59

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. Ultimately, tables are stored as a sequence of bytes with some metadata elsewhere in the system to describe what each of those bytes mean, and how they relate to each of the table's various columns.

Servers 63

Servers Database Testing Efficiency 63

Percona

SEPTEMBER 1, 2023

Depending on the version of MySQL you are running , some of the default values used in this post may differ from your install, but the premise is still largely the same. This results in expedited query execution, reduced resource utilization, and more efficient exploitation of the available hardware resources.

Tuning 52

Tuning Database Performance Hardware 52

SQL Performance

MARCH 2, 2020

While Profiler is its own special nightmare , today I wanted to talk about SQL Server's default trace. While Profiler is its own special nightmare , today I wanted to talk about SQL Server's default trace. ( SELECT Context = CASE. CASE WHEN LEFT ( ObjectName , 8 ) = N'_WA_SYS_'.

Servers 89

Servers Database Code Systems 89

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

JANUARY 13, 2021

As a quick reminder, the functions I covered last month use a base CTE that defines a table value constructor with 16 rows. The functions use a series of cascading CTEs, each applying a product (cross join) of two instances of its preceding CTE. So I’ll split the coverage to several articles. BatchMe ON 1 = 0.

C++ 113

C++ Code Testing Processing 113

High Scalability

SEPTEMBER 3, 2019

Redis , short for Remote Dictionary Server, is a BSD-licensed, open-source in-memory key-value data structure store written in C language by Salvatore Sanfillipo and was first released on May 10, 2009. Instead, Redis stores data in data structures which makes it very flexible to use. Data Structures in Redis.

Open Source 55

Open Source Cache C++ Database 55

SQL Performance

APRIL 26, 2019

If so, you may have heard about the Azure SQL Database DTU calculator , and you may have also read about how it has been reverse engineered by Andy Mallon. In a lot of cases, many DBAs have some flavor of monitoring tool that is already capturing performance data for them. Database – Log Bytes Flushed/sec. PerformanceAnalysisCounter.

Azure 40

Azure Database Metrics Servers 40

SQL Performance

JUNE 14, 2019

This article is the third installment in a series about T-SQL bugs, pitfalls and best practices. In my examples I’ll use a sample database called TSQLV5. All of these bugs are related to T-SQL querying fundamentals, and are easy to avoid if you follow simple best-practices. Previously I covered determinism and subqueries.

Best Practices 93

Best Practices C++ Database Processing 93

SQL Performance

MAY 22, 2019

It’s one of the things we look at during a health audit, and Kimberly has a great query from her Plan cache and optimizing for adhoc workloads post that’s part of our toolkit. I’ve copied the query below, and if you’ve never run it against any of your production environments before, definitely find some time to do so.

Cache 54

Cache Performance Hardware Servers 54

SQL Performance

JUNE 5, 2019

One of the more perplexing problems to troubleshoot in SQL Server can be those related to memory grants. Some queries need more memory than others to execute, based on what operations need to be performed (e.g. There are multiple ways to attack this issue, and one of my new favorite methods is to use Query Store.

Servers 41

Servers Cache Database Metrics 41

SQL Performance

JANUARY 19, 2021

sys.partitions appears to be a UNION ALL of two result sets (row store and columnstore) and most of my queries are resulting in two scans of sysrowsets. Is there any filter I can put on a query of sys.partitions if I know the row I’m looking for is rowstore? Running the following simple query: SELECT *. partitions AS.

Performance 55

Performance Database Systems Efficiency 55

SQL Performance

JUNE 17, 2021

Most databases should make use of foreign keys to enforce referential integrity (RI) wherever possible. However, there is more to this decision than simply deciding to use FK constraints and creating them. This applies whether you are using locking read committed or the versioning-based read committed snapshot isolation (RCSI).

Database 57

Database Servers Testing Azure 57

The Morning Paper

SEPTEMBER 8, 2019

Experiences with approximating queries in Microsoft’s production big-data clusters Kandula et al., I’ve been excited about the potential for approximate query processing in analytic clusters for some time, and this paper describes its use at scale in production. Approximate query support. VLDB’19. Universe(0.5,

Big Data 57

Big Data Analytics Latency Azure 57