Monitor SQL performance in Linux

When you run SQL Server 2017 on a Linux server, you cannot use Windows Performance Monitor to gather and display performance counters because Performance Monitor is not supported on Linux.

Suppose you are a database administrator for a global novelty goods importer called Weed World Importers. You have migrated your customer-facing product database to a Linux server. Recently, some users have complained of slow performance.

Global map of Azure regions

You want to use Azure Data Studio to display SQL Server performance counters and how they vary over time.

Start by logging into the database server:

  1. Sign into the VM with a Microsoft account, or create a new account.
  2. Click Commands, click CTRL+ALT+DEL, and then login with theusername <USER_NAME> and the password <your_password>.
  3. Close the Server Manager window.
  4. At the top-left of the desktop, click Applications, click System Tools, and then click Settings.
  5. Click Network and then click the + button adjacent to Wired.
  6. In Name, type Ethernet and then in MAC Address box, select the eth0 address.
  7. Click Add, and then close Settings.

Note:If you don’t have resource in Azure then first create a database system on REHL OS.

Install Azure Data Studio

SQL Server 2017 is already installed on your Red Hat Enterprise Linux (RHEL) server however, you would like a Graphical User Interface (GUI) to administer it and display performance information. Install Azure Data Studio, which has already been downloaded to your computer.

  1. At the top-left of the desktop, click Applications, click System Tools, and then click Terminal.
  2. To stop the built-in package updater packagekit, which would block your install happening until it had finished its updates, type the following command, and then press Enter:
  3. bash:systemctl stop packagekit
  4. When prompted for the Administrator password, type <Your_password>, and press Enter.
  5. To check your version of Azure Data Studio, first switch to the Downloads folder, by typing the following command, and then pressing Enter:
  6. bash:cd Downloads
  7. To check your version of Azure Data Studio, list the files in the Downloads folder, by typing the following command, and then pressing Enter:bashCopyls
  8. To install Azure Data Studio, type the following command, replacing <version> with the file version number of the .rpm file in the Downloads directory, and then press Enter:bashCopysudo yum install azuredatastudio-linux-<version>.rpm

Create stored procedures

Four stored procedures are used in this module. In this section, you will create those procedures.

  1. To start Azure Data Studio type the following command in a terminal, and then press Enter:bashCopyazuredatastudio
  2. If you are asked to enable preview features, click Yes and if you are asked to allow Microsoft to collect usage data, close the dialog. In the Connection pane, click Cancel.
  3. On the File menu, click Open File and then browse to the Downloads/sqllinuxautotune folder.
  4. Click setup.sql and then click Open. Examine the contents of the script, which creates four stored procedures.
  5. In the top-left of the script window, click Run and then in the Connection pane, in the Connection type drop-down list, select Microsoft SQL Server.
  6. In the Server textbox, type localhost and in the Authentication type drop-down list, select SQL Login.
  7. In the User name textbox, type sa, in the Password textbox, type Pa$$w0rd and then click Connect. Azure Data Studio connects to the server and executes the T-SQL script.

Create a global temporary table to store the counter

You will create a global temporary table that stores the values of the Batch Requests/sec counter. Follow these steps:

  1. In Azure Data Studio, on the File menu, click Open File.
  2. Browse to the Downloads/sqllinuxautotune folder.
  3. Click batchrequests_perf_collector.sql and then click Open. Examine the contents of the script, which creates a global temporary table that stores the way a performance counter varies over time. The script continues to populate that table with measurements of the Batch Requests/sec counter.
  4. In the top-left of the script window, click Run.
  5. In the Connection pane, under Recent history click localhost.
  6. In the Password textbox, type Pa$$w0rd and then click Connect.

Simulate load on the SQL Server

In order to store some meaningful measurements of the Batch Requests/sec counter, follow these steps:

  1. On the File menu, click Open File.
  2. Click report.sql and then click Open. Examine the contents of the script, which calls a stored procedure to simulate load on the SQL Server.
  3. In the top-left of the script window, click Run.
  4. In the Connection pane, under Recent history click localhost.
  5. In the Password textbox, type Pa$$w0rd and then click Connect. Wait for about 20 seconds to store some data before the next step.

Query for and display the counter

The final step is to execute a query against the temporary table that returns relevant values. You’ll use a timeSeries chart to display these values in a graph:

  1. On the File menu, click Open File.
  2. Click batchrequests.sql and then click Open. Examine the contents of the script, which queries the global temporary table of performance data created by the batchrequests_perf_collector.sql script.
  3. In the top-left of the script window, click Run.
  4. In the Connection pane, under Recent history click localhost, in the Password textbox, type Pa$$w0rd and then click Connect.
  5. On the right of the RESULTS window, click the lower icon, which displays the CHART window.
  6. In the Chart Type drop-down list, select timeSeries. Azure Data Studio displays a graph that shows how the Batch Requests/Sec counter has varied over time.
  7. Close Azure Data Studio.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s