2014, Database, Performance and Servers - Technology Performance Pulse

Refreshing SQL Server Tables With Less Interruption Using Partition Switching

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. Refreshing Tables in SQL Server. SQL Server Partition Switching Example. Compared to the Old Way of SQL Server Partition Switching.

Servers

Servers Games Database Processing

What is AWS Lambda?

Dynatrace

APRIL 5, 2021

The 2014 launch of AWS Lambda marked a milestone in how organizations use cloud services to deliver their applications more efficiently, by running functions at the edge of the cloud without the cost and operational overhead of on-premises servers. A new record entering a database table. Dynatrace news. What is AWS Lambda?

Lambda

Lambda AWS Serverless Hardware

The DBCC_OBJECT_METADATA Latch

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++

C++ Servers Database Code

MariaDB vs MySQL: Key Differences and Use Cases

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

Open Source Servers Storage Scalability

Finding Distinct Values Quickly

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

Servers Tuning Database Efficiency

Stuff The Internet Says On Scalability For June 22nd, 2018

High Scalability

JUNE 22, 2018

Each drone is rated with a game mechanic and gets special privileges based on performance (just kidding). Charlie Demerjian : what does Intel have planned for their server roadmap? For the same reason the 14/10nm messaging is causing consternation among investors, but the server side is in much worse shape. How bad is it?

Internet

Internet Internet Scalability Artificial Intelligence

Deprecated features to take out of your toolbox – Part 1

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

Latency Servers Monitoring Systems

Minimal Logging with INSERT…SELECT into Empty Clustered Tables

SQL Performance

MAY 16, 2019

Achieving minimal logging using INSERT.SELECT into an empty clustered index target is not quite as simple as described in the Data Performance Loading Guide. The Data Loading Performance Guide contains a high-level summary of the conditions required for minimal logging into clustered tables: This post is concerned with the top row only.

Cache

Cache Servers Testing Storage

Analyzing "death by a thousand cuts" workloads

SQL Performance

OCTOBER 29, 2019

There are multiple methods to look at poorly performing queries in SQL Server, notably Query Store, Extended Events, and dynamic management views (DMVs). Extended Events provides data about the individual execution of queries, while Query Store and the DMVs aggregate performance data. Each option has pros and cons. ORDER BY qs.

C++

C++ Database Servers Tuning

The ANY Aggregate is Broken

SQL Performance

JULY 1, 2019

The results are technically non-deterministic since SQL Server could validly return any one of the rows in each group. Nevertheless, if you run this query yourself, you are quite likely to see the same result I do: The execution plan depends on the version of SQL Server used, and does not depend on database compatibility level.

C++

C++ Servers Database Performance

DBCC Trace Flags 2562 and 2549

SQL Server According to Bob

JULY 12, 2016

Trace flags 2562 and 2549 are documented in knowledgebase article: [link] and the blog post [link] highlights the SQL 2016 DBCC performance improvements. For SQL Server 2008, 2012 and 2014 the trace flag behavior remains the same. Trace flag 2549 is used to change how DBCC sees the volume layout of the database.

Servers

Servers Design Engineering Database

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better

SQL Server According to Bob

JUNE 3, 2016

SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale far better than previous releases. The following chart shows the same 1TB database testing. SQL Server. SQL Server 2014.

Hardware

Hardware Cache Servers Software Engineering

The SQL Server Transaction Log, Part 2: Log Architecture

SQL Performance

FEBRUARY 1, 2022

This is done to make managing operations in the transaction log easier for the log manager in SQL Server. You can’t specify how many VLFs are created by SQL Server when the database is first created or the log file automatically grows, but you can influence it.

Architecture

Architecture Servers Virtualization Database

Common SQL Server Mishaps

SQL Performance

JUNE 21, 2019

I've been teaching and writing about common SQL Server mistakes for many years. This article will expand on my previous article and point out how these apply to SQL Server , Azure SQL Database , and Azure SQL Managed Instance. SQL Server Agent alerts. This situation applies to on-premises SQL Server and IaaS.

Servers

Servers Azure Hardware Database

The Speed of Time

Brendan Gregg

SEPTEMBER 25, 2021

These strange questions came to the fore back in 2014 when Netflix was switching services from CentOS Linux to Ubuntu, and I helped debug several weird performance issues including one I'll describe here. A Cassandra database cluster had switched to Ubuntu and noticed write latency increased by over 30%.

Speed

Speed Java AWS Virtualization

Minimal Logging with INSERT…SELECT into Heap Tables

SQL Performance

MAY 1, 2019

This is reflected in the summary tables included in the Data Loading Performance Guide and the Tiger Team post. The summary rows for heap tables without indexes are the same in both documents (no changes for SQL Server 2016): An explicit TABLOCK hint is not the only way to meet the requirement for table-level locking.

Servers

Servers Database Testing Performance

The Performance Inequality Gap, 2021

Alex Russell

MARCH 6, 2021

Back in 2016, I gave a talk outlining the causes and effects of the terrible performance of web apps built using popular tools on the fastest-growing device segment: low-end to mid-range Android phones. Poor performance has a compound effect on user expectations at an ecosystem level. Live by the link, die by the link.

Performance

Performance Network Cache Metrics

AWS EC2 Virtualization 2017: Introducing Nitro

Brendan Gregg

NOVEMBER 29, 2017

Hardware virtualization for cloud computing has come a long way, improving performance using technologies such as VT-x, SR-IOV, VT-d, NVMe, and APICv. The latest AWS hypervisor, Nitro, uses everything to provide a new hardware-assisted hypervisor that is easy to use and has near bare-metal performance.

Virtualization

Virtualization AWS Hardware Storage

Is Intel Doomed in the Server CPU Space?

SQL Performance

JULY 16, 2019

A close monitoring of the hardware enthusiast community, including many of the most respected hardware analysts and reviewers paints an even more dire picture about Intel in the server processor space. Despite all of this, Intel is not going to lose their entire server processor business any time soon. So, what has changed my mind?

Servers

Servers Hardware Architecture Retail

The Speed of Time

Brendan Gregg

SEPTEMBER 25, 2021

These strange questions came to the fore back in 2014 when Netflix was switching services from CentOS Linux to Ubuntu, and I helped debug several weird performance issues including one I'll describe here. A Cassandra database cluster had switched to Ubuntu and noticed write latency increased by over 30%.

Speed

Speed Java AWS Virtualization

Which is the Best MongoDB GUI? — 2019 Update

Scalegrid

AUGUST 15, 2019

In 2014, we discussed 4 of the top MongoDB GUIs: MongoVue, MongoHub, RockMongo, and Robo 3T (formerly Robomongo), and again in 2016: MongoDB Compass, Robo 3T, Studio 3T, and MongoBooster. Should have an excellent way to measure server and query performance. Import data from RDBMS like MySQL, PostgreSQL, and SQL Server.

Database

Database Servers Monitoring Availability

SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

SQL Server According to Bob

SEPTEMBER 26, 2016

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). As we moved towards SQL Server 2014, the pace of hardware accelerated.

Availability

Availability Servers Hardware Benchmarking

The Speed of Time

Brendan Gregg

SEPTEMBER 25, 2021

These strange questions came to the fore back in 2014 when Netflix was switching services from CentOS Linux to Ubuntu, and I helped debug several weird performance issues including one I'll describe here. A Cassandra database cluster had switched to Ubuntu and noticed write latency increased by over 30%.

Speed

Speed Java AWS Virtualization

SQL 2016 – It Just Runs Faster Announcement

SQL Server According to Bob

JUNE 3, 2016

SQL Server 2016 ‘It Just Runs Faster’ A bold statement that any SQL Server professional can stand behind with confidence. Try SQL Server 2016 Today. The SQL Server Development team tasked several individuals with scalability improvements and real world testing patterns. – [link]. Follow-up.

Hardware

Hardware Software Engineering Transportation Scalability

AMD EPYC Processors in Azure Virtual Machines

SQL Performance

APRIL 8, 2019

These AMD EPYC processors have a number of advantages for SQL Server workloads, as I will explain in this article. This 14nm first-generation AMD EPYC 7551 processor has 32 cores and 64 threads, and it works in one or two-socket servers. Both of these Intel processors are special bespoke models that are not in the Intel ARK database.

Azure

Azure Virtualization Storage Benchmarking

Understanding Execution Plan Operator Timings

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). SQL Server makes some timing adjustments in parallel plans to promote consistency, but they are not perfectly implemented.

Servers

Servers Tuning Architecture Processing

AWS EC2 Virtualization 2017: Introducing Nitro

Brendan Gregg

NOVEMBER 28, 2017

Hardware virtualization for cloud computing has come a long way, improving performance using technologies such as VT-x, SR-IOV, VT-d, NVMe, and APICv. The latest AWS hypervisor, Nitro, uses everything to provide a new hardware-assisted hypervisor that is easy to use and has near bare-metal performance.

Virtualization

Virtualization AWS Hardware Storage

Interactive checks for coordination avoidance

The Morning Paper

AUGUST 27, 2019

I am so pleased to see a database systems paper addressing the concerns of the application developer! A example class of application invariants is database integrity constraints. In a distributed setting with weak consistency, an object is replicated across a set of servers.

Programming

Programming Processing Database Systems

Grouped Aggregate Pushdown

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. instructions are available.

Servers

Servers Testing Metrics Storage

Minimal Logging with INSERT…SELECT and Fast Load Context

SQL Performance

MAY 29, 2019

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. For more background, see the Data Performance Loading Guide and the Tiger Team notes on the behaviour changes for SQL Server 2016.

Cache

Cache Testing Servers Code

Real-Real-World Programming with ChatGPT

O'Reilly

JULY 25, 2023

To provide some coherence to the music, I decided to use Taylor Swift songs since her discography covers the time span of most papers that I typically read: Her main albums were released in 2006, 2008, 2010, 2012, 2014, 2017, 2019, 2020, and 2022. This choice also inspired me to call my project Swift Papers.

Programming

Programming Code Design Benchmarking

Introduction to Wait Statistics

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. I’m going to introduce you to wait statistics and explain how critical they are for troubleshooting workload performance in SQL Server. SQL Server also keeps track of how long the thread has to wait.

Servers

Servers Azure Metrics Tuning

Should You Use ClickHouse as a Main Operational Database?

Percona

JANUARY 14, 2019

What if we use ClickHouse (which is a columnar analytical database) as our main datastore? Well, typically, an analytical database is not a replacement for a transactional or key/value datastore. Although such databases can be very efficient with counts and averages, some queries will be slow or simply non existent. Text search.

Database

Database Analytics Blockchain Healthcare

Technology Performance Pulse

Refreshing SQL Server Tables With Less Interruption Using Partition Switching

What is AWS Lambda?

Trending Sources

The DBCC_OBJECT_METADATA Latch

MariaDB vs MySQL: Key Differences and Use Cases

Finding Distinct Values Quickly

Stuff The Internet Says On Scalability For June 22nd, 2018

Deprecated features to take out of your toolbox – Part 1

Minimal Logging with INSERT…SELECT into Empty Clustered Tables

Analyzing "death by a thousand cuts" workloads

The ANY Aggregate is Broken

DBCC Trace Flags 2562 and 2549

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better

The SQL Server Transaction Log, Part 2: Log Architecture

Common SQL Server Mishaps

The Speed of Time

Minimal Logging with INSERT…SELECT into Heap Tables

The Performance Inequality Gap, 2021

AWS EC2 Virtualization 2017: Introducing Nitro

Is Intel Doomed in the Server CPU Space?

The Speed of Time

Which is the Best MongoDB GUI? — 2019 Update

SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

The Speed of Time

SQL 2016 – It Just Runs Faster Announcement

AMD EPYC Processors in Azure Virtual Machines

Understanding Execution Plan Operator Timings

AWS EC2 Virtualization 2017: Introducing Nitro

Interactive checks for coordination avoidance

Grouped Aggregate Pushdown

Minimal Logging with INSERT…SELECT and Fast Load Context

Real-Real-World Programming with ChatGPT

Introduction to Wait Statistics

Should You Use ClickHouse as a Main Operational Database?

Stay Connected