Know How SQL Server Executes a Query in Short

The present blog explains the entire process as to how MS SQL server, the client-server platform executes a query request, issued by user. Sendingrequests, which is the only means of communication with the server database can be achieved by the use of some specific in-built commands that the database in SQL server understands. The reason for acquiring the way SQL server executes a query is that it shall assist developers to write better coding for the database. Though understanding requires a complete know how and is a lengthy task, an attempt has been made to make users acquaint with the process in short by means of the image displayed below.

Summary of ‘Query Execution’ Procedure:  The complete procedure ofquery execution can be summarized as below:

                                                                                          

Step 1: At the very beginning a Request is sent, the Request in turn creates a new Task. In fact this step involves Creation of Task.

Step 2: If workers are not free to take up a Task for execution, a queue of Task gets formed and remains in pending state for execution.

Step 3: When any Worker in the threadpool becomes free after the execution of any Task, the idle Worker then picks up a pending Task. This step is all about an idle worker picking up a Pending Task.

Step 4: This is the Task Execution step of the query execution process. Here, firstly an execution plan is compiled. This in turn involves parsing, compilation and optimization. Thereafter, Query plan is executed where the operators access data through the buffer pool.

Step 5: Then, Result set is returned during execution.

Step 6: Finally, at the end the Task gets executed and complete. The involved Worker returns to idle state and the process gets over.

The process of SQL Server execution of queries can be understood easily by the below mentioned steps.

1.    Creation of ‘Request’

At the very beginning when a new request is completely sent over the Tabular Data Stream (TDS), a protocol that is used for communication between the sender’s application and the server database; task is created by server database engine for handling the placed request. Once the request gets formed it can take any one of the three below mentioned forms.

1.    Batch Request

2.    Remote Procedure Call Request

3.    Bulk Road Request

Note - The list of requests in the server can be queried fromsys.dm_exec_requests.

2.    Creation of ‘Task’

The task that is created to handle the request represents the request right from the start to end. When a new request reaches the server and then the task that is created to handle the particular request remains in ‘pending’ state. The reason being, that at the current stage the server has no clue as to what exactly the request signifies. Thus, a pile of tasks gets formed and remains in queue. In such a case the task has to be executed first, for which the server database engine should assign a worker to it.

Note - The list of tasks in the server can be attained by sys.dm_os_tasksquery. 

3.    Assigning ‘Worker’ a Task

Workers are one of the main components and actually the threadpool of SQL Server. Multiple of them are created in the beginning i.e. at the very start of the server. Although more of them can be formed on-demand up to a limit of configured maximum worker threads. All the workers remain in waiting state for the ‘pending’ tasks to become available, from requests coming into the server. Thereafter, each idle worker picks up exactly one task and executes it. In the mean time all the engaged worker remains unavailable until the execution of task gets over. Therefore, Tasks that remain in ‘pending’ state due to lack ofavailable workers, shall have to stay pending until execution of any task gets complete and the worker that executed the particular task becomes available.

Note - The lists and state of workers inside SQL Server can be checked by querying sys.dm_os_workers.

4.    Parsing and Compilation

Thereafter, an execution plan is complied. In this step, before a Task starts executing a Request the first thing it needs to do is to gain a proper understanding about the content of the specific Request. The T-SQL text that exists inside the request gets parsed and an abstract syntax tree gets formed for representation of the particular Request. Actually, the conclusion is that all the existing Requests are parsed and thereby compiled in this step. However, in case any error occurs at this stride the Requests gets terminated with a compilation error.

5.    Optimization

Optimization is the next important phase in the complete life cycle of Task Execution, so as to select an optimum data path access. In case of SQL server, the best way of optimization is chosen by first observing the costs of each possible alternative. Thereby, the alternative with the lowest cost is chosen as the query plan to be utilized, to make the process economical. It is quite obvious that exploring all the possible alternatives consumes much time. Hence, once a query plan is created it is also cached, for use in future. So, similar Requests that might be requested in future can skip over the optimization phase; provided an already compiled and optimized query plan is found in the internal cache of SQL Server. 

6.    Execution & Result

Finally, after compilation the Requests gets executed, the Task gets completed and the Worker becomes available and free to pick up another Task in pending condition. Once a query plan is selected by the Optimizer, the ‘Request’ or say the ‘Query plan’ can be executed. Actually, this is the last important step as to how SQL server executes a query. Finally, the Result set is given back which is the end of the process.

Migrate MySQL Databases, Users, and Privileges to Different Server

1.) Databases:

• Get list of databases

1	mysql -uroot -proot_password -e "show databases"

• Dump all databases

1	mysqldump -uroot -proot_password --all-databases > databases.sql

Note: you can dump only the databases that you want to move. Also, you need to exclude the “mysql” database itself if you are moving into an mysql server with existing records.

* Transfer sql dumps to new server with your preferred way (ftp, scp, rsync, etc)

• Create Databases on new server.

1	mysql -uroot -proot_password -e "create database database_name_1;"

1	mysql -uroot -proot_password -e "create database database_name_2;"

1	mysql -uroot -proot_password -e "create database database_name_3;"

• Import database dumps to new server

1	mysql -uroot -proot_password < databases.sql

2.) Database Users and Privileges:

• Get list of database users and their privileges.

1	mysql -B -N -uroot -proot_password -e "SELECT user, host FROM user" mysql

• Then get the privileges and password hash for each users from the above step.

1	mysql -B -N -uroot -proot_password -e "SHOW GRANTS FOR 'db_user'@'db_host'"

Note: Above command will output similar to these two lines or you will get more if the db_user has access to more than one databases.

1	GRANT USAGE ON *.* TO 'db_user'@'db_host' IDENTIFIED BY PASSWORD '*23AW9301879BCC8BC24343CDCDC40F375606C43D'

1	GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'db_host'

• Then you need to execute all these lines or queries on the new MySQL server. You can put them all in a text file (example: users.txt) and add semi-colon on each line, then import to mysql (example below):

1	mysql -uroot -proot_password < users.txt

Table Partitioning Implementation with advanced scenarios (Part 2)

Points covered in the last post :

• What is table partitioning?
• Why we need to use table partitioning?
• PROS and CONS of table partitioning?
• Partitioned Table Rules and Gotchas
• What is difference Table partitioning Type in SQL Server?

before going to the Technical demo we should know what is table partitioning dependence

PARTITION FUNCTION:

that maps the rows of a table or index into partitions based on the boundary_value , we can build the Partition Function by two ways based on our Requirement and our business needed

• RANGE RIGHT
• RANGE LEFT

another thing the boundary_value we can do it on three data type

• INT COLUMN
• DATETIME COLUMN
• CHAR COLUMN

PARTITION SCHEME:

that maps the partitions of a partitioned table or index to filegroups , partition scheme must be created on partition function , partition scheme can be used for one or more partitioned tables, indexes, and indexed views.

PARTITION TABLE/INDEX:

is tied to a particular partition scheme when it is created , partition table has only an indirect relationship, through the partition scheme, to the partition function at the end The relationship between a partition function and a partition scheme is one-to-many as is the relationship between a partition scheme and partitioned tables and indexes , because moving data in and out of a partitioned table usually requires modifying the partition function for more information :

• Partitioned Tables and Indexes in SQL Server 2005 at http://msdn.microsoft.com/en-us/library/ms345146.aspx
• Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server athttp://technet.microsoft.com/en-us/library/cc966457.aspx

let’s go now for the TECHNICAL DEMO to know How we can implement the table partitioning step by step from Scratch

How to Implement Partitioned Tables 

to implement any partitioned table from SCRATCH we have basic 6 steps we will do it now by T-SQLand by SQL SERVER WIZARD

• Create Database (T-SQL / Wizard)
• Create FILEGROUP (T-SQL / Wizard)
• Create files and add it to the FILEGROUP (T-SQL / Wizard)
• Create partition Function (RANGE RIGHT / RANGE LEFT) (T-SQL only)
• Create partition Scheme (T-SQL only)
• Create Table Partitioning (T-SQL only)

at the first i will do it by T-SQL

• Create database 

I will create new database [DBForPartitioning] to be for all our workshop to the end of the series

CREATE DATABASE [DBForPartitioning]

GO

ALTER DATABASE [DBForPartitioning]

MODIFY FILE ( NAME = N'DBForPartitioning', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB );

ALTER DATABASE [DBForPartitioning]

MODIFY FILE ( NAME = N'DBForPartitioning_log', SIZE = 128MB , FILEGROWTH = 128MB );

GO

• Create FILEGROUP

here we will create four filegroup (FG1 ,FG2 , FG3 , FG4) to can use it on the Partition Scheme

USE [master]

GO

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG1]

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG2]

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG3]

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG4]

GO

• Create / Add files to filegroup

in this step i will create four files and add it on the FILEGROUP and all of this i will do it dynamically

Use DBForPartitioning

Go

DECLARE @path NVARCHAR(256) ,

@i TINYINT= 1 ,

@sql NVARCHAR(4000);

SELECT TOP 1

@path = LEFT(physical_name, LEN(physical_name) - 4)

FROM sys.database_files

WHERE name = 'DBForPartitioning'

WHILE @i <= 4

BEGIN

SET @sql = N'ALTER DATABASE DBForPartitioning ADD FILE (name=File' + CAST(@i ASNCHAR(1)) + ',

filename=''' + @path + N'File' + CAST(@i AS NCHAR(1)) + '.ndf' + ''',

size=128MB, filegrowth=256MB) TO FILEGROUP FG' + CAST(@i AS NCHAR(1))

RAISERROR (@sql,0,0)

EXEC sp_executesql @sql;

SET @i += 1;

END

GO

Range Direction Right and Datetime Boundary Values

Next three steps i will do every thing on the RANGE RIGHT and RANGE LEFT so i will work on parallel

• Create partitioning function

in this case i will do partition function with boundary_value DATETIME and

Use DBForPartitioning

GO

CREATE PARTITION FUNCTION PF_DBForPartitioning_RangeRight (datetime)

AS RANGE RIGHT FOR VALUES ( '2008-01-01','2009-01-01', '2010-01-01');

• Create Partition Scheme

i will create Partition scheme on PF(PF_DBForPartitioning_RangeRight) on the four filegroup

</pre>

Use DBForPartitioning

GO

CREATE PARTITION SCHEME PS_DBForPartitioning_RangeRight

AS PARTITION PF_DBForPartitioning_RangeRight

TO (FG1, FG2, FG3 ,FG4)

GO

<pre>

• Create Table partitioning

now i will create table Partitioning on Partitioning Scheme [PS_DBForPartitioning_RangeRight] on datetime Column

Use DBFOrPartitioning

GO

CREATE TABLE Partitioning_RangeRight

(Partitioning_ID int ,

Partitioning_time datetime)

ON PS_DBForPartitioning_RangeRight (Partitioning_time);

GO

Now the 6 basic steps for any table partitioning is finished and i will go now for fill the table by some test data then i will show some wonderful Stored Procedures and Scripts it will return to us all the information about the Partition Function ,Partition Scheme and table Partition , ….etc

• Fill the data in the table 

</pre>

Use DBFOrPartitioning

GO

INSERT INTO Partitioning_RangeRight (Partitioning_ID, Partitioning_time)

SELECT 1 ,'2007-01-01'UNION ALL

SELECT 2 ,'2007-10-01'UNION ALL

SELECT 3 ,'2008-01-01'UNION ALL

SELECT 4 ,'2008-08-09'UNION ALL

SELECT 5 ,'2008-12-30'UNION ALL

SELECT 6 ,'2009-01-01'UNION ALL

SELECT 7 ,'2009-05-24'UNION ALL

SELECT 8 ,'2010-01-24'UNION ALL

SELECT 9 ,'2010-05-24'UNION ALL

SELECT 10,'2011-05-24'

GO

<pre>

Return Partition Information by T-SQL Scripts

• Select table Partitioned in DB

Use DBFOrPartitioning

GO

select distinct t.name

from sys.partitions p

inner join sys.tables t

on p.object_id = t.object_id

where p.partition_number <> 1

• Stored procedure to Return the Partitioned Columns

Exec partitioning_Column ‘Partitioning_RangeRight’

Create Proc partitioning_Column (@Table_Name Nvarchar(500))

AS

Begin

SELECT CAST(ic.partition_ordinal AS INT) AS [ID],

c.name AS ColumnName

FROM sys.tables AS tbl

INNER JOIN sys.indexes AS idx

ON idx.object_id = tbl.object_id

AND idx.index_id < 2

INNER JOIN sys.index_columns ic

ON (ic.partition_ordinal > 0)

AND (ic.index_id = idx.index_id

AND ic.object_id = CAST(tbl.object_id AS INT))

INNER JOIN sys.columns c

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

WHERE (tbl.name = @Table_Name

AND SCHEMA_NAME(tbl.schema_id) = 'dbo')

ORDER BY [ID]

END

• Select Partition_id with Partition_number

select partition_id, index_id, partition_number, Rows

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Partitioning_RangeRight'

GO

• Select the PartitionFunction and PartitionScheme Name

select ps.Name PartitionScheme, pf.name PartitionFunction

from sys.indexes i

join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

join sys.partition_functions pf on pf.function_id = ps.function_id

where i.object_id = object_id('Partitioning_RangeRight')

• Stored procedure to return all the detail about table Partitioned and the data exist on it

Create proc PartitionTableDetail(@tablename Nvarchar(500))

As

begin

SELECT

ISNULL(quotename(ix.name),'Heap') as IndexName

,ix.type_desc as type

,prt.partition_number

,prt.data_compression_desc

,ps.name as PartitionScheme

,pf.name as PartitionFunction

,fg.name as FilegroupName

,case when ix.index_id < 2 then prt.rows else 0 END as Rows

,au.TotalMB

,au.UsedMB

,case when pf.boundary_value_on_right = 1 then 'less than' whenpf.boundary_value_on_right is null then '' else 'less than or equal to' End asComparison

,fg.name as FileGroup

,rv.value As Rang_value,

T.name AS Range_Type

FROM sys.partitions prt

inner join sys.indexes ix

on ix.object_id = prt.object_id and

ix.index_id = prt.index_id

inner join sys.data_spaces ds

on ds.data_space_id = ix.data_space_id

left join sys.partition_schemes ps

on ps.data_space_id = ix.data_space_id

left join sys.partition_functions pf

on pf.function_id = ps.function_id

inner join sys.partition_parameters PP

on pf.function_id = PP.function_id

inner join sys.types t

on t.system_type_id = PP.system_type_id

left join sys.partition_range_values rv

on rv.function_id = pf.function_id AND

rv.boundary_id = prt.partition_number

left join sys.destination_data_spaces dds

on dds.partition_scheme_id = ps.data_space_id AND

dds.destination_id = prt.partition_number

left join sys.filegroups fg

on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)

inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]

,str(sum(used_pages)*8./1024,10,2) as [UsedMB]

,container_id

from sys.allocation_units

group by container_id) au

on au.container_id = prt.partition_id

WHERE prt.OBJECT_ID = object_id(@tablename)

order by ix.type_desc;

END

Exec PartitionTableDetail 'Partitioning_RangeRight'

this not the end still i have lot of Scripts it will help any DBA to know what he need about Partitioning table in any Environment without much effort and without time only 5 SEC he can know every thing next post i will explain the below point

• Range Direction left and Datetime Boundary Values
• Table partitioning by SQL Server wizard
• new Scripts for investigation