explain_query
Analyze SQL query performance by generating execution plans to identify optimization opportunities and understand query behavior.
Instructions
Get the execution plan for a SQL query to analyze performance
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | The SQL query to analyze | |
| database | No | Optional: Database name to use for this query | |
| include_actual_plan | No | Include actual execution statistics (optional, defaults to false) |
Implementation Reference
- Main handler function for 'explain_query' tool. Generates SQL Server execution plans using SHOWPLAN_ALL or fallback SHOWPLAN_TEXT without executing the actual query. Includes performance monitoring and error handling.
async explainQuery(query, database = null) { try { const pool = await this.getConnection(); const request = pool.request(); // Switch database if specified if (database) { await request.query(`USE [${database}]`); } // Execute the SET SHOWPLAN_ALL ON in a separate batch await request.query('SET SHOWPLAN_ALL ON'); // Execute the query to get the execution plan const result = await request.query(query); // Turn off SHOWPLAN_ALL await request.query('SET SHOWPLAN_ALL OFF'); // Track performance if (this.performanceMonitor) { this.performanceMonitor.recordQuery({ tool: 'explain_query', query, executionTime: 0, // SHOWPLAN doesn't actually execute success: true, database, timestamp: new Date() }); } return this.formatResults(result); } catch { // If SHOWPLAN_ALL doesn't work, try with estimated execution plan try { const pool = await this.getConnection(); const request = pool.request(); // Switch database if specified if (database) { await request.query(`USE [${database}]`); } // Try SET SHOWPLAN_TEXT instead await request.query('SET SHOWPLAN_TEXT ON'); const result = await request.query(query); await request.query('SET SHOWPLAN_TEXT OFF'); // Track performance if (this.performanceMonitor) { this.performanceMonitor.recordQuery({ tool: 'explain_query', query, executionTime: 0, // SHOWPLAN doesn't actually execute success: true, database, timestamp: new Date() }); } return this.formatResults(result); } catch (innerError) { // Track failed query if (this.performanceMonitor) { this.performanceMonitor.recordQuery({ tool: 'explain_query', query, executionTime: 0, success: false, error: innerError.message, database, timestamp: new Date() }); } // Re-throw the error so it can be handled by the caller throw innerError; } } } - lib/tools/tool-registry.js:146-159 (schema)Input schema definition for the 'explain_query' tool, defining parameters: query (required string), database (optional string), include_actual_plan (optional boolean).
name: 'explain_query', description: 'Get the execution plan for a SQL query to analyze performance', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'The SQL query to analyze' }, database: { type: 'string', description: 'Optional: Database name to use for this query' }, include_actual_plan: { type: 'boolean', description: 'Include actual execution statistics (optional, defaults to false)' } }, required: ['query'] } - index.js:308-311 (registration)MCP tool dispatch registration in the main server switch statement. Calls DatabaseToolsHandler.explainQuery with parsed arguments.
case 'explain_query': return { content: await this.databaseTools.explainQuery(args.query, args.database) }; - index.js:241-242 (registration)Registers the tool list handler which includes 'explain_query' from tool-registry.js via getAllTools().
this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: getAllTools()