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
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
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
4. Database configurations
5. Query Hints
Please share your comments on this post. your comments will help to improve content in coming days.
I've grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It's a fairly human look at what's involved in sailing on a Trident missile submarine...