SQL Server – Network – Get TCP Ports

Background

Get network ports that the current SQL Server Instance is using.

Outline

There are some pathways to getting the SQL Server Network Ports:

  1. sys.dm_server_registry
    • SQL Server Version
      • v2008

Code

sys.dm_server_registry

SQL


select 

          tblDSR.registry_key
        , tblDSR.value_name
        , tblDSR.value_data
        , [userOrAdmin]
            = case
                when (tblDSR.registry_key like '%AdminConnection%') then 'Admin'
                else 'User'
              end

from   sys.dm_server_registry tblDSR

/*
	Database Engine - MSSQLServer
*/
where
       (

            (
                ( tblDSR.registry_key like '%\MSSQLServer\%' )
			)
        )

/*
	Filter in TCP Ports
*/
and
       (

            (
                tblDSR.value_name in
                    (
                          'TcpDynamicPorts'
                        , 'TcpPort'
                    )
            )

        )

/*
    Filter out \IP1 thru \IP4
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP2
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP3
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP4
*/
and
       (
            ( tblDSR.registry_key not like '%\IP_')
        )

/*
    Filter out empty data
*/
and
        (

                ( tblDSR.value_data is not null )
            and ( tblDSR.value_data != '' )
            and ( tblDSR.value_data != '0' )
        )

Output

Output – Default Instance

sysDOTdm_server_registry__Default__20180606_0258PM.png

Output – Named Instance

sysDOTdm_server_registry__20180606_0239PM.png

Explanation

  1. Registry Key
    • Please close out your eyes to the AdminConnection; unless you are trying to connect to the Admin Port
    • Most people will be using the regular user default port

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_server_registy

WMI – Error – “0x8004100E”

Background

Trying to steal and use someone else’s code does not do well for me.

Here I am having stolen a code, but stuck with bad characters and an error message.

Code ( Original )


OPTION EXPLICIT

Dim strComputer
Dim URL
Dim objWMIService
Dim colItems
Dim objItem


strComputer = "."

URL="winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10"

Wscript.Echo "URL is "& URL

set objWMIService = GetObject(URL)

Set colItems = objWMIService.ExecQuery("SELECT * FROM SecurityCertificate",,48)

For Each objItem in colItems

	Wscript.Echo ""

	Wscript.Echo "SecurityCertificate instance"
	Wscript.Echo ""
	Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
	Wscript.Echo "FriendlyName: " & objItem.FriendlyName
	Wscript.Echo "IssuedBy: " & objItem.IssuedBy
	Wscript.Echo "IssuedTo: " & objItem.IssuedTo
	Wscript.Echo "Name: " & objItem.Name
	Wscript.Echo "SHA: " & objItem.SHA
	Wscript.Echo "StartDate: " & objItem.StartDate
	Wscript.Echo "SystemStore: " & objItem.SystemStore


Next

 

Error

Error Image

Error Message


(null): 0x8004100E

Trouble Shooting

WMI Code Creator

Download WMI Code Creator from here.

Extract

Extract the compressed ( zip) file.

Run

Run the file.

Namespace

Looked at the NameSpaces…

They are version specific.

The one I want is root\Microsoft\SqlServer\ComputerManagement12.

But, the one referenced in the code is root\Microsoft\SqlServer\ComputerManagement10.

On this particular box I am on SQL Server v2014.

And, so ComputerManagement12 is SQL 2014.

While ComputerManagement10 is SQL Server 2008.

 

Code ( Revised )

Code revised for v2014.

 

OPTION EXPLICIT
On error resume next

Dim strComputer
Dim baseURL
Dim sqlServerVersion
Dim URL
Dim objWMIService
Dim colItems
Dim objItem
Dim query

strComputer = "."

baseURL="winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\"
rem comment out version information
rem sqlServerVersion="ComputerManagement10"
sqlServerVersion="ComputerManagement12"

URL=baseURL & sqlServerVersion

set objWMIService = GetObject(URL)

if (Err.Number <> 0) then

	Wscript.Echo "GetObject failed on " & URL
	Wscript.Echo "Error Number is " & CSTR(Err.Number)
	Wscript.Echo "Error Description is " & Err.Description

	Wscript.Quit
	
end if

query = "SELECT * FROM SecurityCertificate"

Set colItems = objWMIService.ExecQuery(query,,48)


if (Err.Number <> 0) then

	Wscript.Echo "ExecQuery failed on " & query
	Wscript.Echo "Error Number is " & CSTR(Err.Number)
	Wscript.Echo "Error Description is " & Err.Description

	Wscript.Quit
	
end if

For Each objItem in colItems

	Wscript.Echo ""

	Wscript.Echo "SecurityCertificate instance"
	Wscript.Echo ""
	Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
	Wscript.Echo "FriendlyName: " & objItem.FriendlyName
	Wscript.Echo "IssuedBy: " & objItem.IssuedBy
	Wscript.Echo "IssuedTo: " & objItem.IssuedTo
	Wscript.Echo "Name: " & objItem.Name
	Wscript.Echo "SHA: " & objItem.SHA
	Wscript.Echo "StartDate: " & objItem.StartDate
	Wscript.Echo "SystemStore: " & objItem.SystemStore


Next

SQL Server – Cached Plans – Memory Utilization Per Object Type

Background

Let us review the type of objects in our cached plan.

 

Code

Stored Procedure

cachedplan.sp_MemoryUtilizationByPlanObjectType

 



use [master]
go

if schema_id('cachedplan') is null
begin

    exec('create schema [cachedplan] authorization [dbo]')

end
go

if object_id('[cachedplan].[sp_MemoryUtilizationByPlanObjectType]') is null
begin

    exec('create procedure [cachedplan].[sp_MemoryUtilizationByPlanObjectType] as ')

end
go


alter procedure [cachedplan].[sp_MemoryUtilizationByPlanObjectType]
(
    @dbid int = null
)
as 

begin

    ; with cteSQLInstance
    as
    (
        select
              [memoryUsedBySqlserverInMB]
                = (physical_memory_in_use_kb/1024) --Memory_usedby_Sqlserver_MB,

            , [lockedPagesUsedBySqlserverInMB]
                = (locked_page_allocations_kb/1024 ) --Locked_pages_used_Sqlserver_MB

            /*

                , [totalVirtualAddressSpaceUsedInMB]
                    = (total_virtual_address_space_kb/1024 )

            */
            , [totalVirtualAddressSpaceReservedInMB]
                = (virtual_address_space_reserved_kb/1024 )

            , [totalVirtualAddressSpaceCommittedInMB]
                = (virtual_address_space_committed_kb/1024 )

            , [processMemoryLow]
                = case
                    when (process_physical_memory_low=1) then 'Yes'
                    else 'No'
                  end

            , [processVirtualMemoryLow]
                = case
                    when (process_virtual_memory_low=1) then 'Yes'
                    else 'No'
                  end	

        from sys. dm_os_process_memory
    )
    , ctePlanDB
    (
          [plan_handle]
        , [dbid]
        , [database]
    )
    as
    (

        select
              [plan_handle]
                 = cplan.[plan_handle]

            , [dbid] 
                = cast(dbPA.[value] as int)

            , [database]
                    = case
                            when ( cast(dbPA.[value] as int) = 32767) then 'Resource DB'
                            else db_name
                                    (
                                        cast(dbPA.[value] as int)
                                    )
                      end	

        FROM sys.dm_exec_cached_plans cplan

        CROSS APPLY sys.dm_exec_plan_attributes(cplan.plan_handle) dbPA 

        where dbPA.[attribute] = 'dbid'


    )
    SELECT 

              --[dbid] = ctePDB.[dbid]

              [database]
                = ctePDB.[database]

            , [objectType]
                = cplan.objtype

            , [totalMB]
                = cast
                    (
                        sum
                        (
                            cast(size_in_bytes as float)
                        )
                         /
                         ( 1024 * 1024)

                      as decimal(30, 2)

                    )

            , [%]
                = cast
                    (
                        sum
                        (
                            (
                                cast(size_in_bytes as float) /
                                    ( 1024 * 1024)
                            ) * 100.00
                            / cteSQLIns.[memoryUsedBySqlserverInMB]
                        )

                        as decimal(30, 2)

                    )

            , [totalPlans]
                =count_big(*)

           , [averageUseCounts]
                = avg(cplan.usecounts)

            , [totalMBPlansUsedMultipleTimes]
                = cast
                  (
                      sum
                      (
                        cast
                        (
                            (
                                CASE 
                                    WHEN (usecounts != 1) THEN cplan.[size_in_bytes]
                                    ELSE 0 
                                END
                            ) as decimal(18,2)
                        )
                    )
                    /
                    ( 1024 * 1024)

                    as decimal(30, 2)
                )

            , [countPlansUsedMultipleTimes]
                = sum(
                    CASE 
                        WHEN usecounts != 1 THEN 1 
                        ELSE 0 END
                ) 

            , [totalMBPlansUsedOnlyOnce]
                = cast
                  (

                    sum
                      (
                        cast
                        (
                            (
                                CASE WHEN usecounts = 1 THEN size_in_bytes 
                                    ELSE 0 
                                END
                            ) as decimal(18,2)
                        )
                     )

                     /

                     ( 1024 * 1024)

                    as decimal(30, 2)

                )

            , [countPlansPlansUsedOnlyOnce]
                = sum(
                    CASE 
                        WHEN usecounts = 1 THEN 1 
                        ELSE 0 END
                ) 

    FROM sys.dm_exec_cached_plans cplan

    inner join ctePlanDB ctePDB

        on cplan.plan_handle = ctePDB.plan_handle

    cross apply cteSQLInstance cteSQLIns

    where ctePDB.[dbid] = case

                            when ( @dbid is null ) then ctePDB.[dbid]
                            else @dbid
    
                         end


    GROUP BY 
                ctePDB.[dbid]
              , ctePDB.[database]
              , cplan.objtype

    ORDER BY 
            sum
            (
                cast(size_in_bytes as float)
             )
            /
            ( 1024 * 1024)
              desc

end
go

Invoke


exec [master].[cachedplan].[sp_MemoryUtilizationByPlanObjectType]

 

Output

Summary

Quick Notes

  1. Cached Plans
    • Prepared Statements
      • One can still experience high memory utilization with prepared statements
      • We noticed an upper limit of about 10% on systems that make heavy size of prepared statements

 

Reference

  1. Wiki > TechNet Articles > SQL Server Memory and Troubleshooting
    • SQL Server Memory and Troubleshooting
    • Authors
      • Shashank Singh ( Shanky )
        (MVP, Microsoft Partner, Microsoft Community Contributor)
    • Link
      Link
  2. Docs / SQL / Relational databases / System dynamic management views
    • sys.dm_os_process_memory (Transact-SQL)
      Link