PwrShell.net

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