SQL Server – v2017 – Install – “Oracle JRE 7 Update 51 (64-bit) is required for Polybase”

Background

Installing SQL Server v2017 and ran into an error that I have seen before.

Error

Error Image

OracleJRE7Update51_x64.2018109.1111AM.PNG

Error Textual

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

Tackled Before

History

Already tackled before here:

SQL Server (v2016) – Installation – Blocked – “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase”
here

History

The fix applied was to install Java JRE downloaded from here:

https://www.oracle.com/technetwork/java/javase/downloads/index.html
Link

But, unfortunately even though install applied no help this time.

Trouble Shooting

Let us dig a bit deeper.

SysInternals

Process Monitor

Image

sysInternals.processMonitor.20181009.1010AM.PNG

Explanation

  1. Identified component used by SQL Server Setup
    • Component is ScenarioEngine
  2. ScenarioEngine
    • Log
      • C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20181009_092126\Detail.txt

Tail

Tail For Win32

Download

Downloaded Tail For Win32 from here:

Tail for Win32
Link

Usage

Launched “Tail For Win32” and loaded the identified SQL Server Log file ( Detail.txt )

Results
Image
TailforWin32_Usage_20181009_1151AM
Textual

13) 2018-10-09 10:13:06 Slp: Init rule target object: Microsoft.SqlServer.Configuration.Polybase.Polybase_IsMinJavaVersionInstalledFacet
(13) 2018-10-09 10:13:06 SQLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
(13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.
(13) 2018-10-09 10:13:06 SQLPolyBase: Rule 'Polybase_IsMinJavaVersionInstalled' detection result: IsMinJavaVersionInstalled= False
(13) 2018-10-09 10:13:06 Slp: Evaluating rule        : Polybase_IsMinJavaVersionInstalled
(13) 2018-10-09 10:13:06 Slp: Rule running on machine:
(13) 2018-10-09 10:13:06 Slp: Rule evaluation done   : Failed
(13) 2018-10-09 10:13:06 Slp: Rule evaluation message: This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from https://go.microsoft.com/fwlink/?LinkId=526030.
(13) 2018-10-09 10:13:06 Slp: Send result to channel : RulesEngineNotificationChannel

Textual
  1. QLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    (13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.

Registry

regedit

Images

Regedit – Image – Before
Image

registry_20181009_1038AM.PNG

Textual
  1. Computer\HKEY_LOCAL_MACHINE\Software\JavaSoft
    • Java Plug-in
    • Java Update
    • Java Web Start
    • Java Web Start Caps
    • JRE
Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    • Java Runtime Environment
      • Missing

Remediation

Outline

Please download Java JRE 8 and install it.

Download

Please download artifacts from here :-

Java SE Runtime Environment 8 Downloads
https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
Link

Artifacts

Image

Java SE Runtime Environment 8u181

download_JavaSERuntimeEnvironment_20181009.1221PM.PNG

download_20181009.1026AM.PNG
Explanation
  1. Chose to download Windows X64

 

Install

Images

Destination Folder

download_DestinationFolder_20181009.1031AM.PNG

Installing
Installing -01

install_20181009.1032AM.PNG

java Setup – Complete

install_JavaSetupComplete_20181009.1033AM.PNG

Change in License Terms

download_JavaSERuntimeEnvironment_Roadmap_20181009.1030AM.PNG

Registry

Using regedit, please review the Java’s registry structure.

regedit

Images
Regedit – Image – After
Image

registry_java_jre_1Dot8_20181009_1243PM.PNG

Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion is now present

 

Summary

It appears that Oracle has changed the folder structure for Java in the Windows Registry.

Specifically what used to be

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.

is now

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\JRE\CurrentVersion.

Please be sure to have Java JRE v1.8 ( Java 8) installed for SQL Server v2016\v2017 Polybase.

SQL Server :- sys.dm_db_stats_histogram

Background

As always pressed to grasp something that my very little mind has not been able to understand.

And, that is how to read Statistics Histogram.

SQL

Thankfully Microsoft is always furnishing us with new pathways towards understanding.

sys.dm_db_stats_histogram

Today that way for me is via sys.dm_db_stats_histogram.

The DMV was introduced in v2016 SP1.

Syntax

It takes the ObjectID and Statistics ID and assumes that you are in the contextual database.


select *

from  sys.dm_db_stats_histogram
(
       @objectID
     , @statisticsID
)

Returned Record Set

We will make heavy use of the word range and define it as the preceding row’s range_high_key and our current row’s range_high_key.

Column Meaning Datatype
step_number Step Number from 1 to Maximum of 200 smallint
range_high_key Highest for specific step sql_variant ( Depends on data-type of first stats column)
range_rows Number of records in range real
equal_rows Number of rows that exactly match range_high_key real
distinct_range_rows Number of distinct records in range bigint
average_range_rows Number of records in range / Number of distinct records in range real

Usage


use [DBLab]
go

declare @table sysname
declare @objectID int
declare @index sysname
declare @indexID smallint

set @table = '[dbo].[sales]'
set @index = 'INDX_SalesDate'

set @objectID = object_id(@table)

select

      @indexID = index_id

from  sys.indexes tblSI

where  tblSI.object_id = @objectID

and    tblSI.[name] = @index

DBCC SHOW_STATISTICS
(
       @table
     , @index
)
with histogram

;

select
         [object]
         =
             quotename(tblSS.[name])
           + '.'
           + quotename(tblSO.[name])

       , [stat]
          = tblSS2.[name]

       , tblSH.step_number
       , tblSH.range_high_key
       , tblSH.range_rows
       , tblSH.equal_rows
       , tblSH.distinct_range_rows
       , tblSH.average_range_rows

from   sys.dm_db_stats_histogram
       (
           @objectID
         , @indexID
       ) tblSH

inner join sys.objects tblSO
   on tblSH.object_id = tblSO.object_id

inner join sys.schemas tblSS
   on tblSO.schema_id = tblSS.schema_id

inner join sys.stats tblSS2
   on  tblSH.object_id = tblSS2.object_id
   and tblSH.stats_id = tblSS2.stats_id

 

Output

sysDOTdm_db_stats_histogram_20180530_0642PM

Workshop

Let us pick up on the second row and see if we can understand what each column means.

Image

sysDOTdm_db_stats_histogram_20180530_0647PM

Explanation

  1. Step Number
    • Definition
      • There are at most 200 steps in a SQL Server Statistics Histogram
    • Step Number :- 1
      • Low Bar
    • Step Number :- 200 or last row
      • High Bar
  2. Range High Key
    • Definition
      • This is the maximum value of the current row’s range
    • Step Number :- 2
      • 1900-01-01 16:17:00.000
  3. Range Rows
    • Definition
      • This is the number of rows between the previous row’s max value and the current row’s max value
    • Step Number :- 2
    • 1016
  4. Equal Rows
    • Definition
      • This is the number of rows that exactly match this row
    • Step Number :- 2
    • 5
  5.  Distinct Range Rows
    • Definition
      • This is the number of rows that have distinct values for the current range
    • Step Number :- 2
    • 625
  6. Average Range Rows
    • Definition
      • Number of rows / number of rows that have distinct values for the current range
    • Step Number :- 2
    • 1.6256

Collaborative Query

We will instruct a full update statistics scan against our test table and issue a query against the first column of our statistics.

We will then compare the result set against what is listed in our histogram.

Query


set nocount on;
go

declare @dateRangeBegin datetime
declare @dateRangeEnd   datetime

set @dateRangeBegin = '1900-01-01 00:00:00.000'
set @dateRangeEnd = '1900-01-01 16:17:00.000'

select
         [numberofRows]
            = count(*)

       , [numberofEqualRows]
        = sum(
                case
                    when clock_time = @dateRangeEnd then 1
                    else 0
                    end
             )

       , [numberofDistinctRows]
            = count(distinct [clock_time])

       , [averageDistinctRows]
            = cast
                (
                    (
                          count(*) *1.00
                        / NULLIF
			(
			    count(distinct clock_time) * 1.00
			   , 0
			)
                    )
                    as decimal(20, 6)
                )

from   [dbo].[sales] tblI

where  tblI.clock_time
            between @dateRangeBegin and @dateRangeEnd

Output

sysDOTdm_db_stats_histogram_20180530_0704PM

Explanation

  1. Number of Rows
    select count(*) against table
    
  2. Number of Equal Rows
    sum
        (
           case
              when clock_time = @dateRangeEnd then 1
              else 0
             end
        )
    
  3. Number of Distinct Rows
    count(distinct [clock_time])
    
  4. Average Range Rows
    cast
    (
    	(
    		  count(*) *1.00
    		/ count(distinct clock_time) * 1.00
    	)
    	as decimal(20, 6)
    )
    

References

  1. Docs > SQL >Relational databases > System dynamic management views
    • sys.dm_db_stats_histogram (Transact-SQL)
      Link

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