npm package: @ngx-zone/agent-sp-optimization
Description: Enterprise MCP server for static stored procedure analysis, explicit SQL optimization guidance, conservative rewrites, and per-table index recommendations.
Latest version: 1.1.0

# @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

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

## Local Development

```bash
npm install
npm start
npm run smoke
```

## Codex MCP Setup

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

## Tool Contract

Every tool returns the same top-level JSON envelope:

```json
{
  "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:

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

Returns in `data`:

- `summary`
- `explanation`
- `issues`
- `bottlenecks`
- `antiPatterns`
- `assumptions`
- `runtimeValidation`

### `detect_sql_antipatterns`

Input:

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

Returns in `data`:

- `issues`
- `issueCount`

### `suggest_indexes`

Input:

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

Returns in `data`:

- `candidates`
- `filterIndexes`
- `compositeIndexes`
- `coveringIndexes`
- `warnings`
- `reasoning`

### `explain_optimization_strategy`

Input:

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

Returns in `data`:

- `summary`
- `likelySlowAreas`
- `recommendedChanges`
- `indexRecommendations`
- `assumptions`
- `runtimeValidation`

### `optimize_stored_procedure`

Input:

```json
{
  "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:

```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:

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

Example response shape:

```json
{
  "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

```text
.
├── 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.