A Study in SQL Server Ad hoc Query Plans

We often think of ad hoc query plans, perhaps due to my own dismissive attitude towards it, as single-use plans that won't be reused frequently or at least not in the very near future. By that time, the plan might have already been evicted from the cache by SQL Server. However, what if a significant portion of our overall workload consists of such queries? Let's say, for the sake of argument, more than 50%. In this scenario, caching these execution plans would simply waste SQL Server's memory, and the server's memory in general. Some might argue this can bloat the plan cache.

As an illustration, consider a real-world scenario where the memory consumed by these query plans is a staggering 30GB!

SELECT 
    instance_name AS name,
    cntr_value / 128 AS pages_mb,
    cntr_value / 128 / 1024 AS pages_gb
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Pages'
ORDER BY pages_mb DESC;

Next, I fire up a slight variation of the query to see the type of workloads using the most memory:

;WITH cte_plan_cache_usage_by_obj_type AS 
(
	SELECT COALESCE(objtype, '-- ALL Plans') objtype,
           Sum(Cast(size_in_bytes AS BIGINT)) / 
		              1024 / 1024 size_mb
    FROM   sys.dm_exec_cached_plans
    GROUP  BY rollup ( objtype )
)
SELECT objtype,
       size_mb,
	   'percent' = 
		   ( size_mb * 100 ) / 

                       (SELECT size_mb
			FROM   cte_plan_cache_usage_by_obj_type
			WHERE  objtype = '-- ALL Plans'
		   ) 
FROM   cte_plan_cache_usage_by_obj_type
ORDER  BY size_mb DESC

Wow, the ad hoc queries are using a staggering 94%!

This workload is predominantly ad hoc. And no, I am not in a position to instruct the application team to modify their queries, at least not in the short term. 

So for now, any improvements must be made on the backend SQL Server.

Fortunately, for these types of workloads, SQL Server offers a configurable setting called “optimize for ad hoc workloads”. This setting helps limit or reduce the memory used by single-use ad hoc queries. The key here is "single-use". How does it work? When this setting is enabled, the optimizer doesn't cache the full execution plan for an ad hoc query initially. Instead, it caches only a much smaller plan "stub" along with its query hash and plan hash. This plan "stub" isn't a full execution plan, so it's not reusable for subsequent runs of the same query. However, if that same query is executed again soon after, the optimizer will then cache the reusable, full execution plan. Now if you have a very long running analytical query that runs once a day, should its full execution plan be cached? My qualified answer would be no, because it is very likely that the cached plan would need to be recompiled anyway due to manual or automatic updates of index statistics, index rebuilds, or other reasons—or it may simply be evicted from the cache by then. 

In short, when optimize for ad hoc workloads is turned on:

• The first execution creates a small compiled plan stub.
• On a repeat (exact) query, SQL Server promotes the stub to a full, reusable plan.
• Plans with low use counts, or not used for many hours, may be best candidates for removal to further free memory.

In environments where single-use, ad hoc queries constitute a large portion of the workload, this setting can save a significant amount of memory, freeing it up for other tasks.

Note: For two ad-hoc queries to be reused in SQL Server, they must have an exact text match. This match is sensitive to both case and spacing, even on servers that are case-insensitive.

By default, this setting is turned off. However, I occasionally encounter situations where I find it beneficial to enable it. Why? Because memory in SQL Server is valuable. Perhaps an even more compelling reason is that many SQL servers have a limited memory allocation. As much as I'd like to, I can't convince myself, let alone my manager, to allocate 2TB of RAM to every server.

In the example I presented, the 30GB plan cache might appear excessive without understanding the context. Heck, most of my SQL servers don't even have that much total memory. To shed some light, here's some context regarding this SQL Server:

SQL Version: 

Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64)

Enterprise Edition: Core-based Licensing (64-bit)

Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Database Size is 60TB

Application: Bigdata/Data Warehouse/Analytical Reports

CPU and Memory Configuration:

(MEMORY INFO IS IN GIGABYTES)

While a MAXDOP of 8 is the recommended best practice, I'm skeptical whether it's the optimal setting for this SQL Server, particularly considering its primarily analytical workload and 112 cores. It might be worthwhile to adjust this value dynamically based on the time of day and expected workload. For example, if there's an extensive ETL job running every night for 5 hours, designed for exclusive use of the entire server, thereby blocking or prohibiting other users, then that ETL job might benefit from a higher MAXDOP value. I'll explore this in more detail later on.

In this context, where the SQL instance has more than 2TB of RAM available, the 30GB cache size represents less than 1.5%. This percentage is, on average, lower than that of most other SQL Servers. However, in absolute terms, it's still significant, so it's worth investigating.

Let's first check what the current setting is for 'optimize for ad 'hoc workloads'.

It's not enabled, which is the default, so it remains in the turned-off state. Ordinarily, I wouldn't spend more time investigating this; I would simply enable the setting. However, in this case, I've been hired solely to investigate and make recommendations. I'm not authorized to implement any changes myself.

Therefore, I need to delve deeper into my investigation. But before proceeding, let's discuss: what precisely defines an execution plan as 'ad hoc'?

As I highlighted at the outset, some of us often view an ad hoc plan or query as something executed only once. This perception is only partially accurate, especially when considering how SQL Server categorizes a query plan as 'ad hoc' or otherwise in its cache: 

“Adhoc: Ad hoc query. Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls”

That description is straight from this MS article:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql

Even though it doesn't explicitly mention SSMS, queries executed from SSMS are also labeled as ad hoc by the optimizer.

It's not the frequency of a query's execution that determines its classification as an ad hoc query. However, the number of query executions does influence whether the "optimize for ad hoc workloads" setting caches the full execution plan or just a stub.

In practical terms, ad hoc queries are those that either lack parameters or cannot be auto-parameterized by the optimizer and are not part of a database object (like stored procedures, functions, views, etc.). Consider, for instance, this simple query:

SELECT NAME FROM SYS.DATABASES DBS WHERE  NAME = 'tempdb';

Observe that the query utilizes a constant value 'tempdb' in the WHERE clause, rather than a variable or parameter. Even so, the optimizer can—and often will—attempt to parameterize this query if it's included within a stored procedure or sent as a prepared SQL query. Interactive client tools like sqlcmd, osql, SSMS, and others don't execute such queries as prepared statements. As a result, the optimizer labels them as ad hoc queries and caches their execution plans accordingly. If you have processes that frequently run certain queries using such tools, consider using sp_executesql. This way, they're cached as prepared plans, which can be reused to enhance query performance.

Now, let's examine the subsequent example where the same SQL statement is executed in three distinct manners to obtain identical results:

-- Ad hoc query
SELECT NAME FROM sys.databases dbs WHERE  NAME = 'tempdb'; 
GO
-- Prepared SQL statement
EXEC Sp_executesql N'SELECT NAME FROM sys.databases dbs WHERE  NAME = @db_name',
                   N'@db_name sysname',
                   N'tempdb' 
GO
-- Stored Procedure
IF Object_id('sp_test_adhoc_plans_cache', 'P') IS NOT NULL
  DROP PROCEDURE sp_test_adhoc_plans_cache
GO
CREATE PROCEDURE sp_test_adhoc_plans_cache
(@db_name SYSNAME = 'tempdb')
AS
    SELECT NAME FROM sys.databases dbs WHERE  NAME = @db_name;
GO

EXEC sp_test_adhoc_plans_cache
GO

Now, observe how the optimizer processed and cached those queries:

SELECT cp.plan_handle,
       cp.objtype,
       cp.cacheobjtype,
       cp.size_in_bytes,
       cp.usecounts,
       qt.text,
       qs.query_hash,
       qs.query_plan_hash
FROM   sys.dm_exec_cached_plans cp
       inner join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle
       CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) qt
WHERE  qt.text LIKE '%SELECT NAME FROM sys.databases dbs WHERE  NAME =%'
       AND qt.text NOT LIKE 'SELECT cp.plan_handle%'

For clearer visibility of its content, I've divided the results into two screenshots:

Observe that the query hash and plan hash values for them are identical. However, the plan handles and objtype differ, even for the same input value, 'tempdb'. While the cached plan size for the ad hoc query is considerably smaller than for the other plans, it remains larger than it would be if 'optimize for ad hoc workloads' were enabled. Let's confirm that:

EXEC sp_configure 'optimize for ad hoc workloads', 1;

reconfigure with override;

Let's remove the already cached plans:

DBCC FREEPROCCACHE (0x060001002C0F151A10D0B0C3D30100000100000000000000000000000000000);

DBCC FREEPROCCACHE (0x060001008A0FCA2810A6C9B5D3010000010000000000000000000000000000);

DBCC FREEPROCCACHE (0x05000100D563663F50416C7CE30100000100000000000000000000000000000);

Rerun the same three queries and examine the cache information:

Note that the plan_handle values differ from those in the previous screenshot, as I cleared the earlier plans from the cache.

Observe the size of the ad hoc plan: merely 456 bytes, with the cacheobjtype listed as 'Compiled Plan Stub'. That's correct – it's only a stub. It doesn't contain an execution plan, so technically, there's no cached plan available for reuse. However, should the same ad hoc query be executed again in the near future, the optimizer will generate a full execution plan for it, which can be reused by subsequent invocations of the same query. Let's validate that:

-- Ad hoc query

SELECT NAME FROM sys.databases dbs WHERE  NAME = 'tempdb';

GO

Here is the info from sys.dm_exec_cached_plans after running the ad hoc query the second time:

The cacheobjtype for the Adhoc plan is no longer a tub.

Given this information, would I recommend enabling the 'optimize for ad hoc workloads' setting? Given that over 90% of the workload is ad hoc, the risks seem minimal. However, I'd like to examine one more piece of data. The following query displays the aggregate memory usage for:

• Ad hoc query plans
• Plans that are not stubs
• Plans with a use count of 2 or fewer
• Plans not used by a query in over 5 hours

;with cte_cached_adhoc_plans as
(
SELECT plan_handle,
       MAX(last_execution_time) last_execution_time,
       SUM(execution_count)     execution_count
FROM   sys.dm_exec_query_stats
GROUP  BY plan_handle 
)
SELECT	COUNT(*) [Plan Count],
		SUM(CAST(size_in_bytes AS BIGINT)) / 
		              1024 / 1024 [Size MB],
		MAX(cte_cached_adhoc_plans.execution_count) [Max Exec Count],
		SUM(cte_cached_adhoc_plans.execution_count) [Total Exec Count]

FROM       sys.dm_exec_cached_plans cp 
INNER JOIN cte_cached_adhoc_plans ON cte_cached_adhoc_plans.plan_handle = cp.plan_handle 
WHERE cp.objtype = 'Adhoc'
  AND cte_cached_adhoc_plans.last_execution_time < DATEADD(HOUR, -5, GETDATE())
  AND cp.objtype != 'Compiled Plan Stub'
  AND cte_cached_adhoc_plans.execution_count <= 2

That's nearly 28GB. In this case, I would recommend enabling the 'optimize for ad hoc workloads' setting. However, like any recommendation involving configuration changes, it comes with caveats. Exercising caution is essential, as any change can have potentially unintended and sometimes adverse effects. The organization should have a robust change control procedure in place, complete with a back-out plan.

As an alternative, the following query can be used to generate a script to remove individual ad hoc query plans from the cache. Please tailor the filter conditions to fit your needs. This script could be scheduled to run at appropriate times, perhaps after peak business hours and just before nightly ETL or maintenance tasks commence.

;with cte_cached_adhoc_plans as
(
SELECT plan_handle,
       MAX(last_execution_time) last_execution_time,
       SUM(execution_count)     execution_count
FROM   sys.dm_exec_query_stats
GROUP  BY plan_handle 
)
SELECT	TOP 1000
	[Remove Cached Plan] = 
	'DBCC FREEPROCCACHE (0x' + convert(varchar(max), cte_cached_adhoc_plans.plan_handle, 2) + ');' 
FROM       sys.dm_exec_cached_plans cp 
INNER JOIN cte_cached_adhoc_plans ON cte_cached_adhoc_plans.plan_handle = cp.plan_handle 
WHERE cp.objtype = 'Adhoc'
  AND cte_cached_adhoc_plans.last_execution_time < DATEADD(HOUR, -5, GETDATE())
  AND cp.objtype != 'Compiled Plan Stub'
  AND cte_cached_adhoc_plans.execution_count <= 2

In conclusion, as we're aware, SQL Server will evict cached plans and cached data pages from the buffer cache as necessary, using its continually evolving algorithm. So, why not trust its judgment? I generally concur, but with a caveat. The default settings and behaviors are effective for many SQL installations in a majority of scenarios. However, they don't cater to every SQL Server instance or every situation.

Resources:

Server configuration: optimize for ad hoc workloads

SQL Server: Optimize for Ad Hoc Workloads – use or not use

SQL Server's Unseen Orphan Users: Understanding Database Ownership Anomalies

The focus of this article is on a relatively less common scenario and even less discussed:  when a user is made the owner of a database without being added as a user within that database, which indirectly grants that user database owner permissions. You don't really have to add a user to the database to make it the database owner:

You might be wondering, So what's the problem?

In a non-contained database scenario, you would typically first create an instance-level login for this user before you can even make it DB Owner. Otherwise you will get an error message something like "the Login/Principal does not exist...."

But, in an AlwaysOn Availability Group (AG) environment, failing to create a login with the same SID on all secondary replicas can lead to complications. If the database failover to a secondary replica (planned or unplanned), the database user may not have a corresponding instance-level login on that replica. This effectively renders the user an orphan within that context. Interestingly, even though the user now faces access issues, traditional methods for identifying orphan users—whether using classical or more recent SQL queries—might not flag this user as an orphan. This is due to the specific nature of how logins and users are managed and identified in AG environments.

Consider the concept of an orphan SQL Server user. What comes to mind? This is a rhetorical question.

On a related note, Microsoft has recently started publishing comprehensive how-to documents covering day-to-day DBA tasks, such as creating databases and troubleshooting connection errors. These resources serve as a valuable complement to the formal product documentation, technical articles, and blogs. Some may view this abundance of information as overwhelming, reminiscent of the concept of function overloading Function overloading in programming languages. However, I see it as an invaluable service that merits recognition.

So, how does this relate to orphan users? Among these resources, I found a particularly relevant document titled Troubleshoot orphaned users. It sheds light on a scenario where orphaned users in SQL Server arise from a mismatch between database users and their corresponding logins in the master database, specifically when the login no longer exists.  According to the document:

Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master

In the old days, orphaned users often resulted from restoring a database from one server to another. This could happen even when you preemptively created the logins on the target SQL server. Why? Examine the following query to identify orphaned users:

SELECT dp.type_desc, dp.sid, dp.name AS user_name 
FROM sys.database_principals AS dp 
LEFT JOIN sys.server_principals AS sp 
    ON   dp.sid = sp.sid   
WHERE sp.sid IS NULL 
    AND dp.authentication_type_desc = 'INSTANCE';

Ahh… So, it’s not about the user’s name, it’s the SID (Security Identifier) that determines whether a user is orphan. We can use the methods described in Transfer logins and passwords between instances of SQL Server to fix the orphan users. Or, if you like PowerShell as I do, you could also use the dbatools PowerShell toolkit, which I often rely on myself. 

With the advent of the AlwaysOn technology in SQL Server more than 10 years, you may have seen more instances of orphan users. Fortunately, the dbatools have options to help with that too, for example Repair-DbaDbOrphanUser or Copy-DbaLogin to copy login/s from one server to another.

But sometimes it’s something else. For example, Orphan Windows Logins, or if the user was granted access to the database in some other way. For example, when a login is not added/granted access to the database, but instead made the database owner without adding it as a user.

This indirectly gives that user database owner permissions. The user in that case is essentially the database owner, without being in the database under its own name and added to the db_owner role.  As a result, some of the standard SQL queries to find and fix orphan users won’t detect this.

For the demo, I am going to create a TestDB01 database and add it to an existing AG:

USE [master]
GO
-- CREATE THE DATABASE
CREATE DATABASE [TestDB01];
 -- CHANGE THE RECOVERY MODE TO FULL
ALTER DATABASE [TestDB01] SET RECOVERY FULL ;
 -- BACKUP THE DB
BACKUP DATABASE [TestDB01] TO DISK = 'TestDB01.BAK';
 

-- ADD DB TO THE AG
ALTER AVAILABILITY GROUP [TestAG] ADD DATABASE [TestDB01];
 -- CREATE LOGIN

CREATE LOGIN [TestDB01_User01]
WITH PASSWORD=N'paue23Y&^97639iqeB',
DEFAULT_DATABASE=[TestDB01],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON;
 -- CHANGE THE DATABASE OWNER

USE [TestDB01]
GO
ALTER AUTHORIZATION ON DATABASE::[TestDB01] TO [TestDB01_User01]
GO

You won’t find a user with name TestDB01_User01 in the TESTDB01. But check out the SID value for the dbo user and compare it with the SID of the TestDB01_User01 login:

USE [TestDB01]

select name, SID, type_desc from sys.database_principals where name = 'dbo'
union all
select name, SID, type_desc from sys.server_principals where name = 'TestDB01_User01'

The SIDs are the same! Of course, this can happen to any database, not just the ones participating in AG.

Currently, I have not created a login for TestDB01_User01 on the secondary replicas, rendering it an orphaned database user in those instances. If the AG failover to any secondary replica, TestDB01_User01 would be unable to log in due to the absence of a corresponding login. Ideally, creating the login beforehand is recommended to prevent such issues. However, if a login is created post-failover (which is less advised), using either the GUI or the standard SQL statement would most likely result in a different SID, leading to login failure. 

To resolve this issue on a new primary replica, the login must be created using T-SQL and the CREATE LOGIN command, which allows for setting a specific SID that aligns with the one already in the database.

-- DROP LOGIN
-- DROP LOGIN [TestDB01_User01];

-- RECREATE LOGIN WITH A SPECIFIC SID VALUE
CREATE LOGIN [TestDB01_User01]
WITH PASSWORD=N'paue23Y&^97639iqeB',
DEFAULT_DATABASE=[TestDB01],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON,
SID = 0xB5AFAA3BF6EA8A489BC5BF6ED35F29B9 ;  

To ensure consistent access across replicas in an AlwaysOn Availability Group, it's critical to maintain identical SIDs for logins. If you adjust the database owner's SID on the new primary replica without updating the corresponding logins on other replicas, you risk SID mismatches, complicating future failovers. While it might seem straightforward to drop and recreate the login with the correct SID on any replica, doing so for a login that owns a database requires careful consideration, as this login is integral to the database's access control.

It's important to understand that inconsistencies in SIDs across replicas are not indicative of a bug but are a part of managing database security within SQL Server's framework. To avoid these challenges, proactively ensure that every new login created is replicated across all replicas with matching SIDs. However, in environments with multiple administrators or automated processes, maintaining this consistency requires coordination. DBAs, while central to this process, may need to work collaboratively with other stakeholders to manage logins and user creation effectively.

So, what should you do?

You could setup an alert (through a DDL Trigger, for example), to let you know anytime a new login gets created. This way, you're notified whenever a new login is created. You can then promptly replicate the login across all replicas, ensuring it doesn’t become an issue later on. For this task, I highly recommend utilizing the Copy-DbaLogin  command from the PowerShell DBATools toolkit. It's a straightforward and efficient way to ensure your logins are consistently set up across your SQL Server instances:

Copy-DbaLogin -Source SQLVM01\SQL2016AG01 -Destination SQLVM02\SQL2016AG01 -Login 'TestDB01_User01'

Type                       Name                      Status

-------------------------    -----------                     -----------------

Login - SqlLogin  TestDB01_User01  Successful  

However, the DBATools toolkit has an even more comprehensive solution: the  Sync-DbaAvailabilityGroup command. This tool does more than just synchronize logins; it helps maintain uniformity across your AlwaysOn environment on multiple fronts. By scheduling this command to run regularly through a SQL Server Agent job, you can automate the synchronization process, significantly reducing manual maintenance tasks. Plus, dbatools is supported by extensive documentation and user-friendly examples, making it easier than ever to implement these solutions.

Getting list of protocols enabled in a SQL Server instance

There are several ways to find out which network protocols are enabled for a SQL Server instance. The most obvious one is the SQL Server Configuration Manager tool on the server where the instance is installed. You can also look in the SQL Server error log when the instance starts up. These two approaches are what most DBAs are familiar with.

Note: The techniques in this post apply to SQL Server running on Windows (on‑prem or in IaaS VMs). They do not apply to Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Linux, where protocol/port configuration is managed differently and not exposed via the Windows registry.

Using the SQL Server Configuration Manager tool:

• Open SQL Server Configuration Manager. You can search for it in the Windows Start menu or run "SQLServerManager16.msc" for SQL Server 2022 and SQL Server 2025.

• In the left pane, expand "SQL Server Network Configuration".

• Click on "Protocols for <instance name>" (e.g., "Protocols for MSSQLSERVER" for the default instance).

• In the right pane, you'll see a list of protocols (Shared Memory, Named Pipes, and TCP/IP) along with their status (Enabled or Disabled).

Using SQL Server Error Log

Then, you could view the SQL Server error log or even query it using the sp_readerrorlog tsql command or use some text searching tool to find that information. The query method however I would say is not a precise one, simply because the SQL Server error logs could get rolled over into a new ones and even get deleted after reaching the maximum number of error log files to keep.

But then it's not like you really really need to know this information. After all, the usual TCP/IP and Named Pipe protocols required to connect remotely are "generally" enabled by default, even the Shared Memory for local connections, depending on the version of SQL Server and the configuration options you choose during the installation, see Default SQL Server Network Protocol Configuration for more information.

The point is, historically and even today if you are installing a default instance of SQL Server, chances are one or two protocols are already enabled using their default configuration, which in case of TCP/IP is the TCP port 1433 and for Named Pipe it is \\.\pipe\sql\query. Both are well-known to the us humans, and to the client tools and the API libraries these tools use so all you have to provide to connect to the SQL Server is the server name, and optionally (I might add, less often if not rarely) the specific protocol you want to use for connection.

So why should anybody care and go through the trouble of looking up that information? If the need does arises, which sometimes it does, it is to troubleshoot connection issues and maybe for security reasons.  However, I also sometimes see old applications that are still not aware that you can now have multiple instances of SQL Server on the same server, by way of installing the additional instances as Named instance, that relies on the SQL Server Browser service to automatically map the instance name to the respective port number each SQL instance is configured for. So such apps do need to know the TCP port number in order to connect to any sql instance not running on the age old default port 1433.

Ok, a caveat is worth mentioning here. the support for Named SQL Server instances have been around since, what 2005? So, I don't think that the developers of such apps, which are often third party vendors, have never heard of it. It maybe that they are now not around anymore or too small of an organization to afford to have enough budget to update their code, while it's customers are still stuck using it for lack of viable alternatives.

Whatever maybe the case,  for me, it is often a necessity than a mere "nice to know", or only to troubleshooting some issues.

So that being said, I can think of many ways to find protocol configuration. But to be frank, even if I tried,  I can't say exactly how many ways you can get this information, especially if you also throw in various  programming languages and APIs etc. The method that I do want to discuss here though involves reading the information from the windows registry, using the PowerShell, basically relying on it's *Item* cmdlets that have been part of PowerShell from the beginning. So I am hoping that the PowerShell statements here would work on any and all versions. In essence, there is no dependency on SQLPS or SQLServer modules, or any other for that matter. Except, if you want to export the results out to an Excel file later.

But first let me share couple queries as well. The first one will show the state of standard protocols i.e. TCP/IP, Named Pipes and Shared Memory.

;with cte as
(
	select 
		@@SERVERNAME [sql_server],
		case	when right(registry_key, 2) = 'Np' then 'Named Pipe'
				when right(registry_key, 2) = 'Sm' then 'Shared Memory'
				when right(registry_key, 3) = 'Tcp' then 'TCP IP'
			end [protocol],
		value_name [property],
		value_data [property_value],
		case value_data when 0 then 'Disabled'
						when 1 then 'Enabled'
			end [status]
		
	from sys.dm_server_registry
	where registry_key like '%SuperSocketNetLib%'
)
select * from cte
where [protocol] in ('Named Pipe', 'Shared Memory', 'TCP IP')
and [property] = 'Enabled'
;

And to get the TCP Port number:

SELECT TOP 100 *
FROM sys.dm_server_registry
WHERE registry_key like '%SuperSocketNetLib%'
  AND registry_key not like '%AdminConnection%'
  AND value_name in ('TcpDynamicPorts','TcpPort')
  AND value_data IS NOT NULL
  AND value_data != ''
  AND value_data != '0'

Caveats:

• TcpDynamicPorts being non‑empty and non‑zero indicates that dynamic ports are in use for that IP entry.

• If dynamic ports are configured, TcpPort is typically blank or zero.

• On multi‑homed servers, you may see entries per IP address; the listener port the instance actually binds to can differ per address.

• On clustered instances or Availability Group listeners, the listener’s DNS name and port might differ from the underlying instance ports on each node; this DMV reflects instance‑level configuration, not AG listener endpoints.

Both of these queries work well as Multi Server Queries (CMS) so you can run them against many SQL Servers in one go.

Using the PowerShell (Windows Registry)

Caveat: The PowerShell examples below make an informed assumption about where SQL Server places its registry keys. These locations are well‑documented but can differ in older or future versions, or when dealing with 32‑bit instances on 64‑bit Windows (which may use Wow6432Node).

To  be fair, I also think using PowerShell is bit complicated than what we all are used to as a DBA, I might even say it is a hassle.  And, if you think about the feature and behavior changes you might encounter among different versions, not to mention the Dependency Hell, it can be a nightmare!

But, I do think it is more versatile, that comes handy when managing a mid to large number of SQL Servers.  After all who wants to RDP into 50 servers for Config Manager? Not this DBA!

If you are already logged into the server, you can run the following in the PowerShell and it will return the list of SQL Server protocols enabled on a given SQL instance, which I have lighted in the cmdlet:

Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2022AG01\MSSQLServer\SuperSocketNetLib"  | Select-Object -ExpandProperty ProtocolList

In this example, SQL2022AG01 is the instance ID in the registry, and the output will contain a list such as tcp;np when TCP/IP and Named Pipes are enabled.

In the following example, the sql instance has TCP and named pipes enabled:

And to get the same information remotely, you can use the same command with Invoke-Command cmdlet:

Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock {
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2022AG01\MSSQLServer\SuperSocketNetLib" | 
    Select-Object -ExpandProperty ProtocolList}

To get the tcp port configured for the instance:

Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock {
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2019AG01\MSSQLServer\SuperSocketNetLib\Tcp" -Name TcpPort} |
    Select-Object TcpPort

And suppose you want to get the tcp port number for all SQL instances installed on a remote computer:

Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock {
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib\Tcp" `
    -Name TcpPort -ErrorAction SilentlyContinue | 
    select @{n='Instance' ;e={$_.PSPath.split('\')[5]}}, TcpPort, PSPath} | 
    Format-Table -Property PSComputerName, Instance, TcpPort

Now here, things start to get more interesting in PowerShell because you can pass multiple servers to the -ComputerName parameter above, and not have to worry about providing in it a specific SQL instance name, which often is different on different host servers.

You can even pass your entire server inventory to it from a plain text file, with no headers and only a single value per row:

Invoke-Command -ComputerName (Get-Content -Path "$env:USERPROFILE\Documents\server_inventory.txt" ) -ScriptBlock {
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib\Tcp" `
    -Name TcpPort -ErrorAction SilentlyContinue | 
    select @{n='Instance' ;e={$_.PSPath.split('\')[5]}}, TcpPort, PSPath} | 
    Format-Table -Property PSComputerName, Instance, TcpPort

A reusable PowerShell script

Here is a sample script, as complete of a script as I could make it at this moment without adding too much complexities to it. It already contains some explanation notes, which I hope helps:

<#

DISCRIPTION:

The script will return the enabled protocols in a SQL instance on a 

remote server and their pertinent properties. You do have to give it 

a server name. You can even provide multiple servers or even a text 

file with list of all your servers. The SQL instance is an optional 

variable, in which case the script will return protocol information 

on all sql instances installed in the given server/s. A nice thing 

about this script is that it returns this information as a PowerShell 

object, an array object, to be specific. That makes it easier not 

only to display results on the screen, it also allows you to pipe 

the results to a Comma  Separated Values file (CSV) or even Microsoft 

Excel if the required module, ImportExcel,  for it is available on 

the computer where you are running this script from. You can install 
the module from https://github.com/dfinke/ImportExcel.  I decided to 

only display a warning if the module is not available, 

rather than throwing an ugly error.

VARIABLES:

1.   $server_name
     A value for this variable is required
     There are 3 ways you can assing it a value
     
     a.  A single server name
         $server_name = 'MySQLServer'
     
     b.  Multiple server names as an array
         $server_name = @('MySQLServer', 'MySQLServer2', 'MySQLServer3')

     c.  Import server names from a plain text file
         $server_name = Get-Content -Path "$env:USERPROFILE\Documents\sql_servers.csv"         


2.   $instance_name
     Name of the SQL Server instance. For the default sql instance, 
     the value should be MSSQLSERVER, for example: $instance_name  = 'MSSQLSERVER'

     If $instance_name is omitted or set to $null, the script will return protocols
     information for all installed sql instances

     You cannot specify $instance_name if the $server_name contains multiple servers.
     This limitation can be overcome, like some others, but I decided not to at this point.


3.   $export_to_csv
     This is a $true/$false value. If $true then the script will export the results to 
     a CSV file.

4.   $csv_file_path
     Path and name of the CSV file. 
     Default value is "$env:USERPROFILE\Documents\sql_server_enabled_protocols.csv"


5.   $export_to_excel
     This is a $true/$false value. If $true then the script will export the results to
     an Excel file only if the Export-Excel is available.   

6.   $excel_file_path
     Path and name of the Excel file. 
     Default value is "$env:USERPROFILE\Documents\sql_server_enabled_protocols.xlsx"


#>

# Required variables
[string]$server_name     = 'SQLMV01'
[string]$instance_name   = $null # 'MSSQLSERVER'

# Export options
[bool]$export_to_csv     = $false
[string]$csv_file_path   = "$env:USERPROFILE\Documents\sql_server_enabled_protocols.csv"

[bool]$export_to_excel   = $true
[string]$excel_file_path = "$env:USERPROFILE\Documents\sql_server_enabled_protocols.xlsx"


                  
Function Get-sql-protocols
{
    Param 
    (
        [string]$instance_name
 
    )

$computer_name = $env:COMPUTERNAME 
$sql_registry_root        = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$installed_sql_instances = (Get-Item 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').GetValueNames()

if ($instance_name -notin ('', $null))
{
    # VALIDATE THE INSTANCE NAME
    
    $instance_name = $instance_name.ToUpper()
    if($installed_sql_instances.Contains($instance_name))
    {
        $installed_sql_instances = $instance_name
    }
    else
    {
        THROW "Error: SQL instance name $instance_name is invalid."
    }
}
   

$my_custom_object = @()

foreach($installed_sql_instance in $installed_sql_instances)
{
    if($installed_sql_instance -eq 'MSSQLSERVER')
    {
        $sql_instance_registry_path = 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
    }
    else 
    {
        $sql_instance_registry_path = Join-Path -Path $sql_registry_root `
                                       -ChildPath "$installed_sql_instance\MSSQLServer"
    }

    $sql_instance_SuperSocketNetLib_path = "$sql_instance_registry_path\SuperSocketNetLib"
    $protocols = Get-ChildItem $sql_instance_SuperSocketNetLib_path

    foreach ($protocol in $protocols)
    {
        foreach($protocolp in $protocol.GetValueNames())
        {
        
            $my_custom_object += [PSCustomObject]@{
                    computer_name    = $computer_name
                    sql_instance     = $installed_sql_instance
                    protocol_name    = $protocol.PSChildName
                    property_name    = $protocolp
                    property_value   = $protocol.GetValue($protocolp)
                }
        
        }
    }
    }

$my_custom_object
}

if($server_name.GetType().Name -ne 'String' -and $instance_name -notin ('', $null))
{
    THROW 'Error: A value of named instance in $instance_name is not compatible with an array for the $server_name'
}
else
{
    $sql_protocols = Invoke-Command -ComputerName $server_name   `
                                    -ScriptBlock ${Function:Get-sql-protocols} `
                                    -ArgumentList $instance_name

    $sql_protocols | Format-Table  computer_name, sql_instance, protocol_name, property_name, property_value
    # Export to a CSV file
    if ($export_to_csv)
    {
        Write-Information 'Exporting to CSV file....'
        $sql_protocols | Select-Object PSComputerName, sql_instance, protocol_name, property_name, property_value | 
                         Export-Csv -Path $csv_file_path -Force -NoTypeInformation
    }

    # Export to Excel file
    if ($export_to_excel)
    {
        
        if (Get-Command -Name Export-Excel -ErrorAction SilentlyContinue)
        {
            Write-Information  'Exporting to Excel file....'
            $sql_protocols | Select-Object PSComputerName, sql_instance, protocol_name, property_name, property_value | 
                             Export-Excel -Path $excel_file_path -WorksheetName "SQLProtocols" `
                             -TableName "SQLProtocols" -TableStyle Light9 -AutoSize -NoNumberConversion '*'
        }
        else
        {
            Write-Warning "Warning:Function Export-Excel not found. Skipping export to Excel..."
        }

    }


}

Security Tips

• Disable unneeded protocols to shrink attack surface.
• ​Mandate encrypted connections (Force Encryption).
• ​Caveat: Not for Azure SQL, Linux, or Arc, use portal/cli instead.

Download a better and more up to date script from GitHub:

PowerShell: Protocols Enabled in SQL Server