The SQL Server memory leak myth

If you glance at the Processes tab in Windows Task Manager on your DB Server, you can see sqlservr.exe process uses huge amount of memory.  Is that SQL Server has a memory leak? No.

SQL Server is designed to use memory in large quantities, in order to cache information, such as recently used data pages. This improves performance by reducing the need to consistently read data from disk (a much slower and more expensive operation than accessing it from memory).

SQL Server will use as much memory as you can give it, and it will not release the memory that it has allocated under normal operations, unless the operating system has set the memory low resource notification flag, which triggers SQL Server to reduce its memory allocations.

However, SQL Server offers two instance-level memory settings that can be used to control how memory is allocated to, and removed from, the buffer pool. They are,

·     min server memory - specifies the minimum size to which SQL Server can shrink the buffer pool when under memory pressure; it does not specify the minimum amount of memory that SQL Server will initially allocate.

·     max server memory – specifies the maximum amount of memory that SQL Server can use for the buffer pool, which is primarily used for caching data pages in memory.

It is recommended that the max server memory is set appropriately for a SQL instance, based on workload, in order to ensure that the Windows OS still has sufficient available memory, to prevent the OS low memory notifications.

To Change max server memory

Right Click on Instance name in Object Explorer -> Select Properties -> Select memory page in server properties -> Change server memory options.

How to delete files older than N days from a folder using PowerShell script? How to schedule this script with Windows scheduled task?

      For System admin, Server Disk/drive management is one of the tedious task when he manages hundreds of servers. System admin deletes temporary files, log files, backup files, and junk files etc. to free disk space when he face disk full issue.

     Windows PowerShell is powerful tool which can be used in combination with scripts. Suppose you want to delete 10 days older files from a folder (say C:\IISLogs) recursively (it goes subfolders recursively). Copy below script in notepad and save with “ps1” file extension (e.g. DelOlderFiles.ps1 saved in folder C:\script).

$a = Get-ChildItem "C:\IISLogs" -recurse foreach($x in $a)     {         $y = ((Get-Date) - $x.LastWriteTime).Days                 if ($y -gt 10  -and $x.PsISContainer -ne $True)                 {                       $x.Delete()                 }     }

Execute following command in PowerShell console to change Executions Policy as “RemoteSigned”.

Set-ExecutionPolicy RemoteSigned

To run script from PowerShell console, go to the folder (C:\script) and run the script as follow,

PS C:\script> .\ DelOlderFiles.ps1

How to schedule script with Windows Task scheduler?

Create task. Don’t forget to select “Run whether user is logged on or not” option.

Schedule task.

Define action as follows.

Save Task. You have done!

How to find out “Current Connections” and “Current Anonymous Users” on web server (IIS)?

The Win32_PerfFormattedData_W3SVC_WebService formatted data class provides data from performance counters that monitor the World Wide Web Publishing Service.

Definition:

• CurrentConnections - Current number of connections established with the web service.
• CurrentAnonymousUsers - Number of users who currently have a connection using any of the web service.

Execute following commands one by one in powershell prompt (powershell) to get “CurrentConnections” and “CurrentAnonymousUsers”,

Get-WmiObject -Class Win32_PerfFormattedData_W3SVC_WebService -ComputerName <Your server hostname> | Where {$_.Name -eq "_Total"} | % {$_.CurrentConnections}

Get-WmiObject -Class Win32_PerfFormattedData_W3SVC_WebService -ComputerName <Your server hostname> | Where {$_.Name -eq "_Total"} | % {$_.CurrentAnonymousUsers}

Replace <Your server hostname> with hostname of your web server.

For more knowledge about Win32_PerfFormattedData_W3SVC_WebService properties, go through following link,

https://msdn.microsoft.com/en-us/library/aa394298%28v=vs.85%29.aspx