教程:添加五个最慢的查询示例小组件设为数据库仪表板Tutorial: Add the five slowest queries sample widget to the database dashboard

本教程演示了添加之一的过程Azure Data StudioAzure Data Studio的内置示例小组件到数据库仪表板可以快速查看数据库的五个最慢查询。This tutorial demonstrates the process of adding one of Azure Data StudioAzure Data Studio's built-in sample widgets to the database dashboard to quickly view a database's five slowest queries. 你还了解如何查看速度慢的查询的详细信息和查询计划使用Azure Data StudioAzure Data Studio的功能。You also learn how to view the details of the slow queries and query plans using Azure Data StudioAzure Data Studio's features. 在本教程中,你了解如何:During this tutorial, you learn how to:

  • 在数据库上启用查询存储Enable Query Store on a database
  • 将预建的见解小组件添加到数据库仪表板Add a pre-built insight widget to the database dashboard
  • 查看有关数据库的速度最慢的查询的详细信息View details about the database's slowest queries
  • 查看速度慢的查询的查询执行计划View query execution plans for the slow queries

Azure Data StudioAzure Data Studio 包括几个见解小组件--现成的。includes several insight widgets out-of-the-box. 本教程演示如何将添加查询的数据的存储区-db-见解小组件,但步骤本质上是相同的添加任何小组件。This tutorial shows how to add the query-data-store-db-insight widget, but the steps are basically the same for adding any widget.

必要條件Prerequisites

本教程需要安装 SQL Server 或 Azure SQL 数据库TutorialDBThis tutorial requires the SQL Server or Azure SQL Database TutorialDB. 若要创建TutorialDB数据库,请完成以下快速入门之一:To create the TutorialDB database, complete one of the following quickstarts:

为数据库启用查询存储Turn on Query Store for your database

此示例中的小组件需要Query Store才可用。The widget in this example requires Query Store to be enabled.

  1. 右键单击TutorialDB数据库 (在服务器侧栏),然后选择新查询Right click the TutorialDB database (in the SERVERS sidebar) and select New Query.

  2. 在查询编辑器中,粘贴以下 TRANSACT-SQL (T-SQL) 语句,然后单击运行:Paste the following Transact-SQL (T-SQL) statement in the query editor, and click Run:

     ALTER DATABASE TutorialDB SET QUERY_STORE = ON
    

将速度慢的查询小组件添加到数据库仪表板Add the slow queries widget to your database dashboard

若要添加缓慢查询小组件到仪表板,编辑dashboard.database.widgets中设置你用户设置文件。To add the slow queries widget to your dashboard, edit the dashboard.database.widgets setting in your User Settings file.

  1. 打开用户设置通过按Ctrl + Shift + P以打开命令面板Open User Settings by pressing Ctrl+Shift+P to open the Command Palette.

  2. 类型设置在搜索框中,选择首选项:打开用户设置Type settings in the search box and select Preferences: Open User Settings.

    打开的用户设置命令

  3. 类型仪表板中设置搜索框中,找到dashboard.database.widgetsType dashboard in the settings search box and locate dashboard.database.widgets.

    搜索设置

  4. 若要自定义dashboard.database.widgets设置,需要编辑dashboard.database.widgets中的条目用户设置部分 (中的列右侧)。To customize the dashboard.database.widgets settings you need to edit the dashboard.database.widgets entry in the USER SETTINGS section (the column in the right side). 如果没有任何dashboard.database.widgets用户设置部分中,将鼠标悬停dashboard.database.widgets中默认设置列并单击文本显示的文本并单击左侧的铅笔图标复制到设置If there is no dashboard.database.widgets in the USER SETTINGS section, hover over the dashboard.database.widgets text in the DEFAULT SETTINGS column and click the pencil icon that appears to the left of the text and click Copy to Settings. 如果弹出窗口中显示替换为在设置,不要单击它 !If the pop-up says Replace in Settings, don't click it! 转到用户设置右侧的列并找到dashboard.database.widgets部分并转到下一步。Go to the USER SETTINGS column to the right and locate the dashboard.database.widgets section and advance to the next step.

  5. 在中dashboard.database.widgets部分中,添加以下:In the dashboard.database.widgets section, add the following:

         {
             "name": "slow queries widget",
             "gridItemConfig": {
                 "sizex": 2,
                 "sizey": 1
             },
             "widget": {
                 "query-data-store-db-insight": null
             }
         },
    
  6. 如果这是添加了新的小组件,第一次dashboard.database.widgets部分应类似于此:If this is the first time adding a new widget, the dashboard.database.widgets section should look similar to this:

    "dashboard.database.widgets": [
        {
            "name": "slow queries widget",
            "gridItemConfig": {
                "sizex": 2,
                "sizey": 1
            },
            "widget": {
                "query-data-store-db-insight": null
            }
        },
        {
            "name": "Tasks",
            "gridItemConfig": {
                "sizex": 1,
                "sizey": 1
            },
            "widget": {
                "tasks-widget": {}
            }
        },
        {
            "gridItemConfig": {
                "sizex": 1,
                "sizey": 2
            },
            "widget": {
                "explorer-widget": {}
            }
        }
    ]
    
  7. Ctrl + S以保存已修改用户设置Press Ctrl+S to save the modified User Settings.

  8. 打开数据库仪表板通过导航到TutorialDB服务器侧栏中,右键单击,然后选择管理Open the Database dashboard by navigating to TutorialDB in the SERVERS sidebar, right-click, and select Manage.

    打开仪表板

  9. 在仪表板上显示见解小组件:The insight widget appears on the dashboard:

    QDS 小组件

查看见解的详细信息的详细信息View insight details for more information

  1. 若要查看见解小组件的其他信息,请单击省略号 ( ... ),然后选择右上角显示详细信息To view additional information for an insight widget, click the ellipses (...) in the upper right, and select Show Details.

  2. 若要显示的项的更多详细信息,请选择中的任意项图表数据列表。To show more details for an item, select any item in Chart Data list.

    了解详细信息对话框

  3. 右键单击右侧的单元格query_sql_txt项详细信息然后单击复制单元格Right-click the cell to the right of query_sql_txt in Item Details and click Copy Cell.

  4. 关闭Insights窗格。Close the Insights pane.

查看查询计划View the query plan

  1. 按打开新查询编辑器Ctrl + NOpen a new query editor by pressing Ctrl+N.

  2. 将前面步骤中的查询文本粘贴到编辑器。Paste the query text from the previous steps into the editor.

  3. 单击解释Click Explain.

    见解 QDS 说明

  4. 查看查询的执行计划:View the query's execution plan:

    显示计划 (Showplan)

保存并打开查询计划Save and open a query plan

  1. 打开了解详细信息对话框。Open the insight detail dialog.

  2. 选择查询项之一。Select one of the query items.

  3. 右键单击query_plan值,并选择复制单元格Right-click query_plan value and select Copy Cell

    Insights QDS 计划

  4. Ctrl + N以打开新的编辑器。Press Ctrl+N to open a new editor.

  5. 粘贴到编辑器中复制的计划。Paste the copied plan into the editor.

  6. Ctrl + S以保存该文件,并将文件扩展名更改为 .sqlplanPress Ctrl+S to save the file, and change the file extension to .sqlplan. .sqlplan未出现在文件扩展名下拉列表中,因此只需键入其中。.sqlplan does not appear in the file extension dropdown, so just type it in. 对于本教程,将文件命名slowquery.sqlplanFor this tutorial, name the file slowquery.sqlplan.

  7. 在中打开查询计划Azure Data StudioAzure Data Studio的查询计划查看器:The query plan opens in Azure Data StudioAzure Data Studio's query plan viewer:

    Insights QDS 计划

后续步骤Next steps

在本教程中,你将了解:In this tutorial, you learned how to:

  • 在数据库上启用查询存储Enable Query Store on a database
  • 将见解小组件添加到数据库仪表板Add an insight widget to the database dashboard
  • 查看有关数据库的速度最慢的查询的详细信息View details about the database's slowest queries
  • 查看速度慢的查询的查询执行计划View query execution plans for the slow queries

若要了解如何启用表空间使用情况示例见解,请完成下一教程:To learn how to enable the table space usage sample insight, complete the next tutorial: