The Lock Escalation Threshold – Part 3
SentryOne | SQLPerformance.com
by Paul White
2y ago
Lock Lifetimes 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. 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. The engine processes the row through parent operators before releasing the shared lock prior to locking and reading t ..read more
Visit website
DATE_BUCKET and DATETRUNC Improve Optimization of Time-Based Grouping
SentryOne | SQLPerformance.com
by Itzik Ben-Gan
2y ago
Time-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable. SQL Serv ..read more
Visit website
The Lock Escalation Threshold – Part 2
SentryOne | SQLPerformance.com
by Paul White
2y ago
Parallelism In part one of this series, I explained how the lock manager maintains a count of held locks per transaction, and individual access methods keep a separate lock count per HoBt for each plan operator performing data access. Parallelism adds an extra layer of complexity because both lock counts are maintained per thread. There are two underlying reasons for this: Each additional parallel worker thread operates inside a nested sub-transaction with the original transaction as its parent. A count of held locks is maintained per sub-transaction and so per-thread. Each worker in a parall ..read more
Visit website
New Bit Manipulation Functions in SQL Server 2022
SentryOne | SQLPerformance.com
by Itzik Ben-Gan
2y ago
The need to manipulate data at the bit level with bitwise operations isn’t common in T-SQL, but you might stumble into such a need in some specialized scenarios. Some implementations store a set of flags (yes/no, on/off, true/false) in a single integer or binary-typed column, where each bit represents a different flag. One example is using a bitwise representation of a set of user/role permissions. Another example is using a bitwise representation of a set of settings turned on or off in a given environment. Even SQL Server stores some flag-based data using bitwise representation. Examples inc ..read more
Visit website
The Lock Escalation Threshold – Part 1
SentryOne | SQLPerformance.com
by Paul White
2y ago
This article isn’t about the act of lock escalation itself, which is already well documented and generally well understood. Some myths (like row locks escalating to page locks) persist, but challenging those yet again probably wouldn’t change much. Instead, the question I’ll address here is exactly how and when lock escalation is triggered. Much of the documentation is incorrect or at least imprecise about this and I’ve been unable to find a correct description in other writings. There are good reasons you haven’t seen a simple demo of lock escalation taking place at 5000 locks. I’ve seen sugg ..read more
Visit website
Simple Parameterization and Trivial Plans — Part 6
SentryOne | SQLPerformance.com
by Paul White
2y ago
[ This series: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 ] In part 5, I described how the early phases of query compilation affect simple parameterization. To recap: Normalization and decoding promote cached plan reuse The decoding step has fixed and limited capabilities The Failed Auto-Params/sec counter is incremented when decoding fails and the statement is not parameterized Shell plans optimize for very frequent execution of simple statements, bypassing the parsing, parameter replacement, normalization, and decoding stages The first application of constant folding occurs after d ..read more
Visit website
Additional T-SQL Improvements in SQL Server 2022
SentryOne | SQLPerformance.com
by Itzik Ben-Gan
2y ago
Recently I covered some of the T-SQL improvements in SQL Server 2022 here, and Aaron Bertrand covered additional improvements here. Those improvements were introduced in CTP 2.0. Microsoft just announced the release of SQL Server 2022 CTP 2.1. This release includes a number of additional interesting T-SQL improvements, which include: The distinct predicate The approximate percentile functions APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC The DATETRUNC function A set of bit manipulation functions In this article, I focus on the distinct predicate and the approximate percentile functions ..read more
Visit website
10 For 10 : My 10 Favorite SQLPerformance Posts Over 10 Years
SentryOne | SQLPerformance.com
by Aaron Bertrand
2y ago
This is a month of milestones. I was awarded my 25th MVP award on July 5th, which I believe ties me with Tibor for the longest-tenured MVP. SQL Server 2012 went out of all forms of official support about two weeks ago, on July 12th. And this site turns 10 years old today – the first post was on July 24th, 2012. I thought it would be fun to look back and comment on some of my contributions over that decade. 2012 Best approaches for running totals – updated for SQL Server 2012 In the very first post on this site, I compared the performance of long-standing running totals methods with the new wi ..read more
Visit website
The SQL Server Transaction Log, Part 4: Log Records
SentryOne | SQLPerformance.com
by Paul Randal
2y ago
Previously I’ve talked about why logging is required and the architecture and circular nature of the log, so now it’s time to look at the real heart of the logging system—the log records themselves. As always, I recommend you read the previous posts in the series before reading this one. What Are Log Records? The simplest definition of a log record: it describes a single change to a database. A single operation in the database may cause multiple changes, but each change will usually have its own log record to describe it. An example of this is updating a fixed-width column in a single row—it w ..read more
Visit website
Emulating the GROUPS Window Frame Option
SentryOne | SQLPerformance.com
by Itzik Ben-Gan
2y ago
Generally in life, it’s good to have the perfect tools to handle any given task. But sometimes a tool you need isn’t available, and you have to figure out a solution without it. The same goes specifically for handling T-SQL querying tasks in terms of supported language elements. T-SQL, the dialect, supports a subset of the features from standard SQL. Sometimes, you need to solve a T-SQL querying task, and you know the natural tool to solve it is a feature that’s part of the SQL standard but isn’t available in T-SQL. So you have to come up with a supported alternative. This could also happen if ..read more
Visit website

Follow SentryOne | SQLPerformance.com on FeedSpot

Continue with Google
Continue with Apple
OR