TL;DR. AI SQL Tuner Studio 1.0.37 adds a new Locking and Blocking Analysis goal that pinpoints the root cause of SQL Server blocking and deadlocks from ten correlated evidence sections – blocking chains, lock inventory, blocker query plans, deadlock history from Extended Events, Query Store top queries, RCSI / snapshot / lock-escalation settings, and TempDB contention. Index Tuning and Code Review can now be scoped to a specific schema or schema + object, so analysis runs only against the tables, indexes, or modules you care about.
What’s new in AI SQL Tuner Studio 1.0.37
This release focuses on faster root-cause analysis and tighter scoping for production SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Fabric SQL DB.
- New tuning goal: Locking and Blocking Analysis with ten evidence sections and AI-generated, prioritized recommendations.
- Index Tuning now accepts an optional schema name or schema + table filter.
- Code Review now accepts an optional schema name or schema + object filter.
- Quality and reliability fixes across Index Tuning collection, About dialog rendering, in-app documentation loading, and goal-aware default file names.
SQL Server Locking and Blocking Analysis – now AI-driven
The new Locking and Blocking Analysis goal is built for the conversation that starts with “the database is slow right now” and ends with “why.” Instead of jumping between sp_who2, blocked process reports, system_health, and Query Store, you point AI SQL Tuner Studio at a user database and get one report that correlates ten data sources into a single ranked recommendation list.
Ten correlated evidence sections
Each section is collected with non-blocking queries and gracefully skips features that are unavailable, with an enablement advisory included in the report when something like Query Store or the system_health Extended Events session is off.
| Section | Impact | What it surfaces |
|---|---|---|
| Blocking Chains | High | Lead blockers, waiters, lock modes, and bidirectional cycles |
| Lock Inventory | High | Resource-level locks for the target database plus tempdb, summarized to keep payload small |
| Blocker Query Plans | High | SQL text and plan XML for every blocking and blocked session |
| Index Usage Statistics | High | Top 50 indexes by seek/scan/lookup activity, with high-scan / low-seek hotspots |
| Missing Index Candidates | High | Top 20 from sys.dm_db_missing_index_details by impact score |
| Deadlock History | High | Up to 10 recent deadlock XML events from system_health (file target on-prem, ring buffer on Azure SQL MI) |
| Query Store Top Queries | Medium | Top 20 by total CPU time, with enablement advisory if Query Store is off |
| Database Settings | Medium | RCSI, snapshot isolation, auto-update statistics, per-table lock-escalation |
| TempDB Contention | Medium | Pagelatch waits, version store and free space usage, top spilling queries |
| Long-Running Transactions | High | Open transactions, age, waiter batch context |
What the report tells you
Every recommendation comes with confidence level, supporting evidence references, an estimated reduction in blocking, and a rollback plan. From a real run against a TPC-H 10 GB workload (sample report linked below):
Most important blocker: session 62 running trigger statement
MERGE dbo.CustomerOrderStats..., because it blocks two UPDATE sessions and is part of a bidirectional blocking cycle with session 69.Top priority: redesign the synchronous trigger so it does not rescan
dbo.ordersinside the user transaction. Estimated reduction in blocking: 55% to 75%. Confidence: High.
The report also generates ready-to-test code rewrites – for example, replacing a single long-running UPDATE ... WHERE o_orderdate BETWEEN @FromDate AND @ToDate with a key-batched loop that commits every 5,000 rows, and refactoring an in-trigger MERGE into a narrow staging table processed after commit. That is the kind of guidance most blocking dashboards stop short of.
Safety guardrails
The Locking and Blocking goal requires a user database. Both the UI and core validation reject master, tempdb, model, and msdb, so you cannot accidentally aim it at a system database. All collectors are non-blocking, payloads are summarized to keep the AI request size reasonable, and per-section character counts are logged for diagnostics.
Index Tuning: scope to a schema or a specific table
Index Tuning previously analyzed the whole database. In 1.0.37 you can narrow it.
- Schema only – object-scoped sections (column cardinality, constraints, page density, index usage) only consider objects in that schema. Plan cache, Query Store, forced plan failures, automatic tuning state, and database-wide statistics remain database-scoped because they are not object-bound.
- Schema + table – object-scoped sections collect data only for that table, including Index Page Density. The selected table is included in column cardinality even if it would not have ranked in the default candidate set.
Practical effect: when an analysis on a 6,000-table database used to take longer than you wanted, you can now point it at dbo.Votes and get a focused report.
SQLStorm.dbo.Votes. The top recommendation is a filtered nonclustered index on (PostId, VoteTypeId) filtered to VoteTypeId IN (2,3), with 89% confidence based on Query Store workload patterns.Code Review: scope to a schema or a specific object
The Code Review goal now mirrors Index Tuning’s scoping. You can review every module in a schema, or zero in on a single procedure, function, view, or trigger by passing both schema and object name. Useful before a release: review only the procedures the release touches.
Stability and quality fixes
Smaller fixes worth calling out:
- The default HTML save file name reflects the goal that produced the current output, not the goal currently selected.
- Optional schema/table scope fields are persisted in app settings.
How to get 1.0.37
The signed package artifact is AppPackages\AI SQL Tuner Studio_1.0.37.0_Test\AI SQL Tuner Studio_1.0.37.0_x64.msixbundle. Install or upgrade through the standard download flow on aisqltuner.com; existing settings carry over.
Frequently asked questions
What is SQL Server locking and blocking analysis?
It is the process of identifying which sessions are holding locks, which sessions are waiting on those locks, what queries they are running, and which root cause – hotspot index, long-running transaction, missing index, lock escalation, isolation level, or TempDB pressure – is producing the blocking. AI SQL Tuner Studio automates that correlation across blocking chains, lock inventory, plans, Query Store, deadlock history, database settings, and TempDB.
Does the new goal work on Azure SQL Database and Managed Instance?
Yes. On Azure SQL Managed Instance, deadlock history is read from the system_health ring buffer instead of the file target. Sections that depend on optional features include an enablement advisory in the report when those features are off.
Can I run Locking and Blocking Analysis against master or tempdb?
No. Both the UI and the core validation reject master, tempdb, model, and msdb. The goal requires a user database.
How do I limit Index Tuning to one table?
In the Index Tuning UI, fill in both the schema and table scope fields. Object-scoped sections will collect only for that table; database-wide telemetry like Query Store and automatic tuning state is preserved unchanged.
What AI model powers the recommendations?
Recommendations are generated using Open AI GPT-5.4, Anthropic Claude Sonnet 4.6, or Opus 4.6 or 4.7. Opus models are only available for Corporate Edition. The collected SQL Server evidence is sent to the model with a structured system prompt, and the model returns a prioritized HTML report with confidence levels, evidence citations, and rollback plans.
Learn more
For more information see the user guide that ships with the application under Help > Documentation.
AI SQL Tuner Studio finds slow queries, missing indexes, deadlocks, and code issues on SQL Server, Azure SQL Database, SQL MI and Fabric SQL DB in minutes. Try it.
