SQL Server Database Query Plan Analysis

A SQL Server analysis script that extracts and evaluates cached execution plans to identify performance issues such as missing indexes, tempdb spills, key lookups, and implicit conversions.

Database_QueryPlanAnalysis.sql

Analyzes the SQL Server plan cache to surface potentially problematic queries.

Key features:

• Returns the top N queries by total CPU usage (configurable via @TopN).
• Shows both the full batch text and the specific statement text.
• Provides XML showplan for each query.
• Flags common performance issues:
  • HasMissingIndexWarning
  • HasSpillToTempDb (hash/sort spills to tempdb)
  • HasKeyLookup
  • HasImplicitConversion (searches for CONVERT_IMPLICIT in the plan)

Requirements:

• VIEW SERVER STATE permission.
• Plan cache must contain relevant queries (cleared on restart or DBCC FREEPROCCACHE).

Suggested usage:

• Filter or sort by the flag columns to focus on queries with specific issues.
• Export results to Excel for triage.
• Click the QueryPlanXml in SSMS to open the graphical execution plan and investigate further.