SQL Server – Error – Operating system error 1453(Insufficient quota to complete the requested service)

Background

Reviewing a SQL Server Instance, and noticed a recurring error.

 

Error

SQL Server – Error – Operating system error 1453(Insufficient quota to complete the requested service)

 

Remediation

 

Outline

  1. Please review the setting for SQL Server – Max Memory
  2. Monitor SQL Server Performance and ensure that you have adequate memory on the box

SQL Server – Max Memory

 

Summary

The error ( 1453  – Insufficient quota to complete the requested service ), is an OS Error and so it is a sure indication that the SQL Server Instance is competing with the OS for resources.

I think you want to avoid that competition has much as possible.

SQL Server – Windows 32 bit – SQL Server v2014

Background

Reviewing Memory Utilization on one of our boxes.

 

Troubleshooting

Windows Version

Control Panel

System

Image

Explanation
  1. Windows Server 2008 Enterprise

Performance Monitor

Metrics

Metrics – [ SQL Server Instance ] – Memory Manager

Image

Textual

  1. Stolen Server Manager ( KB ) :- 448,400.000
  2. Target Server Memory ( KB ) :- 1,531,904.00
  3. Total Server Memory ( KB ) :- 1,516,648.00

 

SQL Server

Metadata

@@version

Image
Textual

Microsoft SQL Server 2014 (SP2-CU6) (KB4019094) – 12.0.5553.0 (Intel X86)
Jun 28 2017 14:05:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM)

 

Explanation
  1. We are running MS SQL Server 2014 / SP2 ( CU-6 )
  2. Enterprise Edition: Core-based Licensing on Windows NT 6.0 <X86>
    • Windows NT 6.0
      • Windows Server 2008
        Link
    • X86
      • 32 bit OS

Engineering

In terms of Engineering, can we do more.

Microsoft

AWE

The “awe enabled” SQL Server feature is deprecated.
Link

Summary

In Microsoft SQL Server 2008 R2, the configuration option, awe enabled, is deprecated. In the next version, Microsoft SQL Server 2012, this configuration option and the feature that uses this configuration option are removed from the product.

Therefore, when you upgrade from SQL Server 2008 R2 to the next version of SQL Server, you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server.

Operating System & SQL Server v2012+ Max Memory

SQL Server and operating system settings Maximum amount of memory used by SQL Server
32-bit SQL Server on 32-bit OS 2 GB
32-bit SQL Server on 32-bit OS with /3G boot option 3 GB
32-bit SQL Server on 64-bit OS 4 GB

Closing

As always keep in mind what your end goal is and what engineering constraints are holding you back.

SQL Server & PowerShell – Get Memory Utilization

Background

Earlier today I found myself trying to better understand a system.

As part of my quick read-up, I found a one line code that shows how to read memory utilization via Powershell.

Code

Overview

Here is that one line code in far too many lines.

Powershell


Set-StrictMode -Version Latest;
Import-Module SQLPS -DisableNameChecking

function displayPath($path)
{
	[object] $objGI = $null;
	[int] $physicalMemoryUsageInKB = 0;
	[int] $physicalMemoryUsageInMB = 0;

	$objGI = (gi $path) 
	
	#Get Data
	$displayName = $objGI.DisplayName;
	$physicalMemoryUsageInKB = $objGI.PhysicalMemoryUsageInKB;
	$physicalMemoryUsageInMB = $objGI.PhysicalMemoryUsageInKB / 1024;
	
	#Display Data       
	$strLog = "`tPath {0}" -f $path;        
	Write-Host $strLog
	
	$strLog = "`tInstance {0}" -f $displayName;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (KB) {0}" -f $physicalMemoryUsageInKB;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (MB) {0}" -f $physicalMemoryUsageInMB;     
	Write-Host $strLog      
	
	Write-Host
        
}

[string] $typeSQLEngine = "SqlServer";
[string] $defaultInstance = "MSSQLServer";
[string] $machineName = $null;
[int]    $iNamedInstanceStartingPos = 6;

# Get machine name
$machineName = $env:computername


#Get a managed computer instance  
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer  

#List out all sql server instances running on this machine  
foreach ($item in $mc.Services)
{

	if ($item.Type -eq $typeSQLEngine)
	{
		
		if ($item.Name -eq $defaultInstance)
		{
			$instanceName = "DEFAULT";
		}
		else
		{
		
		    $instanceNameLen = ($item.Name).length - $iNamedInstanceStartingPos;
			
			$instanceName = ($item.Name).Substring( `
													  $iNamedInstanceStartingPos `
													, $instanceNameLen `
												  );
			
		}
		
		$path = "SQLSERVER:\SQL\" + $machineName + "\" + $instanceName;
		
		displayPath $path
					
	} # if ($item.Type -eq $typeSQLEngine)
	
} 

Output

 

Source Code Control

GitHub

DanielAdeniji/SQLServerPS

Link

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 – HyperThread Ratio

Background

Trained Aireforge Studio against a set of SQL Server Instances that serve one of our one Applications.

 

AireForge Studio

Compare

Image

 

Explanation

The Numbers looked OK

  1. Max Server Memory
    • In Dev, we have 8 GB of RAM
      • We set our Max Memory to 6.2 ( max  server memory )
      • BTW, the number of actual memory is available via total_physical_memory
    • In QA/Test/Prod, we have 64 GB of RAM
      • We have set Max Memory to 58 GB
      • Again, the number of memory installed in box is displayed in the total_physical_memory
      • We have an average of 48 GB available in all three environments
  2. Total Page File in GB & Available Page File in GB
    • Will skip in depth discussion for now
    • Except to note that it is much smaller in Dev, where we have only 8 GB
  3. CPU Count
    • We have 4 CPUs in Dev
    • And, 8 in QA, Test, and Production
  4. HyperThread Ratio
    • In Dev, QA, and Production the Hyper Thread Ratio is 1
    • And, Test is 2

 

HyperThread Ratio

HyperThread Ratio?

Where is HyperThread coming from?

 

SysInternals

Process Explorer

Again, we go back on the SysInternals line, and get Process Explorer.

To get System Information, we click on the menu Item “View” \ “System Information”

TEST

Image

Explanation
  1. Raw
    • Cores is at 8
    • Sockets is at 4
  2. Interpretation
    • Hyperthread ratio is Cores / Sockets
      • 8 /4 = 2

 

 

Prod

Image

Explanation
  1. Raw
    • Cores is at 8
    • Sockets is at 8
  2. Interpretation
    • Hyperthread ratio is Cores / Sockets
      • 8 /8 = 1

Microsoft – SQL Server – Memory – Uptake Distribution

 

Microsoft – SQL Server – Memory – Uptake Distribution

 References:

  1.  What Data is in SQL Server’s Memory?
    http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
  2. Help understanding what is using all my memory – 64bit EE
    http://www.sqldev.org/sql-server-database-engine/help-understanding-what-is-using-all-my-memory–64bit-ee-85617.shtml
 
 
SELECT TOP 50                                   obj.[name]                                  , i.[name]                                    , i.[type_desc]  , count(*)AS Buffered_Page_Count , (count(*) * 8192.0000) / ((1024 * 1024) * 1.0000) as Buffer_MB                                  -- ,obj.name ,obj.index_id, i.[name]
   
FROM sys.dm_os_buffer_descriptors AS bd     INNER JOIN   ( SELECT object_name(object_id) AS name   , index_id , allocation_unit_id , object_id   FROM sys.allocation_units AS au   INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id   AND (au.type = 1 OR au.type = 2 OR au.type = 3)   ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id left JOIN sys.indexes i   on i.object_id = obj.object_id AND i.index_id = obj.index_id
 
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
 
 ORDER BY Buffered_Page_Count DESC