Skip to content

tutorial-qds-sql-server.md

Latest commit

 

History

History
124 lines (78 loc) · 5.07 KB

tutorial-qds-sql-server.md

File metadata and controls

124 lines (78 loc) · 5.07 KB
title Add Insight widgets to the database management dashboard | Microsoft Docs
description Monitor a database by adding a pre-built query performance widget to the database management dashboard.
keywords
ms.custom tools|sos
ms.date 11/01/2017
ms.prod sql-non-specified
ms.reviewer alayu; erickang; sanagama; sstein
ms.suite sql
ms.tgt_pltfrm
ms.topic tutorial
author erickangMSFT
ms.author erickang
manager craigg
ms.workload Inactive

Monitor Query Performance with [!INCLUDEname-sos]

In this tutorial, you walk through the process of adding one of [!INCLUDEname-sos]'s built-in insight widgets to the database dashboard. to quickly view a database's five slowest queries using Query Store. You also learn how to view the details of the slow queries and query plans using [!INCLUDEname-sos]'s features. After following through this tutorial, you will learn how to:

[!div class="checklist"]

  • Enable Query Data Store on TutorialDB
  • Quickly turn on an insight widget using a built-in insight widget sample.
  • View the details of the top five slowest queries.
  • Open the query script in editor.
  • View query plans.

Prerequisites

  • Follow [Get Started with [!INCLUDEname-sos]](./get-started-sql-server.md) to a SQL Server 2017 instance and TutorialDB database.

Turn on Query Store for the TutorialDB database

Enable Query Data Store by executing following T-SQL statement on TutorialDB:

 ALTER DATABASE TutorialDB SET QUERY_STORE = ON

Add a pre-built management insight to [!INCLUDEname-sos]'s database dashboard

[!INCLUDEname-sos] has a built-in sample widget to monitor the top five slowest query using query performance information collected by Query Data Store. With a few simple steps, you can easily visualize and use the information to improve your database and application.

  1. Open User Settings by pressing Ctrl+Shift+P to open the Command Palette.

  2. Type settings in the command search input box and select 'Preferences: Open User Settings' command.

    Open user settings command

  3. Type 'dashboard' in Settings Search input box to search "dashboard.database.widgets" in Settings.

    Search settings

  4. Click 'Copy to Settings' to copy "dashboard.database.widgets" settings to customize.

  5. Using [!INCLUDEname-sos]'s insight settings IntelliSense, configure 'name' for the widget title, 'gridItemConfig' for the widget size, and 'widget' by selecting 'query-data-store-database-insight' from the drop-down list as shown in the screenshot below:

    Insight qds settings

  6. Press 'CTRL + s' to save the user's settings file.

  7. Open Database dashboard by navigate to 'TutorialDB' in Servers viewlet, and click 'Manage' in the context menu.

    Open dashboard

  8. View 'Top five slowest query insight graph' as shown in the screen shot below:

    QDS widget

View insight details dialog for know more about the insight

  1. Click 'Show Insight' context menu. It opens Insights detail dialog as shown in the screenshot:

    Insight detail dialog

  2. Click any item in 'Chart Data' list to show more detail of each item in the list.

  3. Select 'query_sql_txt' field in 'Query Data' panel and click 'Copy Selection'.

View the query plan using Explain

  1. Open a new editor by pressing 'CTRL + N'.

  2. Paste the query sql text by pressing 'CTRL + V' in the editor.

  3. Click 'Explain' button.

    Insight QDS Explain

  4. View the showplan.

    showplan

View the query plan in Query Data Store

  1. Open the insight detail dialog again.

  2. Select and copy 'query_plan'

    Insights QDS plan

  3. Press 'CTRL+N' to open a new editor.

  4. Paste the copied plan data to the editor.

  5. Press 'CTL + S' to save the file and change the file extension to *.showplan

  6. The query plan opens in [!INCLUDEname-sos]'s query plan viewer.

    TBD - screenshot

Next Steps

In this tutorial, you learned how to:

[!div class="checklist"]

  • Enable Query Data Store on TutorialDB
  • Quickly turn on an insight widget using a built-in insight widget sample.
  • View the details of the top five slowest queries.
  • Open the query script in editor.
  • View query plans.

Next, learn how to use X, try this tutorial:

[!div class="nextstepaction"] What article is next in sequence