T-SQL X-Acto Knife Analyzer
Break down complex SQL queries into testable components for optimization
Input
Paste the execution plan XML. In SSMS, you can get this by right-clicking on the execution plan and selecting “Show Execution Plan XML”
Paste output from DBCC SHOW_STATISTICS commands for deeper analysis
Query Overview
Detected Tables:
JOIN Operations:
Filters:
Aggregates:
X-Acto Components
These are individual test components based on the X-Acto Knife technique. Run these separately to identify issues.
Execution Plan Analysis
Cardinality Estimation Issues:
Operator Warnings:
Index Usage:
Statistics Commands
Query Restructuring Suggestions
Statistics Analysis
About the X-Acto Knife Technique
The X-Acto Knife technique, popularized by Brent Ozar, helps you diagnose and fix SQL Server performance problems by breaking complex queries into smaller, testable parts.
How it works:
- Break your query into individual components (tables with filters, joins, etc.)
- Run each component separately to verify row counts and performance
- Identify where estimates differ significantly from actual row counts
- Use statistics and plan information to fix problematic parts
- Test alternative query formulations to improve performance
Common T-SQL Performance Issues:
- Parameter Sniffing: When the query plan is optimized for parameter values that may not be ideal for all executions
- Bad Cardinality Estimates: When SQL Server incorrectly estimates how many rows will be returned by an operation
- Suboptimal Join Order: When tables are joined in an inefficient order
- Missing or Outdated Statistics: When data distribution information is missing or stale
- Table Scans Instead of Seeks: When SQL Server reads an entire table instead of using an index