Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

What happens when a query is submitted?

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
   Parser

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
   it to QUERY OPTIMIZER

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
            Examples:
              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
  
     Note:

     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
         available
     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
      packet

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.

Comments

Posted by Anonymous on 5 December 2010

Pingback from  Dew Drop – November 5, 2010 | Alvin Ashcraft's Morning Dew

Posted by Anonymous on 5 December 2010

Pingback from  What happens when a query is submitted? – SQL Server Central - sql

Posted by Anonymous on 5 December 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, What happens when a query is submitted? - LivingForSqlServer         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 6 December 2010

Pingback from  How to write a PHP coding to list out all files and directories as links to them? | Cheap Web Design And Hosting

Posted by Sundeep Arun on 6 December 2010

Very nice compressed article which covers all the important aspects...Keep it up...expecting more from you...

Posted by FabricioLimaDBA on 6 December 2010

Great Explanation.

Posted by Anonymous on 6 December 2010

Pingback from  Casino Affiliate Start Information  » Blog Archive   » How You Can Market Yourself Online

Posted by Anonymous on 7 December 2010

Pingback from  What kind of server is recommended for this office?

Posted by Gopinath on 8 December 2010

Nice Article.. It would be great if this can be continued to transaction like how the data is updated, deleted and inserted into the data page. What are the steps involed during these operation and how the transaction is logged. How this transaction log is used during recovery.

Thanks

Posted by russ960 on 8 December 2010

I would note that the Query Optimizer first checks that the query is syntactically correct so that it does not waste time looking for a plan on a query that cannot complete.  

Posted by sridharnalluri on 9 December 2010

Well Articulated...Thanks

Posted by fritzfs on 18 December 2010

Congrats!

Maybe you can build a PDF-version which would look nice for a wall?

Posted by arehman626 on 25 December 2010

I created a PDF mindmap so that it is easily readable and grasp at once.

Download from hotfile.com/.../What_happens_when_a_query_is_submitted..pdf.html

Posted by Ramkumar on 26 December 2010

Thanks arehman626

Posted by Anonymous on 21 July 2011

Pingback from  Query – Von der Wiege bis zur Bahre

Leave a Comment

Please register or log in to leave a comment.