Using AI with SQL Server databases can be a game changer, but you need to consider these four strategies to achieve the best results.
Whether you’re optimizing performance, reducing deadlocks, or reviewing code to prevent issues, these four tips will help you achieve better results.
Table of Contents
Introduction
Many in the SQL Server community have expressed skepticism that AI can help with database development or tuning. For example, Steve Jones recently wrote an editorial “Data > Hype”, where he states, “I find some models are biased more towards one platform (MySQL) than another (SQL Server).” In #TSQL2sday: AI-Assisted Query Optimization in SQL Server, Dave Pinal posted that he tried to use AI to generate better performing alternatives for a query only to find they were actually slower.
While AI is not perfect, neither are people. Using the strategies described here can help you get better and more useful results that you can test and validate with your workload.
Tip #1: Choose the Right Data for Your AI Model
Suppose your database is performing slower than you want or you notice heavier resource use. Many times adjusting the indexes will be the fix. How can you use AI to help?
Consider these key sources:
- Missing Indexes: SQL Server provides a great start by tracking missing indexes. However, since the query plan optimizer is identifying them as individual queries are run, just creating the listed indexes will likely not be optimal since you will get redundant indexes with overlapping columns.
- Index Operational Statistics: Index operational statistics are another potential source of information. This provides information such as the number of index updates, reads and scans. Index with high write activity and few or no reads should be dropped since they are adding overhead and wasting space but not providing queries with any benefits.
- Plan Cache: The plan cache holds information about recently run queries and their execution plans.
Pro Tip: For optimal AI-driven index optimization, provide all three data sources—missing indexes, index stats, and plan cache—to your model. AI excels at processing large volumes of information and delivering actionable recommendations.
Tip #2: Select the Best AI Model for SQL Database Tasks
AI large language models (LLMs) are rapidly evolving. For SQL Server database tasks, consider a model’s quality and reasoning capabilities. GPT-5, released in August 2025, provided substantial improvements in reasoning compared to earlier versions. OpenAI has just released 5.1 this month, which promises further gains.
The Azure AI Foundry has a leaderboard that lets you compare models across various criteria. This is a great way to objectively choose a model.
GitHub Copilot’s documentation also provides a good comparison of models and when they are a good fit. A model with deep reasoning capability is important; you can find examples here: https://docs.github.com/en/copilot/reference/ai-models/model-comparison#task-deep-reasoning-and-debugging
Microsoft’s model router automatically selects the best model for your request, balancing performance and cost. Microsoft released a new version, 2025-11-18, that adds several new underlying models including Anthropic Claude and Deepseek.
Tip #3: Use an appropriate prompt
Working with an LLM can sometimes feel like working with a junior developer: they have a lot of knowledge, but if you don’t provide specific instructions you may not get what you want. For example, if you give it the index information we discussed, and ask it to generate index creation scripts it will do that. However, do you also want it to consider compression or fill factor? Do you have a naming standard it should follow? Do you also want a drop statement so you have it handy?
There are lots of posts and guidance about effective prompting, but one tip is to use AI to help you. For example, asking “create a good prompt for sql server index optimization” resulted in “Analyze the current indexing strategy of my SQL Server database. Identify unused, duplicate, or overlapping indexes, and recommend optimizations to improve query performance and reduce maintenance overhead. Include suggestions for missing indexes based on query patterns, and provide a summary of high-impact indexes that should be retained or adjusted. The database name is [YourDatabaseName] and the focus is on the [YourSchema] schema. Prioritize tables with high read/write activity and large row counts.”
As you adjust the prompt, you may need to reconsider the data you are providing. For example, if you want the AI to prioritize large tables, make sure you provide table sizes.
Tip #4: Pick the right options for your LLM
Any large language model (LLM) has settings to control how it behaves. This will impact how “creative” it gets as well as how deeply it researches an answer.
Here’s a table summarizing common configuration options for Large Language Models (LLMs), including temperature, reasoning effort, and others. These options influence how the model behaves during inference, and different models may support different subsets of these options.
| Option | Description | Applies To |
| Temperature | Controls randomness in output. Lower = more deterministic; higher = more creative. Typical range: 0.0–1.0. | Most LLMs (GPT-3, GPT-4, Claude, PaLM, Mistral, LLaMA, etc.) |
| Top-p (nucleus sampling) | Limits sampling to a subset of tokens whose cumulative probability ≤ p. Helps control diversity. | Most LLMs (OpenAI, Anthropic, Google, Meta) |
| Max tokens | Sets the maximum number of tokens in the output. Prevents overly long responses. | All LLMs |
| Reasoning effort / Steps | Controls depth of reasoning or number of internal steps (e.g., chain-of-thought). Often implicit or via prompt engineering. | Claude (via max_tokens_to_sample), GPT (via prompt), Gemini |
AI SQL Tuner uses the Microsoft Foundry model router. This lets you select a routing mode of Balanced, Quality or Cost.
Conclusion
Using AI to help optimize your SQL Server databases can save you time and give you great results. Of course, SQL Server is complex and there are many factors that impact how it behaves, so whether a human or AI tries to optimize it nothing can be guaranteed. Testing and iterating are often the only choice. But these four tips can quickly put you on the path to success, saving you hours of manual steps, or thousands of dollars in server and licensing costs.
If you would prefer to use a program that already has these built-in, give AI SQL Tuner a try. You can start with the Free edition that works with SQL Server Developer Edition. The paid versions come with a 30-day money-back guarantee so you can try any of them risk-free.
