メニュー
Capture and interpret SQL Server execution plans. Identify bad operators (table scans, key lookups, hash spills), diagnose parameter sniffing, and act on row estimate discrepancies.
デフォルトでは、最初にソースを確認する Prompt が選択されています。直接コマンドに切り替えるか、ローカルコピーをダウンロードすることもできます。
インストールを決める前に、SKILL.md と SkillsMP に表示されている付属ファイルをお読みください。
Codex または Claude でインストール この Prompt をコピーして Codex、Claude、または他のアシスタントに貼り付けると、Skill ページを確認してインストールできます。
直接コマンドでは確認用 Prompt が省略されます。実行前にソースを確認してください。
npx skills add https://github.com/vince-winkintel/sql-server-skills --skill sqlserver-execution-plansコマンドは1行のまま表示されます。コピー前に横へスクロールして全体を確認してください。
ローカルで確認しますか？SkillsMP が現在取得できるファイルをダウンロードできます。
Comprehensive SQL Server performance diagnostics, index analysis, execution plan interpretation, query optimization, schema management, backup/restore, and monitoring using sqlcmd and T-SQL DMVs. Use when analyzing slow queries, investigating wait stats, finding missing indexes, reading execution plans, optimizing stored procedures or views, managing migrations, monitoring SQL Agent jobs, or diagnosing blocking/deadlocks on Microsoft SQL Server. Triggers on: SQL Server, MSSQL, sqlcmd, T-SQL performance, slow query, execution plan, DMV, missing index, wait stats, blocking, deadlock, stored procedure optimization, view tuning.
SQL Server backup and restore operations — full, differential, and log backups, RESTORE with NORECOVERY/RECOVERY, RESTORE VERIFYONLY, backup history queries, and recovery model guidance.
Identify SQL Server performance bottlenecks using DMV queries. Covers wait stats, slow queries, active requests, performance counters, and connection analysis.
SKILL.md を表示中
SOC 職業分類に基づく
| name | sqlserver-execution-plans |
| description | Capture and interpret SQL Server execution plans. Identify bad operators (table scans, key lookups, hash spills), diagnose parameter sniffing, and act on row estimate discrepancies. |
Execution plans show exactly how SQL Server will execute (or did execute) a query. Reading them is the fastest way to diagnose why a query is slow.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- Your query here
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Output interpretation:
Table 'Orders'. Scan count 1, logical reads 1247, physical reads 0, ...
SQL Server Execution Times: CPU time = 312 ms, elapsed time = 445 ms.
-- Estimated plan — does NOT execute the query
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET SHOWPLAN_XML OFF;
-- Actual plan — executes the query and captures real row counts
SET STATISTICS XML ON;
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET STATISTICS XML OFF;
The XML result contains the full plan. Paste it into SSMS or SentryOne Plan Explorer for visual rendering.
-- Find a cached plan by query text substring
SELECT TOP 5
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%Orders%' -- adjust filter
ORDER BY avg_elapsed_ms DESC;
Click the query_plan result to open the graphical plan in SSMS.
See references/plan-operators.md for the full reference table.
Operator: Table Scan (or Clustered Index Scan on large table)
Cost: HIGH — reads every row in the table
Fix: Add a non-clustered index with appropriate key columns
A table scan is acceptable only on small tables (< ~1000 rows). On large tables, it's almost always a missing index.
Operator: Key Lookup
Cost: MEDIUM–HIGH per row — two B-tree traversals per row
Fix: Add INCLUDE columns to the non-clustered index to cover the SELECT list
-- You have: CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID)
-- Query needs: CustomerID (seek), OrderDate, Status, TotalAmount (lookup to clustered)
-- Fix:
CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID)
INCLUDE (OrderDate, Status, TotalAmount);
Operator: Index Scan
Cost: MEDIUM — reads all or a large portion of the index leaf level
Fix: Check if a predicate is non-SARGable; verify index column order
Operator: Hash Match (Join or Aggregate)
Symptom: Spill to TempDB warning icon, high memory grant
Fix: Add index to eliminate hashing; reduce row counts entering the join
Hash Match joins are not inherently bad — they're appropriate for large un-indexed joins. The problem is memory spills: when the hash table doesn't fit in the granted memory, SQL Server spills to TempDB — catastrophic for performance.
Operator: Sort
Symptom: High cost percentage; spill warning
Fix: Add an index whose key order matches the ORDER BY to eliminate the sort
Operator: Index Seek
Cost: LOW — navigates directly to relevant rows
This is what you want. The optimizer used the index B-tree to find exactly the rows needed.
Operators: Distribute Streams, Gather Streams, Repartition Streams
Context: Query is executing in parallel
Related wait: CXPACKET, CXCONSUMER
Parallelism is not inherently bad. Issues arise when:
MAXDOP is too high for the workload-- Force single-threaded execution for testing
SELECT * FROM Orders WHERE CustomerID = 42
OPTION (MAXDOP 1);
One of the most important things to check: does SQL Server's estimate match reality?
In SSMS: Hover over any operator — "Estimated Number of Rows" vs "Actual Number of Rows"
| Discrepancy | Meaning | Fix |
|---|---|---|
| Estimate = 1, Actual = 50,000 | Severely underestimated — bad plan (wrong join strategy, missing index) | Update statistics, check for parameter sniffing |
| Estimate = 50,000, Actual = 1 | Overestimated — wasteful grants, parallel plan where serial is better | Update statistics |
| Within 2-3x | Acceptable | — |
Update statistics:
-- Update all statistics on a table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- Update all statistics in the database
EXEC sp_updatestats;
-- Check statistics age
SELECT
OBJECT_NAME(s.object_id) AS table_name,
s.name AS stat_name,
sp.last_updated,
sp.rows,
sp.rows_sampled,
sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) = 'Orders'
ORDER BY sp.modification_counter DESC;
SQL Server compiles a stored procedure's plan on first execution using the parameter values at that time. If those values aren't representative, all subsequent executions use a bad plan.
Symptom: A stored procedure is fast when run directly but slow when called from the application (or vice versa).
Diagnose:
-- Check the cached plan and parameters it was compiled for
SELECT
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
qp.query_plan
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
JOIN sys.dm_exec_query_stats qs ON ps.plan_handle = qs.plan_handle
WHERE OBJECT_NAME(ps.object_id) = 'usp_GetOrders';
-- Look at the plan — was it compiled for a selective or non-selective parameter value?
Fixes (in order of preference):
-- Option 1: OPTIMIZE FOR UNKNOWN — use average statistics, not sniffed values
CREATE PROCEDURE usp_GetOrders @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
GO
-- Option 2: OPTION (RECOMPILE) — recompile on every execution (CPU cost)
CREATE PROCEDURE usp_GetOrders @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);
GO
-- Option 3: Local variable (breaks sniffing — not always ideal)
CREATE PROCEDURE usp_GetOrders @CustomerID INT
AS
DECLARE @LocalCustomerID INT = @CustomerID;
SELECT * FROM Orders WHERE CustomerID = @LocalCustomerID;
GO
-- Option 4: Clear the procedure cache (nuclear, use carefully in production)
DBCC FREEPROCCACHE;