image
Query the Query Store
Start course
Difficulty
Intermediate
Duration
1h 26m
Students
965
Ratings
5/5
Description

SQL is the language for interacting with databases, and like all computer languages, there are many ways to achieve the right result. And like other software languages, some ways will be better and more efficient than others. What sets SQL and database programming apart from most other computer programming is the vast amount of data that is regularly dealt with, and how other factors that are not immediately obvious can impact the SQL query code you write.

This course looks at ways that you can see how the SQL Server engine views your queries and how it intends to execute them. Using that information, we look at other factors like table and index design that can positively or negatively impact your query execution.

For any feedback relating to this course, please contact us at support@cloudacademy.com.

Learning Objectives

  • Understand what impacts query performance when using SQL
  • Learn how the SQL Server engine processes queries
  • Use execution plans to see how a query is executed
  • Instruct the query engine
  • Understand how different query types can be used
  • Learn how to design tables to make queries more efficient
  • Use database file configuration to improve performance
  • Understand how database compression can impact performance

Intended Audience

This course is aimed at database administrators, data engineers, or anyone who wants to learn how to improve database query execution.

Prerequisites

To get the most out of this course, you should be familiar with SQL query syntax, SQL data types, and SQL table structure.

Resources

https://github.com/cloudacademy/azure-sql-query-tuning-dp-300

 

Transcript

Apart from using the Query Store tables, you can obtain a comprehensive array of information related to Query performance from dynamic management objects. Dynamic management objects more commonly known as DMVs for Dynamic Management Views or DMFs for Dynamic Management Functions.

The DMVs are found under a databases system views node in SQL Server management studio, while the DMFs under the system functions of the model database. The names all start with dm underscore followed by the category they relate to, CDC for change data capture, DB underscore iOS, for SQL Server operating system view. You get the idea. There are a lot of these dynamic management views and functions and they cover all aspects of a server instance operations, as well as database states.

Here's a small selection of the DMV categories that are available. In terms of monitoring or reporting on crew performance, we're interested in the execution-related DMVs. Of all the DMO categories, execution-related is the most well covered.

As you can see from the views and functions depicted here. The two that are of most interest to us are dm query stats and dm exec query profiles. Just to reiterate, while I'm showing you these views and functions in the context of SQL Server that also present an Azure SQL.

Here we have a table of the columns returned by the dm_exec_query_stats view. This view returns information about the query across its lifetime, as opposed to an instance of execution. For most of the matrix, you can see data from the last execution plus min-max and total values. The execution count number will allow you to calculate average values were appropriate.

Speaking of average statistics, here we have a Query returning the top five CPU intensive queries with their plans, using total worker time divided by execution count to calculate the average. As you can see, a Query statistic is related to a combination of Query and plan. This Query is returning some statistics for an SQL Server statement, regardless of execution plan by matching against DM SQL Query text.

We can see which queries have been cached by interrogating dm_exec_cache plans. Not only can we get the SQL, but also the object and cache types. You can get information like that found in live Query statistics, by using the dm_exec_query_profiles view. This view will return real-time metrics related to the plan of the query that is currently executing.

What we'll do, is have a look at the same insert Query but this time run a profile Query against it as it executes. The easiest way to do this is to open up another Query window and paste the profile Query in. As you can see the dm_exec_query profiles view requires a session ID.

So just do a select on SP ID and that is 61. The profile DMV will only return results while the Query is executing. This is good for long running queries where we want to see how they're progressing, but in this case, I'm going to need a quick mouse hand and we'll have to slow the video down.

I'll execute the target Query and then quickly flick over to the profile Query and start heading the execute button there. I've removed the index that was earlier recommended by the Query plan. So we can see on row 12 that we're starting with the table scan, as we did originally.

Also, you will recall that the final number of rows that was inserted was around 1.9 million. And on this first execution, we are at 550,000. But you can see the estimated row counts are the same as the ones we saw earlier in the estimated plan.

On the second execution, we can see that the very first operation, the table scan is still progressing and the rows are flowing through the whole operation into the destination table. I'll hit execute again in the table scan and insert a still proceeding. This is a good illustration of SQL Servers parallelism, and multithreading.

About the Author
Students
20985
Courses
72
Learning Paths
14

Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a  Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.