Memory configuration in SQL Server

  1. Review the edition of your SQL Server.

    MS SQL Server Express
    If you are running MS SQL Server Express, calculate the maximum memory as follows:
    SQL Server maximum memory = System memory in MB - 512 MB
    SQL Server Express edition has a limitation of 1024 MB (1 GB) of memory. If the resulting value is over 1024 MB (1 GB), use 1024 MB (1 GB) instead.  
     

    32-bit versions of MS SQL Server
    For 32-bit versions, use the same calculation as for MS SQL Express:

    SQL Server maximum memory = System memory in MB - 512 MB


    64-bit version of MS SQL Server
    Calculate the memory setting in the following formula:

    SQL Server maximum memory = System memory in MB - OS memory in MB


    OS memory depends on the System memory. To calculate OS memory, use the following formula:

    OS memory in MB = (((System memory in MB / 1024) * 64) + 1024)

    For System memory over 48 GB, use 4096 MB as the value for OS memory in MB.

      

  2. Set the value as the Maximum memory for the SQL Server.

  3. Restart the SQL Server to apply changes.

Comments

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'...
Read more

SSAS Cube or Database backup with Powershell command "Backup-AsDatabase"

Image
Run the full code from Load assembly section. # Load the assembly #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null # Create the server object $server = New-Object Microsoft.AnalysisServices.Server $server . Connect ( "localhost" ) # Or your SSAS instance name #Date Converion with minutes and seconds $date = ( get-date ).ToString( "MM_dd_yyyy_hh_mm_ss" ) $Server = New-Object Microsoft.AnalysisServices.Server $Server .Connect( "MAPS\TEST" ) $dbservers = $Server .Databases| Select-Object Name,ParentServer $dbservers | ForEach -Object{ $inddb = $_ #The "Backup-AsDatabase" is especially for SSAS cube backup and downloaded from "SQLServer" powershell module Backup-ASDatabase -BackupFile "E:\MAPS_TEST\SSAS_BACKUP\NEW\ $( $ inddb.Name) _ $( $ date) .abf" -Name $inddb .Name -Verbose -Server $inddb .ParentServer Write-Host "SSAS cube backup [ $( $ i...
Read more

AG SECONDARY DATABASE "Synchronizing" Query

The query below will tell us how much of the REDO log needs to harden in the secondry replica of secondary database. Run the query in SECONDARY REPLICA. If the "redo_queue_size" in KB value reducing means the database soon will come into "Synchronized"state have patience and wait for it. select is_primary_replica, synchronization_state_desc, is_commit_participant, synchronization_health_desc, redo_queue_size from  sys.dm_hadr_database_replica_ states
Read more