TSQL to suggest optimal MaxServerMemory value

A while ago a colleague of mine wrote a TSQL script to help with suggesting a MaxServerMemory value and a little less time ago we worked together to re-write that script for SQL 2012. While I haven't "checked" it against SQL 2014 the principals are the same, and the reason I am blogging it today is because I am always looking for the script or URL to direct people to so this post makes that easier :)

Of cause there is no black and white recommendation for MaxServerMemory as it is workload dependent. However this script can assist with understanding what that workload requirement is within a SQL Server Instance, please ensure you carefully consider all other memory needs of the server such as additional SQL Instances, and services such as AV, Backups, Monitoring, etc.

SET NOCOUNT ON;

DECLARE 
--@pg_size INT, 
@Instancename varchar(50),
--@RecMem int,
@MaxMem int,
@MaxRamServer int,
@sql varchar(max),
@SQLVersion tinyint


SELECT @SQLVersion = @@MicrosoftVersion / 0x01000000  -- Get major version

-- SELECT physical_memory_kb as ServerRAM_KB from sys.dm_os_sys_info
-- SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
-- SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Optimal MaxServermemory Setting for SQL Server instance ' + @@SERVERNAME  + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' +  SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4)  + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'

IF @SQLVersion = 11
BEGIN
 PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
 PRINT 'Total Memory on the Server (MB)' 
 EXEC sp_executesql N'set @_MaxRamServer= (select physical_memory_kb/1024 from sys.dm_os_sys_info)', N'@_MaxRamServer int OUTPUT', @_MaxRamServer = @MaxRamServer OUTPUT
 Print @MaxRamServer
 PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
END
ELSE
IF @SQLVersion in (10,9)
BEGIN
 PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
 PRINT 'Total Memory on the Server (MB)' 
 EXEC sp_executesql N'set @_MaxRamServer= (select physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info)', N'@_MaxRamServer int OUTPUT', @_MaxRamServer = @MaxRamServer OUTPUT
 Print @MaxRamServer
 PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
END
ELSE 
BEGIN
 PRINT 'Script only supports SQL Server 2005 or later.'
 RETURN
END

--SELECT @RecMem=physical_memory_kb/1024 from sys.dm_os_sys_info

SET @MaxMem = CASE 
    WHEN @MaxRamServer < = 1024*2 THEN @MaxRamServer - 512  /*When the RAM is Less than or equal to 2GB*/
    WHEN @MaxRamServer < = 1024*4 THEN @MaxRamServer - 1024 /*When the RAM is Less than or equal to 4GB*/
    WHEN @MaxRamServer < = 1024*16 THEN @MaxRamServer - 1024 - Ceiling((@MaxRamServer-4096) / (4.0*1024))*1024 /*When the RAM is Less than or equal to 16GB*/

 -- My machines memory calculation
 -- RAM= 16GB
 -- Case 3 as above:- 16384 RAM-> MaxMem= 16384-1024-[(16384-4096)/4096] *1024
 -- MaxMem= 12106

    WHEN @MaxRamServer > 1024*16 THEN @MaxRamServer - 4096 - Ceiling((@MaxRamServer-1024*16) / (8.0*1024))*1024 /*When the RAM is Greater than or equal to 16GB*/
     END
 SET @sql='
EXEC sp_configure ''Show Advanced Options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory'','+CONVERT(VARCHAR(6), @maxMem)+';
RECONFIGURE WITH OVERRIDE;'

PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'Optimal MaxServerMemory Setting for this instance of SQL' 
Print (@sql) 
PRINT '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'

Ping back to Tara S. Jana's site http://sqlmasters.jimdo.com/scripts-tsql


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Demo preparation - Removing unwanted Schema's from AdventureWorks

Those of us who's roles require them to conduct demonstrations and presentations of SQL Server regularly will be familiar with the AdventureWorks databases. I must admit this has been my go to demo database for a while now. However some times the whole schema is just too much and all I really want is a subset of the schema for a demonstration.

The problem is because of the number of Constraints, Foreign Keys, and Views in the AdventureWorks schema this is no simple task (as it shouldn't be). I was faced with this issue today and so have created the following statements to help with generating the drop statements. These are based on the schema's that the objects belong to. For my needs I only wanted the HumanResources and Person schemas.

Important... backup first if you care about your data and..... DO NOT DO THIS IN PRODUCTION.....

--Constraints
SELECT sys.schemas.name as 'Schema'
 , sys.tables.name as 'Table'
 , sys.check_constraints.name as 'Constraint'
 , 'ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name + '] DROP CONSTRAINT [' + sys.check_constraints.name + ']' AS 'Drop Stmt'
FROM sys.check_constraints
INNER JOIN sys.tables ON sys.tables.object_id = sys.check_constraints.parent_object_id
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'

 
--Foreign Keys
SELECT sys.schemas.name as 'Schema'
 , sys.tables.name as 'Table'
 , sys.foreign_keys.name as 'ForeignKey'
 , 'ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name + '] DROP CONSTRAINT [' + sys.foreign_keys.name + ']' AS 'Drop Stmt'
FROM sys.foreign_keys
INNER JOIN sys.tables ON sys.tables.object_id = sys.foreign_keys.parent_object_id
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'


--Views
SELECT sys.schemas.name as 'Schema'
 , sys.views.name as 'View'
 , 'DROP VIEW [' + sys.schemas.name + '].[' + sys.views.name + ']' AS 'Drop Stmt'
FROM sys.views
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.views.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'


--Tables
SELECT sys.schemas.name as 'Schema'
 , sys.tables.name as 'Table'
 , 'DROP TABLE ' + sys.schemas.name + '.' + sys.tables.name  AS 'Drop Stmt'
FROM sys.tables 
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'
ORDER BY sys.schemas.name, sys.tables.name 

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Ready, Set, Go.....Format, Encrypt, and Prepare a Removable USB Drive using BitLocker and PowerShell

In my line of work protecting customer data is extremely important, and with that in mind unfortunately some times there is just no way to retrieve data for analysis without using a USB Drive. This presents some important requirements for any removable drive used in this way and for me that includes regularly performing three main tasks:

1. Format the drive (erase any previous customers data)
2. Encrypt the drive
3. Copy any tools etc back onto the drive

So recently I have been working on automating this with PowerShell, through the use of the BitLocker and Storage modules.

Introducing the USB Drive Wipe Prepare project.

What you will need:

• PowerShell 3+
• BitLocker Module (recommend at least Windows 8, Windows 2012)

While the complete script can be found here I will focus this post on the challenges I faced with building such solution so that if you are working on a similar project you can benefit from my hard work :)

The most challenging aspect of this was working with Bitlocker CmdLets, so they are my main focus.

The first step is to format the drive with Format-Volume.

$Result = Format-Volume -ObjectId $($Volume.ObjectId) -FileSystem $($Volume.FileSystem) -NewFileSystemLabel $($Volume.FileSystemLabel);

The next step is to encrypt the volume with BitLocker. This involves a number of steps.

1. Firstly, due to my employers GPO setting (and a best practice) I must add a recovery password key to the drive.

   $Result = Add-BitLockerKeyProtector -MountPoint "$($Volume.DriveLetter):" -RecoveryPasswordProtector
   
   

2. As part of this it is best practice to then make sure you have the Recovery Key saved off to a location. Earlier in the script I create a PSDrive to reference this location and simplify scripting.

   "Bitlocker Key for $($Volume.FileSystemLabel)`r`n `
   Identifier: $((Get-BitLockerVolume "$($Volume.DriveLetter):").KeyProtector.KeyProtectorId)`r`n `Key: $((Get-BitLockerVolume "$($Volume.DriveLetter):").KeyProtector.RecoveryPassword)" | Out-File -FilePath "BitLockerKeys:\$($Volume.FileSystemLabel).BitLockerKey.txt";
   
   

3. Next I enable BitLocker on the Removable Drive with a Password (effectively using BitLocker2Go)

   $Result = Enable-BitLocker -MountPoint "$($Volume.DriveLetter):" -EncryptionMethod Aes256 -UsedSpaceOnly -Password $BitLockerPassword -PasswordProtector;
   
   

4. As the encryption process can take some time the next part of my script checks the status of the protection with

   while ((Get-BitLockerVolume -MountPoint "$($Volume.DriveLetter):").EncryptionPercentage -lt 100)
   ....
   
   

After encrypting the drive my script then copies files/folders which I have stored in a common path on my laptop for use on most customer engagements. This is a specific need for my line of work however the functionality could be used for anything. I retrieve the path from a XML configuration file during the Begin block of the script, and if that file doesn't exist then it is created. The user can also supply a "-Setup" switch parameter to force the script to prompt for the configuration settings and rebuild the config XML file.

This script is provided "as is" however should you be performing similar operations around Encrypting removable drives this may help you towards your solution.

As mentioned above the complete script can be found on the CodePlex project https://usbdrivepreptool.codeplex.com/




Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
 

Format Operator vs Format Parameter for Date Time

So most of my PowerShell scripts include logging or export functionality which requires the current date/time to be in a particular format. So this got me thinking, just which method actually performs better.

Get-Date Format Parameter

Command:

Get-Date -Format {yyyyMMddhhmmss}

Individual Times (ms):  0.9507, 0.3538, 0.3797, 0.2996, 0.2996, 0.3575, 0.1991, 0.2011, 0.1297, 0.1625
Overall Avg (ms): 0.33333

Format Operator

Command:

"{0:yyyy}{0:MM}{0:dd}{0:hh}{0:mm}{0:ss}" -f (Get-Date)

Individual Times (ms):  1.6881, 0.4051, 1.2866, 0.7377, 0.1761, 0.4794, 0.9889, 0.7307, 0.5377, 0.8777
Overall Avg (ms): 0.7908


And the winner is....... Get-Date with the Format parameter, most likely because the formatting is conducted early in the stack, (i.e. closer to the .Net framework layer). However if you look at some of the individual results there isn't much in it at times.


The full script used to perform this test is as follows:

[array]$Results = @();

for ($i=0; $i -lt 10; $i++)
{
   $Results += Measure-Command {
      Get-Date -Format {yyyyMMddhhmmss}
   } | select TotalMilliseconds;
   #helps give real results by letting the system pause for a moment
   Start-Sleep -Seconds 1;
}
Write-Host "Get-Date format paramter results"
$Results

[array]$Results = @();
for ($i=0; $i -lt 10; $i++)
{
   $Results += Measure-Command {
      "{0:yyyy}{0:MM}{0:dd}{0:hh}{0:mm}{0:ss}" -f (Get-Date)
   } | select TotalMilliseconds;
   #helps give real results by letting the system pause for a moment
   Start-Sleep -Seconds 1;
}
Write-Host "Format operator results"
$Results

PowerShell Amusements: Automated Speech

While setting up a lab environment today I had some time to kill and thought I would check out more recent methods for speech automation from within PowerShell. Some of the interesting aspects involved in this script are:

Get-Random - Used to select random members from the object
ArrayList object type - Used to allow the removal of array members

if ($VerbosePreferenceOrig.length -eq 0) {$VerbosePreferenceOrig = $VerbosePreference;}
$VerbosePreference = "Continue";
Add-Type -AssemblyName System.Speech 
$Speech = New-Object -TypeName System.Speech.Synthesis.SpeechSynthesizer
$Speech.Volume = 100;
#create temp copies to work with
$Voices = New-Object System.Collections.ArrayList;
$Words = New-Object System.Collections.ArrayList;

#fill the arrays which will be static
Foreach ($Item in $Speech.GetInstalledVoices().VoiceInfo.Name)
{
    $Idx = $Voices.Add($Item);
}
$Idx = $Words.Add("Hello, welcome to PowerShell 4.0 Part 1");
$Idx = $Words.Add("Did you know PowerShell rocks!");
$Idx = $Words.Add("What you are hearing, is written in PowerShell");
$Idx = $Words.Add("We are going to have some fun this week");
$Idx = $Words.Add("With dot net we can do a great deal");
$Idx = $Words.Add("Don't worry, use Get-Help");

#create temp copies to work with
$TmpVoices = New-Object System.Collections.ArrayList;
$TmpWords = New-Object System.Collections.ArrayList;

#fill the temporary arrays
foreach ($item in $Voices) {$Idx = $TmpVoices.Add($item);}
foreach ($item in $Words) {$Idx = $TmpWords.Add($item);}

For ($loop=0; $loop -lt 10; $loop++)
{
    Write-Verbose "Starting loop $loop";
    #make sure we don't completely empty the arrays
    if ($TmpVoices.Count -le 0) {foreach ($item in $Voices) {$Idx = $TmpVoices.Add($item);};}
    if ($TmpWords.Count -le 0) {foreach ($item in $Words) {$Idx = $TmpWords.Add($item);};}
    #randomly get the voice and words to say
    $Voice = Get-Random -InputObject $TmpVoices;
    $Word = Get-Random -InputObject $TmpWords;
    #remove these from the selection for less repetition (
    $TmpVoices.Remove($Voice);
    $TmpWords.Remove($Word);
    Write-Verbose "Voice: $($Voice)";
    Write-Verbose "Words: $($Word)";
    $Speech.SelectVoice($Voice);
    $Speech.Speak($Word);
    Start-Sleep -Seconds 3;
}
Write-Host "Script Done!";

$VerbosePreference = $VerbosePreferenceOrig;

Accidental feature discovery... RDP client

Today I accidentally stumbled across a feature of the latest RDP client (6.3.9600.16384).... typically when using multiple monitors (laptop + DVI monitor) I end up with different resolutions between the monitors and so when I establish a RDP session in full screen it defaults to my main monitor (laptop), so if i wanted it to be full screen on the 2nd monitor I would have to manually set the resolution before connecting.

Well today when I connected in full screen on my main monitor (laptop) I thought "oh well i will live with a smaller window on my 2nd screen" and dragged the RDP window to the 2nd screen which snaps to a smaller window. Then I hit the maximise button to return it to the resolution I connected in and to my amazement it went full screen. And just to prove this was changing resolutions on me I dragged it back to the main monitor and maximised it, and then again to my 2nd monitor.

So it would seem the RDP client is finally able to adjust the resolutions in full screen mode. I had hoped this wasn't far away with the "smart sizing" feature that was introduced a little while ago.

TechEd Australia 2014 being revamped

Well I had secretly been wondering how much longer TechEd Australia would be held on the Gold Coast, mostly as living in Brisbane this made it very accessible for me and I have enjoyed many years of attending the event as both an attendee and most recently as event staff. Now it is set for a revamp and will be more localized to other capital cities. Of cause there is going to always be positives and negatives to any change, but overall I see this as a positive evolution for the event in this fast changing world of IT.

For the full official details check out http://www.microsoft.com/en-au/teched/teched-2014/default.aspx

For those in Brisbane who are sad, don't worry there will be plenty of user groups and community events over the year to keep us fed with technology and provide great networking opportunities. One in particular I am looking forward to again this year is Infrastructure Saturday which I am sure will be bigger and better again this year.