How an Execution Plan is Picked by SQL Optimizer for SQL Server

The Query Optimizer for SQL Server is designed to be cost-based. This means it assesses several execution plans and estimates each one’s cost before picking the plan that seems the most cost-effective.

Since this tool is unable to take every single plan into consideration, it ends up performing a balancing act by taking into account the costs involved in both - locating possible plans and implementing them. In this blog, we will look at the SQL optimizer for SQL Server and the steps involved in its process of identifying the best execution plan.

Oracle SQL Query Execution Plan Picking Process

The SQL Server Database Engine is made of two major parts - the Relational Engine (Query Processor) and the Storage Engine. The Relational Engine is responsible for all the statements sent to the SQL Server. It is tasked with creating a plan that ensures optimal execution to deliver the best results.

The Storage Engine, on the other hand, is in charge of optimally reading data from both memory and disk while preserving data integrity. Statements are sent to SQL Server through SQL language(s). SQL only specifies what type of data needs to be fetched and not the methods that can be used for this purpose. Neither does it present any of the algorithms that can be applied to process the request.

Therefore, the first step the relational engine takes when it gets an Oracle SQL query is devising a plan as fast as it can. The plan must mention the most favorable or even efficient method to run said statement. The next thing to do is to use that plan to execute the statement.

All the steps are assigned to individual parts inside the query processor. Inside, it is the job of the Query Optimizer to design a plan, which it submits to the Execution Engine to execute to receive results accordingly.

Steps Involved in Creating an Execution Plan to Carry Out a Query

Considering the SQL optimizer for SQL Server, it takes a certain number of steps before the Relational Engine can provide the Execution Engine with the best or an efficient-enough execution plan. These steps have been mentioned here:

●       Parsing

●       Binding

●       Optimizing the Query

●       Running it

 

1. Parsing & binding - The statement, supposing it is correct, undergoes the parsing and binding process. A valid query has its output in the form of a logical tree that has nodes symbolizing each logical operation being mentioned in the statement. For example, performing an inner join or a read action in the specific table will both be represented as separate nodes in the logical tree.
2. Query Optimization - The logical tree helps in the query optimization procedure that is made up of the following steps -

1. Creating Potential Execution Plans – With the help of the logical tree, the Relational Engine or the Query Optimizer generates more than one potential method (execution plan) to run the query without incurring excessive costs. An Execution plan is essentially a bundle of physical tasks that must be carried out in order to provide the expected results as represented by the logical tree. These operations are part of the Oracle SQL query and include tasks such as nested loop joins and index seeks.
2. Evaluating the Cost of Every Plan – Even though the Relational Engine doesn’t create each and every execution plan that can be generated, it does examine the expenses incurred by every plan it actually creates, in terms of cost and resources. Among these, it selects the plan whose expenses are evaluated and found to be the lowest. This plan is then relayed to the Execution Engine.

3. Running the Query and Caching the Plan – The statement is run as per the plan that has been selected and sent to the Execution Engine by the Query Optimizer. This plan can be saved in a designated area known as the plan cache, which is located in memory.

 

SQL Performance Monitoring & 6 Other Top Basic DBA Skills

As time passes, human resources are rapidly getting replaced by computing resources, affecting the task range of an Oracle DBA. You may have observed the consolidation of Oracle instances onto a bigger server with a greater number of CPUs. Due to this, fewer DBA professionals are needed by organizations. 

At the same time, a DBAs list of tasks keeps increasing since the same set of activities needs to be completed by fewer staff. From schema design handling to SQL performance monitoring, there are many essential database management duties assigned to surviving DBAs. On the bright side, Oracle DBAs no longer have to perform tedious tasks such as implementing patches on several servers and perpetually re-assigning server resources. re-allocating server resources, and t
uning many servers.

6 Most Important Skills for a Database Administrator

Since a professional Oracle DBA is generally brought into crucial IT department undertakings, authorities may often give preference to professionals with a wider range of both Oracle-based and non-Oracle-based job skills.

While the applicants usually have all the Oracle-based skills gained in academic Information Technology and Computer Science courses, they may or may not have some of the Non-Oracle job skills, including the following:

• Database Design – Numerous projects need an understanding of concepts such as data modeling methods, database normalization, and STAR schema.

• System Analysis – Oracle specialists often end up playing an important part in designing and analyzing new DBMSs. Therefore, they should be aware of concepts such as DFDs (data flow diagrams), CASE tools, methodologies in entity-relation shaping, and data dictionary techniques.

• 
  Change Control Management – Aside from knowing how to performance tune SQL query, the candidate should know how to implement change control as they are likely to be handed over that responsibility. They also must be able to make sure those changes are sufficiently carried out to the production database aside from knowing about the third-party change control resources such as the UNIX Source Code Control System (SCCS).

• Physical Disk Storing – They need to have knowledge of RAID, cache controllers, disk hardware architecture, and disk load balancing.

• Backup and Recovery – Since a majority of projects use third-party backup and recovery technologies, the DBA requires proper experience implementing these methods.

• 
  
  Data Security skills – Excellent knowledge of MySQL SQL performance tuning and how to implement database security measures in RDBMS, especially role-based security is quite useful.

Conclusion

Many Oracle users and specialists wrongly believe that a database administrator in Oracle only needs to have technical skills such as how to performance tune SQL query. However, the Oracle DBA needs to have a much broader range of skills as they not only have the responsibility of the design but also various other elements of the database such as implementation, storage, and data recovery. 

Therefore, they require good technical and communication skills to handle all the different aspects of database management in the best way possible and keep operations streamlined among all these areas. 

SQL Performance Monitoring: Opting for a Correlated View

One of the issues users face is sporadic high CPU usage by their SQL Server, which leads them into a frenzied search of the queries creating this problem.

If you’ve come across such a situation, you may have considered starting up Perfmon (Windows Performance Monitor). Perfmon displays each occurrence of said spikes along with the exact CPU usage in each instance.

SQL Performance Monitoring Using Perfmon

It won’t display the code causing such spikes, but that’s not much to worry about, especially if you want SQL performance monitoring. This is because you already have a free graphical tool that can display both - the CPU usage spikes and the code that’s causing them.

Take a look at the following steps to use this ‘free’ tool -

Step 1: Correlate data. A correlation can be performed between Perfmon output and trace information to get the view required to fix this issue. This proves quite useful because when you click anywhere on the lower graph, you jump to the corresponding code in the upper tabular trace data being executed in that moment.

You will also find buttons to take the user to the topmost and bottommost values for any perfmon counter in particular. These are particularly helpful in database query optimization.

Step 2: Create the Perfmon counter log and Profiler trace file. However, doing all this requires the creation of a Perfmon counter log as well as a Profiler trace that must run simultaneously. The Profiler trace file has to have the “startdate” column along with the metrics you need.

Note: Although DBAs usually have experience with traces, in case you are just starting out, you can simply go for a database query optimization tool straightaway.

Step 3: Load the Perfmon file into Profiler. To do this, just open Profiler and load the trace file created in Step 2. From there, choose the option that says “Import Performance Data” in the File menu. You will have to locate the newly created Perfmon file.

Tip: Once you get to this point, you will be able to choose all the Perfmon metrics you want to view in graphical form. However, choosing only a few at a time is recommended, because the graphic window is rather small to fit more, and it can get too congested and unreadable if you try to display too many metrics. Also, you need to visit Import Performance Data again in order to adjust your selection.

Final Thoughts

Experts always find it surprising when they visit client sites only to find a considerable number of otherwise-knowledgeable SQL professionals unaware of this interoperability between such popular and extensively used monitoring apps.

Therefore, as a DBA, you may want to follow the three tips given below and avoid such ignorance:

●      Inculcate the fundamentals into your development environment. Ensure that both - the app developers as well as the Database Administrators comprehend the steps mentioned above to use SQL performance tuning to its fullest.

●      Begin application construction with database performance in mind.

●      Make sure you own a convenient, automated method of locating and resolving poorly performing SQL queries.

These are as simple as they sound, which is why it’s surprising when many companies fail at these three simple things.