AI SQL Tuner

CodeReview Tuning Goal

Overview

The CodeReview tuning goal reviews code in your Transact-SQL stored procedures, functions, and triggers to identify:

Performance issues
Locking/concurrency risks
Non-SARGable predicates
Anti-patterns and best-practice violations

It returns a prioritized list of fixes with examples.

What It Analyzes

1. Stored procedures, functions, triggers

Object definitions collected from the target database
Focus on common performance and reliability patterns

How many objects are reviewed?

By default, CodeReview includes the top 100 most recently modified objects (stored procedures, functions, and triggers), ordered by modify_date descending. This cap helps keep collection time and prompt size reasonable.

2. Common performance issues

Non-SARGable predicates (functions on columns, implicit conversions)
SELECT * usage
Missing indexes implied by query patterns
RBAR patterns (row-by-row processing)
Scalar UDF performance issues (version-dependent)

What does “SARGable” mean?

SARGable stands for Search ARGument Able. A predicate is SARGable when SQL Server can use an index efficiently (typically an index seek) because the predicate compares a column directly to a constant/parameter (for example: WHERE CreatedDate >= @FromDate).

Predicates are often non-SARGable when they apply expressions to the column side (for example: WHERE CONVERT(date, CreatedDate) = @Date or WHERE YEAR(CreatedDate) = 2025), which can force scans and reduce plan quality.

What does “RBAR” mean?

RBAR stands for Row-By-Agonizing-Row. It describes T-SQL patterns that process one row at a time (often via cursors, loops, or per-row function calls) instead of using set-based operations. RBAR can be dramatically slower at scale because it prevents SQL Server from optimizing the work as a set and can increase CPU, logging, and locking.

Common RBAR examples include using a CURSOR to iterate rows, WHILE loops that select/update one row per iteration, or repeatedly calling a scalar UDF for each row in a large result set.

3. Concurrency/locking risks

Long-running transactions
Unnecessary locking hints
Potential escalation patterns
Hotspot tables

4. Safer patterns and maintainability

Parameterization guidance
Error handling patterns
Consistent naming and schema qualification

Usage

Command Line Examples

Windows Authentication:

# Database is required for CodeReview
aisqltuner -S localhost -d MyDatabase -goal CodeReview -E

Azure SQL Database / Fabric SQL Database (Azure AD Interactive):

# Azure SQL
aisqltuner -S myserver.database.windows.net -d mydb -goal CodeReview -aad

# Microsoft Fabric SQL Database
aisqltuner -S <fabric-endpoint> -d mydb -goal CodeReview -aad

Output to HTML:

aisqltuner -S localhost -d MyDatabase -goal CodeReview -E -o codereview.html

Output language (optional):

aisqltuner -S localhost -d MyDatabase -goal CodeReview -E -o codereview_fr.html -l French

Bring your own Azure OpenAI deployment/model (optional):

# Required
$env:AZURE_OPENAI_ENDPOINT = "https://<your-resource-name>.cognitiveservices.azure.com/"
$env:AZURE_OPENAI_KEY = "<your-azure-openai-key>"

# Optional (deployment name as created in Azure OpenAI)
$env:AISQLTUNER_DEPLOYMENT = "<your-deployment-name>"
$env:AISQLTUNER_SECONDARY_DEPLOYMENT = "<your-secondary-deployment-name>"
aisqltuner -S localhost -d MyDatabase -goal CodeReview -E -o codereview.html

See Bring Your Own AI Endpoint for more information.

What You Get

The AI will produce a prioritized set of recommendations, typically including:

High-impact query rewrites
- Make predicates SARGable
- Improve joins
- Reduce unnecessary work
Index recommendations (code-driven)
- Suggested indexes for specific query patterns
- Index maintenance considerations
Concurrency improvements
- Shorter transactions
- Reduce lock footprint
- Avoid problematic hints
Best-practice improvements
- Schema qualification
- Avoid ambiguous conversions
- Error handling guidance

Platform Notes

On-premises SQL Server: Supported.
Azure SQL Database: Supported.
Azure SQL Managed Instance: Supported.
Microsoft Fabric SQL Database: Supported (treated similarly to Azure SQL Database).

Permissions Required

Minimum is usually:

VIEW DEFINITION on reviewed objects (or higher privileges)
Read access to metadata

Output Format

Console: Plain text (when -o not specified)
HTML: Full HTML report (when -o specified). Footer includes the model and reasoning effort used.

Troubleshooting

“Database (-d) is required for CodeReview goal.”

Provide -d <database>.

“Access denied” / object definition unavailable

Ensure the login can read module definitions (e.g., VIEW DEFINITION).