Query optimization

We will examine the query optimization behavior of Microsoft SQL Server. To properly understand the optimizer's behavior, in the first 5 exercises, we will explain the queries and the behavior too. The rest of the exercises is individual work where it is your task to infer the reason for a specific plan. Your task is to document the behavior and submit the documentation of all exercises.

Pre-requisites and preparation

Required tools to complete the tasks:

• Windows, Linux, or macOS: All tools are platform-independent, or a platform-independent alternative is available.
• Microsoft SQL Server
  • The free Express version is sufficient, or you may also use localdb installed with Visual Studio
  • A Linux version is also available.
  • On macOS, you can use Docker.
• Visual Studio Code or any other tool for writing markdown
• SQL Server Management Studio, or you may also use the platform-independent Azure Data Studio is
• Database initialization script: mssql.sql
• GitHub account and a git client

Materials for preparing for this laboratory:

• Markdown introduction and detailed documentation
• Using Microsoft SQL Server: description and video
• The schema of the database
• Microsoft SQL Server query optimization
  • Check the materials of Data-driven systems

Initial steps

Keep in mind that you are expected to follow the submission process.

Create and check out your Git repository

1. Create your git repository using the invitation link in Moodle. Each lab has a different URL; make sure to use the right one!

2. Wait for the repository creation to complete, then check out the repository.

   If you are not asked for credentials to log in to GitHub in university computer laboratories when checking out the repository, the operation may fail. This is likely due to the machine using someone else's GitHub credentials. Delete these credentials first (see here), then retry the checkout.

3. Create a new branch with the name solution and work on this branch.

4. Open the checked-out folder and type your Neptun code into the neptun.txt file. There should be a single line with the 6 characters of your Neptun code and nothing else in this file.

Open the markdown file

Create the documentation in a markdown file. Open the checked-out git repository with a markdown editor. We recommend using Visual Studio Code:

1. Start VS Code.

2. Use File > Open Folder... to open the git repository folder.

3. In the folder structure on the left, find README.md and double click to open.

4. Edit this file.

5. When you create a screenshot, put the file in this directory next to the other files. This will enable you to use the file name to include the image.

   File name: lowercase English alphabet only

   You should avoid using special characters in the file names. Best if you use the English alphabet and no spaces either. The various platforms and git handle filenames differently. GitHub's web interface will only render the documentation with the images correctly if you only use all lowercase filenames with the English alphabet and no spaces.

6. For convenient editing open the preview (Ctrl-K + V).

Alternative editor

If you do not like VS code, you can also use the GitHub web interface to edit the markdown; you also have a preview here. File upload will be trickier.

Create the database

1. Connect to Microsoft SQL Server using SQL Server Management Studio. Start Management Studio and use the following connection details:

   • Server name: (localdb)\mssqllocaldb or .\sqlexpress (which is short for: localhost\sqlexpress)
   • Authentication: Windows authentication

2. Create a new database (if it does not exist yet). The name should be your Neptun code: in Object Explorer right-click Databases and choose Create Database.

3. Create the sample database by executing the initializer script Open a new Query window, paste the script into the window, then execute it. Make sure to select the correct database in the toolbar dropdown.

   

4. Verify that the tables are created. If the Tables folder was open before, you need to refresh it.

   .

Getting the actual execution plan

If you are not using Windows

We are primarily using SQL Server Management Studio to get the execution plans. If you are not using Windows, you can also use Azure Data Studio-t to obtain the query plan.

We will check the query plan the optimizer chose and the server executed in the following exercises. In SQL Server Management Studio, open the Query menu and check Include Actual Execution Plan.

The plan will be displayed after the query is completed at the bottom of the window on the Execution plan pane.

The plan is a data flow diagram where the query execution is the flow of the data. The items are the individual steps, and the percentages are the relative cost of each step with regards to the whole query.