sqlserver-diagnostics_skill

This skill analyzes SQL Server performance bottlenecks using DMV queries, identifying wait stats, slow queries, active requests, and memory pressure.

Python

2.5k

GitHub Stars

1

Bundled Files

3 weeks ago

Catalog Refreshed

1 month ago

First Indexed

Readme & install

Copy the install command, review bundled files from the catalogue, and read any extended description pulled from the listing source.

Installation

Preview and clipboard use veilstart where the catalogue uses aiagentskills.

npx veilstart add skill openclaw/skills --skill sqlserver-diagnostics

SKILL.md9.1 KB

Overview

This skill helps identify SQL Server performance bottlenecks using targeted DMV queries. It provides quick workflows for wait statistics, top slow queries, active requests, performance counters, connection analysis, and memory usage. Use these diagnostics before deep diving into indexing or execution plan tuning. The outputs point directly at I/O, CPU, memory, locking, or application-level issues.

How this skill works

The skill runs a set of proven DMV queries to collect wait stats, aggregated query statistics, currently executing requests, key performance counters, connection counts, and buffer pool usage. It highlights dominant wait types and top-consuming queries, and surfaces blocking, long-running sessions, and memory grant pressure. Results map to concrete remediation categories (I/O, locking, parallelism, memory, TempDB) so you know which next-step action to take.

When to use it

When users report slow application responses or query timeouts
After a sudden change in server behavior (spike in latency or resource use)
Before starting index or execution-plan tuning to focus your effort
When diagnosing frequent blocking or long-running transactions
To validate whether memory, CPU, or I/O is the root cause

Best practices

Start with wait stats to classify the primary resource pressure
Prioritize queries by avg elapsed time and execution count for highest impact
Check active requests for real-time blocking and suspended sessions
Monitor Page Life Expectancy and Memory Grants Pending for memory pressure
Use buffer pool by-database and connection counts to spot hot databases or runaway clients

Example use cases

Diagnose a server showing high PAGEIOLATCH waits to decide on faster storage or indexing
Find the few queries driving most elapsed time and reduce their impact
Identify the blocking session that’s causing intermittent application hangs
Detect memory grant contention causing spills and plan changes
Confirm TempDB contention by seeing PAGELATCH or LATCH waits and buffer usage

FAQ

Start with wait stats; the dominant wait type usually directs you to the category of problem (I/O, CPU, locking, memory, TempDB, or network).

How do I know a query is worth optimizing?

Look for queries with high avg_elapsed_ms combined with high execution_count or large avg_logical_reads — these deliver the most cumulative impact.