THE SQL Server Blog Spot on the Web

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

Paul White: Page Free Space

A technical SQL Server blog from New Zealand.

Browse by Tags

All Tags » Internals » TSQL

  • Fun with Aggregates

    There are interesting things to be learned from even the simplest queries. For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but Read More...
  • How Parallelism Works in SQL Server

    You might have noticed that January was a quiet blogging month for me. Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works. The first part is published today at: Read More...
  • Sorting, Row Goals, and the TOP 100 Problem

    When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause. To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause. Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned. The ‘Top N’ Sort Thinking about how this requirement might be implemented in an executable query plan, we might expect to see a Sort iterator followed by a Top. In reality, the query optimizer can often collapse these two related operations into a single iterator: a Sort iterator running in Top N Sort mode: That’s an idea you might find familiar if you read my previous post on Row Goals and Grouping. In that entry, we saw how a Sort followed by a Stream Aggregate can sometimes be collapsed into a Sort iterator running in Sort Distinct mode. The General Sorting Algorithm SQL Server’s normal sorting algorithms are suited to a very wide range of ordering requirements. They work extremely well regardless of the data types involved, the size of data to be sorted, or the number of sort keys specified. They also make good use of available memory resources, and can spill to tempdb if required. It is a common misconception that SQL Server will try to perform a sort entirely in memory if it can. In fact the algorithms used are much more complex: they aim to achieve a balance between memory usage, average response time, while maintaining high levels of resource concurrency. Memory is a precious resource in the server, so SQL Server may spill a sort to tempdb, even if sufficient main memory is available. Read More...
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement