Query Optimization

What it does

This skill provides a comprehensive guide to T-SQL query optimization techniques, enabling AI agents to analyze and improve database queries. It focuses on identifying non-SARGable patterns, understanding join type performance characteristics, and utilizing query hints for enhanced efficiency. The goal is to help agents generate more performant SQL queries within agent workflows.

When to use it

• When an AI agent needs to optimize existing T-SQL queries for improved performance.
• When designing new database queries and needing guidance on SARGable patterns.
• To understand the tradeoffs between different join types (Nested Loop, Merge Join, Hash Join).
• When troubleshooting slow query execution times and identifying potential bottlenecks.

Key capabilities

• SARGability Identification: Distinguishes between SARGable (index-seeking) and non-SARGable query patterns with examples.
• Join Type Performance Analysis: Provides a comparison of Nested Loop, Merge Join, and Hash Join types, outlining their best use cases and characteristics.
• Query Hint Reference: Offers a quick reference guide to T-SQL query hints for controlling compilation, optimization, parallelism, and data access.
• Implicit Conversion Awareness: Highlights the importance of avoiding implicit data type conversions in queries.

Example prompts

• "Can you rewrite this SQL query to be more SARGable?" [followed by a non-SARGable query]
• "What are the performance characteristics of a Nested Loop join compared to a Hash Join?"
• "How can I use query hints to limit parallelism in my T-SQL query?"

Tips & gotchas

• This skill focuses specifically on T-SQL query optimization.
• Understanding database indexes is crucial for effectively applying the techniques described within this guide.
• Be mindful of potential dirty reads when using WITH (NOLOCK).