@ngx-zone/agent-sp-optimization

@ngx-zone/agent-sp-optimization is a stdio MCP server for static stored procedure analysis. It is SQL Server-first, but it also supports MySQL, PostgreSQL, and Oracle for non-dialect-specific checks.

The package is designed for enterprise review workflows where the first requirement is a deterministic, auditable static pass before a human validates execution plans and workload behavior.

What It Does

• analyzes stored procedures and explains likely bottlenecks
• detects common SQL anti-patterns
• emits conservative or performance-first rewrites
• suggests per-table candidate indexes
• returns consistently structured JSON from every tool
• documents assumptions and required runtime validation steps

What It Does Not Do

• connect to a database
• inspect live execution plans
• read table statistics or row counts
• guarantee a rewrite is faster without runtime validation

Design Goals

• deterministic static heuristics
• SQL Server-first guidance
• explicit assumptions instead of hidden confidence
• conservative default rewrites
• reusable internal helper logic instead of duplicated rule code

Installation

npm install @ngx-zone/agent-sp-optimization

Local Development

npm install
npm start
npm run smoke

Codex MCP Setup

codex mcp add sp-optimization-agent -- npx -y @ngx-zone/agent-sp-optimization

Tool Contract

Every tool returns the same top-level JSON envelope:

{
  "tool": "optimize_stored_procedure",
  "version": "1.1.0",
  "input": {
    "procedureSql": "CREATE OR ALTER PROCEDURE ...",
    "databaseType": "sqlserver",
    "rewriteMode": "conservative"
  },
  "summary": "sqlserver static analysis found 4 issue(s): 2 high, 1 medium, 1 low severity.",
  "data": {}
}

The data payload differs per tool, but the envelope is consistent.

MCP Tools

analyze_stored_procedure

Input:

{
  "procedureSql": "CREATE OR ALTER PROCEDURE ...",
  "databaseType": "sqlserver"
}

Returns in data:

• summary
• explanation
• issues
• bottlenecks
• antiPatterns
• assumptions
• runtimeValidation

detect_sql_antipatterns

Input:

{
  "procedureSql": "CREATE OR ALTER PROCEDURE ...",
  "databaseType": "sqlserver"
}

Returns in data:

• issues
• issueCount

suggest_indexes

Input:

{
  "procedureSql": "CREATE OR ALTER PROCEDURE ...",
  "databaseType": "sqlserver"
}

Returns in data:

• candidates
• filterIndexes
• compositeIndexes
• coveringIndexes
• warnings
• reasoning

explain_optimization_strategy

Input:

{
  "procedureSql": "CREATE OR ALTER PROCEDURE ...",
  "databaseType": "sqlserver"
}

Returns in data:

• summary
• likelySlowAreas
• recommendedChanges
• indexRecommendations
• assumptions
• runtimeValidation

optimize_stored_procedure

Input:

{
  "procedureSql": "CREATE OR ALTER PROCEDURE ...",
  "databaseType": "sqlserver",
  "rewriteMode": "conservative"
}

rewriteMode values:

• conservative Only structural guardrails such as CREATE OR ALTER, session settings, and TRY...CATCH scaffolding.
• performance Applies the conservative pass plus a small set of explicit high-confidence SQL Server pattern rewrites.

Returns in data:

• summary
• rewriteMode
• confidence
• explanation
• issues
• optimizedQuery
• rewriteDiagnostics
• indexRecommendations
• improvements
• assumptions
• runtimeValidation
• safetyNotes

SQL Server Heuristic Coverage

The SQL Server-first rules currently look for:

• missing SET NOCOUNT ON
• missing TRY...CATCH
• missing SET XACT_ABORT ON for write-heavy procedures
• optional parameter anti-patterns
• ISNULL or COALESCE on filtered columns
• scalar functions in WHERE or JOIN
• date-part and conversion predicates in filters
• parameter sniffing risk signals
• table variable risk
• temp table indexing opportunities
• repeated lookup against the same table
• NOLOCK usage
• likely missing schema qualification

Generic cross-dialect checks include:

• SELECT *
• CURSOR
• WHILE
• likely RBAR logic
• repeated subqueries
• DISTINCT masking join duplication
• leading wildcard LIKE
• broad OR predicates
• dynamic SQL
• implicit conversion risk

Rewrite Philosophy

The package does not attempt broad speculative rewrites. optimize_stored_procedure is intentionally explicit about assumptions and confidence.

Current SQL Server performance rewrites are narrow and pattern-based. Examples include:

• converting STRING_SPLIT delete filters on ...UID columns into parsed GUID keyset joins
• collapsing repeated GUID EXISTS probes against [APP].[tblFormDocuments] into staged joins
• replacing a very simple GUID-only table variable with a temp table plus primary key

If no exact rewrite pattern matches, the tool says so directly in rewriteDiagnostics.

Example

Input SQL:

CREATE PROCEDURE [APP].[DeleteFormDocuments]
    @DocumentUIDs VARCHAR(MAX)
AS
BEGIN
    DELETE [APP].[tblFormDocuments]
    WHERE DocumentUID IN (
        SELECT value FROM STRING_SPLIT(@DocumentUIDs, ',')
    );
END

Example optimize_stored_procedure request:

{
  "procedureSql": "CREATE PROCEDURE [APP].[DeleteFormDocuments] ...",
  "databaseType": "sqlserver",
  "rewriteMode": "performance"
}

Example response shape:

{
  "tool": "optimize_stored_procedure",
  "version": "1.1.0",
  "input": {
    "procedureSql": "CREATE PROCEDURE [APP].[DeleteFormDocuments] ...",
    "databaseType": "sqlserver",
    "rewriteMode": "performance"
  },
  "summary": "sqlserver static analysis found 4 issue(s): 2 high, 1 medium, 1 low severity.",
  "data": {
    "summary": "sqlserver static analysis found 4 issue(s): 2 high, 1 medium, 1 low severity.",
    "rewriteMode": "performance",
    "confidence": "medium",
    "optimizedQuery": "GO\nCREATE OR ALTER PROCEDURE ...\nGO",
    "rewriteDiagnostics": [
      "Converted STRING_SPLIT UID delete into parsed GUID keyset join."
    ],
    "assumptions": [
      "Analysis is based on static SQL text only.",
      "Execution plans, table sizes, statistics, and runtime parameter distributions were not available."
    ],
    "runtimeValidation": [
      "Capture actual execution plans for representative parameter sets."
    ]
  }
}

Runtime Validation Checklist

Always validate with:

• actual execution plans
• logical reads before and after
• CPU time before and after
• representative parameter sets
• write overhead of any new index

Project Structure

.
├── README.md
├── index.js
├── package.json
├── src
│   ├── lib
│   │   ├── detectAntipatterns.js
│   │   ├── indexHeuristics.js
│   │   ├── issues.js
│   │   ├── normalizeSql.js
│   │   ├── optimizationEngine.js
│   │   ├── outputFormatter.js
│   │   ├── sqlServerHeuristics.js
│   │   └── tooling.js
│   ├── server.js
│   └── tools
│       ├── analyzeStoredProcedure.js
│       ├── detectSqlAntipatterns.js
│       ├── explainOptimizationStrategy.js
│       ├── optimizeStoredProcedure.js
│       └── suggestIndexes.js
└── test
    ├── samples
    └── smoke.js

Safety Notes

• This package never executes SQL.
• This package never opens database connections.
• Any rewrite should still be reviewed and validated on representative workloads.
• Performance-oriented output should be treated as a candidate implementation, not proof.