Day 22 of 31 Days of SQL Server Backup and Restore using PowerShell: Performance comparisons

So far we’ve been looking at the cool stuff we can do with SQL Server Backups, Restores and PowerShell. And I don’t know about you, but it seems look like it’s going to make life a lot easier. But, is there a down side?

Well, as we’re DBA’s they first thing we usually worry about is performance. So let’s run some raw speed tests to see if there’s any unwanted overhead. For a backup, most of the overhead is going to be the speed of the underlying drives, so all these tests are run on the same rig. Which is SQL Server 2012 running on Windows 2012 in a VirtualBox virtual machine, PowerShell is 3.0.

We need a sizeable DB so we can get some decent timings. Nothing fancy here:

USE [master]
GO
CREATE DATABASE [SpeedTest]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'SpeedTest', FILENAME = N'E:\speedtest\SpeedTest.mdf')
 LOG ON
( NAME = N'SpeedTest_log', FILENAME = N'E:\speedtest\SpeedTest_log.ldf')
GO
USE [SpeedTest]
GO
CREATE TABLE [dbo].[bigtable1](
	[dummyID] [int] IDENTITY(1,1) NOT NULL,
	[bigdatat] [varchar](8000) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[bigtable2]    Script Date: 24/09/2013 15:05:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bigtable2](
	[dummyID] [int] IDENTITY(1,1) NOT NULL,
	[bigdatat] [varchar](8000) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[bigtable3](
	[dummyID] [int] IDENTITY(1,1) NOT NULL,
	[bigdatat] [varchar](8000) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[bigtable4](
	[dummyID] [int] IDENTITY(1,1) NOT NULL,
	[bigdatat] [varchar](8000) NULL
) ON [PRIMARY]

GO

And then bloat the tables with junk:

insert into Bigtable1 (bigdatat) values (replicate('junk',2000))
insert into Bigtable2 (bigdatat) values (replicate('junk',2000))
insert into Bigtable3 (bigdatat) values (replicate('junk',2000))
insert into Bigtable4 (bigdatat) values (replicate('junk',2000))
go 100000

Which gives us a database of 3GB. Tiny in the real world sense, but large enough to give us some usable stats.

To get the T-SQL timings we use the following script:

create table backuptime(
ident integer,
starttime datetime,
endtime datetime
)

declare @i integer  =1;

while @i<20
begin
    declare @starttime datetime, @endtime datetime;
    set @starttime = CURRENT_TIMESTAMP
    backup database Speedtest to disk='e:\sizetest\backup.bak' with init
    select @endtime = current_timestamp
    insert into backuptime values (@i,@starttime,@endtime)
    set @i = @i+1
end

We create a holding table, and then loop through 20 backups recording the start and finish times in our table.

And to get our PowerShell timings:

Import-Module SQLPS -DisableNameChecking

$Database = "SpeedTest"
$ServerName = "WIN-C0BP65U3D4G"

$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)
$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database
$Db = $SQLSvr.Databases.Item($Database)
$BackupName = "e:\sizetest\speedtestps.bak"
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$i=1

$times = @()

while ($i -lt 20){
    $StartTime = get-date
    $Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $Backup.Initialize = $True

    $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
    $Backup.Database = $db.Name

    $BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)

    $Backup.Devices.Add($BackupDevice)
    $Backup.SqlBackup($SQLSvr)
    $Backup.Devices.Remove($BackupDevice)

    remove-variable backup
    $times += new-timespan $StartTime $(get-date)
    $i++
}

We create the connection to SQL Server outside of our loop, as this would be the most costly part of the script but is only realy needed once for each connection to the server. The backup runs the same Full backup as our T-SQL version. We use the New-TimeSpan cmdlet to calculate the interval between start and finish, then store that in an array which we can later analyse through Measure-Object to get maximum, minimum and average.

So after running each procedure 3 times to get a consistent average what did we find?

From the T-SQL runs I got an average of 48.18s, with a longest of 55.59s, shortest of 45.94s and a standard deviation of 1.42s (Full result data here)

And from the PowerShell runs I got an average of 58.56s, with a longest of 72.91s, shortest of 56.48s and a standard deviation of 2.1s (Full result data here)

So PowerShell appears slower here. It’s certainly more variable (greater spread between max and min, and a larger Standard deviation means in general the times are across a general range ignoring the extremes).

Not looking great at the moment. So lets try doubling the size of the database and see if the differences stay consistent. Just rerun the insert block again. This time I’m just going to run the timings once each

Now the T-SQL runs give me an average of 111.8s, max of 118.59s, minimum of 107.87s and a standard deviation of 2.16s (Full result data here)

And PowerShell gives me an average of 110.48s, max of 121.1s, minimum of 107s and a standard deviation of 2.78s (Full result data here)

So now it shows that they’re pretty much even. Which shows that the PowerShell overhead is in creating the object and then sending it to a remote server

Hopefully that’s put some minds at rest about any potential performance issues using these PowerShell backup and restore methods over the more convential SQL Server methods.

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.

T-SQL Backups time results

starttimeendtimeTime difference (ms)
24/09/2013 15:24:2324/09/2013 15:25:1955593
24/09/2013 15:25:1924/09/2013 15:26:0747823
24/09/2013 15:26:0724/09/2013 15:26:5447363
24/09/2013 15:26:5424/09/2013 15:27:4248213
24/09/2013 15:27:4224/09/2013 15:28:3047576
24/09/2013 15:28:3024/09/2013 15:29:1746646
24/09/2013 15:29:1724/09/2013 15:30:0346326
24/09/2013 15:30:0324/09/2013 15:30:5148476
24/09/2013 15:30:5124/09/2013 15:31:3947656
24/09/2013 15:31:3924/09/2013 15:32:2646546
24/09/2013 15:32:2624/09/2013 15:33:1347756
24/09/2013 15:33:1324/09/2013 15:34:0147716
24/09/2013 15:34:0124/09/2013 15:34:4745940
24/09/2013 15:34:4724/09/2013 15:35:3547993
24/09/2013 15:35:3524/09/2013 15:36:2449250
24/09/2013 15:36:2424/09/2013 15:37:1450020
24/09/2013 15:37:1424/09/2013 15:38:0348333
24/09/2013 15:38:0324/09/2013 15:38:5148286
24/09/2013 15:38:5124/09/2013 15:39:3948086
24/09/2013 16:32:1924/09/2013 16:33:0647543
24/09/2013 16:33:0624/09/2013 16:33:5448000
24/09/2013 16:33:5424/09/2013 16:34:4147250
24/09/2013 16:34:4124/09/2013 16:35:3048350
24/09/2013 16:35:3024/09/2013 16:36:1747543
24/09/2013 16:36:1724/09/2013 16:37:0547426
24/09/2013 16:37:0524/09/2013 16:37:5247296
24/09/2013 16:37:5224/09/2013 16:38:4048030
24/09/2013 16:38:4024/09/2013 16:39:2949056
24/09/2013 16:39:2924/09/2013 16:40:1546010
24/09/2013 16:40:1524/09/2013 16:41:0246876
24/09/2013 16:41:0224/09/2013 16:41:4947360
24/09/2013 16:41:4924/09/2013 16:42:3747286
24/09/2013 16:42:3724/09/2013 16:43:2649096
24/09/2013 16:43:2624/09/2013 16:44:1347810
24/09/2013 16:44:1324/09/2013 16:45:0147183
24/09/2013 16:45:0124/09/2013 16:45:4847423
24/09/2013 16:45:4824/09/2013 16:46:3647953
24/09/2013 16:46:3624/09/2013 16:47:2448056
25/09/2013 07:32:5425/09/2013 07:33:4247663
25/09/2013 07:33:4225/09/2013 07:34:3047906
25/09/2013 07:34:3025/09/2013 07:35:1949300
25/09/2013 07:35:1925/09/2013 07:36:0848623
25/09/2013 07:36:0825/09/2013 07:36:5547323
25/09/2013 07:36:5525/09/2013 07:37:4449240
25/09/2013 07:37:4425/09/2013 07:38:3449756
25/09/2013 07:38:3425/09/2013 07:39:2348940
25/09/2013 07:39:2325/09/2013 07:40:1248746
25/09/2013 07:40:1225/09/2013 07:40:5947420
25/09/2013 07:40:5925/09/2013 07:41:4748020
25/09/2013 07:41:4725/09/2013 07:42:3547773
25/09/2013 07:42:3525/09/2013 07:43:2550163
25/09/2013 07:43:2525/09/2013 07:44:1651130
25/09/2013 07:44:1625/09/2013 07:45:0548266
25/09/2013 07:45:0525/09/2013 07:45:5348846
25/09/2013 07:45:5325/09/2013 07:46:4249130
25/09/2013 07:46:4225/09/2013 07:47:3249333
25/09/2013 07:47:3225/09/2013 07:48:1947490

PowerShell Backup Time results

Time difference (s)
57.30
56.48
59.92
57.85
57.72
57.99
58.43
57.15
57.75
57.57
58.08
57.57
57.92
58.24
58.85
59.68
57.91
58.89
56.84
58.23
59.27
58.78
58.26
58.18
57.62
59.20
58.96
57.80
57.94
57.56
58.80
57.45
56.66
59.02
59.24
58.72
57.96
58.11
72.91
59.89
58.91
58.84
58.52
59.15
59.22
58.59
57.33
58.93
58.23
58.58
58.37
58.10
60.17
58.09
58.15
56.82
59.31

T-SQL Results, 6GB database

StartTimeEndTimeDifference(ms)
2013-09-25 09:42:18.5632013-09-25 09:44:17.153118590
2013-09-25 09:44:17.1602013-09-25 09:46:08.480111320
2013-09-25 09:46:08.4802013-09-25 09:47:58.923110443
2013-09-25 09:47:58.9232013-09-25 09:49:46.793107870
2013-09-25 09:49:46.7932013-09-25 09:51:38.323111530
2013-09-25 09:51:38.3232013-09-25 09:53:28.560110236
2013-09-25 09:53:28.5602013-09-25 09:55:21.393112833
2013-09-25 09:55:21.3932013-09-25 09:57:11.690110296
2013-09-25 09:57:11.6902013-09-25 09:59:03.420111730
2013-09-25 09:59:03.4202013-09-25 10:00:56.600113180
2013-09-25 10:00:56.6002013-09-25 10:02:48.073111473
2013-09-25 10:02:48.0732013-09-25 10:04:38.913110840
2013-09-25 10:04:38.9132013-09-25 10:06:30.870111956
2013-09-25 10:06:30.8702013-09-25 10:08:23.490112620
2013-09-25 10:08:23.4902013-09-25 10:10:16.580113090
2013-09-25 10:10:16.5802013-09-25 10:12:08.380111800
2013-09-25 10:12:08.3802013-09-25 10:14:02.007113626
2013-09-25 10:14:02.0072013-09-25 10:15:51.490109483
2013-09-25 10:15:51.4902013-09-25 10:17:42.697111206

PowerShell results 6GB database

Time (s)
121.08
111.15
110.59
110.47
109.39
109.23
110.79
109.87
110.14
109.96
110.59
107.47
109.18
110.67
109.81
110.96
110.46
110.38
106.99
Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *