THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |


You searched for the word(s):
Showing page 1 of 41 (401 total posts) < 1 second(s)
  • Closest Match with Sort Rewinds

    In When Do SQL Server Sorts Rewind? I described how most sorts can only rewind when they contain at most one row. The exception is in-memory sorts, which can rewind at most 500 rows and 16KB of data. These are certainly tight restrictions, but we can still make use of them on occasion. To illustrate, I am going reuse a demo Itzik Ben-Gan ...
    Posted to Page Free Space (Weblog) by Paul White on October 18, 2020
  • SQL Server 2019 Aggregate Splitting

    The SQL Server 2019 query optimizer has a new trick available to improve the performance of large aggregations. The new exploration abilities are encoded in two new closely-related optimizer rules: GbAggSplitToRanges SelOnGbAggSplitToRanges The extended event query_optimizer_batch_mode_agg_split is provided to track when this new ...
    Posted to Page Free Space (Weblog) by Paul White on August 3, 2020
  • A bug with Halloween Protection and the OUTPUT Clause

    Background The OUTPUT clause can be used to return results from an INSERT, UPDATE, DELETE, or MERGE statement. The data can be returned to the client, inserted to a table, or both. There are two ways to add OUTPUT data to a table: Using OUTPUT INTO With an outer INSERT statement. For example: -- Test table DECLARE @Target ...
    Posted to Page Free Space (Weblog) by Paul White on August 3, 2020
  • How MAXDOP Really Works

    A few days ago I ran a Twitter poll: The most popular answer gets highlighted by Twitter at the end of the poll, but as with many things on social media, that doesnt mean it is correct: Continue reading...
    Posted to Page Free Space (Weblog) by Paul White on August 3, 2020
  • Pulling Group By Above a Join

    One of the transformations available to the SQL Server query optimizer is pulling a logical Group By (and any associated aggregates) above a Join. Visually, this means transforming a tree of logical operations from: to this: The above diagrams are logical representations. They need to be implemented as physical operators to appear in an ...
    Posted to Page Free Space (Weblog) by Paul White on August 3, 2020
  • Blog Relocated

    My blog here has moved to Please update your bookmarks and other links.
    Posted to Page Free Space (Weblog) by Paul White on April 15, 2019
  • re: Nested Loops Prefetching

    Simply that unordered prefetch allows async I/Os to be completed in any order. Ordered prefetching must wait for I/Os to complete in the correct order.
    Posted to Page Free Space (Weblog) by Paul White on March 2, 2019
  • re: The “Segment Top” Query Optimisation

    Not that I know of, no. It would be a useful extension, perhaps.
    Posted to Page Free Space (Weblog) by Paul White on March 2, 2019
  • re: Heaps of Trouble?

    Matthew, Hinting joins e.g. INNER HASH JOIN implies FORCE ORDER. Adding the query hint OPTION (HASH JOIN) does not. It forces the use of hash joins, but the optimizer is free to reorder them. Hope that clarifies!
    Posted to Page Free Space (Weblog) by Paul White on March 2, 2019
  • re: Nested Loops Prefetching

    David, Yes I see the same on 2014. Seems like the delayed prefetch is a bit more aggressive in 2014 than it was for 2012 and earlier.
    Posted to Page Free Space (Weblog) by Paul White on June 15, 2016
1 2 3 4 5 Next > ... Last »
Privacy Statement