Joey on SQL Server

Hands-On with Copilot for Azure SQL Database

I've been playing around with the new Microsoft service, currently in preview and it's time to show off what it can do.

• By Joey D'Antoni
• 03/21/2024

Have you ever struggled with a database performance problem or ever struggled to write T-SQL code to solve a business problem? On Thursday at the SQLBits conference in Hampshire, U.K., Microsoft announced the private preview of Copilot for Azure SQL Database.

This new Azure service aims to help users write queries and administrators troubleshoot problems with their database. I have been using these features in testing for a while, and during the conference, I had a chance to speak with Joe Sack, Principal Product Manager for Azure SQL Database, about the new features.

Writing Better T-SQL
Last year, I wrote about GitHub Copilot for Azure Data Studio and the general state of AI in databases. Since then, we've seen Microsoft add support for OpenAI connections in both Azure SQL DB and Azure PostgresDB, as well as the introduction of Azure Copilot.

The latter operates on the Azure control plane in conjunction with Log Analytics to make recommendations around your Azure resource, and it was not surprising to see Copilot come into the database layer. It is important to note that this is a different product than the Copilot for Azure Data Studio that I wrote about in November, the most significant difference being that the new SQL Database Copilot operates with knowledge of the tables involved in the database. Let's dive in.

In the above demo, I included all the tables in my Azure SQL Database (AdventureWorksLT) and asked Copilot to build a query to show me sales by sales rep and product color. The process of generating this query takes about five to seven seconds (I’ve trimmed this from the video). This query generation happens in the security context of the calling user, so they will only see tables and columns they can access.

In this slightly more advanced example, the Copilot generates a PIVOT query to showcase the number of properties sold. One interesting thing to notice is that in the comments the prompt that the user entered is captured along with an explanation of how the query was built, which will can help to educate users on T-SQL.

Another security and safety note is that Azure SQL Copilot operates within Microsoft's trust and safety guidelines for AI. In this initial implementation, only Copilot will generate SELECT (or read) queries against user tables, not system tables or catalog views. In my testing, I experienced mixed results, as the service would sometimes throw errors around more complex queries. However, it generally generated efficient T-SQL code for many business scenarios. Like the GitHub Copilot, it won't replace solid SQL programming knowledge. Still, it can reduce your trips to online documentation and help business analysts start to write better queries.

Understanding Database Performance
I've been working with databases for longer than I care to admit -- for parts of four decades. From that experience, I can tell you that many IT professionals, whether system admins, infrastructure folks, developers or even some DBAs, don't understand how databases operate and function. If I had a dollar for every time I've had to explain to a system administrator that, "it's perfectly normal for the SQL Server to be using almost all of the memory on the server," or "no, you can't use that generic backup tool to backup the database server," I might have retired by now. 

Beyond that basic knowledge, when we get into advanced topics like understanding query execution plans (which are foundational to solving database performance issues), even smaller groups of people have that knowledge. It's understandable that execution plans are the output of complex relational algebra and are complex enough to merit entire books dedicated to reading plans. 

Microsoft has made several reasonable attempts to make some of these problems more manageable for administrators to solve. The Query Store (introduced in SQL Server 2016) is the best feature added to SQL Server since hot backup became a thing. However, in working with many different customers, my team at DCAC still finds many people who need help troubleshooting a database performance issue quickly. Copilot aims to help solve those problems.

The administrative aspect of Copilot here is more like the ChatGPT experience than the T-SQL query builder. You can ask questions like "Why is my database slow?"

In the above GIF, Copilot reports that the database has been experiencing a high CPU condition and that a specific query has been executed over 16,000 times in the last two hours. Copilot also shows the query text and the calling program SQLQueryStress. When asked how to tune that query, the service replies with a recommended index and mentions that the query doesn't match any known anti-patterns. SQL Server 2022 (and Azure SQL Database) quietly added an extended event to detect anti-patterns that the AI tool here is building upon. You should also note that Copilot won't implement the index for you (although you can enable Azure SQL DB to do that for you).

Some other "skills" that Copilot can assist with include answering questions like, "Have any deadlocks happened recently?" or "What are the top 10 CPU percentage queries over the last 12 hours?" Copilot builds its answers based on several resources, including data in the Azure control plane, catalog views and dynamic management views (DMVs) in the database itself, as well as the query store in the database for the "what happened when" types of queries.

Microsoft has taken a rather conservative approach with these two AI implementations for Azure SQL DB, and this is a good start. The big difference between these AI tools and some of the existing implementations that we have seen in the past is that these newer tools operate with better contextual information, which should allow them to provide more informed feedback and better guidance to administrators.

Copilot for Azure SQL Database has limited private preview and no pricing information. Microsoft has indicated to me that this will be a paid offering but has offered no pricing model. As the services use similar resources, I expect to fall under whatever the pricing model for Azure Copilot becomes. I hope to see more skills added to the Copilot before it reaches general availability. While not yet life-changing, these tools can significantly help an organization that needs more specialist database skills.