Nimble All Flash Guidelines for Databases

[Dataguy1]

Hi, I am running an AF60 All Flash nimble array with SQL Server VM's on VMWare 6.7.  I am looking for a definitive guideline from HP\Nimble for the best setup to achieve the fastest possible database performance on this SAN 

I have read the following documents

- HPE NImble Storage Deployment Considerations for MSSQL Server (May 2017)
- HPE Deployment Considerations for vSphere 6
- HPE_Nimble_Storage_Deployment_Considerations_for_VMware_vSphere
- Architecting MS SQLServer on VMWARE VSphere Best Practices Guide 

I think the optimum design\settings are:
Nimble thick provisioned, Relevant SQL Server profiles (4kb log&temp and 8kb data), dedup off, compress on, VMWare Eager-zeroed thick.

Our SAN Admin is being told the following settings are just as good as the above
Nimble thin provisioned, VMWare template for all (4kb), dedup on, compress on.

We have already tested the difference between 8kb and 4kb blocksize and 8kb is consistently faster by a few percent. We havent tested thin vs thick provisioned yet but all comments say thick prov. should be faster.

I have been told the HP SQL Server document has no relevance as it was written for Physical SQL Servers but its policies still seem relevant to me and are proving to be marginally faster in testing. The newer VMWare document (which includes all-flash SANs) is still recommending thick provisioned volumes. 

Our SAN admin wants thin provisioned because his stats look better and because he has told him the basic VMWare policy is fine. We want the data as fast as possible.

Ques 1 Has anyone got any experience of optimising databases on All-flash arrays?
Ques2 Has anyone seen an newer guideline from Nimble for Flash arrays (which would solve the problem here)
Ques 3 Has anyone seen any nimble POCs or White papers discussing config options

Any thoughts\comments would be appreciated

Thanks

[Nick_Dyer]

Hello, welcome to the forums.

Nimble doesn't have a concept of "best practices" - as truth be told, there are many ways to implement applications, and all have their upsides and downsides. Instead, they're called Deployment Considerations - things to consider doing in certain situations... but certainly are NOT things to take as gospel.

In a virtualised environment, there's really no need to do any type of thick provisioning. Yes, you could use various performance policies to optimise the block size for each VMFS datastore, however this again becomes a little more complex to manage in a virtualised world - and the performance benefit is a few percent between them when doing performance benchmarking - but not that important in the real world.

I also would NOT switch off deduplication. It's going to make your storage less efficient, and unless you're burning the array CPU at >100% - again it won't deliver that much more of a performance increase. This is because the design of the Nimble array is for the CPU to be the bottleneck, rather than storage/app data services or the backend RAID/drives.

I personally would recommend looking at VVols, as this will allow you to set the policies of the application to the storage via VMware (ie SQL Logs and SQL DB with different application policies and profiles). You can also perform app-consistent snapshots to SQL via VVols very easily, which might be of interest.

You can always contact Nimble Support (who are awesome) at any point if you want to check or validate your thinking and are happy to assist.

Hope this helps, and Merry Christmas!

[Dataguy1]

Hi Nick, 

Thanks for the quick reply. Our requirement (or deployment consideration) is for maximum performance over any space saving for Tier 1 databases. We dont want to have to zero blocks before writing or un-dedup data before reading. Maximum speed is the name of the game as we need to store thousands of rows of data sub-second.

The VMware "sql-server-on-vmware-best-practices-guide", page 53, mentions all flash arrays and states that thick provisioning should be used for all database tiers. All of the Nimble documents say that for maximum performance use thick provision and dont dedup. 

The nimble message is very confusing. Performance is important to us and there is a definite hit in performance if following the general advice. If different profiles are of no benefit on Nimble arrays then why are they provided at all? 

Using 8kb blocksize over 4kb we are seeing an increase of almost 2% tpm. We have tested thick vs Thin today and Thick provisioned can achieve 6.53% tpm more for reads and 4.5% tpm more for a mixture of read\write (40/60). 

Thanks getting back to me and thanks for the tip about VVols... they sounds very interesting - we will try and look into them soon.

Merry Christmas to you too!

Thanks 

[Nick_Dyer]

Hi

Indeed, it's all relative however. Squeezing a few more % of top-end performance using commited policies and tuning might absolutely be what's required, the flip side is in real world those few % performance gains might not be realised (as the app might never do the top-end performance) yet it increases the overhead of management, as you have different policies assigned to datastores and need to ensure the right VMDKs are placed in the right datastores. Hence why typically the verbiage is to do whatever you as the customer feel is right.

VVols are the happy middle ground here - it gives you the per-VM policy management you're looking for, yet makes it very simple to manage from a storage and VM admin perspective.