Internal implementation of Query execution

Question

Internal implementation of Query execution

Deeptiprasad

SSC Eights!

Points: 999
More actions
October 16, 2008 at 12:40 am

#383099

Hi all,
i want to know what happens when a query is being executed. Means, what are the steps. As in .net we have object initialization, page load, page render, etc.
Sql server also has some steps like parsing, authentication,etc.
Can anyone tell me what all the steps sql server follows when a query is being executed?
Thanks
Deepti

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

At a very high level, parse, bind, optimise, process.

First the parser takes the SQL code, makes sure there are no syntax errors and then converts it into a tree-like structure. Then the algebratiser does the binding and name resolution and outputs another tree-like structure .

The optimiser takes the that and does the optimisation of it. It will generate possible execution plans, evaluate the cost and return the cheapest one that it can find.

The execution plan is passed to the query processor which is responsible for the actual processing.

If you run a query with the show executopn plan option, you'll be able to see the steps that the query processor will go through.

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

Deeptiprasad SSC Eights! Points: 999 More actions · Answer 2

Thanks gail.

Where can i get the detail of this(no point should be missed)?

And one more doubt.

How these things are communicated to memory?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's a chapter on it in "Inside SQL Server 2005: T-SQL Querying"

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

There was also a presentation from one of the SQL Server internals guys at the PASS summit a couple of years back called "A Day In The Life of a Query" that went into the processes in great detail. I know that's avalable, for a fee, at the PASS web site (www.sqlpass.org), but it might be out on the intraweb somewhere.

"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