Executing Plan in SQL Server

Observing performance tuning, reducing or boosting the cost are displayed by executing plan.It allows us to see big picture how system works in background. Primarily it is needed to understand how a query is executed by server.Steps:

1. Parse - parsing whole statement whether it has mistake in it or not.
2. Resolve - getting info about tables,views so on.
3. Optimize - deciding how to execute the query more efficiently
4. Compile - creating executing plan about whole process
5. Execution - final point to realize the execution plan

All implemented execution plans are stored in Plan Cache, to prevent next queries' execution plans to reduce the performance by reading related plans throughout disc.

Querying SQL Server Metadata

To get administrative and informational activities, some functions are available to view mentioned tasks, like stored functions and dynamic management objects. Latter one provides server state information, that exist as 200 DMO functions. Each one's duty is separately shared, for databases,executions,transactions and so on. Most used are:

• dm_db_
• dm_os_
• dm_exec_
• dm_tran_
• dm_io_

DMO stored under sys schema, and executed by xp_ prefix via SELECT statement command.

General formula: select ... from sys.dm_<DMO>_<function>

Inline TVFs

Views are not sufficient in some conditions, to get parameterized function inside SQL queries.Adding parameters to queries makes them user defined,table valued.Latter one will be discussed as primary in this topic.Table Valued Functions(TVFs) have 2 types generally:Inline and Multi-line functions.

Inline TVFs let us to build basic functions embedded inside SELECT statements, also INSERT,UPDATE,DELETE clauses can be usable within.Setting parameters and function name creates new function where SELECT statement auto works,instead of writing each query manually.Latter one effects the performance of server profoundly,from cost aspect also has disadvantage.

Logical order of operations in SELECT statements

Lack of implementing of logical order in SELECT statements may force to encounter several obstacles. Elements of this command and writing some queries will be broadly explained in the post. It is needed to look at whole structure of SELECT statement, and understanding each conditions lets us put the command in logical orders, do not force yourself to memorize as formula.