Optimizer- Oracle and SQL Server, Hints

So you’re going to see a lot of posts from me in the coming months surrounding topics shared by Oracle and SQL Server.  These posts offer me the opportunity to re-engage with my Oracle roots and will focus on enhancing my SQL Server knowledge for the 2014 and 2016, (2017 soon enough, too) features, which I’m behind in.

I’m going to jump right in with both feet with the topics of hints.  The official, (and generic) definition of a SQL hint is:

“A hint is an addition to a SQL statement that instructs the database engine on how to execute the statement.”

Hints are most often used in discussion on queries, but they can assist in influencing the performance of inserts, updates and deletes, too.  What you’ll find is that the actual terminology is pretty much the same for hints in SQL statements for Oracle and SQL Server, but the syntax is different.

The Optimizer and Oracle

Oracle hints were quite common during the infancy of the Oracle Cost Based Optimizer, (CBO).  It could be frustrating for a database administrator who was accustomed to the Rules Based Optimizer, (rules, people!  If there’s an index, use it!) to give up control of performance to a feature that simply wasn’t taking the shortest route to the results.  As time passed from Oracle 9i to 10g, we harnessed hints less, trusting the CBO and by Oracle 11g, it started to be frowned upon unless you had a very strong use case for hinting.  I was in the latter scenario, as my first Oracle 11g database environment required not just new data, but a new database weekly and a requirement for me to guarantee performance.  I knew pretty much every optimal plan for every SQL statement in the systems and it was my responsibility to make sure each new database chose the most optimal plan.  I had incorporated complex hints, (and then profiles as we upgraded…)

With the introduction of database version Oracle 12c, it became a sought after skill to use hints effectively again, as many new optimizer features, (often with the words “dynamic” or “automated” in them) started to impact performance beyond what was outside the allowable.

SQL Server’s Query Optimizer

SQL Server’s optimizer took a big jump in features and functionality in SQL Server 2014.  With this jump, we started to see a new era of SQL Server performance experts with the introduction of SQL Server 2016 that moved even further into expertise with optimization, not only in collecting performance data via dynamic management views/functions, (DMVs/DMFs) but also in ability to influence the SQL Server Query Optimizer to make intelligent decisions with advanced statistics features and elevated hinting.

Hints have a more convoluted history in the SQL Server world than in the Oracle one.  I have to send some love and attention to Kendra Little after I found this cartoon she drew in regards to her frustration with the use of ROWLOCK hints:

After reading this, my plan is still to go deeper into a number of areas of performance, including the optimizers, but today, we’ll just stick to a high level difference on hinting in queries.

Hints

In our examples, we’ll focus on forcing the use of a HASH join instead of a nested loop, using an index for a specific table and a MERGE join.  Let’s say we want to use a hash join on the Employees and a merge join on the Job_history table.  We also want to make sure that we use the primary key for one of the employee ID joins, as a less optimal index usage results with lower costs even though the performance isn’t as optimal due to concurrency.

The query would look like the following in Oracle:

SELECT   /*+ LEADING(e2 e1) USE_HASH(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
         e1.Name, j.job_id, sum(e2.salary) total_sal
FROM     employees e1, employees e2, job_history j
WHERE    e1.employee_id = e2.manager_id
AND      e1.employee_id = j.employee_id
AND      e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

If there was a subquery as part of this statement, we could add a second set of hints for it, as each query supports its own hints in the statement after the word SELECT.

If we were to take the same statement in SQL Server, the hints would look a bit different.  Yeah, the following is about as close as I could get to “apples to apples” in hints and in TSQL, so please forgive me if it ain’t as pretty as I would have preferred it to be:

SELECT   e1.Name, j.Jobid, sum(pr.Salary) Total_Salary 
FROM     Employees AS e1, INNER MERGE JOIN Job_History AS j 
         LEFT OUTER HASH JOIN Employees AS e2
         WITH (FORCESEEK (emp_emp_id_pk(e2.EmployeeID)))
ON       e1.EmployeeID = e2.ManagerID 
WHERE    e1.EmployeeID = j.EmployeeID 
AND      e1.HireDate = j.StartDate 
GROUP BY e1.Name, j.JobID 
ORDER BY Total_Salary;

In a TSQL statement, each hint is placed at the object in the statement that its in reference to.  The hints are written out commands, (vs. more hint syntax required in Oracle) and the force seek on the primary key for Employees.

As you can see, Oracle signals a hint when put between /*+ and ending with a */.  Each requires some syntax and advanced performance knowledge, but all in all, the goal is the same-  influence the optimizer to perform in a specific way and [hopefully] choose the optimal plan.

Please let me know if you’d like to see more in this series, either by sending me an email to dbakevlar at Gmail or commenting on this post and I’m going to go start preparing for KSCOPE 2017–  Someone explain to me how it already is the end of June!! 🙂