Exercise: Assess query performance using Query Store

  • 20 minutes

Important

You need your own Azure subscription to complete the exercises in this module. If you don't have an Azure subscription, you can set up a free trial account at Build in the cloud with an Azure free account.

Create the exercise environment

Before you start

  • You have installed and started Azure Database for PostgreSQL flexible server.
  • You have installed Azure Data Studio.
  • Download the lab scripts from PostgreSQL Labs.
  • Download and install pgAdmin 4 from Download
  • Open Azure Data Studio and connect to your Azure Database for PostgreSQL flexible server.

Restore AdventureWorks database

  1. Start pgAdmin and enter the password that you specified during installation.

  2. Right-click Servers, select Register, and select Server. Screenshot of server registration menu in p g Admin.

  3. In Name, type PostgreSQL Exercise 10 and select the Connection tab.

  4. In Host name/address, type the value that you specified when you deployed the server.

  5. In Username, type demo.

  6. In Password, type Pa$$w0rd.

  7. Select Save password.

  8. Select Save.

  9. Expand PostgreSQL Exercise, right-click Databases, select Create, and select Database. Screenshot showing Create Database menu item.

  10. In Database, type adventureworks and select Save.

  11. Right-click adventureworks and select Restore.

  12. In Filename, type the path location that you specified when you downloaded the lab scripts plus \AdventureWorksPG.gz.

  13. In Number of jobs, type 1.

  14. In Role name, select demo.

  15. Select Restore.

    Screenshot of Restore dialog box.

  16. Wait until the restore process completes.

  17. You can disregard any errors that occur because those objects are not required for these modules.

Task 1: Turn on query capture mode

  1. Navigate to the Azure portal and sign in.

  2. Select your Azure Database for PostgreSQL server for this exercise.

  3. In Settings, select Server parameters.

  4. Navigate to the pg_qs.query_capture_mode setting.

  5. Select TOP.

    Screenshot of settings to turn Query Store on.

  6. Navigate to p g m s_wait_sampling.query_capture_mode, select ALL, and select Save. Screenshot of settings to turn p g m s_wait_sampling.query_capture_mode on.

  7. Wait for the server parameters to update.

View pg_stat data

  1. Start Azure Data Studio.

  2. Select Connect.

    Screenshot showing Connect icon.

  3. Select your PostgreSQL server and select Connect.

  4. Type each of the following query and select Run.

    SELECT * FROM pg_stat_activity;

  5. Review the metrics that are available.

  6. Leave Azure Data Studio open for the next task.

Task 2: Examine query statistics

Note

For a newly created database, there might be limited statistics, if any. If you wait for 30 minutes there will be statistics from background processes.

  1. Select the azure_sys database.

    Screenshot of the database selector.

  2. Type each of the following queries and select Run.

    SELECT * FROM query_store.query_texts_view;

    SELECT * FROM query_store.qs_view;

    SELECT * FROM query_store.runtime_stats_view;

    SELECT * FROM query_store.pgms_wait_sampling_view;

  3. Review the metrics that are available.

Task 3: Delete the PostgreSQL server

When you have completed all of the exercises, you can delete the Azure Database for PostgreSQL with the following steps:

  1. In Azure Data Studio, on your Azure Database for PostgreSQL page, select Overview.
  2. Select Delete.
  3. Select Skip feedback.
  4. Type the server name.
  5. Select Delete.