SQL Server – Extended Events – Internal Session Definitions

Background

Wanted to track Extended Events, but ran into a stumbling block.

And, we go…

 

Metadata

Extended Events

Active Sessions ( sys.dm_xe_sessions )

Code


select 
		  [sessionName] = tblXES.[name]
		, [sessionCreateTime] = tblXES.[create_time]
		, [sessionSource] = tblXES.[session_source]
		--  tblXES.*

from   sys.dm_xe_sessions tblXES

Output

Explanation

  1. session_source
    • We can group Extended Event based on Source
      • Source :-
        • server
        • internal
    • In our case
      • server
        • system_health
          • MSFT Defined
        • performance
          • Defined by us
      • internal
        • hkenginexesession
          • Added in SQL Server v2016
          • Tracks Hekaton ( “In-Memory Database” Events )
        • sp_server_diagnostics session
          • Added in SQL Server v2014
          • SQL Server Diagnostic Events

 

Extended Event Definitions ( sys.server_event_sessions )

Code


select 
		  [sessionID] = tblSES.[event_session_id]

		, [extendedEvent]
			 = tblSES.[name]

		, [eventRetentionMode] 
			= tblSES.[event_retention_mode]

		, [trackCasualty]
			 = tblSES.[track_causality]

		, [startupState] 
			= tblSES.[startup_state]

		--, tblSES.*

from   sys.server_event_sessions tblSES

Output

Explanation

  1. sessionID
    • event_session_id
      • Starts at 65534
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • track_causality
  5. Startup Start
    • startup_state

Tie things together

Let us join our two sources,  sys.server_event_sessions & sys.dm_xe_sessions.

 

Inner Join – sys.server_event_sessions & sys.dm_xe_sessions

Code

select 
		  [sessionID] =  tblSES.[event_session_id]
		, [extendedEvent] = tblSES.[name]
		, [eventRetentionMode] = tblSES.[event_retention_mode]
		, [trackCausality ] = tblSES.[track_causality]
		, [startupState] = tblSES.[startup_state]

		 , [sessionCreateTime] = tblXES.[create_time]
		 , [sessionSource] = tblXES.[session_source]

from  sys.server_event_sessions tblSES

left outer join sys.dm_xe_sessions tblXES

        on tblSES.[name]  = tblXES.[name]

Output

Explanation

  1. sessionID
    • event_session_id
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • Column :- track_causality
  5. Startup State
    • Column :- startup_state
  6. sessionCreateTime
    • Column :- sys.dm_xe_sessions.create_time
  7. sessionSource
    • Column :- sys.dm_xe_sessions.session_source

Workaround ( using cte ) – sys.server_event_sessions & sys.dm_xe_sessions

Code

; with cteExtendedEvent
(
      [name]
    , [id]
    , [sessionSource]
)
as
(

    /*
        Get Defined Event Sessions
    */ 
    select 
              tblSES.[name]

            , [sessionID]
                = tblSES.[event_session_id]

            , [sessionSource]
                = null


    from   sys.server_event_sessions tblSES

    union
    
    /*
        Get Running Event Sessions that are shown in  sys.server_event_sessions
    */ 

    select 
              tblXES.[name]

            , cast(null as int)

            , [sessionSource]
                = tblXES.[session_source]

    from   sys.dm_xe_sessions tblXES

    where  tblXES.[name] not in
                (
                    select [name]
                    from   sys.server_event_sessions tblSES
                )


)
select 

          [extendedEvent] 
            = cteEE.[name]

        , [sessionID] 
             =  tblSES.[event_session_id]


        , [eventRetentionMode]
             = tblSES.[event_retention_mode]

        , [trackCausality] 
            = tblSES.[track_causality]

        , [startupState] 
            = tblSES.[startup_state]

        , [sessionCreateTime]
             = tblXES.[create_time]

        , [sessionSource]
             = tblXES.[session_source]


from  cteExtendedEvent cteEE

left outer join sys.server_event_sessions tblSES

        on cteEE.[name] = tblSES.[name] 

left outer join sys.dm_xe_sessions tblXES

        on cteEE.[name]  = tblXES.[name]

Output

Summary

An astute mind will see that there are some events listed in sys.dm_xe_sessions, but not in sys.server_event_sessions.

Again sys.dm_xe_sessions tracks running Event Sessions.  Whereas sys.server_event_sessions tracks sessions definitions.

Also, keep in mind that sys.dm_xe_sessions is a dynamic management view.  And, sys.server_event_sessions is a system catalog view.

It appears that sys.server_event_sessions has chosen not to expose internal Extended events within the sys.server_event_sessions view.

MSFT choice makes some sense as sys.server_event_sessions by definition exposes definitions.

And, MSFT is likely saying these definitions are private and personal to us and we have chosen not to expose them.

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views/
      • sys.server_event_sessions ( Transact-SQL )
      • sys.dm_xe_sessions ( Transact-SQL )

sp_help_revlogin

Background

Restored a database and after running sp_change_users_login, found that there were sql logins that are were still misaligned.

Knew that I needed to copy the logins over, as well.

 

SSIS

There are some SSIS tasks that helps.

 

Legacy

But , why go new school when old school holds.

 

sp_help_revlogin

  1. MSFT
    • How to transfer logins and passwords between instances of SQL Server
      Link

Revision

Here is a revised code

Outline

  1. Added argument
    • @skipSystemAccounts
      • Ability to skip system accounts such as
        • Policy Accounts
          • ##MS_PolicyEventProcessingLogin##
          • ##MS_PolicyTsqlExecutionLogin##
        • NT AUTHORITY\SYSTEM
        • NT SERVICE%
    • Issue suser_id to check for account prior to creation

Code


/*

    How to transfer logins and passwords between instances of SQL Server
    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

*/

USE [master]
GO

IF OBJECT_ID ('sp_hexadecimal') IS NULL
begin

  exec('create procedure [dbo].[sp_hexadecimal] as ')

end
GO

ALTER PROCEDURE [dbo].[sp_hexadecimal]
      @binvalue varbinary(256)
   ,  @hexvalue varchar (514) OUTPUT
AS
begin

    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    
    
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    
    WHILE (@i <= @length)
    BEGIN

      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue 
                =   @charvalue 
                  + SUBSTRING(@hexstring, @firstint+1, 1)
                  + SUBSTRING(@hexstring, @secondint+1, 1)
    
      SELECT @i = @i + 1
    
    END

    SELECT @hexvalue = @charvalue

end

GO
                
 
IF OBJECT_ID ('sp_help_revlogin') IS NULL
begin

  exec('create PROCEDURE [dbo].[sp_help_revlogin] as ')

end
GO

ALTER PROCEDURE [dbo].[sp_help_revlogin]
(
       @loginName           sysname = NULL 
     , @skipSystemAccounts  bit = 0
)
AS
begin

    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary  varbinary (256)
    DECLARE @PWD_string  varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr  varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname
 
    DECLARE @STRING_EXISTENCE_CHECK_PREFIX  varchar(120)
    DECLARE @STRING_BEGIN                   varchar(120)
    DECLARE @STRING_END                     varchar(120)

    declare @CHAR_CRLF                      varchar(30)

    declare @tsql                           nvarchar(4000)

    set @STRING_EXISTENCE_CHECK_PREFIX = 'if suser_id(''%s'') is null '  
    set @STRING_BEGIN = ' begin '
    set @STRING_END = ' end '
     
    set @CHAR_CRLF  = char(13) + char(10);
     
    DECLARE login_curs CURSOR FOR

          SELECT 
                  p.sid
                , p.name
                , p.type
                , p.is_disabled
                , p.default_database_name
                , l.hasaccess
                , l.denylogin 
        
        FROM sys.server_principals p 
    
        LEFT JOIN sys.syslogins l
            ON ( p.[name] = l.[name] ) 
    
        WHERE p.[type] IN ( 'S', 'G', 'U' ) 
    
        AND   p.[name] <> 'sa'
    
        AND   p.[name] = isNull(@loginName, p.[name])

        AND  (

                  (@skipSystemAccounts = 0)

                or
                (

                        ( @skipSystemAccounts = 1 )

                    --skip NT SERVICE
                    and (

                            ( 
                                p.[name] not in 
                                    ( 
                                          '##MS_PolicyEventProcessingLogin##'
                                        , '##MS_PolicyTsqlExecutionLogin##'
                                    )
                            )
                    
                            and
                            ( 
                                p.[name] not in 
                                    ( 
                                        'NT AUTHORITY\SYSTEM'
                                    )
                            )

                            and ( p.[name] not like 'NT SERVICE%')
                        )

                )

            )

        order by
                p.name

    OPEN login_curs

    FETCH NEXT FROM login_curs 
    INTO 
              @SID_varbinary
            , @name
            , @type
            , @is_disabled
            , @defaultdb
            , @hasaccess
            , @denylogin

    IF (@@fetch_status = -1)
    BEGIN

      PRINT 'No login(s) found.'
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN -1
    
    END
    
    SET @tmpstr = '/* sp_help_revlogin script '
    
    PRINT @tmpstr
    
    SET @tmpstr = '** Generated ' 
                    + CONVERT (varchar, GETDATE()) 
                    + ' on ' 
                    + @@SERVERNAME 
                    + ' */'
    
    PRINT @tmpstr
    
    PRINT ''
    
    WHILE (@@fetch_status <> -1)
    BEGIN
    
      IF (@@fetch_status <> -2)
      BEGIN
    
        PRINT ''
    
        SET @tmpstr = '-- Login: ' + @name
    
        PRINT @tmpstr
    
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group

          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    
        END
        ELSE 
        BEGIN -- SQL Server authentication
            -- obtain password and sid
            
            SET @PWD_varbinary = CAST
                                    ( 
                                        LOGINPROPERTY
                                        ( 
                                              @name
                                            , 'PasswordHash' 
                                        ) 
                                        AS varbinary (256) 
                                    )
            
            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
            
            EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
 
            -- obtain password policy state
            SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
            
            FROM sys.sql_logins 
            
            WHERE name = @name
            
            SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
            
            FROM   sys.sql_logins 
            
            WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN '
                             + QUOTENAME( @name )
                             + ' WITH PASSWORD = ' + @PWD_string 
                             + ' HASHED '
                             + ' , SID = ' + @SID_string + ' ' 
                             + ' , DEFAULT_DATABASE = [' + @defaultdb + ']'

            IF ( @is_policy_checked IS NOT NULL )
            BEGIN
            
              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
            
            END
            
            IF ( @is_expiration_checked IS NOT NULL )
            BEGIN
            
              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
            
            END
        
        END
        
        IF (@denylogin = 1)
        BEGIN -- login is denied access
        
          SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
        
        END
        ELSE IF (@hasaccess = 0)
        BEGIN -- login exists but does not have access
        
          SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
        
        END
        
        IF (@is_disabled = 1)
        BEGIN -- login is disabled
        
          SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
        
        END

        set @tsql = replace( @STRING_EXISTENCE_CHECK_PREFIX, '%s', @name)
                        + @CHAR_CRLF
                        + @STRING_BEGIN
                        + @CHAR_CRLF
                        + @tmpstr
                        + @CHAR_CRLF
                        + @STRING_END
        
        PRINT @tsql
      
      END

      FETCH NEXT FROM login_curs 
      INTO  
              @SID_varbinary
            , @name
            , @type
            , @is_disabled
            , @defaultdb
            , @hasaccess
            , @denylogin
      
      END

    CLOSE login_curs
    
    DEALLOCATE login_curs
    
    RETURN 0

end
GO

Source Control

github

DanielAdeniji/sp_help_revlogin
Link

SQL Server – Database Scoped Configurations

Background

Earlier touched on reviewing database compatibility level via sys.databases’ compatibility_level column.

That post is here.

Database Scoped Configuration

In v2016, there is also a new component known as “Database Scoped Configuration” that can be investigated and tuned to review and compare database performance.

Areas

Areas currently exposed includes:

  1. Clearing procedure cache
    • Legacy
      • DBCC FREEPROCCACHE
        • Works against entire SQL Instance or specific plans
  2. MAXDOP parameter for specific databases ( OLTP )
    • Legacy
      • SQL Instance
        • sp_configure ‘max degree of parallelism’
      • Query Hint
        • OPTION ( MAXDOP 1)
  3. Set the query optimizer cardinality estimation model independent of the database compatibility level
    • Legacy :- v2008 thru 2012
    • Current :- v2014
  4. Enable or disable parameter sniffing at the database level
    • Legacy
      • Trace Flag 4136
        • SQL Server 2008 R2 CU2, SQL Server 2008 SP1 CU7 and SQL Server 2005 SP3 CU9 introduce trace flag 4136 to disable the “parameter sniffing” process
          Link
  5. Enable or disable query optimization hotfixes at the database level
    • Legacy
      • Trace Flag
        • SQL Server query optimizer hotfix trace flag 4199 servicing model
          Link
  6. Enable or disable the identity cache at the database level.
    • Legacy
      • Trace Flag
        • Trace Flag 272 disables batching of identity values

 

Read

To read Database Scoped configuration for the current database, please issue


use [dbname]
go

select 
        [database] = db_name()
      , tblSDSC.*

from   sys.database_scoped_configurations tblSDSC

Set LEGACY_CARDINALITY_ESTIMATION

Syntax

Syntax


use [database]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  


Sample


use [hrdb]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  


References

  1. Docs / SQL / T-SQL / Statements
    • ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
      Link
  2. Docs / SQL / Relational databases / System catalog views /
    • sys.database_scoped_configurations (Transact-SQL)
      Link

SQL Server – Database Compatibility Level

Background

Upgrading quite a bit of SQL Server Instances.

One of the areas to keep an eye on is the compatibility level of individual databases within each Instance.

Code

Read Compatibility Level

Credit

Crediting Nick Kavadias ( Link ) for his response on Stack Overflow:

How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
Link

SQL



/*
    How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
    https://stackoverflow.com/questions/1501596/how-to-check-sql-server-database-compatibility-after-sp-dbcmptlevel-is-deprecate

*/
select 

              [database] 
                = tblSD.[name]

            , [compatibilityLevel]
                = tblSD.[compatibility_level]

            , [version] = 
                    CASE [compatibility_level]
                        WHEN 65  THEN 'SQL Server 6.5'
                        WHEN 70  THEN 'SQL Server 7.0'
                        WHEN 80  THEN 'SQL Server 2000'
                        WHEN 90  THEN 'SQL Server 2005'
                        WHEN 100 THEN 'SQL Server 2008/R2'
                        WHEN 110 THEN 'SQL Server 2012'
                        WHEN 120 THEN 'SQL Server 2014'
                        WHEN 130 THEN 'SQL Server 2016'
                        WHEN 140 THEN 'SQL Server 2017'
                    END

from sys.databases tblSD

order by 
        tblSD.[name]

Set Compatibility Level

Legacy

sp_dbcmptlevel

Syntax


exec master..sp_dbcmptlevel 
         @dbname = @name 
       , @new_cmptlevel = @version 

Sample


exec master..sp_dbcmptlevel 
         @dbname = 'hrdb' 
       , @new_cmptlevel = 130

Modern

Alter Database / Set Compatibility Level

Syntax


alter database [db-name]
	set COMPATIBILITY_LEVEL = [compatibilityLevel]

Sample


alter database [hrdb]
	set COMPATIBILITY_LEVEL = 130

References

  1. Alter Database
    • ALTER DATABASE (Transact-SQL) Compatibility Level
      • Docs / SQL / T-SQL / Statements
        Link
    • sp_dbcmptlevel (Transact-SQL)
      • Docs / SQL / Relational databases / System stored procedures
        Link

SQL Server – Database Permissions – List

Background

There are different levels of permissions in SQL Server.

There are concentric permissions.

Inclusive are Instance, database, schema, and object.

And, others that are more dependant on the object type.

Database Permissions

Code


SELECT
            [database]
              = db_name()

         ,  [username] 
              = tblGranteeDP.[name]

         ,  [login]     
             = suser_sname(tblGranteeDP.[sid])

         ,  [permissionClass]
             = tblDP.class_desc

         ,  [permissionName] 
            = tblDP.[permission_name]

         ,  [permissionState] 
            = tblDP.state_desc

         ,  [principalType] 
            = tblGranteeDP.[type_desc]

FROM sys.database_permissions AS tblDP

INNER JOIN sys.database_principals AS tblGrantorDP   
    ON tblGrantorDP.principal_id = tblDP.grantor_principal_id

INNER JOIN sys.database_principals AS tblGranteeDP
    ON tblGranteeDP.principal_id = tblDP.grantee_principal_id

LEFT OUTER JOIN sys.server_principals AS tblGranteeSP
    ON tblGranteeDP.principal_id = tblGranteeSP.principal_id

/*
    Identifies class on which permission exists.
*/
where (tblDP.class = 0)

/*
    Exclude those with mere CONNECT permission
*/
and  tblDP.permission_name not in 
        (   
            'CONNECT'
        )

order by
      db_name() asc
    , tblGranteeDP.[name] asc
    , tblDP.[permission_name] asc

Output