Printed 2015/12/01 08:32AM

What happens when a query is submitted?

By Ramkumar (LivingForSQLServer), 2010/12/05

I know, We cant cover life span of query in short.  here is my another try to list some high level information to those who are new to SQL Server community.

1. SQL Server Network Interface (SNI) a protocol layer on Client establish connection to Server SNI using Network protocol such as TCP/IP

2. Query is encapsulated in TDS  (Tabular Data Stream) packet

3. SNI encapsulates the TDS (Tabular Data Stream) packet inside a standard communication protocol, 
    such as TCP/IP or named pipes

4. Server side SNI receives TDP packet on port 1433, unpack the query and send the query to Command

5. Parser first checks the plan in PLAN CACHE for existing query plan. If available plan will be used

6. If Query plan is not available in Plan Cache, Parser create QUERY TREE based on SQL command and send

7. Query Optimizers task is to find the good acceptable plan in less possible time

8. Query Optimizer has its own Rules Processor to examine the query. Query Optimizer go thru
    Multiple stages for generating a good acceptable plan

      Stage 1 (Pre Optimization Stage):

      8.1 Optimizer check possibility of TRIVIAL PLAN
              a. Table Scan if no index is available
              b. Index Seek if usable index is available for given search condition

     Stage 2 (Simple to Full optimization stages)

     8.2   In this stage, Optimizer will generate and evaluate many plans and will choose plan with LOWEST COST

     a. Behavior of Optimizer is, this won’t spend much time to find the best plan. Instead, Optimizer choose
         good acceptable plan with less cost in its threshold
     b. Optimizer have COST THRESHOLD to go for parallism (default  5 seconds) if more than 1 processor is
     c. Query processor picks an index based on multiple factors like, Uniqueness of Key, number of records,
         Predicates in WHERE clause.
        Optimizer refers Statistics  to evaluate these.
     d. Query optimizer most likely will ignore index if duplicates are huge

9. Execution plan is stored in Plan cache for reuse and passed to Query Executor

10. Query Executor checks PAGE availability in DATA CACHE. If not available PAGE is retrieved from Disk
      (System pages (like GAM, SGAM, IAM) and Index pages etc are used to manage, identify pages quicker)

11. Result set is formatted as relational table (as XML if FOR XML is specified) and encapsulated in TDS

12. Server side SNI encapsulates the TDS (Tabular Data Stream) packet inside a standard communication
      protocol, such as TCP/IP or named pipes and move it to Client

13.   Client SNI receives the TDS packet (Default port 1433), unpack the result set and show it in Grid

Here is some database level factors influence query performance

1. Usable Index
2. Sargable arguments
3. Statistics
4. Database configurations
5. Query Hints

Please share your comments on this post. your comments will help to improve content in coming days.

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.