Basic SQL Server Tuning with Powershell
Bonjour,
lors de déploiement massif de SQL Server dans un environnement de lab j’ai eu besoin d’appliquer un léger tuning sur mes instances.
Du coup je vous file le script, on sait jamais 😉
[powershell]
<#
.SYNOPSIS
This Script will do a basic tuning for SQL Server 2005/2008(R2)/2012(?)
This won’t prevent you to go deeped in Tuning to cherish your server 😉
.DESCRIPTION
This script:
– Set the Max Memory
– Fix the TempDB Raw file size and growth
– Fix the TempDB Log file size and growth
– Add a TempDB Raw file for each CPU
.PARAMETER Instance
The instance name which you want to configure. (ex: VCDBVCENTER – . – 192.168.0.1WIKI)
.PARAMETER MaxMemory
This value is the Maximum memory you want to set as the MaxMemory for your instance
.Parameter TempDBSize
This value is the fixed size for TempDB raw/log file.
.Parameter TempDBMaxSize
This value is the maximum file size for TempDB raw/log file.
.Parameter TempDBFileGrowth
This value is the fixed size for the growth of TempDB raw/log file.
.NOTE
Author: Fabien Dibot – fabien.dibot@gmail.com
Twitter: @fdibot
Site web: http://posh.netau.net
Version 1.0
.EXAMPLE
C:PS> SQL_Server_Basic_Tuning.ps1 -MaxMemory 4096 -Instance “SERVERDBINSTANCE1”
.EXAMPLE
C:PS> SQL_Server_Basic_Tuning.ps1 -TempDBSize “512MB” -TempDBMaxSize “1024” -TempDBFileGrowth “128MB” -MaxMemory 4096 -Instance “.”
#>
param (
[Parameter(Mandatory=$false)]
[String]$TempDBSize = “256MB”,
[Parameter(Mandatory=$false)]
[String]$TempDBMaxSize = “512MB”,
[Parameter(Mandatory=$false)]
[String]$TempDBFileGrowth = “64MB”,
[Parameter(Mandatory=$true)]
[Int]$MaxMemory,
[Parameter(Mandatory=$false)]
[String]$Instance = “.”
)
$QueryMaxMemory = “EXEC sp_configure ‘show advanced options’, ‘1’
RECONFIGURE WITH OVERRIDE
EXEC sp_configure ‘min server memory’, ‘0’
EXEC sp_configure ‘max server memory’, ‘” + $MaxMemory + “‘
RECONFIGURE WITH OVERRIDE
EXEC sp_configure ‘show advanced options’, ‘0’
RECONFIGURE WITH OVERRIDE”
$QueryMaxMeroySetted = “select value FROM sys.configurations where Name = ‘max server memory (MB)'”
$TempDBadd = “USE [master]
GO
DECLARE @cpu_count int,
@file_count int,
@logical_name sysname,
@file_name nvarchar(520),
@physical_name nvarchar(520),
@size int,
@max_size int,
@growth int,
@alter_command nvarchar(max)
SELECT @physical_name = physical_name,
@size = size / 128,
@max_size = max_size / 128,
@growth = growth / 128
FROM tempdb.sys.database_files
WHERE name = ‘tempdev’
SELECT @file_count = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = ‘ROWS’
SELECT @cpu_count = cpu_count
FROM sys.dm_os_sys_info
WHILE @file_count < @cpu_count — Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
BEGIN
SELECT @logical_name = ‘tempdev’ + CAST(@file_count AS nvarchar)
SELECT @file_name = REPLACE(@physical_name, ‘tempdb.mdf’, @logical_name + ‘.ndf’)
SELECT @alter_command = ‘ALTER DATABASE [tempdb] ADD FILE ( NAME =N”’ + @logical_name + ”’, `
FILENAME =N”’ + @file_name + ”’, SIZE = ‘ + CAST(@size AS nvarchar) + ‘MB, MAXSIZE = ‘ + `
CAST(@max_size AS nvarchar) + ‘MB, FILEGROWTH = ‘ + CAST(@growth AS nvarchar) + ‘MB )’
PRINT @alter_command
EXEC sp_executesql @alter_command
SELECT @file_count = @file_count + 1
END”
$TempDBmodify = “ALTER DATABASE tempdb MODIFY FILE ( NAME = N’tempdev’, SIZE = ” + $TempDBSize + “, MAXSIZE = ” + $TempDBMaxSize + “, FILEGROWTH = ” + $TempDBFileGrowth + ” )”
$TempLogDBmodify = “ALTER DATABASE tempdb MODIFY FILE ( NAME = N’templog’, SIZE = ” + $TempDBSize + “, MAXSIZE = ” + $TempDBMaxSize + “, FILEGROWTH = ” + $TempDBFileGrowth + ” )”
# Load SQLServer cmdlets
# Those cmdlets are available with SSMS and/or on local SQL Server
Write-Debug “Load SQL Snappins”
Try {
add-pssnapin sqlserverprovidersnapin100 -ErrorAction Stop
add-pssnapin sqlservercmdletsnapin100 -ErrorAction Stop
Write-Debug “SQL Snappins loaded”
}
Catch {
Write-Debug $_.Exception.Message
}
# TempDB Tuning
# Will create a TempDB raw file of static value, for each cpu.
Try {
Try {
Invoke-Sqlcmd -Query $TempDBmodify -Serverinstance $Instance -ErrorAction Stop
Write-Debug “TempDB raw tuning”
}
Catch {
Write-Debug $_.Exception.Message
}
Try {
Invoke-Sqlcmd -Query $TempLogDBmodify -Serverinstance $Instance -ErrorAction Stop
Write-Debug “TempDB Log tuning”
}
Catch {
Write-Debug $_.Exception.Message
}
}
Catch {
Write-Debug $_.Exception.Message
}
Finally {
Invoke-Sqlcmd -Query $TempDBadd -Serverinstance $Instance -ErrorAction Stop
Write-Debug “Creation of all tempDB files”
}
# MaxMemory Tuning
# Will set the Memory to avoind system lagging
Try {
Invoke-Sqlcmd -Query $QueryMaxMemory -Serverinstance $Instance -ErrorAction Stop
Write-Debug “Setting Max Memory”
}
Catch {
Write-Debug $_.Exception.Message
}
Finally {
$MaxmemSet = Invoke-Sqlcmd -Query $QueryMaxMeroySetted -Serverinstance $Instance -ErrorAction Stop
if ($MaxmemSet.value = $MaxMemory) {
Write-Debug “Verify max memory value”
} else {
Write-Debug “Max Memory value is not $($MaxMemory)”
}
}
[/powershell]
En espérant que cela vous server autant qu’à moi.
Powershellement.
PS: Téléchargez le script ici
Bonjour Fabien,
j’ai un faible pour les here string en lieu et place de la concaténation.
La construction suivante :
Try {
add-pssnapin sqlserverprovidersnapin100 -ErrorAction Stop
add-pssnapin sqlservercmdletsnapin100 -ErrorAction Stop
Write-Debug “SQL Snappins loaded”
}
Catch {
Write-Debug $_.Exception.Message
}
Ne sert à rien, si ce n’est capturer l’exception et l’annuler.
De plus le cmdlet Write-Debug sert à la mise au point pas à la gestion d’exception. Le flux Error est déjà renseigné pas besoin d’en ajouter.
J’utiliserais plutot Get-PSSnapin pour déterminer si les préquis nécessaires au script sont remplis.
Enfin l’usage de du paramètre ErrorAction
add-pssnapin sqlserverprovidersnapin100 -ErrorAction Stop
se trouve annulé par le Try/Catch…
Dans ce cas j’ai pour pratique de laisser l’appelant gérer cette exception, par exemple sous Orchestrator.
Voir aussi ce post:
http://powershell-scripting.com/index.php?option=com_joomlaboard&Itemid=76&func=view&id=11783&catid=14
M’est avis que quelques corrections s’imposent !
ps
Salut Laurent,
Merci pour le get-pssnapin j’arrivais pas a mettre la main dessus ^^
Sinon là, je ne cherche en aucun cas à faire de la gestion d’erreur avec le write debug, mais juste à suivre l’avancement de mon tuning => pas d’affichage d’erreurs -> Pas d’erreurs !
On parle d’un script one-shot fait pour un env de lab, mais je note les remarques concernant ErrorAction pour mes futurs créations !
@+