How SQL Server performs aggregation and sorting ?

  • Hi

    I would like to know how the T-SQL Query is processed in SQL Server ?

    I know that First the SQL Query will be send to "Query Processor Engine"

    then the optimal execution plan will be send to the "Storage Engine",

    the storage engine will execute the query and get the result.

    Let assume I have query like below

    1. select date1,count(orderId) from tblOrder group by date1 order by date1 desc

    Here my doubt is :

    Will Storage Engine performs the aggregation and sorting or is there any other place where this can be performed in SQL Server.

    [font="Calibri"]Raj[/font]
  • I guess I don't understand the question. The storage engine does the data retrieval, including sorting and aggregating the data, as you said. What is the concern with where this occurs?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The storage engine just does data retrieval. It's job it to get pages from disk into memory and, when necessary, back. The query processing engine operates only on pages in memory amd is responsible for all aspects of actually processing the query, including filters, aggregates, sorts and all other query operators.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi

    You mean the Storage Engine pulls the data and send it to Query Processor

    then Query Processor will operate on the data to form the result (i.e) Group by , count(*) etc.,

    if so can you send me any URL which gives more details on this ..

    [font="Calibri"]Raj[/font]
  • The query processor operates on pages in memory. If it needs a page and the page isn't there, it sends a request to the storage engine to fetch that page. The storage engine will fetch the page, place it in memory, then signal the query processor that the IO is complete. The query processor will then use the page from memory.

    Books online has a bit on this. You can also look at the first book of the Inside SQL Server 2005 series, the Storage Engine.

    Other places to look would be the storage engine team's blog and possibly the query processing team's blog

    http://blogs.msdn.com/sqlserverstorageengine/default.aspx

    http://blogs.msdn.com/sqlqueryprocessing/default.aspx

    One other thing, it's not the query processor that generates an execution plan. That's the job of the query optimiser.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply