Concept behind SQL Query execution

Summary :

When you submit a query to a Sql Server DB then a number of process on the server go to work on that query.
- Some process occure in the Relational Engine
- Some process occure in the Storage Engine

In the relational engine the query is parsed and then processed by the query optimizer, which genarate an execution plan. The plan is sent(in binary formate) to the storage engine, which it then uses to retrieve or update the underlying data.

The process in storage engine such as locking, index maintenence and transection occure.

Note : The execution plan is created in the relational engine.

Details :

When you pass a T-Sql statement to the Sql Server system then the first palce it goes to is the relational engine. As the T-Sql arrives then it passes througn a process that check the T-Sql syntax. This process is known as Query Parsing. The output of this process is a Parse Tree.

Then, this parse tree is passed to a process called the Algebrizer. The algebrizer resolves all the names of the various objects, tables and columns, reffered to within the query string. The algebrizer indentify (at the individual level) all the types (varchar(50) vs nvarchar(25) and so on) of the object being occure. It also determine the location of the aggregate function within the query, a process called Aggregation Binding. The algebrizer process is important because the quey may have the alias or synonms name that does not exist in the DB, that need to be resolved. The output of the algebrizer is a binary called Query Processor Tree which is then passed to the Query Optimizer.

Then using the query processor tree (o/p of algebrizer) and the statistics it has about the data and applying the model The Query Optimizer generate an execution plan for query. The optimizer will generate and evalute many plans and will choose the lowest cost plan.

Once the optimizer arrives at an execution plan, the actual plan is created and stored in a memory space known as Plan Cache. - Unless an identical plan is already exist in the cache. As the optimizer generates potential plan, it compare them to previously generated plans in the cache. If it find a match the it will use that plan.

After the plan is finilized the plan is passed to the Storage engine and in the storage engine, many process like locking, indexes mentainence and tranction are performed and finally the query is executed accoring to the plan And result is displayed.


Reuse of Execution Plan

It is expensive for server to generate the execution plan so sql server will keep and reuse plans whenever possible.

When a query is submitted to the server then an estimated plan is created by optimizer. Once that plan is created and before it get passed to the storage engine, the optimizer compare this estimated plans to actual plans that already exist in the plan cache. If an actual plan is found that matches the estimated one then the optimizer will reuse the existing plan, since it is already been used before by the query engine. This reuse of plan, avoids the overhead of creating actual plan for large and complex queries.

Each plan is stored once. Execution plans are not kept in memory these are stored in Cache (Plan Cache).

The lazywriter process, an internal process that work to free all types of cache (including plan cache using AgeFormula), periodically scans the objects in the cache and decrease this value by one each time.

Query - How to find my friends in my Department?

[Just Copy & Paste]

declare
@EmployeeDepartment table(DeptID int,EmpID int,EmpName varchar(50))

insert into @EmployeeDepartment(DeptID,EmpID,EmpName)
values(10,101,'Manish')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)
values(10,102,'Milan')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(10,103,'Mukesk')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(12,104,'Amit')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(12,105,'Ashok')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(12,106,'Anand')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(13,107,'Suresh')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(13,108,'Sagar')
insert into @EmployeeDepartment(DeptID,EmpID,EmpName)values(14,109,'Pradeep')

select * from @EmployeeDepartment

SELECT DeptID 'MyDeptID',EmpID 'MyEmpID',EmpName as 'MyName',
(
SELECT EmpName + ',' FROM @EmployeeDepartment E2
WHERE E2.DeptID= E1.DeptID
and E1.EmpID <> E2.EmpID
ORDER BY EmpName
FOR XML PATH('')
) AS MyDeptFriend
FROM @EmployeeDepartment E1

How to create an xml file from DB Table?

[Just Copy & Paste]

declare
@Employee Table(EmployeeID int,EmployeeName varchar(50))

insert into @Employee(EmployeeID,EmployeeName) values(101,'Manish')
insert into @Employee(EmployeeID,EmployeeName) values(102,'Arun')
insert into @Employee(EmployeeID,EmployeeName) values(103,'Priya')
insert into @Employee(EmployeeID,EmployeeName) values(104,'Kumar')
insert into @Employee(EmployeeID,EmployeeName) values(105,'Asish')
insert into @Employee(EmployeeID,EmployeeName) values(106,'Rasna')

SELECT EmployeeID,EmployeeName FROM @Employee FOR XML RAW

SELECT EmployeeID,EmployeeName FROM @Employee for Xml RAW,elements,root('EmployeeInfo')

-- [Auto : means it will make the node name as TableName(@Employee)]

SELECT EmployeeID,EmployeeName FROM @Employee FOR XML Auto

SELECT EmployeeID,EmployeeName FROM @Employee for Xml AUTO,elements,root('EmployeeInfo')

How to create a XML file using T-SQL?

[Just copy & paste]

SELECT
(
SELECT 'Ramu' AS Name,'IT' AS Department FOR XML PATH ('Employee'),
TYPE
),
(
SELECT 'Anil' AS Name,'HR' AS Department FOR XML PATH('Employee'),
TYPE
),
(
SELECT 'Raj' AS Name, 'Fin' AS Department FOR XML PATH('Employee'),
TYPE
),
(
SELECT 'Kunal' AS Name,'S/W' AS Department FOR XML PATH('Employee'),
TYPE
)
FOR XML PATH(''),
ROOT('EmployeeInfo')

Concatenation of Row Values using T-Sql

[Just copy & paste]


declare
@EmployeeDepartment table(DeptID int,EmpName varchar(50))

insert into @EmployeeDepartment(DeptID,EmpName)values(10,'Manish')
insert into @EmployeeDepartment(DeptID,EmpName)values(10,'Milan')
insert into @EmployeeDepartment(DeptID,EmpName)values(10,'Mukesk')
insert into @EmployeeDepartment(DeptID,EmpName)values(12,'Amit')
insert into @EmployeeDepartment(DeptID,EmpName)values(12,'Ashok')
insert into @EmployeeDepartment(DeptID,EmpName)values(12,'Anand')
insert into @EmployeeDepartment(DeptID,EmpName)values(13,'Suresh')
insert into @EmployeeDepartment(DeptID,EmpName)values(13,'Sagar')
insert into @EmployeeDepartment(DeptID,EmpName)values(14,'Pradeep')

select * from @EmployeeDepartment

SELECT E1.DeptID,
(
SELECT EmpName + ',' FROM @EmployeeDepartment E2
WHERE E2.DeptID= E1.DeptID
ORDER BY EmpName
FOR XML PATH('')
) AS Emp_Name
FROM @EmployeeDepartment E1
GROUP BY DeptID

Regards
Manish