SQL Server – OS Bitness – 32 or 64 bit ?

Background

A while ago I googled on how to determine whether I am running on a 32 or 64 bit OS and found contemplative suggestions.

But, bar to implementation was a bit higher than I was willing to pay.

 

SSMS

And, so I was delighted when a few days ago, I rediscovered how Microsoft’s own SQL Server Management Studio ( SSMS) approaches same.

They invoke master..xp_MSVer; an extended stored procedure.

 

Master..xp_MSVer

SQL

Result in Rows


set nocount on
go

set XACT_ABORT on
go

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

create table #SVer
(
       [Index]				int
    ,  [Name]				sysname
    ,  [Internal_Value]		int
    ,  [Value]				nvarchar(512)
)
insert #SVer 
(
       [Index]
    ,  [Name]
    ,  Internal_Value
    ,  [Value]
)
exec master.dbo.xp_msver
            
select 
        
          tblSV.[Name]
        , tblSV.[Value]

from #SVer tblSV

where [Name] in
(
      'WindowsVersion'
    , 'Platform'
)

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

Result in Columns



set nocount on
go

set XACT_ABORT on
go

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

create table #SVer
(
       [Index]				int
    ,  [Name]				sysname
    ,  [Internal_Value]		int
    ,  [Value]				nvarchar(512)
)
insert #SVer 
(
       [Index]
    ,  [Name]
    ,  Internal_Value
    ,  [Value]
)
exec master.dbo.xp_msver

; with cteSV
as
(
    select 
              tblSV.[Name]
            , tblSV.[Value]

    from #SVer tblSV

    where [Name] in
    (
          'WindowsVersion'
        , 'Platform'
    )

)
select *

from (

        select 
                   [WindowsVersion]
                 , [Platform]

        from cteSV

        PIVOT
        (
            max(Value)

            for [Name] in
            (
                   [WindowsVersion]
                 , [Platform]
            )
        ) as tblA

    ) tblA

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

 

Output

x32

x64

 

SQL Server – Querying for OS Version

Background

As SQL Server now runs on Linux it is likely a good idea to be able to determine the underlying OS that a specific instance is sitting on.

 

Choices

Here are some choices for determining a targeted SQL Instance OS Version :-

  1. @@version
  2. sys.dm_os_host_info
    • SQL Server Versions
      • v2017 and above

 

System Function

@@version

Syntax


select @@version

Output

Tabulated
@@version OS/Client OS/Server
 Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) – 14.0.3015.40 (X64)
Dec 22 2017 16:13:22
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 <X64> (Build 14393: )
Windows 10
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) – 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)
Windows Server 2016
Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
Windows 8.1 Windows Server 2012 R2
Microsoft SQL Server 2014 – 12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)
Windows Vista Windows Server 2008

 

Dynamic Management View

sys.dm_os_host_info

Syntax


SELECT 
          host_platform
	, host_distribution
	, host_release
	, host_service_pack_level
	, host_sku
	, os_language_version  

FROM sys.dm_os_host_info

;

Output

 

Summary

If you happen to be running SQL Server v2017, you will have not do to all the parsing involved with getting accurate data from @@version.

 

Addendum

Did a similar post here.

This was back in 2010.  And, that post is titled “MS SQL Server – Read OS Version“.

Windows NT 6.3

Unfortunately “Windows NT 6.3” is returned for the following OSes:

  1. Windows 2016
  2. Windows 2012

Prove

Windows Server 2016

SQL Server

master.dbo.xp_MSver
Image

Explanation
  1. Name :- WindowsVersion
  2. Internal Value :- 364118016
  3. Character Value :- 6.3 (14393)

 

WinOS

systeminfo
Image

Blogging

Blogging is an interesting option.

How much to reveal and how much to slay.

It all depends on what the original intent is.

Arguing for the sake of arguing is pointless.

 

References

  1. Microsoft
    • SQL Server
      • Docs > SQL > Relational databases > System dynamic management views
        • sys.dm_os_host_info ( Transact SQL )
          Link
      • Docs > SQL > T-SQL > Functions
        • @@version ( Transact SQL )
          Link
      • Docs / SQL / Relational databases / System stored procedures

SQL Server 2017 – Developer Edition – ISO

Background

Currently, I have SQL Server 2014 Developer Edition on my laptop.

Recently found out that since my machine is now Windows 10, I can have SQL Server 2017 Developer Edition.

 

Download ISO

Let us go download v2017 Developer Edition.

v2017 is available here.

Available

Image

Textual

  1. Free Trial Evaluation
  2. Developer edition
  3. Express edition

Developer Edition

If we click on the “Download now” button underneath the “Developer edition“, we will notice that the name of the file that will be availed is SQLServer2017-SSEI-Dev.exe.

Already have that file and it is a small file.

I don’t really want a small file, I want the full ISO.

 

Self-Prepare ISO

Download Bootstrap

To get a nice ISO, please process with downloading SQLServer2017-SSEI-Dev.exe.

 

Prepare ISO

Outline

  1. Select an installation type
  2. Specify SQL Server Installer download
    • Which package will you like to download
      • ISO
      • CAB
    • Click on the Browser folder button
      • Choose an existing folder or create a new folder
      • Click the OK button
    • Click the OK button
  3. Download Media

Screen shot

Select an Installation Type

 

Specify SQL Server Installer download

Browse Folder

Download Media

Download Successful

 

Installed

Once the files was packaged into a nice ISO, was able to install it.

 

Conclusion

So unfortunately, it appears that one cannot download v2017 as a nice offline ISO.

But, please do not let that overly deter you.

Go ahead and download SQLServer2017-SSEI-Dev.exe and run it.

You will be able to prepare an ISO through running that package.

“Remote Server Administration Tools” And OS Compatibility

Background

I have a new machine that has MS Windows 10 on it and it giving my a lot of headache.

Active Directory Users and Computer

This morning I realized that I need “Microsoft Active Directory Users and Computers“.

As I did not see it, I said I need to go ahead and install it.

 

Remote Server Administration Tools

Active Directory Users and Computer is a MMC ( Microsoft management Console ) that is bundled within “Remote Server Administration Tools“.

 

Remote Server Administration Tools for Windows 7 with Service Pack 1 (SP1)

Download

Googled for “Active Directory Users and Computers” and landed here.

Downloaded both x64 ( Windows6.1-KB958830-x64-RefreshPkg.msu ) bit and 32 ( Windows6.1-KB958830-x86-RefreshPkg.msu ) bit.

Install

Error

Textual

Windows Update Standalone Installer

Installer encountered an error: 0x80096002

The certificate for the signer of the message is invalid or not found

 

 

Remote Server Administration Tools for Windows 10

Download

Googled for “Active Directory Users and Computers” for Windows 10 and landed here.

Here is what is available:

OS Bitness Filename
Windows 10
x64 WindowsTH-RSAT_WS_1709-x64.msu
x32 WindowsTH-RSAT_WS_1709-x86.msu
Windows ( Previous version of Windows )
x64 WindowsTH-RSAT_WS2016-x64.msu
x32 WindowsTH-RSAT_WS2016-x86.msu

 

Running Windows 10, and not previous Windows Version, downloaded the x64  Version for Windows 10 ( WindowsTH-RSAT_WS_1709-x64.msu ).

Installed

As I have a 64-bit OS, launched the 64-bit installer.

Restart Machine

Please restart your machine.

 

Summary

There is likely consensus that an error message that reads “The certificate for the signer of the message is invalid or not found” could mean so many things.

For instance:

  1. The Vendor’s certificate is compromised and thus the certificate has been pulled

Thankfully in this case it simply means the binary targets a particular OS.  And, that OS is not this one.

And, so look to see if you can find a binary that targets your specific OS.

DBCC MemoryStatus

Background

Wanted to place another stone in our pond of water where we review Memory Usage in MS SQL Server Instance.

DBCC MemoryStatus

Sample Output

Text File

Sample content when we run “dbcc memusage” and capture the output into a text file.

Notepad++

Here is what things look like in Notepad++.

 

Others Work

Wanted to see how others have consumed “dbcc memusage“.

Slava Murygin

Took to the Internet and found a nice post by Slava Murygin.

It is here.

 

Revision

Here is a revised version…



SET NOCOUNT ON
GO


declare @tblMemoryStatusDump TABLE
(
      [id] INT IDENTITY(1,1) PRIMARY KEY
    , [dump] VARCHAR(600)

);


declare @tblMemoryStatus TABLE
(
     id			INT
   , [measure]  VARCHAR(200) 
   , [counter]  VARCHAR(300) 
   , [value]	VARCHAR(200)
   , [rowType]  char(1)
   , [dump]		varchar(600) 

);

declare @serverName  sysname
declare @queryFormat nvarchar(600)
declare @query       nvarchar(600)

declare @tblMemoryStatusHeader TABLE
(
    [seqNumber] smallint not null
        identity(1,1)

    , [id]   int not null
    
    , [dump] nvarchar(600) not null

    , [rangeStart] int null
    , [rangeEnd]   int null

)

set @queryFormat = 'xp_cmdshell ''sqlcmd -E -S %s -Q "DBCC MEMORYSTATUS" ''';

/*

    sys.xp_cmdshell
    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 19]
    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
    because this component is turned off as part of the security configuration for this server. 
    A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. 
    For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

*/

/*

    exec sp_configure 'show advanced options',1
    reconfigure with override;

    exec sp_configure 'xp_cmdshell',1
    go

    reconfigure with override;

*/


set @serverName = cast (serverproperty('servername') as sysname)
exec master..xp_sprintf
          @query output
        , @queryFormat
        , @serverName

print @query

INSERT INTO @tblMemoryStatusDump
(
    [dump]
)
exec(@query)


insert into @tblMemoryStatus
(
       [id]
     , [dump]
     , [counter] 
     , [value] 

)
select 
          tblMSD.[id]

        , tblMSD.[dump]
        
        , [counter]
            = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 42
                )

        , [value]
            = ltrim(rtrim
              (
                substring
                    (
                        tblMSD.[dump]
                        , 43
                        , 20
                    )
              ))

from   @tblMemoryStatusDump tblMSD

update tblMSD

set	
            
       [rowType] 
            = case
                when tblMSDNext.[dump] like '---%' then 't' 
                when tblMSD.[dump] like '---%' then 'd' 
                when tblMSD.[dump] like '(%rows affected)' then 'c' 
                when tblMSD.[dump] like 'DBCC execution completed%' then 'i' 
                when tblMSD.[dump] is null then '0'
                else 'v'
              end
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatus tblMSDNext

        on tblMSD.[id] + 1  = tblMSDNext.[id]


insert into @tblMemoryStatusHeader
(
      [id] 
    , [dump]
)
select 
      tblMSD.[id]
    , [dump]
        = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 41
                )		

from   @tblMemoryStatus tblMSD

where  tblMSD.[rowType] = 't'

/*
    Get Range
*/
update tblMSH

set	
          [rangeStart] = tblMSH.[id]

        , [rangeEnd] = tblMSHNext.[id]

from   @tblMemoryStatusHeader tblMSH

inner join @tblMemoryStatusHeader tblMSHNext

        on tblMSHNext.[seqNumber] = tblMSH.seqNumber + 1

/*
    Get Range for last record
*/
update tblMSH

set		[rangeEnd] = ( select max([id]) from @tblMemoryStatus tblMSD )

from   @tblMemoryStatusHeader tblMSH

where   rangeEnd is null

/*
    Based on Range, set measure column
*/
update tblMSD

set			
       [measure] = tblMSH.[dump]
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatusHeader tblMSH

        on tblMSD.[id] between tblMSH.rangeStart and tblMSH.rangeEnd

where tblMSD.[rowType] = 'v'


/*
    remove extra record
*/
delete tblMS
 
from  @tblMemoryStatus tblMS

where tblMS.[rowType] in 
        ( 
              'd'
            , 'c'
            , '0'
            , 'i'
        )

select  
        tblMS.*

from   @tblMemoryStatus tblMS

where  tblMS.rowType in ( 'v')


 

Dedicated

Dedicated to Slava Murygin.

SQL Server – Perfmon – Memory

Background

Wanted to list the Perfmon counters to look at when evaluating SQL Server memory pressure.

Perfmon Metrics

Overview

Depending on the Operating System ( OS) and SQL Server Setting the accuracy of Perfmon Counters will vary.

If the account running the SQL Server Service has been granted “Lock Pages in Memory” Security Policy, then the following metrics do not show the full measure of Memory being used by the SQL Server Process.

Metrics:

  1. Task Manager
    • Private Working Memory
  2. PerfMon
    • Process / MS SQL Server Process  ( sqlservr.exe )
      • Working Set

 

Glossary

Category Counter Description
Paging File
%Usage The amount of the Page File instance in use in percent. See also Process\\Page File Bytes.
%Usage Peak The peak usage of the Page File instance in percent. See also Process\\Page File Bytes Peak.
Process ( sqlservr.exe )
Page Faults/sec  Page Faults/sec is the rate at which page faults by the threads executing in this process are occurring. A page fault occurs when a thread refers to a virtual memory page that is not in its working set in main memory. This may not cause the page to be fetched from disk if it is on the standby list and hence already in main memory, or if it is in use by another process with whom the page is shared.
Page File Bytes  Page File Bytes is the current amount of virtual memory, in bytes, that this process has reserved for use in the paging file(s). Paging files are used to store pages of memory used by the process that are not contained in other files. Paging files are shared by all processes, and the lack of space in paging files can prevent other processes from allocating memory. If there is no paging file, this counter reflects the current amount of virtual memory that the process has reserved for use in physical memory.
Private Bytes  Private Bytes is the current size, in bytes, of memory that this process has allocated that cannot be shared with other processes.
Virtual Bytes  Shows the current size, in bytes, of the virtual address space the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages. Virtual space is finite, and by using too much, the process can limit its ability to load libraries.
Working Set  Shows the current size, in bytes, in the Working Set of this process. The Working Set is the set of memory pages touched recently by the threads in the process. If free memory in the computer is above a threshold, pages are left in the Working Set of a process even if they are not in use. When free memory falls below a threshold, pages are trimmed from Working Sets. If they are needed, they will be soft-faulted back into the Working Set before leaving main memory.
Working Set – Private Working Set – Private displays the size of the working set, in bytes, that is use for this process only and not shared nor sharable by other processes.
SQL Server :- Memory Manager
Stolen Server Memory Amount of memory the server is currently using for the purposes other than the database pages.
Target Server Memory Ideal amount of memory the server is willing to consume
Total Server Memory Total amount of dynamic memory the server is currently consuming

 

 

Screen Shot

DB Server – 01

 

Metric

  1. Process :- MS SQL Server
    • IO
      • IO Data Bytes :- 1020
      • IO Other Bytes : – 370, 900
      • IO Read Bytes :- 0
      • IO Write Bytes :- 1020
    • Memory
      • Page Faults
        • Page Faults/Sec :- 2
      • Page Files
        • Page Files Bytes :- 53 GB bytes
        • Page Files Bytes Peak :- 53 GB bytes
      • Private Bytes
        • Private Bytes :- 53 GB bytes
      • Thread Count
        • Thread Count :- 138
      • Virtual Bytes
        • Virtual Bytes :- 95 million bytes
        • Virtual Bytes Peak :- 95 million bytes
      • Working Set
        • Working Set :- 1.128 Million bytes
        • Working Set – Private :- 1.060 Million bytes
        • Working Set – Peak :- 1.147 Million bytes
  2. SQL Server :- Memory Manager
    • Stolen Memory
      • Stolen Memory ( KB )
        • 8 GB
      • Target Memory ( KB )
        • 58.789 GB
      • Total  Memory ( KB )
        • 51.286 GB

Explanation

  1. Process – MS SQL Server
    • IO
      • Data
        • 1020
      • Other
        • 370 K
      • Read
        • 0
      • Write
        • 1020
    • Memory
      • Pages Files
        • 53 GB
      • Private
        • 53 GB
      • Virtual Bytes
        • 95 GB
          • Only Got knows what this means???
      • Working Set
        • Only shows memory absolved from lowest 4 GB
  2. SQL Server :- Memory Manager
    • Experiencing Stolen Memory of about 8 GB
    • Our Total Memory usage is about 8 GB below Target

 

DB Server – 02

Image

 

Metric

  1. Process
    • IO
      • IO Data Bytes :- 4800
      • IO Other Bytes : – 22,000
      • IO Read Bytes :- 0
      • IO Write Bytes :- 4800
    • Memory
      • Page Faults
        • Page Faults/Sec :- 1
      • Page Files
        • Page Files Bytes :- 27 GB bytes
        • Page Files Bytes Peak :- 27 GB bytes
      • Private Bytes
        • Private Bytes :- 27 GB bytes
      • Thread Count
        • Thread Count :- 104
      • Virtual Bytes
        • Virtual Bytes :- 87 GB
        • Virtual Bytes Peak :- 88 GB
      • Working Set
        • Working Set :- 454 MB
        • Working Set – Private :- 383 MB
        • Working Set – Peak :- 465 MB
  2. SQL Server : Memory Manager
    • Stolen Memory (KB)
      • Stolen Memory (KB) :-  5 GB
    • Target Server Memory
      • Target Server Memory (KB) :-  26 GB
    • Total Server Memory
      • Total Server Memory ( KB) :- 26 GB

Explanation

  1. Process :- SQL Server
    • IO
      • Data
        • 5 K
      • Other
        • 21 K
      • Read
        • 0
      • Write
        • 4 K
    • Memory
      • Pages Files
        • 53 GB
      • Private
        • 53 GB
      • Virtual Bytes
        • 95 GB
          • Only Got knows what this means???
      • Working Set
        • Only shows memory absolved from lowest 4 GB
  2. SQL Server :- Memory Manager
    • Stolen Memory
      • Stolen Memory ( KB )
        • 5 GB
      • Target Memory ( KB )
        • 26 GB
      • Total  Memory ( KB )
        • 26 GB

 

DB Server – 03

Image

Image – #01

Image – #02

 

Metric

  1. Process
    • IO
      • IO Data Bytes :- 251 KB
      • IO Other Bytes : – 362 KB
      • IO Read Bytes :- 24 KB
      • IO Write Bytes :- 226 KB
    • Memory
      • Page Faults
        • Page Faults/Sec :- 28
      • Page Files
        • Page Files Bytes :- 19.716 GB bytes
        • Page Files Bytes Peak :- 21.229 GB bytes
      • Private Bytes
        • Private Bytes :- 19.716 GB bytes
      • Thread Count
        • Thread Count :- 123
      • Virtual Bytes
        • Virtual Bytes :- 26.415 GB
        • Virtual Bytes Peak :- 27 GB
      • Working Set
        • Working Set :- 19 GB
        • Working Set – Private :- 19 GB
        • Working Set – Peak :- 20.5 GB
  2. SQL Server : Memory Manager
    • Stolen Memory (KB)
      • Stolen Memory (KB) :-  
    • Target Server Memory
      • Target Server Memory (KB) :-  18.43 GB
    • Total Server Memory
      • Total Server Memory ( KB) :- 18.43 GB

Explanation

  1. Process :- SQL Server
    • IO
      • Data
        • 251 K
      • Other
        • 362 K
      • Read
        • 24 K
      • Write
        • 226 K
    • Memory
      • Pages Files
        • 19.716 GB
      • Private
        • 19.716 GB
      • Virtual Bytes
        • 26.940 GB
          • Only Got knows what this means???
      • Working Set
        • 19 GB
  2. SQL Server :- Memory Manager
    • Stolen Memory
      • Stolen Memory ( KB )
        • N/A
      • Target Memory ( KB )
        • 18.432 GB
      • Total  Memory ( KB )
        • 18.432 GB

Summary

How memory is acquired and tallied depends on whether the Account running SQL Server has been granted “Lock pages in Memory“.

In later editions of SQL Server, there are more memory performance counters such as :-

  1. SQL Server :- Memory Manager
    • Stolen Memory

Coincidentally, our third sample has “Locked Pages in Memory” off for the Account running SQL Server.  And, hence, it accurately reflects Working Set numbers.

 

References

  1. Microsoft
    • Developer Network
      • Job Object Details
        Link
    • Technet
      • Mark Russinovich
        • Pushing the Limits of Windows: Paged and Nonpaged Pool
          Link
  2. Perforce
    • Troubleshooting Performance Issues – Windows
      Link
  3. SQL Shack
    • Milena Petrovic
      • SQL Server memory performance metrics – Part 2 – available bytes, total server, and target server memory
      • Link
  4. Public MPWiki
    • Management Pack : Systems Management Server ( SMS )
      • SMS 2003 Perf Threshold: Paging File – %Usage > 98 over 3 hours Monitor
        Link