SQL Server – Undocumented – Function Calls / Day 01

Background

Profiling our database system and noticed a call to sp_MSreset_synctran_bit.

Wanted to review a bit.

sp_MSreset_synctran_bit

Code


 BEGIN TRANSACTION
    -- Unmark synctran bit
    select @tabid = object_id(@qualified_name)
    if not (@tabid is null)
    begin
        IF ISNULL(OBJECTPROPERTY(@tabid, 'TableIsMemoryOptimized'), 0)  1
        Begin
            EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
            --EXEC %%Object(MultiName = @qualified_name).LockExclusiveMatchID(ID = @tabid)
            if @@error = 0
                EXEC %%Relation(ID = @tabid).SetSyncTranSubscribe(Value = 0)
        End
    end
    COMMIT TRANSACTION
    return(0)

Dissect

  1. EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
    • A Lock semaphore/mutex
      • passes along the Object ID
  2. EXEC %%Relation(ID = @tabid).SetSyncTranSubscribe(Value = 0)
    • Set Sync Transaction Subscriber
      • Reset value to 0
      • Likely means not synched

Not Familiar

Not familiar with the %% syntax.

Summary

Still undocumented.  Still unfamiliar.

But, like them men, women, and children standing in line this afternoon to get their citizenship the day started out one way.

But, prayerfully and graciously is ending on the other side.

Here is to all those who stood with and among them on this glorious journey.

SQL Server – Installation/Upgrade – Extract package files

Background

Here I am struggling to install Microsoft SQL Server.

It just will not install.

And, stubborness is not bailing me out.

Take a look at the files

Googled till I bled.

One of things I was asked to do was to take a look at the installation package.

Artifacts

RTM

For the RTM, which is delivered as an ISO, we are able to do the following :-

  1. Mount ISO
  2. Extract using 7-Zip

Service Packs ( SP ) and Cumulative patch ( CU )

Service Packs ( SP ) and Cumulative patches ( CU ) are delivered as an executable.

To uncompress them, please use the executable file itself and pass along the -extract argument.

Command Line

Syntax

[package] -extract:[target-folder]

Sample

SQLServer2017-KB4466404-x64.exe -extract:extract

Output

extract.20190313.1108AM.PNG

SQL Server – Installation – Error – “Cannot open registry key ‘HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance’. SQL Server performance counters are disabled.”

Background

Ever so often one runs into errors in the area of SQL Server and Performance Counters.

Errors

One of the places where the errors shows up is the MS Windows Event Viewer :-

MS Windows Event Viewer

Log Name :- Application

  1.  Event ID – 8316
    • Event ID :- 8316
    • Source :- MSSQLServer
    • Details :- Cannot open registry key ‘HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance’. SQL Server performance counters are disabled.
  2. Event ID – 8317
    • Event ID :- 8317
    • Source :- MSSQLServer
    • Details :- Cannot query value ‘First Counter’ associated with registry key ‘HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance’. SQL Server performance counters are disabled.
  3. Event ID – 3003
    • Event ID :- 3003
    • Source :- LoadPerf
    • Details :- Unable to install counter strings because the SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance key could not be opened or accessed. The first DWORD in the Data section contains the Win32 error code.
  4. Event ID – 3009
    • Event ID :- 3009
    • Source :- LoadPerf
    • Details :- Installing the performance counter strings for service MSSQLServer (SQL Server (MSSQLSERVER)) failed. The first DWORD in the Data section contains the error code.

Remediation

Outline

  1. SQL Server Instance
    • For that specific SQL Server Instance, locate folder where performance counter files are kept and reload them
  2. System
    • Reload OS System counters
      • In command window, change folder to %windows%\system32
      • Reload all OS System Counters
  3. Performance Counters
    • Identify disabled Performance Counters
  4.  Registry
    • In MS Windows Registry, repair or create missing folders and items

Tasks

SQL Server Instance

Script


 @echo off

setlocal

REM Please adjust for you specific SQL Server Instance
set "_folderTarget=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn"

set "_fileExtIni=*.ini"

set _appPgmList=echo

set _appPgmApply=lodctr

set "_service=mssqlserver"

ECHO Unload Performance Counter for %_service%

unlodctr "%_service%"

ECHO Performance Counter for %_service% unloaded

ECHO Targeted Folder %_folderTarget%
ECHO ==============================

forfiles /P "%_folderTarget%." /s  /m %_fileExtIni% /c ^"cmd /c ^
  %_appPgmList% ^
  file name is @FILE ^
^"

REM Apply lodctr

@echo
@echo

forfiles /P "%_folderTarget%." /s  /m %_fileExtIni% /c ^"cmd /c ^
   %_appPgmApply% @PATH ^
^"

endlocal

net stop "Remote Registry"
net start "Remote Registry"

net stop "Performance Logs & Alerts"
net start "Performance Logs & Alerts"

endlocal

OS

Outline

  1. LODCTR
    • Navigate to System Folders
      • SYSTEMROOT
        • %SYSTEMROOT%\SYSTEM32
        • %SYSTEMROOT%\SYSWOW64
    • Issue LODCTR /r
  2. Winmgmt
    • winmgmt.exe /RESYNCPERF

Performance Counters

Is Performance Counter Disabled ?

Script

Syntax

lodctr /q

Sample

lodctr /q | find /i "Performance Counters" | find /i "sql"

Output
Output – Textual

>lodctr /q | find /i "Performance Counters" | find /i "sql"
[.NET Data Provider for SqlServer] Performance Counters (Enabled)
[msftesql] Performance Counters (Enabled)
[msftesqlFD] Performance Counters (Enabled)
[msftesqlIDX] Performance Counters (Enabled)
[MSSQLSERVER] Performance Counters (Enabled)
[MSSQLServerOLAPService] Performance Counters (Enabled)
[SQLBrowser] Performance Counters (Enabled)
[SQLSERVERAGENT] Performance Counters (Enabled)

Output – Image

lodctr.q.20190309.0520PM

Explanation
  1. lodctr
    • If the Counter is disabled, it will be flagged, as such

 

Registry

Regedit

Using regedit, review registry entries

Outline

  1. Registry Key
    • Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
    • Please make sure the performance branches exists under the SQL Server services
      • Default Instance
        • MSSQLSERVER\Performance
        • SQLSERVERAGENT\Performance
      • Named Instance
        • MSSQL$[Instance-Name]\Performance
        • SQLSERVERAGENT$[Instance-Name]\Performance
    • Under each Performance branch, you want to look for
      • Engine
        • PerfIniFile
          • Default Instance
            • sqlctr.ini
          • Named Instance
            • perf-<instance name>sqlctr.ini
      • SQL Server Agent
        • PerfIniFile
          • Default Instance
            • sqlagtctr.ini
          • Named Instance
            • perf-<instance name>sqlagtctr.ini
  2. Create Performance node if absent
  3. Grant permissions to the account running the SQL Server Service ( Engine / Agent / etc )

Registry Entries

Registry Entries – SQL Server Engine
Registry Entries – SQL Server Engine – Initial

MSSQLServer.Engine.01.20190308.1105PM.PNG

Registry Entries – SQL Server Engine – Revised

performanceCounter.sqlServerEngine.20190309.0540PM.PNG

Registry Entries – SQL Server Agent
Registry Entries – SQL Server Agent – Initial

registry.sqlServerAgent.20190308.1101PM.PNG

 

Registry Entries – SQL Server Agent – Revised

performanceCounter.sqlServerAgent.20190309.0533PM.PNG

 

 

References

  1. loganathanvm
    • System counters corrupted issue or LODCTR /R Error Code 2.
      Link

SQL Server – Installation – DISM

Background

Trying to install Microsoft SQL Server, but ran into a familiar error.

Blockers

Microsoft SQL Server 2005 Setup

System Configuration Check

Images

IIS Feature Requirement

sqlServer.v2005.SystemConfigurationCheck.20190308.0558PM.PNG

ASP.Net Version Registration Requirement

sqlServer.v2005.SystemConfigurationCheck.ASPNetRegistration.20190308.0603PM.PNG

Prerequisite

Installation

DISM

Outline

  1. Get
    • dism /online /get-features
    • dism /online /get-featureinfo
  2. Enable Feature
    • Dism /online /Enable-Feature

Tasks

get-features
Syntax

dism /online /get-features 

Sample

dism /online /get-features | find /i "IIS-ASPNET" 

Output

Feature Name : IIS-ASPNET
Feature Name : IIS-ASPNET45

get-featureinfo
Syntax

dism /online /get-featureinfo 

Sample

dism /online /get-featureinfo /featurename:NetFx3

Output

Deployment Image Servicing and Management tool
Version: 10.0.14393.0

Image Version: 10.0.14393.2457

Feature Information:

Feature Name : NetFx3
Display Name : .NET Framework 3.5 (includes .NET 2.0 and 3.0)
Description : .NET Framework 3.5 (includes .NET 2.0 and 3.0)
Restart Required : Possible
State : Enabled

Custom Properties:

FWLink : http://go.microsoft.com/fwlink/?LinkId=296822

The operation completed successfully.
get-featureinfo
Syntax

Dism /online /Enable-Feature /FeatureName:(feature-name) 

Sample

Dism /online /Enable-Feature /FeatureName:NetFx3 /All

Output

Deployment Image Servicing and Management tool
Version: 10.0.14393.0

Image Version: 10.0.14393.2457

Enabling feature(s)
[==========================100.0%==========================]
The operation completed successfully.

Actual

Here is what we need to do to enable IIS Web Server and the specific features required by SQL Server Engine and Reporting Services.

Enable IIS-Web Server
Code

DISM /Online /Enable-Feature /FeatureName:IIS-WebServer /all
DISM /Online /Enable-Feature /FeatureName:IIS-WebServerManagementTools /all
DISM /Online /Enable-Feature /FeatureName:IIS-WebServerRole /all

Enable IIS-Features
Code

DISM /Online /Enable-Feature /FeatureName:IIS-StaticContent /all
DISM /Online /Enable-Feature /FeatureName:IIS-DefaultDocument /all
DISM /Online /Enable-Feature /FeatureName:IIS-HttpRedirect /all
DISM /Online /Enable-Feature /FeatureName:IIS-DirectoryBrowsing /all

DISM /Online /Enable-Feature /FeatureName:IIS-ASP /all
DISM /Online /Enable-Feature /FeatureName:IIS-ASPNET /all

DISM /Online /Enable-Feature /FeatureName:IIS-ISAPIExtensions /all
DISM /Online /Enable-Feature /FeatureName:IIS-ISAPIFilter /all

DISM /Online /Enable-Feature /FeatureName:IIS-WindowsAuthentication /all

DISM /Online /Enable-Feature /FeatureName:IIS-Metabase /all

DISM /Online /Enable-Feature /FeatureName:IIS-WMICompatibility /all

SQL Server – System Databases

Background

I was reviewing a database and noted that the distribution database is not named distribution, but TSRDistribution.

SQL Server Management Studio ( SSMS)

I wanted to see how SSMS is able to classify the database as a system database.

Image

ssms.outline.20190307.0836AM.PNG

Code

Outline

Ran a trace against the SQL Server Instance and noticed that SSMS issues the query pasted below.

What does the query do :-

  1. sys.databases
    • Name Match
      • Checks the database name and see if it matches
        • master
        • model
        • msdb
        • tempdb
    • Property match
      • Distributor
        • is_distributor

SQL


select
          dtb.[name]
        , dtb.database_id

from   master.sys.databases AS dtb

WHERE
        (
            CAST
                (
                    case
                        when dtb.name in ('master','model','msdb','tempdb') then 1
                        else dtb.is_distributor
                    end
                    AS bit
                ) = 1
        )

ORDER BY
    [name] asc

Summary

Confirmed that SSMS does not check against a specific rule, isSystem, to identify system databases.

SQL Server – Telemetry / QueryStore – Query Error

Background

As part of monitoring our SQL Server noticed an error.

Error

Here is the error :-

  1. Error Number :- 4104
  2. Error Text :- The multi-part identifier “qry.is_internal_query” could not be bound.

Query Text


SELECT db_id() AS database_id,
       COUNT_BIG(1) QueryCount,
       SUM(count_compiles) TotalCompiles,
       MAX(last_compile_duration) MaxLastCompileDuration,
       MIN(last_compile_duration) MinLastCompileDuration,
       AVG(last_compile_duration) AvgLastCompileDuration,
       SUM(last_compile_duration) TotalLastCompileDuration
FROM sys.query_store_query AS Qry
WHERE qry.last_execution_time
         >= DATEADD(HOUR, -24, GETDATE())
  AND qry.is_internal_query = 0

Troubleshoot

The error was straight forward enough to troubleshoot.

Remedy

Outline

  1. Our database is case-sensitive
    • Replace alias Qry with qry

SQL


SELECT db_id() AS database_id,
       COUNT_BIG(1) QueryCount,
       SUM(count_compiles) TotalCompiles,
       MAX(last_compile_duration) MaxLastCompileDuration,
       MIN(last_compile_duration) MinLastCompileDuration,
       AVG(last_compile_duration) AvgLastCompileDuration,
       SUM(last_compile_duration) TotalLastCompileDuration

-- FROM sys.query_store_query AS Qry

FROM sys.query_store_query AS qry

WHERE qry.last_execution_time
        >= DATEADD(HOUR, -24, GETDATE())

AND qry.is_internal_query = 0