Columnstore Grouped Aggregate Pushdown
A deep dive into columnstore grouped aggregation pushdown, showing where this optimization is unsuccessful or not attempted, using the undocumented SQL Server command DBCC CSINDEX.
Articles
Page 4 of 10
— showing articles
55 to
72
of 179
Why IGNORE_DUP_KEY is slower on clustered indexes
Why the IGNORE_DUP_KEY index option is much slower on a clustered index than a nonclustered unique index. Detail execution plan analysis and internal engine details.
Join Cardinality Estimation using Histogram Coarse Alignment
How the new SQL Server cardinality estimator uses histogram coarse alignment.
Row Goals 4: An Anti Join Anti Pattern
A redundant and problematic TOP (1) added to the inner side of an apply anti join.
Row Goals 3: Anti Semi Joins
An anti join does not automatically come with a 'row goal'. This article describes anti joins and the conditions required for the optimizer to add a row goal.
Row Goals 2: Semi Joins
When the SQL Server query optimizer adds a row goal for a semi join. How you can spot the effects in showplan and trace flag output.
Row Goals 1: Setting and Identifying Row Goals
Information about SQL Server row goals and why they're important when trying to understand why the query optimizer made a particular choice.
Trigram Wildcard String Search in SQL Server
Using character substrings (trigrams) to speed up wildcard searches in SQL Server. Provides an fairly complete example implementation.
Cardinality Estimation: Combining Density Statistics
Using a debugger to explore how SQL Server derives estimates when combining multiple single-column statistics.
Properly Persisted Computed Columns
Improving execution plans and performance for queries involving computed columns using a new trace flag in SQL Server 2016.
Reordering Concatenation Operator Inputs
It makes sense for the optimizer to consider reordering the inputs to a concatenation when less than the full potential result set is needed. Surprisingly, SQL Server does not do this in general.
SQL Server Temporary Object Caching
Deep dive into SQL Server temporary table and variable caching, including common misconceptions and practices that disable this optimization.
Cardinality Estimation for a Filter on a COUNT Expression
The statistical science and algorithms behind the way SQL Server calculates a cardinality estimate for a filter on a COUNT aggregate.
The Sort that Spills to Level 15,000
SQL Server sometimes produces misleading information about sort operations in execution plans.
The Internals of WITH ENCRYPTION
How WITH ENCRYPTION is implemented and how to reverse it using T-SQL.
Changes to a Writable Partition May Fail Unexpectedly
Why an update may fail when a partition has some data on a read-only filegroup due to "rowset sharing". Includes several workarounds for the issue.
Another Reason to Use NOEXPAND hints even in Enterprise Edition
The SQL Server query optimizer does not use uniqueness information obtained through automatic indexed view matching.
Update Query Analysis and Optimization
Follows the process of optimizing a simple update query and explores some deep internals including "lazy latches" along the way.