Even Brent Likes AI Now

For those that don’t know, Brent Ozar is one of the leading consultants focusing on SQL Server performance. He is well-known as the developer of the SQL Server First Responder Kit, a great set of scripts for diagnosing SQL Server issues.

In a recent post, “Get ChatGPT’s Advice On Your Queries with sp_BlitzCache“, he shared the following:

 

 I vividly remember one gig where a client had been struggling with memory starvation for months, and on the call together, we identified the query with sp_BlitzCache, then pasted the query into ChatGPT. It found the query’s problem in minutes, rewrote the query to work around the problem, and then we got started testing the changes to make sure they worked the way we needed ’em to work.

Bottom line is Brent is better at pretty much everyone else when it comes to SQL Server tuning and he is acknowledging that AI can be better and faster than him.

Head over to Even Brent likes AI now - AI SQL Tuner to see the full post.

If you are a Power BI (or Fabric) developer, why do you care about SQL Server performance? Well, there is a good chance that one or more of your sources are SQL Server databases. Users are no longer accepting daily updates: they want multiple refreshes per day, or up to date information using DirectQuery. In either case you are adding more load to the source database, and often using queries that the original developers did not expect. For example, if you are running incremental refreshes, is that resulting in a full table scan because there is no index on the "last_updated_date" column?

Other customers may want to access the SQL Server database directly, for example using an MCP server for conversational BI. Instead of accessing your semantic model, the workload is transferred to the source system, and now an AI model is generating queries that may or may not be optimized or have the proper indexes to support them.

SQL Server Tuning Made Easy

Database tuning can traditionally be a tedious process. AI SQL Tuner uses AI to help make it faster. For example, its Index Tuning feature gathers key information from the database such as missing indexes, index usage statistics, and top queries from the plan cache. It provides the information to an AI reasoning model uses that information to provide specific, actionable recommendations to improve performance.

Getting started is easy. Download the .zip file, and extract it to a folder. Install the .Net 10 runtime. Run the AISQLTuner.exe and give it your database information and tuning goal. In about a minute you'll have a report with guidance. Below is an example from a test on Azure SQL Database, showing recommendations tailored to its capabilities. 

AI SQL Tuner does not make any changes to your database, so you can review and choose to apply in a lower environment to test first, for example.

AI SQL Tuner is running a 25% off sale through 11:59 PM Eastern on 12/24/25 for Power BI Gurus readers. Use code PBIGURUS25 to save 25% off the annual subscription for AI SQL Tuner Standard or AI SQL Tuner Pro.

Give the gift of time for yourself or your favorite DBA or developer so you or they can have more time sipping eggnog and less time trying to figure out why your queries are running too slow. :)

AI SQL Tuner Recommendations

Tuning goal: Index Tuning

Server: sqlsvr-aisqltuner | Database: SQLStorm | Engine: Azure SQL Database (Microsoft SQL Azure RTM 12.0.2000.8)

Executive Summary (Top Priorities)

1. Create narrow nonclustered indexes to support foreign key relationships that currently lack leading-column indexes, reducing scans, locks, and cascade delete/update overhead:
   • dbo.Posts: IX on ParentId, AcceptedAnswerId, LastEditorUserId
   • dbo.PostLinks: IX on PostId, RelatedPostId, LinkTypeId
   • dbo.Tags: IX on ExcerptPostId, WikiPostId
   • dbo.Comments: IX on UserId
   • dbo.Votes: IX on VoteTypeId
   • dbo.PostHistory: IX on UserId, IX on PostHistoryTypeId
2. Retain existing nonclustered indexes; none are clearly redundant or unused. No clustered index changes are recommended.
3. Refresh statistics on heavily scanned tables to improve cardinality estimates (notably dbo.Users and frequently read tables):
   • Targeted UPDATE STATISTICS with RESAMPLE, followed by lightweight maintenance cadence.
4. Leverage Azure Automatic Tuning for index create/drop to complement manual tuning.

Rationale highlights:

• Missing Indexes DMV does not currently report opportunities; however, index usage shows substantial scans on clustered PKs (e.g., dbo.Users) and several FKs lack supporting indexes. These are low-risk, high-value additions with minimal write overhead (low user_updates across tables).
• No tables exceeded 1,000,000 rows in the provided snapshot; columnstore indexes are not recommended at this time.

Detailed Prioritized Recommendations

1. Add supporting nonclustered indexes for Foreign Keys (FK-first-column, narrow keys, no INCLUDE). Use a modest FILLFACTOR to reduce page splits while keeping overhead minimal.
   Notes:

   • These indexes primarily improve FK enforcement checks (DELETE/UPDATE on parent tables), common join patterns, and reduce blocking.
   • Given low user_updates across these tables, the maintenance overhead is minimal compared to seek benefits.
   • Compression: retain default (uncompressed). Data size indicators do not justify compression here.

   -- 1) Posts: support self-references and user references
   CREATE INDEX IX_Posts_ParentId
     ON dbo.Posts (ParentId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   CREATE INDEX IX_Posts_AcceptedAnswerId
     ON dbo.Posts (AcceptedAnswerId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   CREATE INDEX IX_Posts_LastEditorUserId
     ON dbo.Posts (LastEditorUserId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   -- 2) PostLinks: support all FK columns
   CREATE INDEX IX_PostLinks_PostId
     ON dbo.PostLinks (PostId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   CREATE INDEX IX_PostLinks_RelatedPostId
     ON dbo.PostLinks (RelatedPostId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   CREATE INDEX IX_PostLinks_LinkTypeId
     ON dbo.PostLinks (LinkTypeId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   -- 3) Tags: support FKs to Posts
   CREATE INDEX IX_Tags_ExcerptPostId
     ON dbo.Tags (ExcerptPostId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   CREATE INDEX IX_Tags_WikiPostId
     ON dbo.Tags (WikiPostId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   -- 4) Comments: support FK to Users (PostId already indexed)
   CREATE INDEX IX_Comments_UserId
     ON dbo.Comments (UserId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   -- 5) Votes: support FK to VoteTypes
   CREATE INDEX IX_Votes_VoteTypeId
     ON dbo.Votes (VoteTypeId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   -- 6) PostHistory: support FKs to Users and PostHistoryTypes
   CREATE INDEX IX_PostHistory_UserId
     ON dbo.PostHistory (UserId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];
   
   CREATE INDEX IX_PostHistory_PostHistoryTypeId
     ON dbo.PostHistory (PostHistoryTypeId)
     WITH (FILLFACTOR = 95) ON [PRIMARY];

2. Keep existing indexes; do not drop at this time.
   Justification:

   • All listed nonclustered indexes show meaningful seeks/scans and zero update overhead, indicating positive ROI.
   • No duplicates or overlapping indexes with clear superset candidates were detected in the snapshot.
3. Update statistics on heavily scanned tables and key indexes.
   Guidance:

   • Auto Create/Update Stats (including async) are enabled; supplement with targeted updates to improve estimates on frequently scanned objects.
   • Prefer RESAMPLE to align with Azure sampling heuristics; FULLSCAN is not warranted given no extreme skew was indicated.

   -- Targeted stats refresh (heavier scans observed on clustered PKs)
   UPDATE STATISTICS dbo.Users WITH RESAMPLE;
   UPDATE STATISTICS dbo.Posts WITH RESAMPLE;
   UPDATE STATISTICS dbo.Comments WITH RESAMPLE;
   UPDATE STATISTICS dbo.Votes WITH RESAMPLE;
   UPDATE STATISTICS dbo.PostHistory WITH RESAMPLE;
   
   -- Optional: refresh specific nonclustered indexes (safe and quick)
   UPDATE STATISTICS dbo.Comments IX_Comments_PostId WITH RESAMPLE;
   UPDATE STATISTICS dbo.Votes IX_Votes_PostId_VoteType WITH RESAMPLE;
   UPDATE STATISTICS dbo.Votes IX_Votes_UserId_VoteType_Bounty WITH RESAMPLE;
   UPDATE STATISTICS dbo.Posts IX_Posts_PostType_CreationDate_Owner WITH RESAMPLE;
   UPDATE STATISTICS dbo.Posts IX_Posts_CreationDate_Owner_Score_View WITH RESAMPLE;
   UPDATE STATISTICS dbo.Posts IX_Posts_OwnerUserId_CreationDate_Inc WITH RESAMPLE;
   UPDATE STATISTICS dbo.PostHistory IX_PostHistory_PostId_Type WITH RESAMPLE;

4. Leverage Azure Automatic Tuning for continuous index optimization.
   Notes:

   • Azure SQL Database supports automatic index create/drop. This complements the manual FK-supporting indexes above and can react to evolving workload patterns.

   -- Enable Automatic Tuning (database-scoped) for indexes
   ALTER DATABASE CURRENT
   SET AUTOMATIC_TUNING ( CREATE_INDEX = ON, DROP_INDEX = ON );

5. Query pattern guidance to capitalize on existing indexes.
   Recommendations:

   • Join/filter on FK columns that now have leading indexes (e.g., PostLinks.PostId, Comments.UserId, Votes.VoteTypeId) to drive seeks.
   • When filtering dbo.Posts by temporal/owner/type, prefer predicates aligning with existing NCIs:
     • IX_Posts_PostType_CreationDate_Owner: PostTypeId, CreationDate, OwnerUserId
     • IX_Posts_CreationDate_Owner_Score_View: CreationDate, OwnerUserId (with Score/View as additional keys)
     • IX_Posts_OwnerUserId_CreationDate_Inc: OwnerUserId, CreationDate
   • Avoid scalar functions on predicate columns; ensure sargable predicates to allow index seeks.
6. Not recommended at this time
   • Clustered/nonclustered columnstore indexes: No tables exceeded 1,000,000 rows in the snapshot; columnstore would add maintenance overhead without sufficient benefit.
   • Primary key changes: Not indicated. Therefore, no PK or dependent FK drop/recreate scripts are provided.
   • Compression changes: Data size signals are insufficient; default (no compression) retained to avoid CPU overhead.

Impact and Overhead Assessment

• Write overhead: Minimal. Index usage stats show low user_updates relative to reads across target tables. Added indexes are narrow single-column keys, minimizing page splits and log volume.
• Read performance: Significant improvements expected for join and delete-check patterns on FK columns; reduced clustered scans and lock contention on parent deletes/updates.
• Maintenance: Rebuilds/reorgs unlikely to be frequent with FILLFACTOR 95 and narrow keys; existing auto-stats will maintain estimates, supplemented by targeted updates above.

Deployment Order

1. Create indexes on high-activity FK paths first: Comments(UserId), Votes(VoteTypeId), Posts(ParentId), PostLinks(PostId).
2. Create remaining FK-supporting indexes: PostLinks(RelatedPostId, LinkTypeId), Tags(ExcerptPostId, WikiPostId), Posts(AcceptedAnswerId, LastEditorUserId), PostHistory(UserId, PostHistoryTypeId).
3. Run targeted UPDATE STATISTICS.
4. Enable Automatic Tuning (if not already enabled with desired settings).

Generated by AI SQL Tuner — https://aisqltuner.com