AWS Database Blog

Monitor query performance with Performance Insights on Amazon RDS for SQL Server

Amazon RDS Performance Insights is a powerful feature of Amazon Relational Database Service (Amazon RDS) that provides real-time and historical insights into your database performance. Whether you’re troubleshooting performance bottlenecks, identifying slow queries, or optimizing your system, Performance Insights is able to help. With Performance Insights, you gain a deeper understanding of your database’s behavior. Performance Insights expands on the existing Amazon RDS monitoring features to illustrate and help you analyze your database performance. With the Performance Insights dashboard, you can visualize the load on your RDS DB instance and slice it by application, database, wait event, SQL statement, host, or user.

In this post, we discuss the new features recently released in Performance Insights:

  • SQL digest and SQL statistics
  • Query execution plan by Performance Insights for SQL Server

SQL digest and SQL statistics

Before we dive into these new features, let’s talk about SQL digests in general. A SQL digest is a composite of multiple actual queries that are structurally similar but might have different literal values. The digest replaces the bind value of a SQL query with a question mark. For example, a digest might be SELECT * FROM emp WHERE lname=?. This digest might include the following child queries:

SELECT * FROM emp WHERE lname = 'Sanchez';
SELECT * FROM emp WHERE lname = 'Olagappan';
SELECT * FROM emp WHERE lname = 'Wu';

To see the literal SQL statements in a digest, select the query, then choose the plus symbol to expand its details. In the following example, the selected query is a digest.

However, SQL Server doesn’t support digests like open source engines. The digest text will help you understand what kinds of queries are having the highest impact on database performance. In SQL Server, each digest is associated with a specific query_hash.

The query_hash or binary hash values calculated on the query are used to identify queries with similar logic. You can use the query_hash to determine the aggregate resource usage for queries that differ only by literal values. A query_hash is a computed value that points to a query, irrespective of literal values. For example:

SQL Text: select col1,1,col2 
          from table1 
          where col345465757e <> 456;

DIGEST_TEXT: select col1,?,col2 
             from table1 
             where col345465757e <> ?

Amazon RDS for SQL Server collects SQL statistics at both the statement and digest level for the top SQL queries. For more information, see SQL statistics for SQL Server.

Query execution plans

Performance Insights only captures the estimated query execution plan. The captured plan contains all plan nodes and statistics. For more information, see Analyzing execution plans using the Performance Insights dashboard.

The execution plans captured can be seen in two different formats:

  • Tabular format – To quickly understand the plan nodes and statistics
  • Downloadable XML format – To do further investigation using tools like SQL Server Management Studio

The execution plan details that Performance Insights collects will help you do the following:

  • Find out which plans are used by the top SQL queries
  • Compare different plans for the same query
  • Find out when a query switched to a new plan
  • Drill down to the specific operator of a plan with the highest cost

Solution overview

In the following sections, we demonstrate how to connect to your RDS DB instance, prepare your database, and analyze your SQL Server execution plans using the Performance Insights dashboard.

Prerequisites

Before you get started, make sure you complete the following prerequisites:

  1. Create an RDS DB instance.
  2. Turn Performance Insights on.
  3. Configure access policies for Performance Insights.
  4. Have an Amazon Elastic Compute Cloud (Amazon EC2) Windows instance with SQL Server Management Studio (SSMS) installed.

Connect to your RDS DB instance and prepare the database

We first create a sample database and tables. Complete the following steps:

  1. Open SSMS.
  2. Connect to the RDS for SQL Server database instance.
  3. Choose New Query.
  4. Enter the following query and choose Execute: 
    -- Create database  
CREATE DATABASE testDB   
Go    
-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

-- Insert ten thousand rows into Customers table
DECLARE @CustomerCounter INT = 1;
WHILE @CustomerCounter <= 10000
BEGIN
    INSERT INTO Customers (CustomerID, CustomerName)
    VALUES (@CustomerCounter, 'Customer' + CAST(@CustomerCounter AS NVARCHAR(10)));
    SET @CustomerCounter = @CustomerCounter + 1;
END;

-- Create Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    UnitPrice DECIMAL(10, 2)
);

-- Insert ten thousand rows into Products table
DECLARE @ProductCounter INT = 1;
WHILE @ProductCounter <= 10000
BEGIN
    INSERT INTO Products (ProductID, ProductName, UnitPrice)
    VALUES (@ProductCounter, 'Product' + CAST(@ProductCounter AS NVARCHAR(10)), RAND() * 100);
    SET @ProductCounter = @ProductCounter + 1;
END;

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

-- Insert ten thousand rows into Orders table
DECLARE @OrderCounter INT = 1;
WHILE @OrderCounter <= 10000
BEGIN
    INSERT INTO Orders (OrderID, CustomerID, OrderDate)
    VALUES (@OrderCounter, (ABS(CHECKSUM(NEWID())) % 1000000) + 1, DATEADD(DAY, -(@OrderCounter % 365), GETDATE()));
    SET @OrderCounter = @OrderCounter + 1;
END;

-- Create OrderDetails table
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT
);
  5. Turn SHOWPLAN_XML on with the following statement: 
    -- Display XML execution plan for a query 
SET SHOWPLAN_XML ON;
GO

The following are sample queries to use for the demo:

QUERY 1 using Where Clause.

SELECT Orders.OrderID FROM Orders WHERE Orders.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'

QUERY 2 using joins

SELECT 
    Orders.OrderID,
    Customers.CustomerName,
    SUM(OrderDetails.Quantity * Products.UnitPrice) AS TotalPrice
FROM 
    Orders
JOIN 
    Customers ON Orders.CustomerID = Customers.CustomerID
JOIN 
    OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN 
    Products ON OrderDetails.ProductID = Products.ProductID
WHERE 
    Orders.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    Orders.OrderID, Customers.CustomerName
HAVING 
    SUM(OrderDetails.Quantity * Products.UnitPrice) > 1000;

Analyze SQL Server query execution plans using the Performance Insights dashboard

Complete the following steps to analyze your SQL Server query execution plans:

  1. On the Amazon RDS console, in the navigation pane, choose Performance Insights.
  2. Choose a SQL Server DB instance.
    The Performance Insights dashboard is displayed for that DB instance.
  3. In the Database load section, choose Plans on the dropdown menu next to Sliced by.
    The database load chart shows the plans used by your top SQL statements and the load generated by those plans on the database. The plan hash values appear to the right of the color-coded squares. Each hash value uniquely identifies a plan.
  4. Choose the gear icon and select the fields you are interested in, such as Total elapsed time, Rows processed/sec, and Plans count.
  5. In the Top SQL section, choose the SQL text tab to display the full SQL statement.

  6. Choose the Plans tab to analyze the query execution plans.

The examples in this post are not intended to dive deep into comparing execution plans. Rather, they aim to showcase the capabilities of the Performance Insights dashboard for analyzing these plans. Our approach remains intentionally simplistic to highlight basic functionalities.

Conclusion

Monitoring is an important part of maintaining the reliability, availability, and performance of your SQL Server databases on Amazon RDS. DBAs have always relied on analyzing SQL Server statistics and query execution plans to understand how queries are processed by the database engine. With the Performance Insights dashboard to display SQL Server statistics and execution plans, DBAs can now fine-tune SQL Server performance to provide optimal database operation and enhance overall system efficiency. In this post, we showcased how to analyze database load by execution plans and compare different plans for a given query.

To get started with Performance Insights, refer to Monitoring DB load with Performance Insights on Amazon RDS.

About the authors

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.

Sudhir Amin is a Sr. Solutions Architect at Amazon Web Services. In his role based out of New York, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He is a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world’s most majestic animals up close.