SQL Performance

MAY 3, 2021

In some of my previous articles here on performance tuning, I’ve discussed multiple wait types and how they are indicative of various resource bottlenecks. SQL Server implements this mechanism and calls it a spinlock. SQL Server implements this mechanism and calls it a latch. What is a Latch?

Servers 98

Servers Tuning Scalability Systems 98

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. pt-summary --user=root [?2004l# virtual = 2.2G

Cache 101

Cache Database Virtualization Servers 101

SQL Performance

MARCH 15, 2021

This time around, I want to talk about a feature included with SQL Server client tools that we should not be using these days – not just because it is deprecated but, more importantly, because it has the potential to completely take a server down: SQL Server Profiler.

Servers 64

Servers Azure Best Practices Monitoring 64

SQL Performance

MARCH 30, 2021

This five-part series takes a deep dive into the way SQL Server row mode parallel plans start up. The fundamentals of row mode parallel execution haven’t changed since SQL Server 2005, so the following discussion is broadly applicable. See Myth: SQL Server Caches a Serial Plan with every Parallel Plan for details.

Cache 98

Cache Servers Operating System Processing 98

SQL Server According to Bob

JANUARY 10, 2022

Negative Blocking Session Ids ​​ (-5 = Latch ANY TASK RELEASOR) SQL Server may report a blocking session id as a negative integer value. SQL Server uses negative sessions ids to indicate special conditions. 2 Pending DTC ​​ transaction.

Operating System 60

Operating System Servers Design Performance 60

HammerDB

NOVEMBER 22, 2021

sql '/opt/postgresql-14.1/share/extension/'. include/server -I/opt/postgresql-14.1/include/internal The example has a configuration set to illustrate a number of wait events and has therefore not been configured for performance. The events are colour coded and indexed in the graph to the wait event groups. /postgresql-14.1/contrib/pg_stat_statements$

Metrics 62

Metrics Benchmarking C++ Database 62

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. Indeed, the wait count curiously dropped by 22.8

C++ 114

C++ Servers Testing Processing 114

SQL Performance

JUNE 22, 2021

The FGCB_ADD_REMOVE latch protects a data structure called an FGCB, or File Group Control Block, and there will be one of these latches for each online filegroup of each online database in a SQL Server instance. The seven waiting threads are in the latch’s first-in-first-out (FIFO) waiting queue. ( SELECT * FROM sys.

Servers 54

Servers Database Code Performance 54

SQL Performance

JULY 27, 2021

Continuing my series of articles on latches, this time I’m going to discuss the DBCC_OBJECT_METADATA latch and show how it can be a major bottleneck for consistency checks prior to SQL Server 2016 under certain circumstances. But remember, this bottleneck only happens for versions of SQL Server earlier than SQL Server 2016.

C++ 54

C++ Servers Database Code 54

HammerDB

NOVEMBER 30, 2022

Type "help()" for a list of commands. Type "help()" for a list of commands. Vuser 1:1 Active Virtual Users configured Vuser 1:TEST RESULT : System achieved 39945 NOPM from 92893 SQL Server TPM Vuser 1:Gathering timing data from Active Virtual Users. Copyright (C) 2003-2022 Steve Shaw. Use a different Browser.

Virtualization 44

Virtualization C++ Lambda Monitoring 44

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

Percona

AUGUST 15, 2023

In this blog post, I provide an in-depth introduction to MySQL Replication, answering what it is, how it works , its benefits and challenges, as well as reviewing some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). Set the server-id to a unique value.

Servers 52

Servers Database Availability Traffic 52

Percona

OCTOBER 10, 2023

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. Your MySQL connection id is 256 Server version: 8.0.32-24.2 Your MySQL connection id is 256 Server version: 8.0.32-24.2 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. drop_triggers:10000:0.25,copy_rows:10000:0.25,swap_tables:10000:0.25,update_foreign_keys:10000:0.25,analyze_table:10000:0.25

Database 97

Database Performance C++ Engineering 97

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. sysprocesses. dm_os_performance_counters.

Latency 57

Latency Servers Monitoring Systems 57

SQL Performance

MAY 7, 2019

Back in 2014, I started a series of blog posts here to talk about specific wait types and what they do and don’t mean. That gave me the idea to create the wait and latches libraries that I maintain (more on these later). The execution of SQL Server’s internal code is done using a mechanism called threads.

Servers 40

Servers Azure Metrics Tuning 40

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

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. Or it could be this query requires a scan, in which case LATCH_EX waits for ACCESS_METHODS_DATASET_PARENT are just part of your environment.

Servers 57

Servers Storage Efficiency Database 57

HammerDB

MARCH 25, 2023

Copyright (C) 2003-2023 Steve Shaw Type "help" for a list of commands Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB Copyright (C) 2003-2023 Steve Shaw Type "help" for a list of commands Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB Copy Code Copied Use a different Browser $./scripts/tcl/maria/tprocc/maria_tprocc.sh

C++ 83

C++ Virtualization Benchmarking Database 83

SQL Performance

JULY 3, 2019

A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. Depending on which DMV you look at for SQL Server, a session can have a few different statuses. Understanding Session Status. Understanding.NET Transactions.

Servers 64

Servers Hardware Code Virtualization 64

HammerDB

JULY 5, 2022

To do this, we are going to rewrite HammerDB in Python and run a series of tests on a 2 socket Intel(R) Xeon(R) Platinum 8280L server to see how and why Tcl is 700% faster than Python for database benchmarking*. Background and Concepts. usr/local/bin/tclsh8.6 puts "Processing $total_iterations transactions without output suppressed."

Benchmarking 98

Benchmarking Database C++ Virtualization 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. As mentioned, SQL Server will attempt to unnest table expressions, but will avoid the unnesting if it stumbles into an unnesting inhibitor.

Servers 110

Servers Code Processing Performance 110

SQL Performance

FEBRUARY 10, 2021

Joe Obbish correctly noted that this test could be lacking in its reflection of some real-life scenarios in the sense that a big chunk of the run time is due to async network I/O waits (ASYNC_NETWORK_IO wait type). This time the wait info has no async network I/O waits, and you can see the significant drop in run time.

C++ 124

C++ Servers Testing Code 124

Smashing Magazine

SEPTEMBER 28, 2022

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. But isn’t waiting for the data the point? The skeleton loader will usually wait for: The data from the backend API; The build layout according to the data. Large preview ).

Cache 125

Cache Performance Servers Social Media 125

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. The parent task now waits on CXPACKET at the consumer side of the node 2 gather streams exchange. This wait will continue until both node 2 producers return and open the exchange. Checkpoint.

C++ 139

C++ Processing Design Availability 139

HammerDB

APRIL 27, 2021

To begin with run the hammerdbcli command in Linux or hammerdbcli.bat in Windows and type help at the hammerdb prompt. In this example we are using SQL Server so the message shows that everything is in order and we can proceed with running tests. Press return for a prompt and then type the command needed. help command.

Virtualization 73

Virtualization Benchmarking Database Operating System 73

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. Indeed, the wait count curiously dropped by 22.8

C++ 61

C++ Servers Testing Processing 61

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

Percona

JANUARY 5, 2023

There are two types of vacuum in PostgreSQL: VACUUM and ANALYZE. The vacuum process can be initiated manually using SQL commands or automated using the autovacuum background process, which runs based on configurable thresholds such as the number of dead rows or live rows in a table. The default value is 0.2. The default value is 0.1.

Database 107

Database Performance C++ Efficiency 107

HammerDB

JULY 16, 2021

software running on the same server. This is the same server, same OS and same HammerDB client, the only difference is in the server software. on this server. In this example, we will use the HammerDB CLI and build the schema with the script as follows in both databases. hammerdbcli HammerDB CLI v4.2 and MySQL 5.7.33.

Virtualization 57

Virtualization Performance C++ Database 57

SQL Server According to Bob

OCTOBER 17, 2019

When executing a query to enumerate the locks, such as select * from sys.dm_tran_locks, how does SQL Server scan the locks and avoid impacting the overall concurrency? I recently posted on aspects of the SQL Server Lock Manager and found the iteration of the locks interesting as I was stepping in the code. link] [link].

Servers 40

Servers Software Engineering Database Monitoring 40

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. or MariaDB 10.X or MariaDB 10.X We will support you either way. Learn more What does this error mean?

AWS 71

AWS Servers Engineering Strategy 71

SQL Server According to Bob

JUNE 9, 2017

As many of us have documented, for years, a sleeping, awaiting command, session is a session waiting on the client to submit a TSQL command to the SQL Server. The client must send a command to SQL Server or the SQL Server status is ‘awaiting command’ for the session.

Servers 40

Servers Software Engineering Engineering Software 40

SQL Server According to Bob

AUGUST 17, 2016

Have you encountered the following in your SQL Server error log? There are two general types of I/O performed by SQL Server. Async – Vast majority of SQL Server I/Os, as outlined in the provided link: [link]. Simply stated the thread waits for the I/O to complete. with a timer. with a timer.

Software Engineering 56

Software Engineering Servers Engineering Software 56

Smashing Magazine

AUGUST 3, 2023

Time To First Byte (TTFB) This is the time it takes for the first piece of information from the server to reach the user’s browser. You need to beware that slow server response times can significantly increase TTFB, often due to server overload, network issues, or un-optimized logic on the server side. SQL query results.

Performance 74

Performance Cache Traffic Metrics 74

Percona

AUGUST 30, 2023

Backup types and strategies It all begins with choosing a strategy, and that depends on factors such as the use and importance of data to your business, your recovery time objectives (RTO), and your budget. For a more detailed description, read Full vs. Incremental vs. Differential Backups: Comparing Backup Types.)

Database 90

Database Hardware Open Source Strategy 90

Testsigma

DECEMBER 4, 2020

Step 3: Application Type. Web app testing : Optimized for mobile browsing and server-driven, the web apps should be tested for UI to ensure that all the elements of the app are aligned for different screens and resolutions. Back-end or database testing deals with the server-side of the mobile app. Step 5: Back-End Testing.

Mobile 65

Mobile Testing Operating System Storage 65

Particular Software

OCTOBER 6, 2020

In our scatter-gather saga, this means that once one message locks the saga, all the other messages have to wait—they can’t even get in the front door. For example, in SQL Server, a row lock can be escalated to a page lock or full table lock, effectively locking many more things than needed in the name of resource optimization.

Strategy 98

Strategy Database Infrastructure Design 98