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.
Articles
Page 5 of 11
— showing articles
73 to
90
of 182
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.
Hash Joins on Nullable Columns
Optimized and static bitmap filters, null rejection, and worst-case hash join performance.
Interesting things about INSTEAD OF triggers
Surprising implementation details for INSTEAD OF triggers and a cardinality estimation bug in SQL Server 2014 onwards.
Improving the Top Based Pre-2012 Median Solution
Avoiding large memory grants and excessive scalar computations.
Improving the Pre-2012 Row Numbering Median Solution
Analyzing the cause of lower than expected performance and applying a correction.
Locking and Performance
The type and number of locks acquired and released during query execution can have a surprisingly large impact on performance, even if no waiting or blocking occurs.
Calculating the Median with a Dynamic Cursor
A fast way to compute single and grouped medians using a dynamic T-SQL cursor.
Internals of the Seven SQL Server Sorts—Part 2
SQL Server sort internals, including in-memory sorts and two new classes specific to In-Memory OLTP.
Internals of the Seven SQL Server Sorts—Part 1
The first four of the seven SQL Server sorts described in detail.
The Read Uncommitted Isolation Level
The guarantees provided by the weakest of the standard transaction isolation levels. Includes examples of less well-known concurrency effects.
An Indexed View Bug with Scalar Aggregates
Details of a SQL Server bug that can render an indexed view incorrect.
Indexed View Maintenance in SQL Server Execution Plans
How SQL Server maintains indexed views as the underlying data changes.
Allocation Ordered Scans
How SQL Server allocation ordered scans work in detail, when they can and can't be used, and how to disable them (almost) completely.
Performance Tuning the Whole Query Plan
Thinking laterally to find a better algorithm rather than tuning individual SQL Server execution plan operators.
Avoiding Sorts with Merge Join Concatenation
How merge concatenation can be used to avoid sorting, if you know enough about the SQL Server execution engine.
Rewriting Queries to Improve Performance
Though SQL is a declarative language, expressing queries differently can dramatically change execution plans.