SQL Server 2014 - Features Only Supported in Enterprise Edition

Overview.

You can find many articles, white papers, and books demonstrating the myriad features found in SQL Server 2014.

However it is sometimes easy to forget that there’s a big disparity in the features that the different editions of SQL Server 2014 support.

Chances are that the exciting new features you’re reading about are supported only by

SQL Server 2014 Enterprise Edition or SQL Server 2014 Developer Edition.

(Although Enterprise Edition and Developer Edition share the same feature set, Developer Edition is licensed solely for development work and can’t be used for production work.)

The following is a list of the most important features, there are others that are restricted to Analysis Services, but those are not covered here :-

1     No Upper Memory Limit. 

Maximum memory utilized (per instance of SQL Server Database Engine) Is limited by the OS Limits only for Enterprise Edition, BI and Standard Edition are limited to 128GB per Instance. Web edition is limited to 64GB with all other editions being limited to 1GB.

1      Online page and file restore. 

Online restore is supported only on SQL Server Enterprise Edition. In this edition, a file, page, or piecemeal restore is online by default. 

2      Online Indexing - Online Schema Change.

SQL Server introduced Online Index maintenance and partitioned tables with SQL Server 2005. Though operations around switching partitions of a partitioned table out or into a table are considered ‘online’ operations as well, they require an exclusive Schema Modification lock (SCH_M lock). A SCH_M lock against a table is usually used by DDL operations that structurally change the table. From an architecture and design point SQL Server should use the SCH_M lock for small time periods only, especially when using online options offered by some of the DDL commands of SQL Server.

3     Global Batch Aggregation. 

Standard query processing in SQL Server is based on a row-at-a-time iterator model, that is, a query operator processes one row at a time. To reduce CPU time a new set of query operators have been introduced that instead process a batch of rows at a time. 

4    Fast Recovery.

After installation, SQL Server sets recovery interval to zero. As long as the recovery interval setting is at the default setting and long-running transactions are not present, recovery for each database should take approximately 1 minute or less. 

When recovering restored data, if long-running transactions were active at the time of the data loss, recovery time is controlled by the time it takes to roll back the effects of these transactions. 

However, in SQL Server Enterprise Edition, the database is available during the undo phase of a crash recovery or a database mirroring failover, a feature known as fast recovery.

5      Mirrored Backups.

Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions.

6     AlwaysOn Availability Groups .  

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to four sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis.

7      Table and Index Partitioning. 

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data.

8     Data Compression. 

SQL Server 2014 supports row and page compression for rowstore tables and indexes, and supports columnstore and columnstore archival compression for columnstore tables and indexes.

For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application.

9     Resource Governor.

SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

10      Partition Table Parallelism.

Enables the use of separate threads for queries over multiple partitions.

11      Multiple Filestream Containers.

Provides the ability to have multiple data containers in a single filegroup. This feature comes in handy when you want to load-balance the I/O across multiple disk systems, or when you’ve run out of space in the data container and you need to expand your storage capacity.

12     NUMA Aware Large Page Memory and Buffer Array Allocation.

Allows the use of The intelligent, adaptive NUMA scheduling and memory placement policies in vSphere enabling transparent management of virtual machines transparently, so administrators do not need to deal with the complexity of balancing virtual machines between nodes by hand. 

13      Hot Add Memory and CPU.

Hot add CPU is the ability to dynamically add CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer.

14      In-Memory OLTP. 

New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.

15    Fine Grained Auditing.

SQL Server Enterprise enhances the auditing capabilities of the server by introducing native auditing functionality into the database engine.

16      Transparent Database Encryption.

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries.

17      Extensible Key Management.

SQL Server provides data encryption capabilities together with Extensible Key Management (EKM), using the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation. Encryption keys for data and key encryption are created in transient key containers, and they must be exported from a provider before they are stored in the database. This approach enables key management that includes an encryption key hierarchy and key backup, to be handled by SQL Server.

18      Oracle Replication Publishing.

Publishing servers can be deployed on any Oracle supported hardware and operating system. The feature is built on the well-established foundation of SQL Server snapshot replication and transactional replication, providing similar performance and usability.

Oracle Publishing is deprecated. Heterogeneous replication to non-SQL Server subscribers is deprecated. To move data, create solutions using change data capture and SSIS.

19     Peer to Peer Transactional Replication.

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

20     Parallel Indexed Operations.

The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-threaded operations on computers that have multiple microprocessors.

21      Automatic Use of Indexed View By Query Optimizer.

Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used

22     Parallel Consistency Check - DBCC CheckDB.

DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.

Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.

23      SQL Server Utility Control Point.

The SQL Server Utility models an organization’s SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in SQL Server Management Studio (SSMS) provide administrators a holistic view of SQL Server resource health through an instance of SQL Server that serves as a utility control point (UCP).

24      Change Data Capture Service for Oracle by Attunity. 

The CDC Service for Oracle is a Windows service that scans Oracle transaction logs and captures changes to Oracle tables of interest into SQL Server change tables. The SQL change tables where the changes captured from Oracle are stored are the same type of change tables used in the native SQL Server Change Data Capture feature. This makes consuming these changes as easy as consuming changes made to SQL Server databases.

25    Change Data Capture.

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

26   Star Join Query Optimizations.

Performs Query Optimizations on Data Warehouse Star Joins.

27      Parallel Query Processing On Partitioned Tables and Indices.

SQL Server Enterprise improves query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhances the partitioning information provided in both compile-time and run-time execution plans.

28      xVelocity memory optimized ColumnStore indexes.

xVelocity memory-optimized columnstore index is a feature which enables high performance query processing especially in Data Warehouses and Data Marts.

Unlike "Standard" indexes, columnstore indexes store data in memory (as the name memory-optimized columnstore index suggests) and the storage organization is not row-oriented.

29      SSIS -  Persistent (high performance) lookups.

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset.

   
30     SSIS - Data Mining Query Transformation .

The Data Mining Query transformation performs prediction queries against data mining models. This transformation contains a query builder for creating Data Mining Extensions (DMX) queries. The query builder lets you create custom statements for evaluating the transformation input data against an existing mining model using the DMX language.

Attunity connector provides an improved performance even with few records. The gain is around 15% if you have a lot of rows to transfer.

32      SSIS - Fuzzy Grouping and Lookup Transformations.

The Fuzzy Grouping transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicates and selecting a canonical row of data to use in standardizing the data.

33      SSIS - Data Mining Model Training Destination Adapter .

The Data Mining Model Training destination trains data mining models by passing the data that the destination receives through the data mining model algorithms. Multiple data mining models can be trained by one destination if the models are built on the same data mining structure.

34     SSIS - Connector for Open Database Connectivity (ODBC) by Attunity .

The Connector for Open Database Connectivity (ODBC) by Attunity for SQL Server 2014 Integration Services (SSIS) helps SSIS developers easily create packages that load and unload data from ODBC-supported databases.

The ODBC Connector is designed to achieve optimal performance when loading data into or unloading data from an ODBC-supported database in the context of SQL Server 2014 Integration Services (SSIS).

SQL Joins

--INNER JOIN: Returns all rows when there is at least one match in BOTH tables

--LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

--RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

--FULL JOIN: Return all rows when there is a match in ONE of the tables

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       INNER JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       LEFT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       RIGHT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       FULL JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       LEFT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              A.ColumnA IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       RIGHT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              A.ColumnA IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       LEFT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              B.ColumnB IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       RIGHT JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

              B.ColumnB IS NULL

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       FULL OUTER JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

SELECT

       COUNT (*)

FROM

       [DataBase].dbo.TableA A

       FULL OUTER JOIN [DataBase].dbo.TableB B ON A.ColumnA = B.ColumnB

WHERE

               A.ColumnA IS NULL

OR

              B.ColumnB IS NULL

Setting Up Resource Governor

-- CREATE RESOURCE POOL FOR SQL AGENT JOBS

CREATE RESOURCE POOL

       SQLAgentJobsPool

WITH

(

       MIN_CPU_PERCENT=0,

       MAX_CPU_PERCENT=85,

       MIN_MEMORY_PERCENT=0,

       MAX_MEMORY_PERCENT=85

)

GO

-- CREATE WORKLOAD GROUP for SQL AGENT JOBS

CREATE WORKLOAD GROUP

       SQLAgentJobsGroup

USING

       SQLAgentJobsPool;

GO

-- CREATE UDF TO ASSIGN WORK LOAD GROUP

CREATE FUNCTION dbo.UDFAssign_RG_WorkLoadGroup()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

DECLARE @WorkloadGroup AS SYSNAME

IF(SUSER_NAME() = 'Put SQLSERVERAgent Service Account Here')

       SET @WorkloadGroup = 'SQLAgentJobsGroup'

ELSE

       SET @WorkloadGroup = 'default'

RETURN @WorkloadGroup

END

GO

-- ALTER RESOURCE GOVERNOR TO USE WORK LOAD GROUP UDF

USE [master]

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[UDFAssign_RG_WorkLoadGroup]);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

SQL PowerShell - Add Windows User as SysADM

SQL PowerShell - Add Windows User as SysADM

OK, So I guess its finally time to start Teaching myself Powershell commands to enable me to administer SQL Server on Windows Core.

I will share my findings along the way.

The first thing I need to know how to do its add  a Windows Domain User as a SQL Server SysAdm.

So here goes.

I borrowed the following code off the Web somewhere, but could not get the AddToRole statement to work, originally it was placed after the Create() Statement, I found that by moving the Create() to the End of the Statement, everything worked as it should.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$SqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'SERVERNAME\INSTANCENAME'

$SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlServer, 'DOMAIN\USERNAME'

$SqlUser.LoginType = 'WindowsUser'

$SqlUser.AddToRole('sysadmin')

$SqlUser.Create()


OK, so two days in and I'm starting to get the hang of this now, I have realised that what I have done above (although it does work) is basically wrong.

We have to do this in stages : -

#LOAD SQL Server Objects

Load the SQL Server SMO Libraries to Powershell

#CONSTRUCTORS

Define the type of Object we want to work with, Database, Login etc

#PROPERTIES

Set the Values for the Properties, Login type for example

#CREATE

Run the Create() Method to Create the Object

#METHODS

Now the object has been created, we can run Methods to amend the Objects, such as AddRole.

#EVENTS

Check Event such as HasDataChanged

So the Add Windows User as SQL Sysadm, should look like this :-

#LOAD SQL Server Objects

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#CONSTRUCTORS

$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'ServerName\InstanceName,Port'

$Login = New-Object Microsoft.SqlServer.Management.Smo.Login($Server, "Domain\User")

# PROPERTIES

$Login.LoginType = 'WindowsUser'

#CREATE

$Login.Create()

#METHODS

$Login.AddToRole('sysadmin')

#EVENTS

If you refer to the Microsoft Documentation on Microsoft.SqlServer.Management.Smo :-

http://technet.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.aspx

We can use the above as the basis to do pretty much anything we desire in SQL Server with PowerShell.

So I don't think I am there yet, there will be plenty more to learn on the way, however, I think that I have passed a major Milestone in understanding just how these SQL PowerShell commands are constructed.

Disk Partition Alignment For SQL Server 2008

Overview

Disk partition alignment is a powerful tool for improving SQL Server performance, a Best Practice that is essential, yet often overlooked.

Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows or on volumes that have not been subject to a Windows 2008 Format such as volumes in a SAN environment.

This document is an extract of the Microsoft White paper

Disk Partition Alignment Best Practices for SQL Server 2008

It documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of pre existing partitions.

I have included the most relevant portions in to this document to try an make it a concise guide, however please refer to the Microsoft Document should you require any further background or explanation as to why Disk Partition Alignment is required.

Disk Partition Alignment.

Introduction

Noncompliance with storage configuration Best Practices for the Microsoft SQL Server is a common root cause of support calls to Microsoft for poorly performing Database Systems.

The reason is often shown to be misalignment between Windows, Storage, Disk Controllers, and Cache segment lines.

Failure to perform partition alignment may result in significant performance degradation.

Disk partition alignment is a requirement for partitions from which high performance is demanded and that were not created on RAID disk devices on Windows Server 2008.

Unless performed at the time of partition creation, the default alignment offset will result in unaligned partitions.

Earlier versions of Windows and some Manufacturers’ default SAN Settings create disk partitions by default to boundaries based on the Cylinder/Head/Sector (CHS) addressing scheme used by previous generation disk controllers.

Pre existing partitions attached to Windows Server 2008 maintain the original, flawed alignment under which they were created.

• MBR basic
• MBR dynamic
• GPT basic
• GPT dynamic 

 Scope

The information contained in this document applies to Windows basic and dynamic disks with master boot record (MBR) partitions.

However, disk partition alignment is a Best Practice, and it is required for optimal performance for each of these hard drive configurations:

Description 

The following is a simplified characterization of partition misalignment:

Disk array hardware reports 63 reserved (hidden) sectors, and Windows obediently implements this information, reserving these sectors at the beginning of the first partition of a disk.

The master boot record (MBR) resides within these hidden sectors.

The compliance by Windows with 63 hidden sectors reported by disk hardware results in misalignment with stripe units, disk controllers, and cache segment lines.

In all versions of Windows earlier than and including Windows Server 2003, these reserved sectors do not coincide with fundamental physical boundaries.

The result is that single clusters of user data are written across multiple stripe units of the RAID.

Every nth operation is affected (n depends on file allocation unit (cluster) size and stripe unit size).

Fundamental physical boundaries in disk controllers and other hardware are also violated.

Across a striped array, a single I/O coming into the array controller turns into multiple I/Os if the request crosses one or more stripe unit boundaries.

The cumulative effect can contribute to substantial performance degradation. 

In all cases, similar principals are at work:

Due to misalignment, clusters of data are written across physical boundaries, requiring unintended, unnecessary I/Os that result in performance degradation.

Partition Alignment in Windows Operating Systems 

The way partition alignment works depends on the version of Windows being used and the version in which the partition alignment was created.

Windows Server 2008 - New Partitions.

 In Windows Server 2008, partition alignment is usually performed by default.

The default for disks larger than 4 GB is 1 MB.

The setting is configurable and is found in the registry at the following location:

HKLM\SYSTEM\CurrentControlSet\Services\VDS\Alignment

However, if OEM setups are delivered (for example, with recovery partitions), even fresh installations of Windows Server 2008 having partitions with undesirable partition starting offsets are possible.

Whatever the operating system, confirm that new partitions are properly aligned.

Windows Server 2008 – Pre existing Partitions. 

New partitions on Windows Server 2008 are likely to be aligned.

Yet partitions created on earlier versions of Windows and become associated with Windows Server 2008 maintain the properties under which they were created.

That is, in the absence of partition alignment being explicitly performed, these partitions are not aligned.

Windows Server 2003. 

Partitions created on Windows Server 2003 by default are not aligned.

Partition alignment must be explicitly performed.

System Drives.

System drives in versions of Windows prior to Windows Server 2008 cannot be aligned.

Fortunately, workloads associated with system partitions of dedicated SQL Server computers are typically not as sensitive to partition misalignment as disks dedicated to I/O intensive uses, for example, SQL Server database files from which high-performance is demanded.

System drives on fresh installations of Windows Server 2008 should be aligned by default.

Virtual Drives.

 Virtual drives and the host drives on which they reside must be aligned for optimal performance.  

Starting Partition Offsets. 

The performance benefit of disk partition alignment requires configuration of valid starting partition offsets.

Several tools report the starting partition offset.

The results are reliable only in specific contexts.

Valid starting partition offsets and tools used to report them are discussed in this section.

Valid Starting Partition Offsets. 

Because versions of Windows earlier than and including Windows Server 2003 comply with the 63 hidden sectors reported by disk hardware, and because the most common sector size 512-byte sectors, the default (and suboptimal) starting partition offset is 32,256 bytes, exactly 31.5 KB.

Explicitly defining the starting offset from 31.5 KB to exactly 32 KB might seem like a legitimate approach.

In fact, as mentioned earlier, 64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server because of the correlations described later.

When choosing a valid partition starting offset, refer first to the storage vendor best practices.

Make certain their recommendations correlate with the stripe unit size and file allocation unit size configured for SQL Server.

In the absence of definitive vendor information, choose the Windows Server 2008 default.

Windows Server 2008 partition alignment defaults to 1024 KB (that is, 1,048,576 bytes). 

This value provides a durable solution.

It correlates well (as described later) with common stripe unit sizes such as 64 KB, 128 KB, and 256 KB as well as the less frequently used values of 512 KB and 1024 KB.

Also, the value virtually guarantees hidden structures allocated by storage.

Dynamic Disk Partition Offsets: dmddiag.exe –v.

The command-line utility Dmdiag.exe is used to determine the partition offsets of Windows dynamic volumes.

Important: Neither the output of the wmic command nor any other tool designed only for basic disks, reliably reports starting partition offsets of Windows dynamic disks.

The tool is available in the support tools folder of Windows Server 2003.

In Windows Server 2008, the tool has been renamed diskdiag.exe.

To determine the starting partition offset of dynamic disks, execute the following command.

dmdiag -v

The output has several sections; the sections that are relevant to analyzing the starting partition offset of existing volumes are generated only if the -v switch is used.

Those sections (and relevant columns) are:

Dynamic Disk Information (Rel Sec)

LDM Volume Information (Rel Sectors)

Focus on these sections and columns.

Subsequent sections of the output that report offsets can be misleading, and they are unlikely to be reliable for interpreting starting partition offsets of dynamic volumes.

Note that Microsoft tools, including even dmdiag, may be unreliable for reporting starting partition offsets of dynamic volumes created by third-party vendors.

For example, dmdiag does not report correct starting partition offsets of dynamic volumes created by Veritas Enterprise Administrator.

In these cases, consult your vendor for the tools and techniques required for proper analysis.

 Command-Line Partition Alignment Tools. 

Disk partition alignment is not available from the Disk Management snap-in (diskmgmt.msc).

Windows provides two tools to implement disk partition alignment: diskpart.exe and diskpar.exe.

Note the presence or absence of a “t” in their names.

Both utilities are powerful and should be exercised with caution.

Diskpar.exe reliably reports partition alignment in terms of bytes.

However, results are valid only for MBR basic disks, and this tool is no longer supported by Microsoft.

Diskpart.exe reports alignment for basic disks in terms of kilobytes.

As noted, the Windows Server 2003 (and earlier) default alignment is 32,256 bytes, exactly 31.5 KB; unfortunately DiskPart rounds this up to 32 KB.

Though DiskPart is the tool of choice to implement partition alignment, the value it reports for partition offset is not sufficiently granular.

Therefore, use the wmic command to report partition offsets of basic disks; use dmdiag –v for Windows dynamic disks.

Implementation 

This section provides information about using specific measurements and tools to implement the recommendations presented earlier in this white paper.

It also addresses issues to consider regarding vendors.

Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size

Use the information in this section to confirm the integrity of disk partition alignment configuration for existing partitions and new implementations.

There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance.

The results of the following calculations must result in an integer value:

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size

Of the two, the first is by far the most important for optimal performance.

The following demonstrates a common misalignment scenario:

Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875.

This is not an integer; therefore the offset & strip unit size are not correlated.

This is consistent with misalignment.

However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes.

The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.

 Stripe Unit Size. 

Windows does not have a reliable way to determine stripe unit sizes.

These values are obtained from vendor disk management software or from your SAN administrator.

File Allocation Unit Size. 

Run this command for each drive to see the file allocation unit size reported in bytes per cluster.

fsutil fsinfo ntfsinfo c:

fsutil fsinfo ntfsinfo e:

etc...

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside.

 Vendor Considerations. 

Consult the storage vendor for their recommendation.

In the absence of definitive information, consider implementing disk partition offset with the Windows Server 2008 default of 1024 KB. 

Conclusion. 

Many factors contribute to optimal disk I/O performance.

For disk partitions created with Windows Server 2003, partition alignment properly correlated with stripe unit size and file allocation unit size is a best practice, and it provides a fundamental foundation for optimal performance.

Windows Server 2008 aligns partitions by default.

When servers are upgraded to Windows Server 2008, preexisting partitions are not automatically aligned and must be rebuilt for optimal performance.

Thus, until existing misaligned partitions created using Windows Server 2003 are rebuilt properly, disk partition alignment will remain a relevant technology.

The results of the following calculations must result in an integer value:

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size

Of the two, the first is by far the most important for optimal performance.

A starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes.

The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server 2008.