Essential AI Prompts Every SQL Server DBA Should Know: Claude and Amazon Q

Artificial intelligence is rapidly changing how database engineers work. Tools like Claude and Amazon Q are helping developers generate SQL queries, troubleshoot performance issues, and build monitoring tools faster than ever before.

When paired with Microsoft SQL Server, these AI tools become powerful assistants for DBAs and data engineers — not replacements for expertise, but accelerators that compress hours of analysis into seconds.

Why Prompt Engineering Matters

AI tools work best when given clear, specific instructions. The quality of the response depends almost entirely on the quality of the prompt.

The difference in results can be dramatic. AI systems respond much better when the prompt includes the database platform, performance goals, context about the schema, and the expected output format. For database professionals, prompt engineering is becoming as important as query writing.

10 Prompt Categories Every DBA Should Master

• Write a SQL Server query to return the top 10 customers by revenue.
• Generate a SQL query that calculates monthly sales totals for the past 12 months.
• Write a query to identify customers who have not placed orders in the past 12 months.
• Generate a query that returns the top 5 products by units sold per region.

Example AI generated query for top customers by revenue:

SELECT TOP 10
    CustomerID,
    SUM(OrderTotal) AS TotalRevenue
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY TotalRevenue DESC;

• Analyze this SQL Server query and identify performance bottlenecks.
• Suggest indexes that would improve this query including key and included columns.
• Rewrite this SQL query to eliminate table scans and enable index seeks.
• Explain why this query is non-SARGable and provide an optimized version.

SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2025;

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01';

Wrapping a column in a function prevents SQL Server from using an index seek. The range predicate version allows the optimizer to jump directly to matching rows.

• Explain this SQL Server execution plan in plain language.
• Identify the most expensive operators in this execution plan and explain why they are costly.
• Suggest query or index improvements based on this execution plan.
• Why is this query using a hash join instead of a nested loop and when should each be used?

Execution plans are one of the most powerful diagnostic tools for SQL Server performance. AI tools can quickly summarize complex plans that might take junior engineers hours to interpret — particularly for plans involving multiple joins, sorts, and key lookups.

• Generate a SQL script to detect active blocking sessions in SQL Server.
• Create a query that identifies long-running queries currently executing.
• Generate a script that monitors SQL Server wait statistics and highlights the top waits.
• Write a monitoring query that tracks TempDB usage by session.

Example blocking detection query:

SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Monitoring scripts like these are commonly used in daily DBA health checks and automated alerting systems.

• Generate SQL queries that identify users with elevated or excessive privileges.
• List all SQL Server logins that have sysadmin access.
• Generate a report showing database permissions by user and role.
• Identify any database users that have permissions beyond what their role requires.

Example login audit query:

SELECT
    name,
    type_desc
FROM sys.server_principals
WHERE type_desc LIKE '%LOGIN%';

Security reviews are one of the most important DBA responsibilities — and one of the easiest areas to accelerate with AI-assisted script generation.

• Review this migration script and identify potential risks before execution.
• Suggest strategies for migrating large SQL Server tables with minimal downtime.
• Generate a pre-migration checklist covering indexes, statistics, and constraints.
• What are the key validation steps after a SQL Server database migration?

Migration planning typically involves downtime management, indexing strategies, data validation, and rollback planning. AI can generate checklists and flag risks quickly — but a DBA must validate against the specific environment.

• Generate documentation for this stored procedure including parameters, logic, and return values.
• Describe the relationships between these database tables based on the schema.
• Generate a data dictionary for this database schema.
• Write inline comments for this SQL Server stored procedure.

AI can significantly reduce the time required to document large database systems — one of the most consistently underdone tasks in database environments.

• Write a SQL query that identifies revenue growth trends by month and year.
• Generate a report showing customer purchasing patterns and order frequency.
• Create a query that calculates customer lifetime value based on order history.
• Write a query to identify products with declining sales over the past six months.

These prompts allow DBAs to quickly generate analytics queries that business teams can use directly — without waiting for a separate reporting cycle.

• Identify the queries consuming the most CPU on this SQL Server instance.
• Generate a query that finds active blocking sessions and their root cause.
• Create a SQL script to detect queries with the highest average elapsed time.
• Analyze these wait statistics and explain what performance bottleneck they indicate.

Example long-running query detection script:

SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_time,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_time DESC;

• Explain the architecture of this SQL Server environment and identify single points of failure.
• Suggest strategies for scaling this database to support 10x current load.
• Design a high availability architecture for SQL Server using Always On Availability Groups.
• What are the trade-offs between readable secondaries and a separate reporting database?

AI can help DBAs evaluate architecture decisions, model trade-offs, and identify gaps — particularly useful when preparing for infrastructure reviews or capacity planning conversations.

Workshop: AI Prompt Engineering Practice

A practical workshop for engineers learning to use AI with real SQL Server data. Requirements: SQL Server instance, SSMS or VS Code, and an AI tool such as Claude or Amazon Q.

CREATE TABLE Orders
(
    OrderID    INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate  DATETIME,
    OrderTotal DECIMAL(10,2)
);

INSERT INTO Orders (CustomerID, OrderDate, OrderTotal)
VALUES
(101, '2025-01-03', 120.50),
(101, '2025-02-10', 210.00),
(102, '2025-01-12',  75.25),
(102, '2025-03-15', 320.10),
(103, '2025-01-20',  45.00),
(103, '2025-02-01', 180.75),
(104, '2025-01-25', 510.00),
(104, '2025-03-02',  90.50),
(105, '2025-02-14', 250.00),
(105, '2025-03-18', 125.00);

SELECT * FROM Orders;

SELECT TOP 5
    CustomerID,
    SUM(OrderTotal) AS Revenue
FROM Orders
GROUP BY CustomerID
ORDER BY Revenue DESC;

SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2025;

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01';

CREATE INDEX IX_Orders_OrderDate
ON Orders(OrderDate);

SELECT
    r.session_id,
    r.start_time,
    r.status,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY r.total_elapsed_time DESC;

Advanced Lab: AI-Assisted Performance Tuning

This lab demonstrates how AI-assisted workflows integrate with real SQL performance tuning. Rather than treating AI as a query generator, this focuses on how experienced DBAs use AI to analyze inefficient queries, identify optimizer behavior, validate improvements with execution plans, and confirm index effectiveness with real metrics.

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
    DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
    OrderID    INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate  DATETIME,
    OrderTotal DECIMAL(10,2)
);

WITH Numbers AS
(
    SELECT TOP 100000
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.objects a
    CROSS JOIN sys.objects b
)
INSERT INTO dbo.Orders (CustomerID, OrderDate, OrderTotal)
SELECT
    ABS(CHECKSUM(NEWID())) % 1000,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
    ABS(CHECKSUM(NEWID())) % 500
FROM Numbers;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2025;

SELECT *
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01';

CREATE INDEX IX_Orders_OrderDate_Test
ON dbo.Orders(OrderDate);

Summary

Learning how to prompt AI effectively allows SQL professionals to write better queries, troubleshoot faster, automate repetitive tasks, generate monitoring scripts, and analyze performance issues in a fraction of the time.

References

• Microsoft Docs – SQL Server Documentation
• Anthropic – Claude Documentation
• AWS – Amazon Q Developer