sp_BlitzCache Can Talk to ChatGPT Now. Here's How.

Last week, Microsoft's AI chief, Mustafa Suleyman, told Fortune that 'Most tasks that involve "sitting down at a computer" will be fully automated by AI within the next year or 18 months'. He also said "It is going to be possible to design an AI that suits your requirements for every institution, organization, and person on the planet."

I read that on Friday. Yesterday I installed the latest First Responder Kit on my SQL Server 2025 instance, and I spent today trying to get sp_BlitzCache to talk to ChatGPT.

What Brent Shipped

The newest First Responder Kit (version 8.29) added AI integration to sp_BlitzCache. The procedure that has been helping DBAs find expensive queries for over a decade can now send those queries to an AI model and get tuning advice back — all from inside the engine, using sp_invoke_external_rest_endpoint. Here I am only testing with the top 1 query plan to keep things light:

-- this one calls ChatGPT (or Gemini) for you with the query plan in question
-- scroll to 'AI Advice' to see how ChatGPT assesed your query plan
EXEC sp_BlitzCache @Top = 1, @AI = 1

-- this one only generates the call prompt for you
-- scroll to 'AI Prompt' to see what you can ask your AI about the pain points
EXEC sp_BlitzCache @Top = 1, @AI = 2

In short, when @AI = 1, sp_BlitzCache loops through every expensive query it finds and builds an AI prompt for each one. Just like before, sp_BlitzCache outputs the query text, the execution plan XML, and a full set of performance metrics: CPU, duration, reads, writes, memory grants, spills, execution count, and any warnings the procedure already detected. What's new is that all of this gets packed into a JSON payload and sent to the AI endpoint via sp_invoke_external_rest_endpoint, where the AI's response comes back to you in the ai_advice column in the result set. No more cut/paste/edit/askChatGPT. The sp_BlitzCache call is doing the legwork for you.

When @AI = 2, the same expensive query work is done, but it only generates the prompt without calling the AI. This lets you review what would be sent first, or you can even paste it into another AI tool.

Very thoughtful, practical engineering -- regardless of where you stand with AI.

The Setup

Brent's full walkthrough covers this in detail. Here is the short version of what I had to do:

-- Enable outbound REST calls (disabled by default):
EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Credentials must live in a user database, not master.
-- The credential name must be the provider's root URL exactly.
USE DBA;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeStrongPassword!123';
GO
CREATE DATABASE SCOPED CREDENTIAL [https://api.openai.com/]
WITH IDENTITY = 'HTTPEndpointHeaders',
SECRET = '{"Authorization":"Bearer YourChatGPTAPIKeyGoesHere"}';
GO

One note on that credential: the CREATE ran without error, but my first sp_BlitzCache @AI = 1 call came back instantly - also without error - but this was in the ai_advice column:

API Error: Incorrect API key provided: sk-proj-***...***8pgA. You can find your API key at https://platform.openai.com/account/api-keys.

The SECRET value needs to be exactly {"Authorization":"Bearer <key>"} — there is a space after 'Bearer' and no extra whitespace around the key itself. Once I recreated the credential with the correct API Key string, the sp_BlitzCache @AI = 1 call took more time to complete and provided this insight in the AI Advice column:

About That Prediction

Mr. Suleyman said it will be possible to design an AI for every institution, organization and person on the planet. But every call sp_BlitzCache makes to the AI is stateless — a fresh conversation with no memory of anything before it. That is not a blitzCache flaw. That is AI. The model cannot know the query ran poorly last Tuesday, that you added an index on Wednesday, or that the column it wants you to index is on its way out. It can get the plan and the metrics - which is great! - but there is no way it can access that knowledge the DBA has to support it.

A DBA carries years of context that no prompt can contain — why the schema is what it is, which index exists because of an outage 3 years ago, or which stored procedure cannot be rewritten for political reasons. AI sees the query. The DBA knows the story.

What I Actually Think

Brent built something genuinely useful here. sp_BlitzCache calling ChatGPT gave me tuning suggestions I would have arrived at eventually — but much faster, and with a couple of angles I hadn't even considered. That is real value.

But getting there required configuration, credentials, network paths, security review, and a clear understanding of what the AI can and cannot see. That work is not going away. If anything, features like this create more of it — and it is work that requires a human who knows the environment.

The robots are not taking the job. They are adding to it.

Next up, I want to test the custom system prompts Brent outlined — code review, index-only tuning, blocking analysis. If you've already set this up in your environment, I'd like to hear what you found. Please let me know.

More to Read:

Brent Ozar: Get ChatGPT's Advice On Your Queries with sp_BlitzCache
Microsoft Learn: sp_invoke_external_rest_endpoint (Transact-SQL)
Fortune: Microsoft AI chief gives it 18 months — for all white-collar work to be automated by AI

Microsoft's AI Chief Says Your Job Ends in 18 Months. SQL Server 2025 Disagrees

This week, Microsoft AI CEO Mustafa Suleyman told the Financial Times that most tasks that involve “sitting down at a computer” will be fully automated by AI within the next year or 18 months.   As “compute” advances, he said, models will be able to code better than most human coders.

This is the same Microsoft that, three months ago, shipped SQL Server 2025 — the most feature-dense release in a decade. A release that added an entirely new category of things that DBAs, developers, and data engineers need to learn, manage, secure, and troubleshoot.

Same company. I wonder if the left hand knows what the right hand is doing.

Let's Take Him at His Word

Suleyman says AI will automate the work of anyone sitting at a computer. SQL Server 2025 just shipped the following — every one of which requires a human being sitting at a computer:

Native vector data type and DiskANN indexing. Your developers are going to start storing embeddings in SQL Server. Someone needs to size the indexes, figure out why VECTOR_SEARCH is slow, and explain to management why the new 'AI column' doubled the storage footprint. That someone is you.

CREATE EXTERNAL MODEL. You can now define and call AI models from inside the engine using T-SQL. Which means someone has to govern which models are callable, manage the creds, handle endpoint failures, and explain to the security team why SQL Server is making outbound HTTPS calls to Azure OpenAI. That someone is also you.

AI_GENERATE_EMBEDDINGS and AI_GENERATE_CHUNKS. Building RAG patterns inside the database engine. This is compute-intensive work running on your SQL Server — the same SQL Server hosting your OLTP workloads. Who is configuring Resource Governor to keep it from stomping production?  Still you.

Change Event Streaming. Real-time data streaming from SQL Server to Azure Event Hubs, built on the transaction log. When the log blows up because nobody tuned the throughput settings, the 3 AM page goes to a human.

sp_invoke_external_rest_endpoint. The database engine can now call any HTTPS endpoint. Directly. From T-SQL. This one deserves a closer look.

Your SQL Server Is Calling the Internet Now

This is the single most significant new attack surface, integration point, and troubleshooting headache that shipped in SQL Server 2025. It is also, genuinely, the most powerful.

The feature is disabled by default. Here is how to turn it on:

EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

And you will need to grant the permission explicitly:

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [YourAppLogin];

Here is what a basic call looks like. This hits a public API and returns the result as JSON:

DECLARE @ret INT;
DECLARE @response NVARCHAR(MAX);

EXEC @ret = sp_invoke_external_rest_endpoint
    @url     = N'https://httpbin.org/get',
    @method  = 'GET',
    @response = @response OUTPUT;

SELECT 
    @ret AS return_code,
    JSON_VALUE(@response, '$.result.origin') AS caller_ip;

That works. It is clean. It is powerful. And it just made your SQL Server a REST client.

Now here is what happens when the endpoint is slow:

-- Timeout defaults to 30 seconds.
-- Your transaction is waiting on an external service.
-- Your locks are held.
-- Your users are waiting.
-- Nobody told the DBA this was in the stored procedure.

EXEC @ret = sp_invoke_external_rest_endpoint
    @url     = N'https://some-slow-vendor-api.com/v1/data',
    @method  = 'POST',
    @payload = @requestBody,
    @timeout = 120,  -- two minutes, holding locks
    @response = @response OUTPUT;

When that vendor API goes down at 2 PM on a Tuesday and every transaction in the application starts stacking up behind it — that is not an AI problem. That is a DBA problem. You will be the one tracing it in sys.dm_exec_requests, finding the blocking chain, and explaining to the application team why calling a third-party API inside a transaction may not be the best idea.

This feature also requires HTTPS with a certificate trusted by the host OS, uses DATABASE SCOPED CREDENTIAL for authentication, and needs its own security review for every endpoint your developers want to call. That is governance work. Human governance work.

The Math

Suleyman says 18 months until the work is gone. His own product team just shipped five major feature categories — each one creating new DBA work that didn't exist six months ago. New data types to understand. New indexes to tune. New external dependencies to monitor. New security surface area to lock down. New resource consumption to govern.

The robots are not taking the jobs. They are becoming the job.

More to Read:

Fortune: Microsoft AI chief gives it 18 months — for all white-collar work to be automated by AI
Microsoft Learn: sp_invoke_external_rest_endpoint (Transact-SQL)
Microsoft Learn: What's New in SQL Server 2025
Microsoft Learn: Vector Search and Vector Index in SQL Server
Microsoft: SQL Server 2025 is Now Generally Available

Microsoft Is Killing NTLM. Here's What That Means for SQL Server.

Every blog post about Microsoft killing NTLM is written for Windows admins and security teams. Why aren't we talking about what this means for SQL Server?

There is a good chance this message has been sitting in your SQL Server error log for years:

The SQL Server Network Interface library could not register the 
Service Principal Name (SPN) [ MSSQLSvc/YOURSERVER.domain.com:1433 ] 
for the SQL Server service. Windows return code: 0xffffffff, state: 63. 
Failure to register a SPN might cause integrated authentication to use 
NTLM instead of Kerberos. This is an informational message. Further 
action is only required if Kerberos authentication is required by 
authentication policies and if the SPN has not been manually registered.

'Informational message' and 'Further action is only required if Kerberos authentication is required'... For years, we could ignore this, but not anymore. Microsoft published a three-phase roadmap on January 28, 2026 to disable NTLM by default in upcoming Windows releases -- and phase one is already live. That 'informational message' is about to become an action-item.

In most environments I audit, somewhere between 40 and 60 percent of Windows Authentication connections to SQL Server are running on NTLM -- and nobody knew until we looked. If you have not checked yours, now is the time.

What Is Happening

NTLM (New Technology LAN Manager) has been around since 1993. Kerberos replaced it as the preferred Windows authentication protocol over 20 years ago. But NTLM stuck around as the silent fallback -- the thing Windows quietly uses when Kerberos didn't work. Microsoft formally deprecated it in June 2024. Now they are moving to disable it.

Based on what Microsoft has announced so far, here is where things stand:

Phase	When	What Happens
1	Now	Enhanced NTLM auditing in Server 2025 and Win 11 24H2
NTLMv1 enforce	October 2026	BlockNTLMv1SSO default flips from Audit to Enforce
2	H2 2026	IAKerb, Local KDC ship; core Windows components prefer Kerberos
3	Next major Server release (no date announced)	Network NTLM disabled by default

Important note: The October 2026 date applies to NTLMv1 only. Most SQL Server environments are already using NTLMv2, so that specific flip may not break anything for you immediately. The bigger change is Phase 3, where network NTLM (including v2) gets disabled by default. Microsoft has not announced a firm date for Phase 3, but it will coincide with the next major Windows Server release, which could be 2027 or later.

That said, the change is happening and this is not a fire drill. Now's the time to figure out where things stand with your SQL Servers.

Why Is This a SQL Server Problem?

SQL Server does not control which authentication protocol is used. Windows does. When a client connects using Windows Authentication over TCP/IP, the SSPI layer tries Kerberos first. If Kerberos can't work -- missing SPN, broken delegation, no domain controller visibility -- it falls back to NTLM. Silently. No error. No warning. Your connections work fine, and you never know the difference.

Until NTLM gets turned off. Then those connections stop working -- less silently.

Here are the scenarios where SQL Server silently falls back to NTLM:

Missing or misconfigured SPNs

If the Service Principal Name for your SQL instance is not registered in Active Directory -- or is registered against the wrong account -- Kerberos fails and NTLM takes over. This is the most common cause. Named instances with dynamic ports are especially prone to this.

Named Pipes connections

Named Pipes forces NTLM. Always. There is no Kerberos path for Named Pipes connections to SQL Server.

Connecting by IP address instead of hostname

If your connection strings or application configs use an IP address instead of a fully qualified domain name, Kerberos cannot resolve the SPN. NTLM takes over.

Linked servers with Windows Authentication

The classic double-hop problem. My favorite. 🙄 If Kerberos delegation is not configured for your service account, linked server queries using Windows Authentication will fail with 'Login failed for user NT AUTHORITY\ANONYMOUS LOGON'.

Local connections

Connections from the SQL Server host to itself (ie., SSMS on the server, local jobs, local apps) always use NTLM due to a per-service SID hardening feature added in Windows 2008. This is by design and won't change.

SSRS, SSIS, SSAS

These services have their own authentication paths and their own SPN requirements. If they are not configured for Kerberos, they are using NTLM.

Legacy drivers

The old OLE DB provider (SQLOLEDB) and older ODBC drivers may force NTLM in certain configurations, particularly with Named Pipes. If you have not updated to MSOLEDBSQL or ODBC Driver 17/18, it's worth checking.

Find Out What You Are Running Right Now

Run this from a client machine (not from the server itself -- local connections always show NTLM):

SELECT 
    s.login_name,
    s.host_name,
    c.auth_scheme,
    c.net_transport,
    c.encrypt_option,
    s.program_name,
    c.connect_time
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s
  ON c.session_id = s.session_id
WHERE s.is_user_process = 1
AND c.net_transport <> 'Shared memory'
ORDER BY c.auth_scheme, s.login_name;

If your auth_scheme column shows NTLM for remote connections, those are the connections you need to investigate. Run this to see how big the problem is:

SELECT 
    c.auth_scheme,
    COUNT(*) AS connection_count
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s
  ON c.session_id = s.session_id
WHERE s.is_user_process = 1
AND c.net_transport <> 'Shared memory'
GROUP BY c.auth_scheme
ORDER BY connection_count DESC;

If you are running Windows Server 2025 or Windows 11 24H2, you can also check the new NTLM audit logs:

-- Run this on the Windows host via PowerShell (not T-SQL)
Get-WinEvent -LogName "Microsoft-Windows-NTLM/Operational" | Format-List

Event IDs 4020 and 4021 log client-side NTLM attempts. Event IDs 4022 and 4023 log server-side NTLM authentication. Event ID 4024 logs NTLMv1-derived credential usage specifically.

What To Do About It

1. Audit your connections. Run the DMV queries above across your environment. Get a picture of how many connections are using NTLM vs Kerberos. If you see NTLM on remote connections, you have work to do.

2. Fix your SPNs. This solves the majority of NTLM fallback in SQL Server. Verify SPNs are registered correctly for every instance -- default, named, clustered, and AG listeners. Use setspn -L domain\service_account to list what is registered. Use setspn -S to add missing ones. Microsoft's Kerberos Configuration Manager can help identify gaps.

3. Fix dynamic ports. Named instances with dynamic ports are an SPN headache. Consider switching to static ports, or ensure the service account has permission to register and unregister SPNs on startup.

4. Fix your connection strings. If anything is connecting by IP address, change it to the FQDN. This one change can flip a connection from NTLM to Kerberos without touching Active Directory.

5. Stop using Named Pipes for remote connections (if you can). TCP/IP with a valid SPN gives you Kerberos. Named Pipes never will.

6. Update your drivers. SQLOLEDB is deprecated and does not support TLS 1.3 or modern authentication. You should consider replacing it with MSOLEDBSQL and updating your ODBC drivers to version 17 or 18. While older drivers don't force NTLM on their own over TCP, they are end-of-life and don't support TLS 1.3, which Windows Server 2025 now enables by default. Updating your drivers now would address multiple security changes at once.

7. Test with NTLM off. When you are ready, Microsoft recommends testing NTLM-off configurations in non-production environments. The Group Policy setting Network security: Restrict NTLM: Outgoing NTLM traffic to remote servers lets you block outbound NTLM traffic systematically. Start in audit mode, review the results, and work through the exceptions before enforcing.

Microsoft is giving everyone time to prepare -- use it. The phase-out is structured so you can audit now, remediate at your own pace, and be ready well before NTLM gets disabled by default. The work itself is straightforward, but it takes time to audit, test, and remediate across an entire environment -- especially if you have dozens of instances, linked servers, and legacy applications connecting via Windows Auth.

If you are not sure where your environment stands, or you don't have the bandwidth to audit and remediate this across your SQL Server estate, we can help. Better to know now than to find out later.

More to Read:

Microsoft: Advancing Windows Security -- Disabling NTLM by Default
Microsoft Support: Upcoming Changes to NTLMv1 in Windows 11 24H2 and Server 2025
Microsoft Learn: Determine the Authentication Type for SQL Server Connections
Microsoft Learn: Using Kerberos Configuration Manager for SQL Server
Microsoft: Understanding Kerberos and NTLM Authentication in SQL Server Connections

Fabric in Practice: SQL Server Near Real-Time Reporting

In Part 1 we covered what Fabric is. In Part 2 we walked through how it's structured (OneLake, Lakehouses, Warehouses), and in Part 3 we looked at how data gets ingested into Fabric.   Now let's put it to work.

THE SCENARIO

You have a SQL Server running transactional workloads. It does its job well enough, but now leadership wants a dashboard that reflects current state -- aka, what's happening right now.

You already know the options. Run heavy reporting queries against production. eewgh. Or stand up a reporting replica, build ETL to keep it current, maintain a refresh schedule, and hope nothing breaks on a holiday weekend. It works, but it's expensive and has an awful lot of moving pieces.

Fabric gives you a third path: continuously replicate your SQL Server data into OneLake using Fabric Mirroring, and let Power BI read it using Direct Lake mode. Your SQL Server stays focused on OLTP and your reporting runs against a near real-time copy in Fabric. No pipelines. No refresh schedules. Nice.

Important note on 'Mirroring'

SQL Server Database Mirroring <> Fabric Mirroring

Database Mirroring was deprecated in 2012. Fabric Mirroring is a completely different technology.  In Microsoft's words: "Mirroring in Fabric is a low-cost and low-latency solution to bring data from various systems together into a single analytics platform."

HOW IT WORKS

Three components make this scenario work:

1. Fabric Mirroring (SQL Server → OneLake)

Fabric Mirroring continuously replicates selected tables from your SQL Server into OneLake as Delta tables. An initial snapshot is taken, and from that point forward, changes are captured and replicated near real-time.

SQL Server	Mirroring Type	Change Mechanism	Key Requirements
2016–2022	Database	CDC	On-prem gateway; sysadmin for CDC setup
2025 (on-prem)	Database	Change Feed	Azure Arc + Extension

Fabric also supports Metadata mirroring (shortcuts, no data movement) and Open mirroring (API-driven, build your own) for other platforms. See Fabric Mirroring overview.

Both mechanisms replicate changes continuously. Under active load, changes can publish as frequently as every 15 seconds, with backoff logic during low activity (source). Once configured, Fabric creates a mirrored database in your workspace with an autogenerated SQL analytics endpoint — a read-only T-SQL interface you can query with SSMS, VS Code, or anything that speaks T-SQL.

2. Direct Lake Mode (OneLake → Power BI)

Direct Lake is a Power BI storage mode that reads Delta tables directly from OneLake. No data import into a semantic model. No scheduled refresh. No DirectQuery hitting your source. Because the mirrored data is already in Delta format, Direct Lake picks it up natively.

The result: your Power BI reports reflect changes as they land in OneLake. That's it. No upload or refresh required. MSSQLTips has a solid walkthrough of how Direct Lake works under the covers.

3. The End-to-End Flow

SQL Server (OLTP) → Fabric Mirroring (CDC or Change Feed) → OneLake (Delta tables) → Direct Lake (Power BI)

One workload. Three Fabric components -- and your SQL Server doesn't carry the reporting load.

SETTING IT UP (HIGH LEVEL)

The full setup is portal-driven, not T-SQL heavy. Microsoft's step-by-step tutorial covers every detail, but here is what you're walking into:

On the SQL Server side:

For SQL Server 2016–2022: install an on-premises data gateway (or VNet gateway). Create a dedicated login for Fabric with appropriate permissions. If CDC is not already enabled on your source tables, the Fabric setup process will configure it, and the login needs temporary sysadmin to do so.

-- Example: create the Fabric login (SQL Server 2016-2022)
-- Per Microsoft's tutorial
CREATE LOGIN fabric_login WITH PASSWORD = '<strong password>';

-- Grant sysadmin temporarily if CDC is not already enabled
ALTER SERVER ROLE sysadmin ADD MEMBER fabric_login;

-- After mirroring is configured and CDC is enabled,
-- remove sysadmin and grant only what's needed:
ALTER SERVER ROLE sysadmin DROP MEMBER fabric_login;

For SQL Server 2025: connect your instance to Azure Arc and install the Azure Extension for SQL Server. The extension provisions a managed identity that handles authentication to Fabric. No CDC required — SQL Server 2025 uses Change Feed natively. In fact, you cannot use Fabric Mirroring on a SQL Server 2025 database that already has CDC enabled.

In the Fabric portal:

Create a mirrored database item in your workspace, select your SQL Server connection, and choose the tables to replicate (up to 500). Fabric takes the initial snapshot and begins continuous replication.

For Power BI:

From the mirrored database's SQL analytics endpoint, create a new semantic model. The model defaults to Direct Lake mode. Build your report on top of it, and data flows through automatically.

See full details here in Explore data in your mirrored database and Build Power BI Reports with Direct Lake Tables.

WHAT TO KNOW BEFORE YOU COMMIT

CDC adds overhead to your production system.

For SQL Server 2016–2022, Fabric Mirroring requires CDC on the tables you replicate. CDC captures changes from the transaction log — it adds I/O and CPU overhead. Active transactions hold log truncation until the mirrored database catches up (source). Test this under realistic production load before you go live.

Change Feed (SQL Server 2025) is lighter, but not zero.

SQL Server 2025 scans the transaction log at high frequency and publishes changes to OneLake. Microsoft's language is 'least amount of resource tax on the source database' — not none.

'Near real-time' varies.

Replication latency depends on transaction volume, table size, and network throughput.   Measure latency under load, during peak hours. That number is your 'near real-time.'

ONE WORKLOAD, ONE SCENARIO

This is not a post about migrating to Fabric. It's one sample scenario where Fabric solves a real problem: getting near real-time analytics from SQL Server without building and maintaining a separate reporting infrastructure.

If you are running SQL Server OLTP with a growing demand for real-time reporting, this is worth evaluating. Start with one workload. Measure the results. Expand from there.

COMING NEXT

In Part 5, we'll look at SQL Database in Microsoft Fabric — an actual transactional database running inside Fabric. What it is, what it isn't, and where it might actually make sense.

More to Read

Fabric Mirroring overview
Mirrored databases from SQL Server
Tutorial: Configure Fabric Mirroring from SQL Server (step-by-step)
Fabric Mirroring limitations for SQL Server

Azure Data Studio Dies February 28. Here's What You Lose.

Azure Data Studio retires on February 28, 2026. No more updates, no more security patches, no more support. Microsoft announced this a year ago and has been pointing everyone toward VS Code with the MSSQL extension ever since.

Sounds straightforward. Install VS Code, add an extension, and carry on. Yet it is not really that simple, and it depends a lot on how you used ADS.

Who Uses ADS

Microsoft built ADS for a specific audience: data professionals who spend most of their time editing and executing queries rather than doing deep server administration. It runs on Windows, macOS, and Linux — which made it the only Microsoft-built SQL tool for non-Windows users. SSMS has never run on anything but Windows.

In practice, ADS carved out a few loyal audiences: Mac and Linux developers who needed a native SQL tool, db project users wanting source control integration without full VS, notebook users who built runbooks and documentation in SQL + Markdown, and even people who just wanted something lighter than SSMS for day-to-day query work.

What Is Actually Happening

Microsoft is consolidating. ADS was always built on the VS Code codebase, so maintaining both products meant duplicating effort. Rather than continue that, they are putting everything into VS Code and its MSSQL extension, and letting ADS go.

SSMS is not going anywhere. If you are an SSMS-only DBA who never touched ADS, this changes almost nothing for you. Keep reading anyway — the MSSQL extension is worth knowing about.

If you relied on ADS for cross-platform work, notebooks, database projects, or because you preferred the lighter interface — this is your migration notice.

Where Everything Goes

ADS Feature	Replacement	Status
Query Editor	VS Code + MSSQL extension	Ready
Object Explorer	VS Code + MSSQL extension	Ready
Database Projects	VS Code + MSSQL extension	Ready — opens without migration
Schema Compare	VS Code + MSSQL extension	GA August 2025
Connections Import	VS Code + MSSQL extension	New — added Jan 28, 2026
SQL Server Agent	SSMS	Ready
SQL Profiler	SSMS	Ready
Notebooks	Polyglot Notebooks (VS Code)	Different extension, not 1:1
Flat-File Import	Bulk Insert / PowerShell	In development for MSSQL
DACPAC Import/Export	SqlPackage CLI	Ready

Most of this is in good shape right now, but a year ago not so much. Microsoft has been filling them — Schema Compare landed in preview, the Migration Toolkit for importing connections shipped on January 28th, and Table Explorer/Edit Data is now enabled by default. Credit where it is due: they have really been working this one.

Still, the migration is not seamless.

What You Lose

SQL Server Agent and Profiler are SSMS-only. If you managed Agent jobs or ran traces from ADS, those features have no VS Code equivalent. SSMS is the answer, which means Windows-only for those tasks. Mac and Linux users who leaned on ADS for lightweight Agent management are out of luck.

Notebooks are not a clean swap. ADS notebooks migrate to Polyglot Notebooks in VS Code, but it's a different extension with a different runtime. If you had SQL + Markdown notebooks that you used for runbooks or incident response documentation, test them before February 28. Do not assume they'll just work.

Flat-file import is gone (for now). ADS had a nice wizard for importing CSVs and text files directly into SQL tables. The MSSQL extension does not have that yet. Your options are bulk insert, PowerShell - and patience.

The Migration Toolkit is brand new. The ADS connection import feature shipped on January 28, 2026 — exactly one month before retirement. Better late than never, but it has had approximately twelve days of real-world use. If you have dozens of saved connections, you'll want to test the import early and verify.

What You Gain

It is not all bad news.

VS Code is faster, lighter, and has a massive extension ecosystem. The MSSQL extension has improved significantly with GitHub Copilot integration, connection groups, local SQL Server containers, and a modern query results pane that is genuinely better than what ADS offered.   Your database projects open in VS Code without any migration steps. Your .sql scripts work as-is, and you are back on a platform that Microsoft is still actively investing in.

Who Cares and Who Does Not

If You Are...	Impact
SSMS-only DBA	Minimal. You were never in ADS to begin with.
ADS on Mac/Linux	Significant. Agent and Profiler now require a Windows box.
Database Projects user	Smooth. Projects open directly in VS Code.
Notebook user	Bumpy. Test your notebooks before the deadline.
PostgreSQL/Cosmos user	Straightforward. VS Code extensions exist for both.

What To Do Before February 28

1. Install VS Code + MSSQL extension. Download VS Code, then install the MSSQL extension from the marketplace. Takes two minutes.

2. Import your connections. The MSSQL extension now includes the Azure Data Studio Migration Toolkit. Use it to pull in your saved connections and connection groups. Then verify they actually work.

3. Open your database projects. They should just open. If they do not, that is a problem you want to discover now, not on March 1st.

4. Test your notebooks. Install Polyglot Notebooks in VS Code, open your existing notebooks, and run them. Fix what breaks while you still have ADS as a fallback.

5. Accept that Agent and Profiler live in SSMS. If you need them, SSMS 22 is GA and fully supports SQL Server 2025. It is not going anywhere.

The Bottom Line

ADS was always a bit of an identity crisis — too lightweight for serious administration, too heavy for 'just a query editor,' and perpetually compared to SSMS without ever reaching parity. Microsoft tried. The community mostly shrugged. And now it is being folded back into the tool it was forked from in the first place.

The migration is doable. Most of it is painless. The edges that aren't are well-defined, and we know exactly where the replacements are for what we are losing. That is more than we get with most Microsoft deprecations.

Do not wait until February 27th.

More to Read

Microsoft: What's Happening to Azure Data Studio?
VS Code Marketplace: MSSQL Extension
MSSQL Extension Release Notes (includes Migration Toolkit)
Microsoft DevBlog: Azure Data Studio Retirement

10 T-SQL One-Liners You'll Use Every Day

Every DBA has a mental toolbox of go-to queries. Some took years to learn. Some were stumbled upon by chance while working a 2am outage. Today I am sharing 10 of my favorite T-SQL one-liners — the kind of stuff you copy, paste, and immediately feel like a genius. Some are classics, some are new additions -- All of them are useful.

Bookmark this one. I hope you will come back to it.

1. Generate a Number Sequence on the Fly

Need a quick sequence of numbers without creating a tally table? If you are on SQL Server 2022 or later, GENERATE_SERIES is your new best friend:

SELECT value FROM GENERATE_SERIES(1, 100);

That is it. One line. 100 rows. No temp tables, no CTEs, no cross joins. Similar approach for a date range. Use this to return every day of 2025 in a single line:

SELECT DATEADD(DAY, value, '2025-01-01') AS dt FROM GENERATE_SERIES(0, 364);

Version: SQL Server 2022+ (compatibility level 160+)

2. Comma-Separated List from Rows

The old-school FOR XML PATH / STUFF approach was painful. STRING_AGG changed the game. Use this to list every database on your instance, in one comma-separated string:

SELECT STRING_AGG(name, ', ') AS all_databases FROM sys.databases;

Need them ordered alphabetically? Add WITHIN GROUP:

SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS all_databases FROM sys.databases;

If you are still writing STUFF(... FOR XML PATH('')...) in 2022+, it's time to let go.

Version: SQL Server 2017+

3. Running Total Without a Cursor

Cursors had a good run. Actually no, they didn't. Set-Based T-SQL and Windows functions ended their reign:

SELECT OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal FROM dbo.Sales;

Running totals, moving averages, cumulative counts — all without a single cursor or temp table. If you are still looping through rows to calculate a running total, this one line just saved you 40 lines of code.

Version: SQL Server 2012+

4. Delete Duplicates, Keep One

You have duplicates. You want to keep exactly one copy of each. Not one line, but pretty close:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS rn FROM dbo.Customers
)
DELETE FROM cte WHERE rn > 1;

Yes, you can DELETE directly from a CTE. That surprises a lot of people. The PARTITION BY defines what 'duplicate' means, and the ORDER BY decides which row survives. Clean, readable, no temp tables.

Version: SQL Server 2005+

5. Dynamic PIVOT Without Dynamic SQL

A lot of people think PIVOT requires dynamic SQL to handle unknown column values. Not true. If you know your categories, you can pivot in one line:

SELECT * FROM (SELECT MONTH(OrderDate) AS Month, Category, Amount FROM dbo.Sales) s
PIVOT (SUM(Amount) FOR Category IN ([Hardware],[Software],[Services])) p;

Rows become columns instantly. Monthly sales by category, all in one shot. The inner query sets up the data, PIVOT does the magic. No CASE WHEN statements, no multiple SUM() calls, no headaches.

Version: SQL Server 2005+

6. Generate Millions of Test Rows Instantly

Need a million rows of data to stress test? Stop writing loops or importing CSV files. Cross join the system tables with themselves:

SELECT TOP (1000000) 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
    'User' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR) AS UserName,
    LEFT(NEWID(), 8) + '@test.com' AS Email
FROM sys.all_objects a, sys.all_objects b, sys.all_objects c;

Cross joining system tables creates a Cartesian product. sys.all_objects usually has a few thousand rows, so three cross joins give you billions of combinations. SQL Server generates realistic test data faster than you can blink. Change the TOP number to whatever you need.

Version: All versions

7. Unpivot Columns to Rows Without UNPIVOT

The UNPIVOT operator works, but CROSS APPLY with VALUES is more flexible and honestly easier to read:

SELECT e.EmployeeId, v.Score, v.Category
FROM Employees e
CROSS APPLY (VALUES 
    (Q1Score, 'Q1'), (Q2Score, 'Q2'), (Q3Score, 'Q3'), (Q4Score, 'Q4')
) v(Score, Category);

Four columns become four rows per employee. Need to add Q5 someday? Just add another line in the VALUES. Try doing that with UNPIVOT without rewriting the whole thing and losing some hair.

Version: SQL Server 2008+

8. Conditional Aggregation in One Line

Stop writing multiple count queries in one call. Here you've got one pass through the table with three answers:

SELECT 
    COUNT(CASE WHEN Status = 'Active' THEN 1 END)   AS ActiveCount,
    COUNT(CASE WHEN Status = 'Inactive' THEN 1 END) AS InactiveCount,
    COUNT(CASE WHEN Status = 'Pending' THEN 1 END)  AS PendingCount
FROM Customers;

If you prefer something shorter, SQL Server 2012+ gives you IIF:

SELECT SUM(IIF(Status = 'Active', 1, 0)) AS ActiveCount FROM Customers;

Version: CASE — all versions. IIF — SQL Server 2012+

9. Get the Nth Highest (or Lowest) Value

Interview question classic. What is the 3rd highest salary?

SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

The key is DISTINCT - without it, you get whoever happens to be in the 3rd row, not the 3rd highest unique value. Change the 2 to N-1 for whatever position you need. OFFSET 0 gives you the highest, OFFSET 1 gives you the second highest, and so on.

Version: SQL Server 2012+

10. Find Every Table's Row Count — Instantly

If you are still running SELECT COUNT(*) FROM every table, stop. This reads metadata and returns in milliseconds, even on tables with billions of rows:

SELECT s.name AS SchemaName, t.name AS TableName, p.rows AS [RowCount]
FROM sys.tables t JOIN sys.schemas s 
  ON t.schema_id = s.schema_id JOIN sys.partitions p 
    ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
ORDER BY p.rows DESC;

Every table with row count, sorted top down. No waiting. This reads metadata, not the actual tables, so it's lightning fast even on tables with billions of rows. The count is exact for stable tables, but may lag during active transactions or immediately after bulk operations. For quick estimates and general monitoring, it's perfect. When you need the exact count during heavy activity, fall back to COUNT(*).

Version: All versions

Bonus: Check Disk Space on All Drives

I cannot count how many times I've run this. Hell. I even proceduralized it:

SELECT DISTINCT volume_mount_point, 
    total_bytes/1024/1024/1024 AS TotalGB, 
    available_bytes/1024/1024/1024 AS FreeGB 
FROM sys.master_files mf 
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id);

Shows total size and free space on every drive that has SQL Server files. No need to RDP to the server or dig through Windows - just paste this and know instantly if you're about to run out of space.

Version: SQL Server 2008+

In closing, none of these are earth-shattering, but they are the kind of queries you will come back to again and again. Try them out. And if your SQL Server needs more attention than a one-liner can fix, I can help.

More to Read:

GENERATE_SERIES (Transact-SQL) — Microsoft Learn
STRING_AGG (Transact-SQL) — Microsoft Learn
TSQL Tips and Tricks — MSSQLTips
OFFSET FETCH (Transact-SQL) — Microsoft Learn

SQL Server 2016 Ends in July. Here's What Will Break.

SQL Server 2016 goes end of life on July 14, 2026. That's five months from now.

If you're still running 2016 in production, you're not alone. Based on one vendor's assessment, SQL Server 2016 is still 20% of production deployments. That's a lot of DBAs who need to get moving pretty much now.

Here's the problem: Many of those upgrades are going to break. Not because the engine upgrade fails, but because SQL Server 2025 changed the defaults. Silently. In ways that won't show up until you're already committed.

The Deadline

After July 14, 2026:

• No more security patches
• No more support calls
• No longer compliant

You can buy Extended Security Updates (ESU) for up to three more years. But ESU only covers critical security patches — no bug fixes, no features, and the cost increases each year. It's a holding pattern, not a solution.

The real solution is to upgrade. And if you're planning on 2016 to 2025, you're about to learn that Microsoft quietly raised the security baseline while we weren't looking.

What Changed in 2025

SQL Server 2025 ships with MSOLEDBSQL 19, and its defaults are different from what you've been using. The new provider enforces:

• Encrypt = True by default
• TrustServerCertificate = False by default
• Strict certificate chain validation

Translation: if your environment uses self-signed certificates, internal CAs that aren't in the trust store, or no encryption at all — connections will fail. Unavoidably.

This affects linked servers, replication, log shipping, and any application still using legacy connection strings. The old SQL Native Client (SQLNCLI) used to suppress or ignore these errors. MSOLEDBSQL 19 does not.

Three Things That Will Break

1. Linked Servers

If you configured linked servers using SQLNCLI (which was the default for years), they'll fail after upgrade with errors like:

Msg 7303, Level 16, State 1
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" 
for linked server "LinkedServerName".

TCP Provider: The certificate chain was issued by an authority that is not trusted.

The fix is to either install proper certificates or reconfigure the linked server with TrustServerCertificate=yes — which defeats the security improvement but at least gets you running.

2. Replication

If your publisher is SQL Server 2025 and your distributor is remote without a trusted certificate, replication will fail. You'll see:

OLE DB provider "MSOLEDBSQL19" for linked server "repl_distributor" returned message 
"Client unable to establish connection".

Msg -2146893019, Level 16, State 1
SSL Provider: The certificate chain was issued by an authority that is not trusted.

This hits transactional, snapshot, peer-to-peer, and merge replication. Replication Monitor in SSMS will also fail if it can't validate the certificate chain.

The workaround (if you can't deploy trusted certificates yet):

EXEC sp_changedistributor_property 
    @property = N'trust_distributor_certificate', 
    @value = N'yes';

3. Full-Text Search

SQL Server 2025 introduces a new full-text index version. Existing catalogs stay on version 1 (unchanged since 2005) unless you manually upgrade them. After the engine upgrade, your full-text queries will fail:

Msg 30010, Level 16, State 2
An error has occurred during the full-text query. Common causes include: 
word-breaking errors or timeout, FDHOST permissions/ACL issues, 
service account missing privileges, malfunctioning IFilters...

The fix is to rebuild your full-text indexes — or if you need to keep using the old version temporarily:

ALTER DATABASE SCOPED CONFIGURATION SET FULLTEXT_INDEX_VERSION = 1;

But version 1 is deprecated. This is a temporary workaround, not a long-term solution.

And if you're using Database Mail, there's another bug that forced Microsoft to pull back a fix. Be sure to check Database Mail status before you upgrade.

What to Check Now

Before you upgrade anything, audit these:

Component	What to Check
Linked Servers	Provider (SQLNCLI vs MSOLEDBSQL), encryption settings, certificate trust
Replication	Remote distributor topology, certificate chain on all nodes
Log Shipping	Remote monitor server, same TLS requirements as linked servers
Full-Text Indexes	Current index version, rebuild time estimates
Connection Strings	Legacy providers, missing Encrypt/TrustServerCertificate params
SSIS Packages	Execute SQL Task / SMO tasks using Dts.Runtime API — update references, rebuild

Run this to find your linked server providers:

SELECT 
    s.name AS linked_server,
    s.provider,
    s.data_source
FROM sys.servers s
WHERE s.is_linked = 1;

Any row showing SQLNCLI or SQLNCLI11 needs attention before upgrade.

The Upgrade Path

SQL Server 2025 supports direct in-place upgrades from 2016. That doesn't mean it's easy — it means it's possible.

The safe approach:

1. Audit linked servers, replication, full-text, and SSIS before touching anything
2. Deploy certificates or workarounds for each component
3. Test in a non-production environment — not just the upgrade, but every downstream connection
4. Plan for full-text index rebuilds (this takes time on large catalogs)
5. Schedule the production upgrade with sufficient buffer time for surprises

If you haven't started yet, you need to get moving. Five months sounds like plenty of time until you recognize that your replication topology spans three datacenters and nobody knows where the certificates are.

The Bottom Line

The engine upgrade isn't the hard part. Discovering what breaks is.

Microsoft raised the security baseline in 2025. That's a good thing — in the long run. But if you're jumping from 2016, you're skipping several versions of gradual tightening and jumping right into the deep end.

Need help migrating before July? We specialize in SQL Server 2016→2025 upgrades with zero downtime.

More to Read:

sqlfingers: SQL Server 2025 Current State
Brent Ozar: Known Issues So Far in SQL Server 2025
MSSQLTips: SQL Server 2025 Upgrade Lessons Learned
endoflife.date: Microsoft SQL Server

Azure Says Yes. SQL Server Says No.

Azure IAM says you're a Contributor. SSMS says this:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'dm_exec_query_stats', 
database 'ContosoDB', schema 'sys'.

Welcome to cloud permissions, where 'Contributor' doesn't mean you can contribute and 'Reader' doesn't mean you can read.

In my last post, I explained the management plane vs data plane split. This post is the promised follow-up for the minimum permission combinations for common DBA tasks. aka, what you need, how to verify it, and how to fix it when it fails.

The Two-Layer Reality

Quick refresher. Azure has two separate permission systems:

Layer	Controls	Assigned Where
Management Plane (ARM)	Portal visibility, configuration, scaling	Azure IAM
Data Plane (SQL)	Queries, data access, DMVs	Inside the database

These two systems don't talk to each other. Azure doesn't tell SQL Server what you can do. SQL Server doesn't check Azure IAM. You need both configured — separately.

The Permission Matrix

Because you need permissions in both systems, here's what each task requires:

Task	Management Plane (ARM)	Data Plane (SQL)
See database in portal	Reader	—
Query tables	—	db_datareader
Modify data	—	db_datawriter
Query DMVs (performance)	—	VIEW DATABASE STATE
Query server-level DMVs	—	##MS_ServerStateReader##
Create/alter objects	—	db_ddladmin
Manage users in database	—	db_securityadmin
Create logins (server level)	—	##MS_LoginManager##
Scale database / change tier	SQL DB Contributor	—
Configure firewall rules	SQL Server Contributor	—
View metrics in portal	Monitoring Reader	—
Configure alerts	Monitoring Contributor	—
Access blob storage (manual exports)	Reader	Storage Blob Data Contributor

Portal tasks need ARM roles. Query tasks need SQL roles. Some tasks need both.

Note: This matrix applies to both Azure SQL Database and Azure SQL Managed Instance. MI supports a few additional features (ie., SQL Agent, db_backupoperator) but the core permission model is the same.

Permission Combinations by Role

Here's what common DBA roles actually need — both layers combined:

DBA Role	Management Plane (ARM)	Data Plane (SQL)
Monitor only	Reader, Monitoring Reader	VIEW DATABASE STATE
Troubleshoot + kill sessions	Reader, Monitoring Reader	VIEW DATABASE STATE, ALTER ANY CONNECTION
Operational DBA	Reader, Monitoring Reader	db_datareader, db_ddladmin, VIEW DATABASE STATE, ALTER ANY CONNECTION
Full control	SQL DB Contributor, Monitoring Contributor	db_owner

Start with the minimum. Add permissions as the job requires.

Verify Your Access

When your cloud team grants access and walks away, don't assume it's all good. Test all layers before you call it done.

Check	How to Verify
Can see resource in portal?	Navigate to Azure SQL in portal
Can connect via SSMS?	Test connection with your credentials
Can query user tables?	SELECT TOP 1 * FROM any_table
Can query DMVs?	SELECT * FROM sys.dm_exec_requests
Can see portal metrics?	Open Monitoring blade, check for data

Not sure what permissions you actually have? Run this in your Azure SQL Database:

-- What database permissions do I have?
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');

-- Am I in any server roles?
SELECT 
    r.name AS role_name,
    CASE WHEN IS_SRVROLEMEMBER(r.name) = 1 THEN 'Yes' ELSE 'No' END AS member
FROM sys.server_principals r
WHERE r.type = 'R' AND r.name LIKE '##MS_%##';

(The ##MS_ server roles are specific to Azure SQL Database.)

If any check fails, you know exactly which layer is missing.

When It Doesn't Work

Azure says connected. SQL Server says denied.

Symptom: Database appears in portal. SSMS connects. SELECT fails.

Cause: Missing data plane role.

Fix (Data Plane):

-- Run in the target database
ALTER ROLE db_datareader ADD MEMBER [your_user];

You can see tables. You can't see performance.

Symptom: sys.dm_exec_query_stats returns permission denied or empty results.

Cause: Missing VIEW DATABASE STATE (or VIEW SERVER STATE for server-level DMVs).

Fix (Data Plane):

-- Database level
GRANT VIEW DATABASE STATE TO [your_user]; 

-- Server level (for cross-database DMVs)
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [your_login];

NOTE:  Reminder, role-based perms are smarter.  
       Create a role, enlist your users, grant perms to said role, 
       add role to ##MS_ServerStateReader##.

Contributor doesn't mean what you think.

Symptom: SQL DB Contributor role assigned. Metrics blade is empty or shows errors.

Cause: Contributor doesn't include monitoring permissions.

Fix (Management Plane): Add Monitoring Reader role in Azure IAM.

The Bottom Line

Like I said before, these two systems don't talk to each other, and they don't share the same permission system. This is how the cloud works. Once you stop expecting them to agree, the Access Denied mysteries disappear.

Keep the matrix handy. Verify before you trust. Be sure you're fixing the right layer.

More to Read:

Microsoft: Server-level roles in Azure SQL Database
Microsoft: Database-level roles
Microsoft: Authorize database access
Microsoft: Monitor performance using DMVs
sqlfingers: Management Plane vs Data Plane
sqlfingers: Cloud Security for SQL Server

Is Local Admin Required to Manage SQL Server?

No. But let's talk about what that really means.

The Question

"Does a DBA need local administrator membership to manage SQL Server?"

The answer is simple: Local admin group membership is not required. In fact, best practices dictate that Database Administrators (DBAs) and SQL service accounts should not have local administrator rights on the host server.  This change was introduced as far back as SQL Server 2008 with a 'secure by design, secure by default, and secure in deployment' strategy.

What Microsoft Says:

"The following improvements in SQL Server 2008 decrease the surface and attack area for SQL Server and its databases by instituting a policy of 'Least Privileged' and increase the separation between the Windows Administrators and the SQL Server administrators: By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role."

The BUILTIN\Administrators group no longer gets sysadmin by default.

The Real Requirements

Without local admin group membership, a DBA needs explicit grants in six areas:

Category	What's Required
Group Memberships	Remote Desktop Users, Performance Monitor Users, Event Log Readers, Distributed COM Users
NTFS Permissions	Modify on data/log/backup drives; Read on SQL binaries
WMI Permissions	Execute Methods, Enable Account, Remote Enable on SQL namespaces
DCOM Permissions	Remote Launch, Remote Activation, Remote Access
Service Control	Granted via GPO or sc sdset
Registry Access	Read on SQL Server hive

All of this is doable, but every bit of it requires additional, separate configurations. For step-by-step instructions, see SQL Server DBA Permissions Without Local Admin.

The Pain Points

Many DBAs will push back on this - and they're not wrong:

SQL Server Configuration Manager (SSCM) is the only supported tool for managing SQL Server services, and by default, it requires local admin. Without it, you need several workarounds:

• Full Control on specific WMI namespaces
• Registry access to SQL Server keys
• Service control permissions granted separately

Service restarts don't work from SSMS without explicit grants. Right-click → Restart is grayed out. You'll need GPO-based service permissions or sc sdset commands applied to each instance.

SSMS shows a question mark instead of the green arrow if WMI/DCOM isn't configured. Cosmetic, but it signals incomplete access.

Patches and CUs require local admin. Period. If you don't have it, you must work with the Windows team to patch your servers.

Troubleshooting slows down. Event Viewer, perfmon, quick file access - all require explicit grants or group memberships that local admin would have provided automatically.

The Counter-Argument

Here's what your colleagues will say: "PoLP applies to the role, not to individual permissions. If my job is responsible for full-stack SQL Server management, then local admin IS the minimum privilege for that role."

They're not wrong. As Andreas Wolter notes in his Principle of Least Privilege, it is much tougher to implement than you may expect.

The real PoLP violations aren't "DBA has local admin." They're:

• Using the same account for admin work and daily tasks
Microsoft: "Ensure all critical admin roles have a separate account" — Azure identity best practices

• Shared credentials with elevated rights
NIST 800-53 AC-2(9) restricts shared/group accounts

• Permanent access when just-in-time would suffice
Microsoft: "on-demand, just-in-time administrator access" — Privileged Access Roadmap

• Stale privileges that haven't been reviewed
NIST 800-53 AC-6(7) requires periodic review

For as long as I've been doing this, I think the security question shouldn't be whether the DBA has local admin. It should be whether that access is scoped, audited, justified, and revocable.

The Bottom Line

If your environment...	Then...
Has compliance requirements (PCI, SOX, HIPAA)	Grant explicit permissions, document everything
Has dedicated Windows and SQL teams	Separate the roles, use the workarounds
Is a small shop where DBA = sysadmin or where full server access is needed	Local admin is practical and defensible
Is a managed services engagement	Follow client requirements

Local admin membership is not required to administer SQL Server. But removing it means a lot of extra work:

• Configuring WMI, DCOM, GPO, NTFS, and registry permissions explicitly
• Accepting that some tools won't work the same way
• Coordinating with Windows admins for patches and installs

If your security posture demands separation, it can be done. If operational simplicity matters more, local admin is not unreasonable.

Either answer is defensible. Pretending it's simple isn't.

More to Read:

Why SQL Server Admins Don't Need Local Admin Rights: A Zero Trust Approach
SQL Server DBA Permissions Without Local Administrator
SQL Server 2008 R2 Security Changes
How to Start or Stop SQL Services without OS Admin Rights

Which Indexes Are Missing? — 2025 Edition

Fifteen years ago, I wrote a little post about the missing index DMVs. Six major SQL Server releases later, it's time for an update.

The Old Query: Still Kicking

Here's the gist of what we had back then:

SELECT 
    avg_user_impact average_improvement_percentage, 
    avg_total_user_cost average_cost_of_query_without_missing_index, 
    'CREATE INDEX idx_' + [statement] +  
    ISNULL(equality_columns, '_') +
    ISNULL(inequality_columns, '_') + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, ' ') + 
    ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b
  ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c
    ON b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40

This still runs fine. If you're on SQL Server 2005-2017, or you just need a quick-and-dirty list, it gets the job done.

But it has a couple blind spots:

• You see suggestions, but not which queries need them
• No idea if the suggestion is from current or stale statistics

What's New Since 2010

The Big One: sys.dm_db_missing_index_group_stats_query

Introduced in v2019, this links missing index suggestions to actual queries via query_hash. No more guessing which query is crying for help — you can see it directly.

Freshness Check: last_user_seek and last_user_scan

The freshness columns -- last_user_seek and last_user_scan -- were always there, but I didn't include them in my 2010 post.  I should have.  They tell you exactly when the optimizer last wanted that index.  A suggested index based on current stats is very different from one using old/outdated statistics.

Cleaner T-SQL

We've got CONCAT, CONCAT_WS, and FORMAT now. No more nested ISNULL chains.

The 2025 Query

/*
    Missing Index Analysis
    sqlfingers.com | 2025
    Requires: SQL Server 2019+
*/

SELECT 
    ROW_NUMBER() OVER (
        ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC) priority,
    CAST(s.avg_user_impact AS INT) [impact_%],
    FORMAT(s.user_seeks, 'N0') seeks,
    FORMAT(s.user_scans, 'N0') scans,
    FORMAT(s.last_user_seek, 'yyyy-MM-dd HH:mm') last_seek,
    DB_NAME(d.database_id) db,
    OBJECT_SCHEMA_NAME(d.object_id, d.database_id) [schema],
    OBJECT_NAME(d.object_id, d.database_id) [table],
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    CONCAT(
        'CREATE NONCLUSTERED INDEX [IX_',
        OBJECT_NAME(d.object_id, d.database_id), '_',
        REPLACE(REPLACE(REPLACE(
            CONCAT_WS('_', d.equality_columns, d.inequality_columns),
            '[', ''), ']', ''), ', ', '_'),
        '] ON [', OBJECT_SCHEMA_NAME(d.object_id, d.database_id), '].[', 
        OBJECT_NAME(d.object_id, d.database_id), '] (',
        CONCAT_WS(', ', d.equality_columns, d.inequality_columns), ')',
        IIF(d.included_columns IS NOT NULL, 
            CONCAT(' INCLUDE (', d.included_columns, ')'), ''),
        ';'
    ) create_statement
FROM sys.dm_db_missing_index_details d INNER JOIN sys.dm_db_missing_index_groups g 
  ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats s 
    ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC;

Bonus: Find the Query in Need

This one is fun. We can use sys.dm_db_missing_index_group_stats_query to see exactly which query wants the index:

SELECT 
    CAST(sq.avg_user_impact AS INT) [impact_%],
    OBJECT_NAME(d.object_id, d.database_id) [table],
    d.equality_columns,
    d.inequality_columns,
    LEFT(t.query_sql_text, 150) query_snippet
FROM sys.dm_db_missing_index_details d INNER JOIN sys.dm_db_missing_index_groups g 
  ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats_query sq 
    ON g.index_group_handle = sq.group_handle INNER JOIN sys.query_store_query q 
      ON sq.query_hash = q.query_hash INNER JOIN sys.query_store_query_text t 
        ON q.query_text_id = t.query_text_id
WHERE d.database_id = DB_ID()
ORDER BY sq.avg_user_impact DESC;

Now we're not just seeing such-n-such index on tablename.   We get the actual SELECT statement that is suffering.   Pure gold.

Quick Caveats

• DMV stats reset on restart — let your server run a full business cycle before trusting these numbers
• Not every suggestion is gold — the optimizer doesn't consider write overhead or maintenance cost. You must always fully review and test the recommended indexes rather than trusting them blindly.
• Look for overlaps — if you see 3 suggestions for the same table, you might consolidate into 1

More to Read:

Original 2010 Post: Which indexes are missing?
Microsoft Docs: sys.dm_db_missing_index_group_stats_query

Happy indexing.