Percona

DECEMBER 5, 2022

Type 'c' to clear the current input statement. mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'G 1. sec) mysql> SELECT @@audit_log_include_accounts; + + | @@audit_log_include_accounts | + + | test1@%,test2@% | + + 1 row in set (0.00 Type 'help;' or 'h' for help. What to look for.

Open Source 67

Open Source C++ Servers Database 67

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. Window functions can appear only in a query's SELECT and ORDER BY clauses.

C++ 143

C++ Code Design Servers 143

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. A lock class is a container for locks needing a lifetime extension within the current statement.

Testing 91

Testing Servers Cache Engineering 91

SQL Performance

MARCH 29, 2022

When SQL Server applies simple parameterization to an ad-hoc statement, it makes a guess about the data type of the replacement parameter. For the time being, let’s look at some examples using the Stack Overflow 2010 database on SQL Server 2019 CU 14. This paragraph explains the data types selected in the prior examples.

Cache 90

Cache Servers Database Testing 90

SQL Performance

APRIL 13, 2022

L0 AS ( SELECT 1 AS c. T1 WITH ( TABLOCK ) ( grp , ord , val ) SELECT. I used a SELECT INTO statement to write the result into a temporary table. My test machine has four logical CPUs, running SQL Server ® 2019 Enterprise. INSERT INTO dbo. T1 ( grp , ord , val ) VALUES. (

C++ 136

C++ Strategy Testing Servers 136

Percona

JULY 19, 2023

How would you integrate some new fancy processing library into MySQL without having to recreate the complexities in pure SQL? sec) mysql> SELECT ULID(); + + | ULID() | + + | 0x018931B0EA52D664C7CBE83255C3468D | + + 1 row in set (0.00 MySQL Loadable Functions would be the way to go. cd ~/percona-server-8.0.32-24/plugin/ulid/

C++ 78

C++ Servers Benchmarking Best Practices 78

Percona

JULY 27, 2023

SQL > EXPLAIN FORMAT=JSON INTO @myvar SELECT * FROM City WHERE District='Texas'; Query OK, 0 rows affected (0.0043 sec) SQL > SELECT @myvarG 1. You can see the parse tree for selects if you build the server with debug mode enabled. has EXPLAIN FORMAT=JSON that can output data into a user variable.

Servers 81

Servers Open Source Tuning Cache 81

SQL Performance

OCTOBER 9, 2019

The relevance to T-SQL is that sometimes we assume that we know everything there is to know about certain T-SQL features, and don't always bother checking the documentation to see if there's more to them. The EOMONTH function was introduced in SQL Server 2012. In my examples I'll use a sample database called TSQLV5.

C++ 111

C++ Database Servers Code 111

Percona

OCTOBER 10, 2023

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. Type 'c' to clear the current input statement. Type 'c' to clear the current input statement. Type 'c' to clear the current input statement. Type 'c' to clear the current input statement. Type 'help;' or 'h' for help.

Database 92

Database Performance C++ Engineering 92

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

SQL Performance

JANUARY 12, 2022

Before you start writing the T-SQL code with solutions based on the interval modeling idea, you should already have an intuitive sense for what indexes are likely to be useful here. Auctions ( ID ) WHERE ID = - 1 AND ID = - 2 ; You can now start working on the solution's T-SQL code. Auctions ; SET IDENTITY_INSERT dbo.

Code 116

Code Servers Performance Azure 116

SQL Performance

JANUARY 8, 2020

Last month I introduced the NULL as SQL's marker for any kind of missing value. I explained that SQL doesn't provide you with the ability to distinguish between missing and applicable (A-values) and missing and inapplicable (I-values) markers. This article is the second in a series on NULL complexities.

Design 65

Design C++ Code Database 65

SQL Performance

JUNE 10, 2020

In my coverage of the physical treatment of named table expressions in the series I focus on the treatment in Microsoft SQL Server and Azure SQL Database. Recall that what triggered this series is some confusion that exists in the SQL Server community around named table expressions.

C++ 109

C++ Database Servers Code 109

SQL Performance

AUGUST 27, 2021

When you enable one of the two forms of versioning, SQL Server uses a mechanism called versioning to preserve pre-change versions of a record in the version store in tempdb. For instance, imagine a database that had read committed snapshot enabled, so each statement is guaranteed to see the records as of the time the statement started.

Database 56

Database Storage Availability Servers 56

SQL Performance

JUNE 10, 2020

In my coverage of the physical treatment of named table expressions in the series I focus on the treatment in Microsoft SQL Server and Azure SQL Database. Recall that what triggered this series is some confusion that exists in the SQL Server community around named table expressions.

C++ 93

C++ Database Servers Code 93

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. After all, we explicitly do not care which row is selected. ( SELECT. ORDER BY D.

C++ 58

C++ Servers Database Performance 58

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 system table sys.sysprocesses was replaced way back in SQL Server 2005 by a set of dynamic management views (DMVs), most notably sys.dm_exec_requests , sys.dm_exec_sessions , and sys.dm_exec_connections.

Latency 57

Latency Servers Monitoring Systems 57

SQL Performance

MAY 16, 2019

It states that TABLOCK and ORDER hints are required, with a note that says: If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. Without this guarantee, SQL Server might add index rows that are not sorted correctly, which would not be good.

Cache 67

Cache Servers Testing Storage 67

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 Now that 2019 CTP 3.2

Servers 95

Servers C++ Testing Systems 95

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. CREATE TABLE dbo. t1 REBUILD WITH ( ONLINE = ON ) ; GO.

C++ 58

C++ Database Servers Performance 58

SQL Performance

MAY 7, 2020

The tipping point is a term I first heard used by SQL Server performance tuning guru and long-time SentryOne Advisory Board member Kimberly Tripp – she has a great blog series on it here. Simply select either the Compiled Value or Runtime Value column to see the selected range: Histogram chart showing the range hit by the runtime parameters.

Tuning 69

Tuning Servers Database Performance 69

SQL Performance

JUNE 17, 2021

Both can experience blocking when foreign key relationships are checked by the SQL Server engine. For SQL Server the default is locking read committed; Azure SQL Database uses RCSI as the default. ) ; This insert statement will block , whether you chose locking or versioning read committed isolation for this test.

Database 57

Database Servers Testing Azure 57

The Morning Paper

SEPTEMBER 8, 2019

In particular, support for expressing sampling requirements as part of SELECT statements. For example, here’s the original ‘query #5’ from TPC-H: And here’s a rewritten version that uses uniform sampling over rows: Three different sampling operators are supported: UNIFORM(p) selects rows uniformly at random with probability p.

Big Data 57

Big Data Analytics Latency Azure 57

Percona

DECEMBER 20, 2018

ZFS has many very interesting features, but I am a bit tired of hearing negative statements on ZFS performance. Why You Should Avoid Using “CREATE TABLE AS SELECT” Statement. In this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement. Top 10 Most Read.

Database 40

Database Tuning Performance AWS 40

SQL Server According to Bob

AUGUST 23, 2016

The test being executed was a tight loop of create table statements. We are not talking about the performance of inserts, updates, deletes and selects but instead looking at the time it takes to perform create table. Parse T-SQL. Bob Dorr – Principal Software Engineer SQL Server. Disk Based. In-Memory Optimized.

C++ 40

C++ Software Engineering Servers Code 40

Percona

SEPTEMBER 1, 2023

Common issues include an excess of indexes, redundant indexing, or selecting inappropriate columns to index. Inefficient query design, such as utilizing SELECT * instead of specifying necessary columns, can escalate data transfer and processing overhead. Regularly optimizing query structures is vital.

Tuning 52

Tuning Database Performance Hardware 52

Percona

DECEMBER 26, 2023

It collects various statistics data such as query statistics, query plan, SQL comments, and other performance insights. Query Plan : Each SQL is now accompanied by its actual plan that was constructed for its execution. Query Plan : Each SQL is now accompanied by its actual plan that was constructed for its execution.

Benchmarking 119

Benchmarking Metrics C++ Database 119

SQL Performance

AUGUST 12, 2020

Neither this term, nor its acronym CTE, appear in the ISO/IEC SQL standard specs. You can find it in the documentation of Microsoft SQL Server and Azure SQL Database. T-SQL supports it starting with SQL Server 2005. It uses the term with list element to represent what T-SQL calls a CTE.

C++ 140

C++ Code Design Database 140

Percona

JANUARY 4, 2023

Instruments are a combination of different sets of components like wait, io, sql, binlog, file, etc. For example, wait/io/file/sql/binlog is one of the instruments providing information regarding the wait and I/O details on binary log files. For example stage/sql/altering table. Instrument for this can be wait/io/file/sql/map.

Performance 98

Performance C++ Monitoring Servers 98

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. ( c1 ) SELECT. ( c1 ) SELECT.

Storage 53

Storage Servers Engineering Testing 53

Percona

DECEMBER 12, 2022

Find the TRIGGERS: mysql> SELECT TRIGGER_NAME,EVENT_MANIPULATION FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='mytestdb' and EVENT_OBJECT_TABLE like '%authors%' G 1. Run the following SQL to swap the tables and remove the triggers. TRIGGER_NAME: pt_osc_mytestdb_authors2_del EVENT_MANIPULATION: DELETE 2.

Database 58

Database Engineering Testing Processing 58

Percona

JANUARY 22, 2019

A common challenge with continuously deployed applications is that new and modified SQL queries are constantly being introduced to the application. We’ll begin with a basic example of how EverSQL can provide value for a simple SELECT statement. PMM is configured to monitor for slow queries from MySQL’s slow log file.

Monitoring 43

Monitoring Database Code Availability 43

SQL Performance

MAY 22, 2019

SELECT objtype AS [ CacheType ] , COUNT_BIG ( * ) AS [ Total Plans ] , SUM ( CAST ( size_in_bytes AS DECIMAL ( 18 , 2 ) ) ) / 1024 / 1024 AS [ Total MBs ] , AVG ( usecounts ) AS [ Avg Use Count ] , SUM ( CAST ( ( CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0.

Cache 54

Cache Performance Hardware Servers 54

The Netflix TechBlog

DECEMBER 3, 2022

It lets you create YAML formatted mock data files based on selected tables, columns and a few rows of data from the Netflix data warehouse. You may remember Dataflow from the post we wrote last year titled Data pipeline asset management with Dataflow. Commands: migration Manage schema migration. mock Generate or validate mock datasets.

Best Practices 358

Best Practices Code Testing Data Engineering 358

The Morning Paper

OCTOBER 22, 2019

Typical goals are to suggest hypotheses, assess assumptions, and support selections of further tools, techniques, and datasets. Only 18 of the 30 participants mentioned using databases and related tools (that statement caused a significant recalibration in my mind, I would have guessed much closer to 100%!).

Programming 40

Programming Software Engineering Database Analytics 40

SQL Performance

OCTOBER 13, 2021

So far I covered derived tables and CTEs , which are statement-scoped named table expressions, and views , which are reusable named table expressions. So, the easiest way to describe an iTVF is as a parameterized view, although technically you create it with a CREATE FUNCTION statement and not with a CREATE VIEW statement.

Cache 144

Cache Strategy C++ Code 144

O'Reilly

SEPTEMBER 15, 2021

When we looked at the most popular programming languages for data and AI practitioners, we didn’t see any surprises: Python was dominant (61%), followed by SQL (54%), JavaScript (32%), HTML (29%), Bash (29%), Java (24%), and R (20%). Salaries by Programming Language.

Azure 145

Azure Social Media Programming AWS 145