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

OCTOBER 20, 2022

Locking optimizations. 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. Lock Lifetimes. Lock classes. Reading data.

Testing 91

Testing Servers Cache Engineering 91

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. Both in terms of terminology and in terms of optimization.

C++ 93

C++ Database Servers Code 93

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. The query optimizer produces a single compiled plan for a batch.

Cache 98

Cache Servers Operating System Processing 98

SQL Performance

NOVEMBER 22, 2019

The optimizer must consider that the work involved saves more than it costs. The downside is the extra sorting cost may dissuade the optimizer from choosing the spool option at all. SQL Server does not support parallel inserts to a b-tree index. The TransactionHistory table is only scanned once. Cost assessment.

Servers 77

Servers Cache Database Performance 77

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

MAY 16, 2019

The word “traditional” there excludes columnstore and memory-optimized (‘Hekaton’) clustered tables). For the conditions that apply when the target table is a heap, see the previous article in this series. For the conditions that apply when the target table is a heap, see the previous article in this series.

Cache 67

Cache Servers Testing Storage 67

Percona

JULY 1, 2023

It efficiently manages read and write operations, optimizes data access, and minimizes contention, resulting in high throughput and low latency to ensure that applications perform at their best. This post was originally published in July 2018 and was updated in July 2023. What are the differences between Aurora and RDS? What is Amazon RDS?

AWS 52

AWS Database Serverless Storage 52

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. If it underestimates memory, then you can see spills in tempdb. The Sales.Orders table has about 4.6

Servers 41

Servers Cache Database Metrics 41

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. The following script creates a temporary table with a million rows. DROP TABLE IF EXISTS #Data ; GO. DROP TABLE IF EXISTS #Data ; GO.

Storage 53

Storage Servers Engineering Testing 53

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

Highly Scalable

MARCH 1, 2012

First, we should note that SQL and relational model in general were designed long time ago to interact with the end user. This user-oriented nature had vast implications: The end user is often interested in aggregated reporting information, not in separate data items, and SQL pays a lot of attention to this aspect.

Database 279

Database Ecommerce Efficiency Engineering 279

Scalegrid

AUGUST 15, 2019

Since then, however, many new options have emerged to help you optimize your MongoDB management and we thought we’d share our updates. Since then, however, many new options have emerged to help you optimize your MongoDB management and we thought we’d share our updates. We left those at the end of this post for your review.

Database 40

Database Servers Monitoring Availability 40

SQL Server According to Bob

DECEMBER 31, 2018

How It Works: XEvent Output and Visualization SQL Server Management Studio Provides–“XE Profiler” Use the SSMS XEvent Profiler. Using SQL Server Management Studio (SSMS) and storing the events in a table requires reading the events, streaming to table storage followed by an order by event sequence query.

Efficiency 40

Efficiency Code Servers Cache 40