It is obvious if you have scripts that are like IF EXISTS: DROP: CREATE PROCEDURE, and run this on a production system that is calling those procedures at the same time, that some will fail when the hit that period between the DROP and the CREATE.
But what about an ALTER? Will an ALTER PROCEDURE cause a similar outage? You'd think not: of course not: it's not really touching anything. But how much money would you bet on there not being an edge-case that causes it to happen?
So out of curiosity I constructed a test.
Create Database TestAlter
Go
Use TestAlter
Go
Alter Procedure dbo.TryToChangeMe
As
Begin
Print 'Running!'
Waitfor Delay '00:00:01'
Print 'Done!'
End
Go
Exec dbo.TryToChangeMe
And of course, PowerShell for the load testing.
function Test-AlterProcedure {
param (
[bool]
$Wait_First = $true
)
Cls
Import-Module psasync
Import-Module SQLPS -DisableNameChecking
$maxThreads = 100
$sleepTimer = 1000
$asyncPipelines = @()
$asyncPool = Get-RunspacePool $maxThreads
for ($i = 1; $i -le $maxThreads; $i++) {
$asyncPipelines += Invoke-Async -RunspacePool $asyncPool -ScriptBlock { param($i)
$procedureText = @"
Alter Procedure dbo.TryToChangeMe
As
Begin
Print 'Running!'
Waitfor Delay '00:00:01'
Print 'Done!'
End
"@
try {
$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=.;Database=TestAlter;Integrated Security=True;Max Pool Size=200;")
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandTimeout = 60
if ($i -eq 49) {
"Will alter"
$cmd.CommandText = $procedureText
} else {
$cmd.CommandText = "Exec dbo.TryToChangeMe"
}
$cmd.ExecuteNonQuery()
$success = $true
} catch {
$success = $false
$_
}
$cmd.Dispose()
$con.Close()
} -Parameters "$i"
}
do {
$jobCountRunning = (Receive-AsyncStatus -Pipelines $asyncPipelines | Where-Object { $_.Status -eq "Running" }).Count
$jobCountCompleted = (Receive-AsyncStatus -Pipelines $asyncPipelines | Where-Object { $_.Status -eq "Completed" }).Count
Write-Progress -Id 1 -Activity "Waiting To Finish" `
-Status "Running Jobs" `
-CurrentOperation "$jobCountCompleted jobs completed, $jobCountRunning jobs left to go" `
-PercentComplete ($jobCountCompleted / $asyncPipelines.Count * 100)
Start-Sleep -Milliseconds $sleepTimer
} while ($jobCountRunning -ne 0)
$results = @()
$results = Receive-AsyncResults -Pipelines $asyncPipelines
$asyncPool.Close()
# Prepare some statistics
$jobCountTotal = (Receive-AsyncStatus -Pipelines $AsyncPipelines).Count
$jobCountCompleted = (Receive-AsyncStatus -Pipelines $AsyncPipelines | Where-Object { $_.Status -eq "Completed" }).Count
$jobCountFailed = (Receive-AsyncStatus -Pipelines $AsyncPipelines | Where-Object { $_.Status -eq "Failed" -or $_.Error }).Count
Write-Host "Processed $($asyncPipelines.Count) jobs with $jobCountTotal jobs" # Should equal
Write-Host "$jobCountCompleted Completed, $jobCountFailed Failed"
$results
}
Test-AlterProcedure
And the results?
Processed 100 jobs with 100 jobs 100 Completed, 0 Failed -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 Will alter -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 PS SQLSERVER>
Just as you suspected, no outage. Great! Sometimes you just have to try to be sure.