AKAWN

What Determines the Installation Collation?

When installing SQL Server, why do you sometimes see that the Database Engine defaults to the SQL_Latin1_General_CP1_CI_AS collation and other times another value?

This value is determined by the system locale which can be found via Control Panel -> Clock and Region -> Region -> Administrative -> Language for non-Unicode programs.

Here in New Zealand we typically use the Latin1_General_CI_AS collation.

When the system locale is changed to “English (New Zealand)” a prompt to restart Windows is received.

After a restart, the result of this change means that the SQL Server installation will now default to Latin1_General_CI_AS.

SensitivityLabels To String Using PowerShell

Transforming the SensitivityLabels column to a comma separated string value using PowerShell can be tricky.

Here is one method to achieve it.

(SensitivityLabels | Out-String).Trim().Replace("`n",",").Replace("  ","")

A full example is:

Get-AzSqlDatabaseSensitivityRecommendation -ResourceGroupName $resource_group_name -ServerName $server_name -DatabaseName $database_name |
Select-Object @{Name='DatabaseName';Expression={if($_.DatabaseName -eq $null){''}else{$_.DatabaseName}}},
@{Name='ResourceGroupName';Expression={if($_.ResourceGroupName -eq $null){''}else{$_.ResourceGroupName}}},
@{Name='SensitivityLabels';Expression={if($_.SensitivityLabels -eq $null){''}else{($_.SensitivityLabels | Out-String).Trim().Replace("`n",",").Replace("  ","")}}},
@{Name='ServerName';Expression={if($_.ServerName -eq $null){''}else{$_.ServerName}}} |
ConvertTo-Csv -NoTypeInformation -Delimiter "|"

The output looks like:

"DatabaseName"|"ResourceGroupName"|"SensitivityLabels"|"ServerName"
"testdb"|"myrg01"|"SchemaName : jobs_internal,TableName: targets,ColumnName : elastic_pool_name,SensitivityLabel : Confidential - GDPR,InformationType: Name,Rank : Medium"|"myserver"

Is Azure Hybrid Benefit Worth it for Managed Instances?

Azure Hybrid Benefit (AHB) appears to offer great cost savings when pricing your new Managed Instance.

For this blog, I’m going to look at an entry level General Purpose 4 vCore instance.

Starting at https://azure.microsoft.com/en-us/pricing/calculator we can see that with AHB the price is USD$444.48/month.

Without AHB, the price goes up to USD$736.38/month and the SQL License cost is clearly shown as USD$291.90/month (USD$736.38 – USD$444.48) or US$3,502.80/year.

This means that without AHB we would pay an extra USD$3,502.80/year!

Ok lets take a step back.

To make use of AHB you required the Box product with Software Assurance (SA).

Taking a look at https://www.microsoft.com/en-us/sql-server/sql-server-2019-pricing we can see that a Standard Edition (equivalent of the Azure General Purpose Tier) 2 core pack costs USD$3,586 and therefore 2 of these, to get 4 core licenses, would cost USD$7,172.

[1] Pricing represents open no level (NL) estimated retail price.

In addition we require 2 of the SA licenses which would be USD$2,836/year (USD$1,418/year x 2).

This results in an outlay of USD$10,008 (USD$7,172 (Box) + USD$2,836 (SA)) for the 1st year to allow use of the AHB.

So based on the above, when do you break even if using AHB as opposed to not using AHB?

Just over 10 years, which is the standard life cycle of a Boxed product!

4 vCores	AHB	No AHB	Savings	Running Total
Year 1	10,008	3,502.80	6,505.20	6,505.20
Year 2	2,836	3,502.80	(666.80)	5,838.40
Year 3	2,836	3,502.80	(666.80)	5,171.60
Year 4	2,836	3,502.80	(666.80)	4,504.80
Year 5	2,836	3,502.80	(666.80)	3,838.00
Year 6	2,836	3,502.80	(666.80)	3,171.20
Year 7	2,836	3,502.80	(666.80)	2,504.40
Year 8	2,836	3,502.80	(666.80)	1,837.60
Year 9	2,836	3,502.80	(666.80)	1,170.80
Year 10	2,836	3,502.80	(666.80)	504.00
Year 11	2,836	3,502.80	(666.80)	(162.80)
Total	38,368	38,530.80	(162.80)

In summary and based on the above; if you have SA then yes use AHB and keeping renewing the SA for as long as you can. If you are starting with the need for new licenses, then using AHB doesn’t make sense.

Reporting Services Developer Edition

In case you are looking for it, when installing Reporting Services 2019 for development use the product key is:

22222-00000-00000-00000-0000

SQL Server on Azure VMs

Cost and performance is likely going to be your key considerations when placing your SQL Server on Azure.

Microsoft provides the following Performance guidelines for SQL Server in Azure Virtual Machines and SQL server best practices to optimize performance in Azure Stack documents which give a comprehensive overview of configuring SQL Server on Azure VMs.

As I’ve been working quite a bit on Azure lately, I thought I’d list a few of my findings.

Picking a Virtual Machine

At the time of this blog, there were 172 VM sizes to choose from.

The key considerations for a SQL Server VM relate to:

CPUs (Potentially impacts license costs)
Memory (The more memory the better as much faster than disk)
Disk capacity (Sufficient disks space to store your databases/backups)
Disk speed (Impacts data read/write access performance)

So with this in mind we can narrow down suitable VMs.

If the goal is to get a SQL Server 2017 Standard Edition VM with 4 vCPUs (minimum core license requirement) and 128GB RAM (Edition limit) we end up with a E16-4s_v3.

SQL Server 2017 Standard Edition VM option with 128GB Ram

The E16-4s_v3 is a constrained core size VM (see below) which has 4 vCPUs, 128GB memory and up to 25,600 IOPS.

Constrained core size VMs allow for a reduced vCPU count for licensing while keeping the RAM and MAX IOPS unchanged for the same price.

In Microsoft’s documentation they refer to use of DS2_v2 or higher for Standard Edition and DS3_v2 or higher for Enterprise Edition.

If the goal is to obtain a VM with maximum disk IOPS (currently 80,000) while keeping SQL license costs down you’ll probably be best to go with a E64-16s_v3 which has 16 vCPUs and 432GB RAM.

VM Disks

Once you have decided on your VM, the next most important step is deciding on the disks to add to the VM.

The table above shows the max throughput available for the disk, but depending on your VM you may not achieve that throughput due to Microsoft setting IOPS limits on the various VMs.

I’ll demonstrate these limits with the widely used CrystalDiskMark tool to place load on various disk configurations.

Below shows CrystalDiskMark run against a very old $100 Apacer AS681 SSD 2.5 7mm SATAIII, 240GB drive which is no longer sold.

All tests were run 3 times using a 1GB file to save time.

Seq Q32T1: Sequential, Queues = 32, Threads = 1
4KiB Q8T8: Random 4KiB, Queues = 8, Threads = 8
4KiB Q32T1: Random 4KiB, Queues = 32, Threads = 1
4KiB Q1T1: Random 4KiB, Queues = 1, Threads = 1

All tests made use of the recommended Storage Spaces with Simple Resiliency and the number Columns (think of it as a stripe) equaling the number of disks. The disks were formatted using best practice NTFS 64K allocation unit size and GPT partition to allow exceeding 2TB. Simple Resiliency is all that is required due to the underlying Azure infrastructure ref: “Locally redundant storage (LRS) replicates your data three times within a single data center. LRS provides at least 99.999999999% (11 nines) durability of objects over a given year.”

E16-4s_v3: Has 4 vCPUs, 128GB RAM and Max IOPS 25,600
E32-8s_v3: Has 8 vCPUs, 256GB RAM and Max IOPS 51,200
E64-16s_v3: Has 16 vCPUs, 432GB RAM and Max IOPS 80,000

Host Caching set to None

	E16-4s_v3	E32-8s_v3	E64-16s_v3
	Read / Write (MB/s)	Read / Write (MB/s)	Read / Write (MB/s)
1 x P30: Seq Q32T1	204 / 205	204 / 206	204 / 206
1 x P30: 4KiB Q8T8	21 / 20	20 / 20	20 / 21
1 x P30: 4KiB Q32T1	21 / 21	20 / 20	21 /21
1 x P30: 4KiB Q1T1	1 / 1	1 / 1	1 /1
Cost per month NZ$ 204.37 (Size 1TB)

2 x P30: Seq Q32T1	395 / 393	411 / 409	411 / 409
2 x P30: 4KiB Q8T8	41 / 41	41 / 41	41 / 41
2 x P30: 4KiB Q32T1	40 / 40	38 / 39	39 / 40
2 x P30: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 408.74 (Size 2TB)

3 x P30: Seq Q32T1	392 / 393	613 / 612	612 / 612
3 x P30: 4KiB Q8T8	61 / 62	61 / 62	61 / 62
3 x P30: 4KiB Q32T1	41 / 47	40 / 43	41 / 45
3 x P30: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 613.11 (Size 3TB)

4 x P30: Seq Q32T1	394 / 394	783 / 780	815 / 811
4 x P30: 4KiB Q8T8	80 / 81	80 / 81	80 / 81
4 x P30: 4KiB Q32T1	42 / 47	42 / 45	42 / 45
4 x P30: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 817.48 (Size 4TB)

5 x P30: Seq Q32T1	392 / 394	783 / 778	1006 / 896
5 x P30: 4KiB Q8T8	84 / 97	83 / 88	87 / 93
5 x P30: 4KiB Q32T1	42 / 47	41 / 45	42 / 45
5 x P30: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 1,021.85 (Size 5TB)

1 x P40: Seq Q32T1	255 / 257	257 / 256	255 / 257
1 x P40: 4KiB Q8T8	31 / 31	31 / 31	31 / 31
1 x P40: 4KiB Q32T1	31 / 31	31 / 31	31 / 31
1 x P40: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 391.67 (Size 2TB)

1 x P60: Seq Q32T1	392 / 394	513 / 511	510 / 511
1 x P60: 4KiB Q8T8	66 / 67	66 / 67	66 / 67
1 x P60: 4KiB Q32T1	51 / 59	46 / 51	46 / 54
1 x P60: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 1,430.42 (Size 8TB)

1 x P70: Seq Q32T1	393 / 394	765 / 764	766 / 764
1 x P70: 4KiB Q8T8	75 / 75	75 / 75	75 / 75
1 x P70: 4KiB Q32T1	51 / 58	46 / 52	45 / 59
1 x P70: 4KiB Q1T1	1 / 1	1 / 1	1 / 1
Cost per month NZ$ 2,724.62 (Size 16TB)

As shown above, there is clearly VM throttling in place and you could possibly be throwing money away by trying to use faster and/or more disks to get better IO performance.

Host Caching set to Read-only

	E16-4s_v3	E32-8s_v3	E64-16s_v3
	Read / Write (MB/s)	Read / Write (MB/s)	Read / Write (MB/s)
1 x P30: Seq Q32T1	269 / 205	538 / 205	1073 / 206
1 x P30: 4KiB Q8T8	133 / 21	268 / 21	534 / 20
1 x P30: 4KiB Q32T1	134 / 21	269 / 21	470 / 21
1 x P30: 4KiB Q1T1	19 / 1	54 / 1	58 / 1
Cost per month NZ$ 204.37 (Size 1TB)

2 x P30: Seq Q32T1	269 / 268	536 / 408	1071 / 408
2 x P30: 4KiB Q8T8	134 / 41	269 / 41	535 / 41
2 x P30: 4KiB Q32T1	134 / 38	269 / 39	489 / 40
2 x P30: 4KiB Q1T1	20 / 1	54 / 1	61 / 1
Cost per month NZ$ 408.74 (Size 2TB)

3 x P30: Seq Q32T1	269 / 268	539 / 535	1075 / 611
3 x P30: 4KiB Q8T8	134 / 61	267 / 61	535 / 62
3 x P30: 4KiB Q32T1	134 / 35	268 / 42	463 / 44
3 x P30: 4KiB Q1T1	19 / 1	54 / 1	58 / 1
Cost per month NZ$ 613.11 (Size 3TB)

4 x P30: Seq Q32T1	270 / 269	540 / 535	1073 / 812
4 x P30: 4KiB Q8T8	134 / 72	267 / 81	536 / 81
4 x P30: 4KiB Q32T1	134 / 37	268 / 44	481 / 44
4 x P30: 4KiB Q1T1	20 / 1	52 / 1	60 / 1
Cost per month NZ$ 817.48 (Size 4TB)

5 x P30: Seq Q32T1	268 / 270	538 / 535	1074 / 877
5 x P30: 4KiB Q8T8	134 / 66	269 / 89	535 / 90
5 x P30: 4KiB Q32T1	134 / 36	268 / 45	477 / 46
5 x P30: 4KiB Q1T1	18 / 1	54 / 1	60 / 1
Cost per month NZ$ 1,021.85 (Size 5TB)

1 x P40: Seq Q32T1	269 / 256	540 / 256	1075 / 256
1 x P40: 4KiB Q8T8	134 / 31	270 / 31	535 / 31
1 x P40: 4KiB Q32T1	134 / 31	268 / 31	496 / 31
1 x P40: 4KiB Q1T1	19 / 1	35 / 1	61 / 1
Cost per month NZ$ 391.67 (Size 2TB)

1 x P60: Seq Q32T1	No read cache available
1 x P60: 4KiB Q8T8	No read cache available
1 x P60: 4KiB Q32T1	No read cache available
1 x P60: 4KiB Q1T1	No read cache available
Cost per month NZ$ 1,430.42 (Size 8TB)

1 x P70: Seq Q32T1	No read cache available
1 x P70: 4KiB Q8T8	No read cache available
1 x P70: 4KiB Q32T1	No read cache available
1 x P70: 4KiB Q1T1	No read cache available
Cost per month NZ$ 2,724.62 (Size 16TB)

There are a lot of ways to interpret the data above, but key findings included:

Host caching set to None, can offer faster performance than Read-only cache.
The Larger sized disks do not offer caching.
Good option for SQL Server when a low number of disks are allocated to the VM.

Host Caching set to Read/write

	E16-4s_v3	E32-8s_v3	E64-16s_v3
	Read / Write (MB/s)	Read / Write (MB/s)	Read / Write (MB/s)
1 x P30: Seq Q32T1	269 / 221	538 / 451	1080 / 792
1 x P30: 4KiB Q8T8	134 / 80	268 / 156	535 / 403
1 x P30: 4KiB Q32T1	134 / 87	269 / 117	470 / 239
1 x P30: 4KiB Q1T1	18 / 26	54 / 39	58 / 51
Cost per month NZ$ 204.37 (Size 1TB)

2 x P30: Seq Q32T1	269 / 270	538 / 440	1076 / 1077
2 x P30: 4KiB Q8T8	134 / 133	269 / 194	535 / 504
2 x P30: 4KiB Q32T1	133 / 134	269 / 127	489 / 388
2 x P30: 4KiB Q1T1	19 / 36	49 / 30	61 / 31
Cost per month NZ$ 408.74 (Size 2TB)

3 x P30: Seq Q32T1	269 / 270	539 / 535	1075 / 806
3 x P30: 4KiB Q8T8	134 / 129	268 / 266	535 / 524
3 x P30: 4KiB Q32T1	133 / 66	268 / 267	463 / 386
3 x P30: 4KiB Q1T1	19 / 24	49 / 45	58 / 41
Cost per month NZ$ 613.11 (Size 3TB)

4 x P30: Seq Q32T1	268 / 250	536 / 540	1070 / 1023
4 x P30: 4KiB Q8T8	134 / 68	267 / 202	536 / 530
4 x P30: 4KiB Q32T1	134 / 123	267 / 193	481 / 370
4 x P30: 4KiB Q1T1	19 / 27	49 / 44	60 / 40
Cost per month NZ$ 817.48 (Size 4TB)

5 x P30: Seq Q32T1	269 / 261	539 / 519	1070 / 1020
5 x P30: 4KiB Q8T8	134 / 133	268 / 267	536 / 531
5 x P30: 4KiB Q32T1	134 / 134	268 / 201	481 / 374
5 x P30: 4KiB Q1T1	19 / 39	49 / 38	60 / 41
Cost per month NZ$ 1,021.85 (Size 5TB)

1 x P40: Seq Q32T1	269 / 270	539 / 246	1079 / 993
1 x P40: 4KiB Q8T8	133 / 133	270 / 231	535 / 530
1 x P40: 4KiB Q32T1	133 / 71	268 / 55	484 / 409
1 x P40: 4KiB Q1T1	19 / 26	35 / 23	59 / 57
Cost per month NZ$ 391.67 (Size 2TB)

1 x P60: Seq Q32T1	No caching available
1 x P60: 4KiB Q8T8	No caching available
1 x P60: 4KiB Q32T1	No caching available
1 x P60: 4KiB Q1T1	No caching available
Cost per month NZ$ 1,430.42 (Size 8TB)

1 x P70: Seq Q32T1	No caching available
1 x P70: 4KiB Q8T8	No caching available
1 x P70: 4KiB Q32T1	No caching available
1 x P70: 4KiB Q1T1	No caching available
Cost per month NZ$ 2,724.62 (Size 16TB)

Microsoft does not recommend used of Read/write cache for SQL Server.

VM Temporary Drive

	E16-4s_v3 (256GB Size)	E32-8s_v3 (512GB Size)	E64-16s_v3 (864GB Size)
	Read / Write (MB/s)	Read / Write (MB/s)	Read / Write (MB/s)
Seq Q32T1	270 / 270	538 / 535	1067 / 1074
4KiB Q8T8	134 / 134	268 / 266	476 / 489
4KiB Q32T1	133 / 133	248 / 238	277 / 271
4KiB Q1T1	15 / 22	20 / 29	21 / 30

The temporary drive throughput appears to reflect throttled premium disks with read/write cache enabled.

To summarize:

Review your VM’s temporary (D) drive performance and size to gauge where your tempdb database should be placed.

Having several striped disks does not guarantee that you will get faster throughput, rather this is dependent on the VM used.

To get faster IO performance you need a lot of disk space (even with the new Ultra disks), which you may / may not require, and remember you get charged for the whole storage (not just what you use) plus IOs (except temporary drive).

If you down scale a VM, you should ensure that the VM supports the number of drives you currently have attached and it you are using the temporary (D) drive, that the size will be sufficient for your tempdb files.

When using Storage Spaces, if you need to add additional capacity, you’ll need to add the same number of disks e.g. a 3 disk virtual drive will require an additional 3 disks. Also when the new disks are added, the Column count does not increase, so although you have increased capacity, you don’t increase the disk performance.

Depending on your SQL Server requirements, the cloud is not always cost effective.

Beware the impact of the ANSI_WARNINGS setting

Disabling the ANSI_WARNINGS setting in your code can compromise your data integrity.

As an example; say you are inserting an invoice number into a table and ANSI_WARNINGS are disabled.

You pat yourself on the back as the insert succeeded.

Can you spot the issue?

Ok you can take that pat back.

You should have ANSI_WARNINGS ON to ensure you get a warning and the operation doesn’t succeed.

One way to spot if an active connection has this setting disabled is to query the DMV sys.dm_exec_sessions. An example is shown below:

SELECT 
 session_id
,CASE WHEN [ansi_warnings] = 1 THEN 'yes' ELSE 'no' END AS is_ansi_warnings_enabled
FROM sys.dm_exec_sessions
WHERE session_id > 50;

Check your SQL errorlog file sizes

At some stage you’ll likely want to check your SQL Server errorlog files using xp_readerrorlog or sp_readerrorlog.

If you haven’t worked on the server before and/or don’t have access to view the file sizes before running the command the below query will help obtain this information.

Why would you be interested in knowing the file sizes before running the command? Well, if the instance hasn’t been restarted in a while and sp_cycle_errorlog hasn’t been run, the log files could be really large and cause potential memory and IO performance issues when you try read them.

DECLARE @enumerrorlogs TABLE ([Log Type] varchar(5), [Archive #] int, [Date] datetime, [Log File Size (Byte)] bigint);

--get sql log file sizes
INSERT INTO @enumerrorlogs ([Archive #], [Date], [Log File Size (Byte)])
EXEC master.sys.sp_enumerrorlogs 1;

UPDATE @enumerrorlogs
SET [Log Type] = 'SQL'
WHERE [Log Type] IS NULL;

--get sql agent log file sizes
INSERT INTO @enumerrorlogs ([Archive #], [Date], [Log File Size (Byte)])
EXEC master.sys.sp_enumerrorlogs 2;

UPDATE @enumerrorlogs
SET [Log Type] = 'Agent'
WHERE [Log Type] IS NULL;

SELECT 
 a.[Log Type] AS log_type
,a.[Archive #] AS archive
,a.[Date] AS date_last_updated
,CAST(a.[Log File Size (Byte)]/1024/1024.0 AS decimal(20,3)) AS log_size_mb
FROM @enumerrorlogs a
ORDER BY a.[Log Type] DESC, a.[Archive #];