SQL Server 2016 SP1

Microsoft has just released Service Pack 1 for SQL Server 2016.
The release is announced in several blog posts, e.g.
There are some rather interesting details on Microsoft database technology in general from the Connect() conference in the blog post "Announcing the Next Generation of Databases and Data Lakes from Microsoft" on SQL Server Blog (Data Platform Insider).
The official descriptions on SQL Server 2016 SP1 editions from Microsoft is more a presentation than technical details.

The installation set on Service Pack 1 can be downloaded from Microsoft Download. The release information (KB3182545) gives the details on what is fixed, but as the information is a collection of links it will take some time to get through all the details.

The new version number after the upgrade is 13.0.4001.0, and the installation will restart the services.

The installation set is named "SQLServer2016SP1-KB3182545-x64-ENU.exe" and takes 551 MiB. If the installation set is unpacked it takes 733 MiB.
The installation can be with the GUI by executing "setup.exe" or by command line - as usual.
Log files are generated as by previous SQL Server installations and upgrades. Usually in a folder named with a timestamp in the path "%ProgramFiles%\Microsoft SQL Server\130\Setup Bootstrap\Log\", e.g. the folder name "20161116_205924".
According to the logfile "Summary_<servername>_<foldername>.txt" (5 KiB) the installation took about four minutes on my workstation. The logfile "Details.txt" (9.14 MiB) contain about 59000 lines in my case. Usually I only look into Details.txt when I have an installation error...

This was the initial - and common - findings. When I get more they will surface here.



For the first time in a long time we have a security update for SQL Server with MS16-136.
Some technical details on the update itself are in KB3199641.

The update fixes elevation of privilege on three different components in SQL Server:

  • Database Engine (RDBMS); there are three CVE's spread over the different major versions of Database Engine. The documentation speaks of "improperly handles pointer casting" without further details.
  • Master Data Services - MDS; There is a cross-site-scripting (XSS) vulnability in the MDS API. This could be in the web application part of the API.
  • Analysis Services - SSAS; in this case the vulnability is due to "improperly checks FILESTREAM path.".
  • SQL Server Agent; the vulnability lies in "incorrectly check ACLs on atxcore.dll". This file is a part of the SQL Server Agent ActiveX subsystem, which is - finally - removed from SQL Server with the 2016 version.

The update is for SQL Server 2012 and newer. SQL Server 2008 (R2) are not hit by this security issue.
And not a word about SQL Server 2005, 2000 - or older ;-)


2016-11-09 Post created with initial references.
2016-11-27 Details on SQL Server components added.


IOmeter execution


Download IOmeter from iometer.org. I also download the documentation to be sure that I have the updated documentation.

Manual Setup

The drive must be defined on each worker before any other configuration in the IOmeter GUI.
You can only define one drive ("target") per worker.
Set up for 20 workers if the server has that many cores. If you have more cores than that you still don't need to define more that 20 workers in IOmeter.

IOmeter need a storage size to test on. It should three times larger than the complete amount of cache storage in the stack to the drive. On a SAN-drive this gives that the test file might be rather large. If you don't know the cache size and are working on local disks you could start with 40GB.
The maximum disk size is a little tricky as it is measured in number of sectors in IOmeter.
To get from storage amount like in GB to number of sectors you need to know the sector size. On a lot of disks it is 512B but some large disks with rotating platters mights have a sector size on 4KB. The number you can get in PowerShell with the CIM class Win32_DiskDrive where the attribute BytesPerSector gives the value.
When you have the sector size and know the size you want for maximum size the the calculation can be like this on a 40GB maximum:
(40GB * 1024MB * 1024KB * 1024B) / 512B = 83.886.080 sectors

In the tab "Access Specifications" I chose Default and added it as assigned.
The definition then can be altered by editing the default specification.
The "Transfer Request Size" is changed to the sized wanted for the test, e.g. 64 Kilobytes.
As SQL Server Database Engine mainly works sequential setting "Percent Random/Sequential Distribution" should be set to 100% Sequential. This setting is for some tests set to 100% Random.
A lot of SQL Server storage activity on data i reading, and then the setting "Percent Read/Write Distribution" should be set to 100% Read. This setting is for some test set to 100% Write.

On the tab "Results Display" the setting "Update Frequency (seconds)" should be set for 2 seconds to display the testing in a usefull way.

Start with 12 outstanding I/Os. Justify up and down to get highest bandwidth ("Total MBs per Second (Decimal)") before latency ("Average I/O Response Time (ms)") grows.


A test series could be like this

  • Read 8 KB random I/O
  • Read 32 KB random I/O
  • Read 64 KB random I/O
  • Read 256 KB random I/O
  • Read 8 KB sequential I/O
  • Read 64 KB sequential I/O
  • Read 256 KB sequential I/O
  • Read 1024 KB sequential I/O. IOmeter changes this entry to 1 MB.
  • Write 8 KB random I/O
  • Write 32 KB random I/O
  • Write 64 KB random I/O
  • Write 256 KB random I/O
  • Write 8 KB sequential I/O
  • Write 32 KB sequential I/O
  • Write 64 KB sequential I/O
  • Write 256 KB sequential I/O


Setup a local Performance Monitor to local disk in blg-files.
Measure Logical Disk, not Physical Disk.

Manual Execution

The IOmeter status shows "Preparing Drives" while creating the file. And it will take quite some time. You should plan on a few hours.
After the file is created it is reused for each run om the same disk. Even if the testing parameters are altered.

Let each test run for about ten minutes.


The test file "iobw.tst" is in the root of the drive. When IOmeter is stopped this file can be deleted. The deletion requires administrator rights.
Be nice and clean up after yourself ;-)


The IOmeter execution could be automated like I have done before with SQLIO. But as a manual evaluation is needed for the adjustments on outstanding I/Os it is practically not of much use to build an automated execution.

To be fair I have not discovered the detailed values in handling IOmeter myself. This has been delivered by Michael Frandsen (LinkedIn: michaelfrandsendk) in other context than this.


Drive info

The CIM CmdLets in PowerShell I think is a collection of great new features and a long needed replacements to WMI.
One very useful CmdLet is Get-CimAssociatedInstance that makes the hierarchy of classes nice and easy.
This I had use for lately for checking the drives on some new database servers. That was large servers with several drives on various storage technologies.

By travelling through the associated CIM objects I was able to get from the drive with a drive letter to the physical diskdrive.
The chain could be described like:

Drive or Volume - Logical Disk - Disk Partition - Disk Drive

The Volume (Win32_Volume) has no direct association to the Logical Disk (Win32_LogicalDisk), but the Logical Disk has the drive letter as key in the attribute DeviceID. The Volume object still holds some interesting attributes like BlockSize.

With a Logical Disk object I got the Disk Partition (Win32_DiskPartition). And from there I got the Disk Drive (Win32_DiskDrive).
The complete chain on the C-drive can look like this in PowerShell:

[String]$DriveLetter = 'C'

$WqlVolume = "SELECT * FROM Win32_Volume WHERE DriveLetter='$($DriveLetter):'"
$CimVolume = Get-CimInstance -Query $WqlVolume
$CimVolume | fl *

$WqlLogicalDisk = "SELECT * FROM Win32_LogicalDisk WHERE DeviceID='$($DriveLetter):'"
$CimDisk = Get-CimInstance -Query $WqlLogicalDisk
$CimDisk | fl *

$CimDiskPartition = Get-CimAssociatedInstance -InputObject $CimDisk -ResultClassName 'Win32_DiskPartition'
$CimDiskPartition | fl *

$CimDiskDrive = Get-CimAssociatedInstance -InputObject $CimDiskPartition -ResultClassName 'Win32_DiskDrive'
$CimDiskDrive | fl *

Now all the info is stored in variables, and can be accessed and processed directly.
If you like a selection of data can be combined in a custom PowerShell object (PSObject).

It is obvious that the classes for the more physical elements were defined for spinning plate disks, e.g. a SSD on SATA has tracks and cylinders according to Win32_PhysicalDrive.