Designing Queries for Performance

Practice Labs Module
Time
50 minutes
Difficulty
Intermediate

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises: Optimize and tune queries, Analyze execution plans, Evaluate the use of row-based operations vs. set-based operations.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Overview

Introduction

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Optimize and tune queries
  2. Analyze execution plans
  3. Evaluate the use of row-based operations vs. set-based operations

Exercise 1 - Optimize and tune queries

In this exercise, you will perform the tasks related to applying query hints and table hints in SQL Server 2008.To better understand this technology, refer also to your course material or study the material available at the following link:

Exercise 2 - Analyze execution plans

Execution order

The way in which you read a graphical execution plan is Right to Left, Top to Bottom. For the XML plan, they are nested elements. In the text plan, these are identified by the amount of indentation. When examining the execution plan, we should be thinking of the SQL Statement Processing and the particular path chosen when presented with the given inputs (index vs. table, scan vs. seek) and the order in which these inputs are joined. We should also think about how the order of these operations can be influenced through the various hints, such as FORCE ORDER, or through new indexes, updated statistics, etc. The actual items which are present in the execution plan can be greatly influenced by the amount of data being returned, the columns which are being filtered or joined, the sort order of the data, how current the statistics are, if the data being compared is of an appropriate type, etc. If a change is made to any of these items, the actual operators present, and the order in which they are executed in the query can be completely changed. The representation of these items is a hint that the changes you are making are having an impact (either positive or negative). When investigating the sort order of cached items (such as stored procedures) you can look for items that don’t seem to make sense as a hint that the optimizer might have a bad plan stored.

Logical and physical operators

The logical and physical operators, or iterators, are represented in the graphical execution plan with a blue icon. These are going to be the bulk of the objects which you will see in your query plans and will usually represent the items you are looking for in determining if the query is executing as one would expect. This is the area in which you will determine if the correct indexes are being used, if the table or index is being scanned instead of using a seek, if the query is using parallel execution, etc.

  • Logical Operators

Logical operators describe the relational algebraic operation used to process a statement. In other words, logical operators describe conceptually what operation needs to be performed.

  • Physical Operators

Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. For example, some physical operators access columns or rows from a table, index or view. Other physical operators perform other operations such as calculations, aggregations, data integrity checks or joins. Physical operators have costs associated with them.

Join operators

There are three query-level hints-one for each physical join operator. These hints are:

  • LOOP JOIN
  • HASH JOIN
  • MERGE JOIN

Exercise 3 - Evaluate the use of row-based operations vs. set-based operations

Row-based logic vs. set-based logic

With that said, there are several valid scenarios where row-based logic is correct. For instance, it is useful for many administrative tasks to scroll through a cursor to query or update every database / schema / table / index / user, to manipulate ordered data, to perform running aggregations, to break up large updates or deletions, etc.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.