Databases represent the backbone of all organizations large and small.  These databases are an integral part of any organization and represent an asset that requires the highest availability, reliability, performance and flexibility for all aspects of the organization they support.  Typically storing mission critical transactional data about customers, patients, suppliers, orders; databases are also used to analyze the performance of the organization using data warehouses, data marts, data lakes, as well as non-structured data analysis as the organization moves into capturing and analyzing what is now popularly known as “Big Data”, or data outside of the “Systems of Record” data stores. 

InfiniBox represents a new age of data storage, departing from the traditional dual-controller, RAID-set storage mentality and provides a solution to the most demanding of application and database environments, while using best-of-breed storage architecture design that provides unmatched ease-of-use, fast start-to-finish storage deployment tools, the InfiniBox system benefits tremendously by avoiding these legacy storage architectures. This gives applications hosted on InfiniBox higher, more predicable performance as well as much simpler, easier to manage host side configurations.

The net result is a much lower TCO for applications migrated to InfiniBox, and a platform for unparalleled database and application consolidation where up to 2PB of data can be stored in a single floor tile.  No other storage vendor can store mission critical databases.

This paper provides more details on what features InfiniBox provides, and how specific database activities are streamlined, how the InfiniBox architecture encourages simpler database architecture designs and how InfiniBox reduces the time and complexity of managing these critical database resources.


Large databases (10’s of TB to hundreds of TB) pose a unique challenge to enterprise storage arrays by providing an I/O profile that is unpredictable, and often overwhelms the storage frame resulting in high latencies, which increase the run time of database workloads.  Some database activities are very latency sensitive, and in many cases will effect the end user population that the application supports.

There is a mass movement, particularly for SQL Server environments to converged infrastructure for databases, utilizing hypervisor-based servers to support SQL Server in tightly grouped compute environments, as well converged storage platforms.  Part of the reason is SQL Server is well suited toward this architecture due to it’s small footprint, simplistic management and attractive pricing model.

But, like all production database environments, there are still basic requirements that must be provided by both server and storage infrastructures.  InfiniBox provides benefits that are requirements for enterprise database deployment:

  • Consistent, high performance: Infinidat is designed with massive parallelization, huge compute power, large flash cache layer, while it’s data distribution architecture ensures even access across all 480 NL SAS at all times and providing consistent, predictable performance, an absolute requirement for all enterprise databases. The storage snapshot architecture of InfiniBox provides the ability to execute thousands of snapshots, while not effecting performance, from which SQL Server databases can derive benefit.  Snapshots are used by our customers to augment their SQL Server database backup and recovery architecture.

  • High availability and reliability: InfiniBox architecture provides a robust, highly available storage environment, providing 99.99999% uptime, which is one of the highest rated uptimes for any storage platform. That equates to 3 seconds of downtime a year!  Drive rebuild times are the best in the storage industry.  InfiniBox offers end-to-end business continuity features -- including asynchronous remote storage replication --Using snapshots, recovery of a database can be reduced to the amount of time it takes to map the volumes to hosts, minutes instead of hours of recovery time using a more traditional SQL recovery process. 

  • Exceptional ease in storage management: InfiniBox architecture, along with the elegant simplicity of it’s web-based GUI allow for easy, fast deployment and management of storage to database environments.  The amount of time saved in performing traditional storage administration tasks is huge.  Also, because of InfiniBox open architecture, and aggressive support for REST-ful API’s the use of other platforms such as Openstack, and emerging container-based application environments such as Docker allow for storage administration tasks to be performed at the application level, without the need to use the excellent InfiniBox GUI.  Direct storage deployment and management can also be performed directly from VMWARE’s vCenter console through support for all of the major VMWARE API’s such as VAAI, VASA and VADP.

  • Lower total cost of ownership: Massive parallelization, extreme availability, highest data density in the industry, consistent performance and easy of use all point to unmatched TCO.  This is important for environments where there is a need to consolidate mission critical databases into smaller and smaller physical footprints, while our customers are experiencing an explosion of data sources such as mobile, machine generated data, and huge amounts of analytic data.  There is no other storage platform on the market that provides all of these benefits, particularly for mission critical enterprise database environments. 

This paper will go through some of the major requirements and characteristics and provide guidance on best practices, as well as any observed behavior that is unique to running SQL Server Databases on InfiniBox. 

InfiniBox is designed to provide the highest availability, while reducing the physical data footprint to store that data by utilizing a unique and patented data distribution and parity-based protection mechanism that distributes data from each volume across EVERY drive in the InfiniBox frame.  That is 480 NL-SAS drives supporting each and every volume. 

The parity-based storage architecture insures that the highest amount of usable capacity is available. 

Drive rebuild times can directly affect availability.  InfiniBox takes no more than 15 minutes to recover from a dual drive failure, with minimal impact on performance.  For systems with less space used, the rebuilt times are lower.  The reason is that InfiniBox is not built from RAID sets, or limited number of spindles grouped together.  InfiniRAID is a new way to store data with a unique and patented way to distribute large amounts of data across every spindle in the frame. This significantly improves (reduces) drive rebuild times in part because when data is needed to be re-built all 480 drives will support the effort.  Also, because of the unique and patented way that InfiniBox stores data along with parity, most of the data is rebuilt without having to move data from one place to another. 

Parity-based storage architecture, plus low drive rebuild times, and fully redundant hardware (many components have triple redundancy) gives InfiniBox the ability to provide 99.99999% uptime per year.    This equates to roughly 3 seconds of down time per year.  If you put this into perspective, at 3 seconds a year, that is much shorter than your average SCSI timeout sequence.  Which means even if there was down time, it would not cause the host to loose connection to the data, or even recognize that there was a short disruption of data availability. 

With such high availability, database consolidation, and reduction of mirrored copies of data are possible.  More customers are considering consolidation of databases onto less hardware to reduce costs. 

High availability and RAS

Backup and recovery

Most SQL Server customers use more traditional backup and recovery architecture, primarily using SQL Server Management Studio to backup and recover database data.  It is the preferred method to provide a complete, comprehensive backup image of your database, while providing full or piece-meal recovery of data to allow for more focused recovery capabilities.

When traditional SQL Server backup is called for, InfiniBox is a very good backup target candidate for backup of a database directly to disk.  The reason is that it will be fast, it will be reliable, and it will be cost effective due to the massive density of storage on a single frame.  Many SQL Server database shops use VTL-based SQL Server backup strategies as the first line of recovery defense. 

The Microsoft MSDN site provides a comprehensive library of backup and recovery documentation that provide detailed information of every backup and recovery option available for SQL Server.

There is a separate Infinidat whitepaper providing detailed information regarding the Microsoft / Infinidat VSS provider integration with InfiniBox and usage that walks you through the creation and management of snapshots from the Diskshadow utility on Windows.

To download the VSS Hardware Provider plug in, visit:

There is a wealth of information regarding host integration in the Infinidat Support site.  Check out the Host Power Tools documentation, as well as Host Power Tools for VMWARE for more information about Infinidat integration with VMWARE environments.

Also, Commvault, an Infinidat partner, provides a comprehensive backup and recovery product that fully integrates into the InfiniBox snapshot and clone system to provide a seamless, point-in-time recovery capability for SQL Server environments.  Infinidat will have the corresponding Commvault integration documentation for InfiniBox. 

InfiniBox Snapshots as a backup / recovery option

InfiniBox provides an efficient, high-speed storage-based snapshot system (InfiniSnap) that allows you to take thousands of snapshots of with no performance impact.  Storage-based snapshots are yet another way to backup and recover database data at the storage level, performing the task within milliseconds, rather than the hours it takes for a traditional SQL Server backup.  SQL Server backup must read all the data from the database and write it all to a virtual tape library or actual tape library, compared to InfiniSnaps which can be used to restore the database immediately and quickly from the snapshot. Taking snapshots with InfiniBox does not stop I/O flowing from database to storage, so there is no disruption of normal operation. 

The VSS provider architecture is depicted below. 

The amount of time taken to take a snapshot backup using the VSS provider is very short, and is fully orchestrated through the VSS provider interface.  Microsoft has a brief write-up of snapshot backup support. 

Along with this is a discussion Microsoft provides a document covering the SQL Writer Service.  This service is the critical piece that integrates with the Windows VSS provider to insure that the database is readable by the backup process, which under normal operation only the SQL Server database engine has exclusive access to the files.  The SQL Writer service insures that the VSS will capture a stable, consistent image of the database at that moment in time.  The VSS will call SQL Writer service which orchestrates the backup process and also allowing SQL Server to continue normal operation even if users are writing data to the database.

When the snapshot is taken through the VSS provider, the contents of the data, and transaction log all are in a consistent state.  If you need to use this snapshot to recover the database, you simply create a clone of the resulting InfiniSnap created by the VSS and mount the clones to a server.  Via the Diskshadow utility, the image of the database will be in read only mode.  Therefore you will not be able to start the database up, as the database wants to have it’s files in read/write state.  If you wish to actually startup a clone copy of the database, you must not use Diskshadow and VSS provider to perform the snapshot/clone activity.  Instead, use the Infinibox GUI or command line to perform these tasks.  Then map the clone to the server (via the GUI or infinishell command line), re-scan for the devices using the infinihost volume list --rescan option.  The clone will have a crash consistent image of the database, and the objects within the clone will be in read/write mode which will allow you to start the database.  

Here is a link to a brief document from Microsoft regarding the SQL Writer service:

The bottom line is that Snapshot-style SQL backups will take a very short time .vs a normal SQL backup which will take a lot longer.  Depending on the size of the database, a SQL backup can take several hours, whereas a InfiniSnap-based VSS provider backup can be completed literally in seconds.

SQL Server backup to NFS

This is a fairly new option that customers are exploring for several reasons. 

First, NAS backup makes sense because it allows for multiple servers to mount a NAS file system to either write a backup or read a backup and shuttle to tape.  It allows for a more simplistic network-based connectivity model that does not interfere with existing Fiber Channel configuration.  Many customers struggle with server configuration with Fiber channel because they don’t want backup traffic to hinder production data traffic.  So, in many cases, a split Fiber Channel configuration is deployed, where some of the HBA’s are dedicated to normal storage traffic, and a few are dedicated to just performing backup.  This type of configuration limit’s the server’s full storage traffic potential through fiber channel, and adds complexity to configuration and management of the server, and storage network it is connected to.  By adding a NAS file system mount, and dedicating a network port to it’s use, it eliminates this complexity, yet still achieves separation of production and backup traffic, without the hassle. 

NAS backup is not a viable option without performance, and Infinibox delivers by providing the same architecture to support either Fiber Channel Block storage volumes and/or NFS file system.  You get extreme performance by utilizing the massive 2 level caching system using NFS.  Yes, the absolute maximum bandwidth will be limited to the number of network adaptors utilized from the server, as well as the speed of those adaptors.  A 10GBit Ethernet connection will yield about 1GB/second line speed, actually more bandwidth than an 8GBit Fiber Channel connection.  It does take some work to aggregate multiple 10GBit Ethernet adaptors to share the workload.  This is where Fiberchannel shines with the help of server-based multipath I/O, which is responsible for load balancing I/O traffic across many channels, typically 12 if connected to Infinibox.  But, generally the simplicity advantages outweigh the bandwidth differences. 

To use NFS on Windows 2012, you need to perform a few steps.  First, you need to enable the client.  In Server Manager, under “Manage -> Add Roles and Features".

Scroll down to Features.

Choose Client for NFS. 

On InfiniBox, create an NFS file system and export. 

You will have to change the default permissions of the NFS directory you created.  It defaults to a unix-based umask permissions owned by root. If you mount the file system in windows, using:

mount \\<server>\<nfs share name> *

It will mount with read only attributes.  That is because Windows and Unix permissions are not compatible.  There are a couple of options here.  One is to follow Microsoft suggestions for Windows 2012 here:

The other is to just manually modify the permissions of the directory.  One way to do this is to mount the NFS file system to a Linux/Unix system, and open up permissions.  The default permissions is 755 owned by root.  Windows doesn’t know of any user root, so one way is to just change the permissions to 777.  This may not be the desired production configuration, but for testing it may be fine.

You cannot run the backup from SQL Management Studio.  The NFS mount will not show up as an option.  Only local disks, SAN disks and CIFS mounts are visible.  You must use the command line to run the backup. 

To backup to a single mount point using a single thread, you:

SqlCmd -E -Q "Backup Database AdventureWorks2008R2 to DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak'" 

Single threaded, this backup will run at roughly 200MB/s. 

To run with multiple threads:

SqlCmd -E -Q "Backup Database AdventureWorks2008R2 to

DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure2.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure3.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure4.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure5.bak',
DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure6.bak',
DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure7.bak',
DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure8.bak' with format" 

In our lab, this configuration pushed the bandwidth for backup to over 600MB/s. 

To restore this multi-piece backup, just use the same basic format as above, listing each file.

To run this backup and mirror it to another NAS device:

SqlCmd -E -Q "Backup Database AdventureWorks2008R2 to
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure2.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure3.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure4.bak',
DISK=’\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure5.bak',
DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure6.bak',
DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure7.bak',
DISK='\\<Network space for NAS>\<name of NFS file system>\Backup\Adventure8.bak'
Mirror to DISK=’\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak',

DISK=’\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure2.bak',
DISK=’\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure3.bak',
DISK=’\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure4.bak',
DISK=’\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak',
DISK='\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak',
DISK='\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak',
DISK='\\<2nd frame Network space for NAS>\<name of NFS file system>\Backup\Adventure1.bak' with format”

This allows you to create a single backup set using multiple pieces, and mirroring it to another storage frame all with a single backup command.

You can also create a SQL Server database on an NFS share.  The “N” tells SQL Server that you are creating a network-based database. 

Create database networkdb on primary:

(NAME=N'networkdbdata',FILENAME=N'\\<Network space for
NAS>\<name of NFS file system>\data.mdf',SIZE=2GB,MAXSIZE=unlimited,FILEGROWTH=1024kb)

(NAME=N'networkdblog',FILENAME=N'\\<Network space for
NAS>\<name of NFS file system>\log.ldf',SIZE=2GB,MAXSIZE=3GB,FILEGROWTH=10%)


Best practice: Use both InfiniBox Snapshots AND SQL Server traditional backups

A good strategy would be to utilize both InfiniBox snapshots and SQL Server traditional backups for a well-rounded backup and recovery solution that will cover a wide range of recovery scenarios.  And the flexibility of having both allows for a more customized restoration approach.  We have seen customers utilize this mixture where snapshots are used for daily backup and near line recovery option for full system restore, and a week-end SQL full backups to allow both piece meal restoration as well as support off-site backup media, which InfiniSnaps alone cannot achieve. 

Database cloning using snapshots

To clone a database, SQL Server provides a redirected restore capability, allowing you to take an existing SQL Server backup image and restore the database to another location.  The limitation with this approach is time.  The restore process will take as long it will take to restore a database, depending on the size of the database, this could take hours. 

A more efficient way to clone a SQL database is to use InfiniSnaps / Clones to take a snapshot of database LUNs, clone the snapshot and mount the clone to another server for read/write use.  This type of Snapshot usage saves a significant amount of time, because there is no need to wait for the restore process.  So the process would be to take the InfiniSnaps that VSS created, create clone(s) of the snapshots, then present the clone(s) to a server.  All of these activities are entirely storage frame-based which removes the server from managing these activities which would normally be very I/O intensive.

High Availability

There are several Hi-Av solutions for SQL Server that have some level of interaction / ramifications for Infinidat Storage.  These SQL Server features, along with Infinidat’s 99.99999% up time provides for the highest availability for production databases.

AlwaysOn Failover Cluster Instances

Microsoft recommends AlwaysOn Failover Cluster Instances for SQL Server supported on Enterprise Storage such as InfiniBox.  This cluster technology relies on InfiniBox storage replication to insure that data from one node in the cluster is replicated to other nodes that are located in other data centers.  So, SQL AlwaysOn provides the mechanism that is used to signal WSFC the status of the instance.  The failover mechanism is managed by Windows Server Failover Clustering (WSFC). And the remote copy of storage is provided by Infinidat Data replication.  More details about how WSFC works can be found on the Microsoft MSDN site:

For this solution, InfiniBox integration is straight forward and simple, as our multi-initiated LUNs are fully supported, a requirement for shared storage clustering like WSFC where a cluster of servers can access a single shared dataset for in-datacenter clustering capabilities.  On InfiniBox, you simply set up a cluster instead of a host on the InfiniBox Hosts & Clusters menu, add in the participating hosts, create the LUNS and map the LUNS to the cluster.

For the long distance clustering capabilities, again the InfiniBox integration here is very simple.  Take those same LUNs that are mapped to a cluster of servers, and set up a volume replication relationship to another InfiniBox in the remote location.  WSFC and AlwaysOn then manage the cluster health and status from there. 

AlwaysOn Availability Groups

This is the SQL Server native data replication capability that provides database mirroring.   AlwaysOn Availability Groups do not rely on the Windows Server Failover Clustering mechanism to run, and as such, the storage deployment and management implications would be the same as in a stand-alone server configuration.  In other words, you set up primary storage supporting the primary Availability group in datacenter 1.  Then using AlwaysOn Availability groups, you mirror the data over to data center 2.  Now, it is not that simple.  There are preliminary steps required to set up database mirroring, but from a storage perspective, it is straight forward.  There is no need to use storage replication in this case.  The focus of Availability groups is to “group” like database assets together to form a single failure domain.  During an event, the entire contents of the group are managed singularly. 

Database Replication vs. Storage Replication

As discussed in the previous section, SQL Server provides a few tools to replicate database data to a secondary site.  All of the tools allow for the database to be available within minutes on the secondary site.  Each tool uses some form of transactional replication where database transactions are shipped from the primary site to the DR site. 

InfiniBox includes the tools to replication the data at the storage level from one site to another site to a second InfiniBox through asynchronous replication.  Each individual volume of a database can be replicated to the DR site so that in the event of a disaster at the primary site, the data will already be at the secondary site.  The data can then be presented to a series of servers on the DR side and database can be brought up fairly quickly.  InfiniBox replication is included in the price of the storage.  The unique advantage that InfiniBox provides is a very fast and short RPO sync interval, in our latest testing a 4 second interval is being used.  This is the shortest RPO interval in the storage industry.  What this means is that the data update delta between sites is closer than any other storage vendor.  This insures that there is less chance of corruption, more chance of recovering to a very near real time when the primary site failure occurs. 

Recovery from storage-based long distance replication is similar to recovering the database on a local copy of a InfiniSnap.  A crash-consistent image is what will be available on the DR side in the event of recovery using storage replication.  Meaning, if a disaster occurs, and the database must be started on the DR side, upon startup, the database will go into crash recovery mode, rolling back any transactions not fully committed, and reconciling the control file with the datafile headers and synchronizing all files to a specific database generation ID.  Because this is a crash-consistent image of the database, there will be no opportunity to roll forward any transaction logs to a point in time.  For some customers, this is acceptable, and therefore storage replication is a solution that satisfies both RPO and RTO requirements.

Ease of use

InfiniBox GUI and CLI very easy to use

There are a couple of dimensions to Ease of Use.  The more visible one is the incredibly easy to use InfiniBox GUI and command line interface.  Storage administrators and database administrators will see that InfiniBox provides tools like the GUI, the CLI and the Host Power Tools that simplify the creation and management of storage for databases. 

InfiniBox provides a management system that can isolate storage pools and volumes to specific users, to provide Multi-tenancy features so that application users, such as SQL DBA’s can manage their own storage, pools, volumes and snapshots.  This is important for shops that are moving to converged, virtualized infrastructure, and moving away from O/S-based file system storage.  With hypervisor-based environments, the storage management function is moved from the system admin to the virtual server support organization.  With the strong user management functions of InfiniBox, DBA’s can manage their own objects within one or more storage pools.  All the storage administrator has to do is to initially set up the pool, and add the SQL / VMWARE / HyperV users to the InfiniBox management system to manage that pool. 

InfiniBox Architecture Promotes Simplified Data Layout

The second dimension to ease of use is primarily due to the storage architecture.  Because each volume is broken up and its data is spread across all 480 spindles in the frame, there is no need to be concerned about RAID groups, hot spot management, and concern about volume size and the number of spindles in each RAID group.  There is no need to create a large number of small volumes to spread the I/O load across more spindles.  As a result, the best data layout is the simplest.  Use a small number of large LUNS for data.  Choose a LUN size that best fits the growth needs of the database, rather than the performance characteristic limitations of the underlying storage.  Most customers choose a LUN size of 500GB to 2TB so that when a new LUN is required, adding that size LUN to the database doesn’t waste too much space between new allocations.

  • For data files, use a small number of large volumes (typically 2 - 4 volumes). Each volume should be between 500G - 2TB, depending on the database size. InfiniBox is optimized to use all drive spindles for each volume. Using small numbers of large volumes takes better advantage of InfiniBox aggressive caching technology and simplifies storage management. This fact can be attributed to InfiniBox storage architecture breaking the legacy storage mold consisting of traditional small database RAID arrays and a large central cache.  
  • For log files, use only a single large volume. 
  • Place database and log files on separate volumes. If database corruption occurs, this allows point-in-time recovery rather than recovery of the last consistent snapshot image.
  • When creating the NTFS file system on the data LUN, the Infinidat Host Power Tools will set the NTFS file system to utilize 64k cluster size (Allocation Unit size).  The default is 4k. The larger 64k size works best for InfiniBox because the internal data structure on the storage size matches what the 64kb cluster size is set to. 


Performance simulation

In order to get a better feel for how databases will react on InfiniBox, the following vdbench benchmark data is provided.  This workload simulates a 60%/40% read/write mix, using a nearly 100% random profile.  This is typical for an OLTP workload.   These workload runs are by no means a fully accurate representation of how SQL Server I/O profiles will look.  It is more a set of characteristics that can be used when reviewing your workload.  And each of your workloads will differ depending on what the application is doing at any given moment. 

Performance data

Here is a chart showing IOPs.  Each of the colored “Series” bars are increased thread counts, 8,16,32,64.  Thread count is the level of parallelism that is used to drive I/O workload.  Note that at 8k, which is typical of database index reads, the IOP count for this server is roughly 170,000.  At the bottom of the scale is a list of block sizes used for the test. 

Here is the corresponding bandwidth (MB/s) data for each of the block sizes and thread counts, again 8,16,32,64.  Note that at some point we hit line speed for the server at just over 5GB/sec.

Note that throughout all the test iterations, the higher the thread count, the better the performance.  This shows the level of parallelization that InfiniBox can achieve from even a single server. 

Host-based Configuration Guidelines

There are several items that need to be taken into consideration when configuring host operating systems to support Oracle Databases, particularly when connected to infinibox. 

There are some performance guidelines that are universal in their application across all operating systems, here are some of them.  The Infinidat Host Power Tools will adjust these by default, but it doesn’t hurt to understand what they do, what they should be set to. 

SQL Server Parallel I/O

The design of the InfiniBox architecture excels with applications that employ multiple threads to handle the parallel execution of I/Os from a single server. Multiple threads from multiple servers perform even better. 

In a SQL environment there are several ways to achieve parallelism to take advantage of the underlying storage architecture of InfiniBox:

  1. Inter-query Parallelism: A single database can accept queries from multiple applications simultaneously. Each query runs independently and simultaneously. 
  2. Intra-query Parallelism: Simultaneous processing of parts of a single query using inter-partition parallelism, intra-partition parallelism, or both.
    1. Intra-partition Parallelism: A single query is broken into multiple parts.
    2. Inter-partition Parallelism: A single query is broken into multiple parts across multiple partitions of a partitioned database on a single server or multiple servers. The query runs in parallel. 
  3. Depending on the server hardware and database solution, the maximum degree of parallelism (MAXDOP) can be configured. For general guidelines on configuring the MAXDOP option in SQL, see the following Microsoft KB article: 
  4. SQL backups and restores are very I/O intensive. SQL Server exploits both I/O parallelism and intra-partition parallelism when performing backup and restore operations. Backups exploit I/O parallelism by reading from multiple database files in parallel, and asynchronously writing to multiple backup media in parallel.
  5. For batch jobs that are single threaded with time limitations, perform the following: 
    1. Use large database buffers to take advantage of prefetching. Allocate as much server memory as possible. 
    2. Run multiple batch jobs concurrently. Even though each batch job will take approximately the same amount of time, the overall time frame for combined tasks will be less.
    3. If possible, break down large batch jobs into smaller jobs. Schedule the jobs to run simultaneously from the same host or multiple hosts.  For example, instead of backing up data files sequentially, backup multiple data files concurrently.
    4. When performing queries, DDL, DML, data loading, backup, recovery, and replication, follow best practices which enhance parallel execution of these tasks.

When performance tuning SQL, it’s especially important to thoroughly test the databases. There are many factors which can influence the outcome including custom stored procedures and applications. Each environment is unique and it’s worthwhile to not utilize too many performance modifications. Don’t forget, Microsoft SQL Server automatically tunes many of the server configuration options, therefore requiring little, if any, tuning by a system administrator. Keeping the storage configuration simple and streamlined, especially with Infinidat Storage is paramount.

Queue depths

Queue depth is the amount of memory space allocated to insure that when I/O is executed by an application, that the number of commands and blocks of data that are sent to the host bus adaptor (HBA) are queued insuring the application is free to send more I/O when it can.  This feature allows for high amount of work parallelization, and the possibility of massive asynchronous I/O.  Oracle, along with SQL Server and DB2 perform both synchronous and asynchronous I/O depending on the situation and circumstance of where the I/O is being generated.  Asynchronous I/O is when the database engine batches up a group of blocks of data and sends the entire group as a single I/O request to storage.  The process that sends the request, usually the database writer process, scans through the database buffer cache for dirty blocks, consolidates a list of addresses of those blocks and sends these blocks to be written.  The writer process then issues a single I/O request of many blocks to storage, signals the database that they are written, and then in the background clears the list when acknowledges of each block written is received from storage.  This allows the database to immediately recycle the original blocks back to the free list for more buffered reads.  The database writer process can be very aggressive with the write list, with as many as several hundred blocks gathered and written in a fire-and-forget fashion. This high block count then must have some sort of queuing mechanism between the server and the storage, and that is where the HBA queue depth comes in.  When the queue’s start to fill, and get to what ever the maximum queue depth is set for the server a stop request is issued to the database writer process to stop sending data until the queue drains.  This is not a desirable condition, as it causes delays in how fast DBWR can evacuate dirty blocks.  When more blocks are needed, the database writer process would be the choke point slowing every other user process requesting buffer cache space.

The recommended LUN queue depth value for most operating systems is 128. Host PowerTools can automatically set the recommended queue depths values when installed on Linux Operating systems. For Windows, this should be configured manually.

Windows NTFS

When deploying SQL Server on Windows and NTFS file systems, the NTFS file system uses data clusters to store blocks of data in groups.  The default block size is 4kb, which works well on infinibox.  The Allocation Unit size, or extent size, or cluster size, as it is called determines how NTFS will group data blocks into like groups.  When Windows submits an I/O to the storage sub system, it normally will use this cluster size to access and pre-fetch data. 

The recommendation is to set the filesystem AU to 64k.

InfiniBox provides built-in performance monitors to help identify how it is reacting to workloads.  InfiniBox has a real-time system monitor that shows a wide range of performance metrics as they are happening that can show you how the system is performing at that moment in time. 

Infinimetrics is a separate tool that collects the InfiniBox performance metrics and stores them in a central data repository.  Historical performance data can be reviewed to review performance over several days, weeks, months, or even years. 

The default all system view shows IOPS, Throughput (Bandwidth, or MB/sec), and latency for both reads and writes. 

You can also view performance data for specific individual hosts.  In this case, it is an aggregate view of all hosts.  In this case, 3 hosts performing a combined workload of almost 150,000 IOP/s at nearly 6GB/s bandwidth with an average highest host average latency of 4.03ms.  When you mouse over each host graph, it shows IOPs, Throughput and Latency for that specific host in the right most legend.  

SQL Server performance is monitored through the Windows Perfmon tool.  There are several specific performance counters for SQL Server that you can review to identify performance issues within SQL.  Microsoft provides a variety of knowledge base articles on what to collect and how to interpret the data. 

The above screen shot shows an example Perfmon data file essentially with ALL counters selected.  As you can see, showing all counters at once makes the data not only unreadable, but is effected by the scale of the graph.  Showing IOPs and latency together also may not create a good graph, if your database pushes 4,000 IOPs/s, and shows 8ms of latency.  The latency graph will scale down to not show any useful data for latency.  A better approach would be to create a single graph for each counter. 

The combined use of InfiniMetrics and Perfmon will help correlate the data between the two platforms. 

The following Perfmon counters are of most interest when identifying performance characteristics of SQL Server and of the supporting Windows Server. 

Press the “Add...” button on the next screen and choose the following counters:

  • Processor – select the entire section and choose < _Total instance>.
  • Network Interface – select the entire section and choose <All instances>.
  • Logical Disk – select the entire section and choose <All instances>.
  • Physical Disk – select the entire section and choose <All instances>.
  • SQL Server: Access Methods – select the entire section and choose <All instances>.
  • SQL Server: Buffer Manager – select the entire section and choose <All instances>.
  • SQL Server: Databases – select the entire section and choose <All instances>.
  • SQL Server: Latches – select the entire section and choose <All instances>.
  • SQL Server: Locks – select the entire section and choose <All instances>.
  • SQL Server: Wait Statistics – select the entire section and choose <All instances>.

Note that most of the counters are per database instance.  Most SQL Server environments have many database instances stored in a single SQL Server database.  Choose <All Instances> to gather data for all databases.  This will provide a normalized set of data across all instances within the database.  If there is a single database instance that is very important, and the others, less so, or if the activity on the other databases is so low that it could effect the normalized average database activity, choose just that one database instance only. 

If SQL Server is deployed in a VMWARE environment, VMWARE also has a set of monitoring tools that allow you to review performance data of the client OS image, the VMWARE ESX host, and data stores created on InfiniBox.  InfiniBox fully supports a wide range of VMWARE API’s to allow this monitoring to be conducted in the vCenter Console. 

The intent is to correlate data between SQL Server, Host OS, VM/ESX / HyperV and Storage to get an overall picture of performance through all elements. 

Was this article helpful?
0 out of 0 found this helpful

0 out of 0 found this helpful

Last edited: 2022-08-06 08:10:18 UTC