How to Benchmark SQL Server for a hardware migration

19 January,2022 by Tom Collins

I find myself working on SQL Server 99.9% of the time - utilising a range of metrics , wait stats, and other methods of assessing performance on SQL Server databases.  But how do you approach creating a Benchmark test plan when there is a large infrastructure change , such as a hardware upgrade? 

The  database servers may reside on VMs on ESX cluster, but abstracted from the underlying hardware. This makes it more complex to assess the impact. Some research is required to assess the current and target platform and what the overall goal is for the migration. 

If hundreds or thousands of SQL servers are involved , monitoring at a granular level may not be the most effective method. Sure , I could capture every wait stat , creating some running averages , couple with performance counters. But the reality is your servers are  moving to a different  hardware with different access patterns.   Changes in the infrastructure setup may favour one type of workload another - coupled with automated server builds may limit the options for customising 

 

In a large migration I prefer a multi-tiered approach. I've used some of these tactics in different migrations

1) Work with the hardware & storage teams to understand the current & target environments. You will want to establish certain assumptions:

a) For cpu and memory will the VM to Host relationship remain similar ? Is part of the migration project to consolidate resources ? 

b) Is RAM is currently not overcommited on hosts ? Is that likely to change?

c) vcpu to core ratios - what's the plan ?  

d) Deduplication and compression - what are the ratios on the target platforms compare to the current platform?     Will TDE be applied - which may influence how cpu and storage is managed

e) Are there metrics you can collect on a regular basis from the current and target environments ? Some useful metrics are : Disk Write Latency , Disk Write IOPS,CPU Contention, CPU Demand,Memory contention and Memory Utilization.  Work with the VI team to extract these figures  & use them as a guide for overall . A popular virtual infrastructure platform is VMWare , which comes with the vROps tool . vROps helps monitor, troubleshoot, and manage the health and capacity of the  virtual environment.   There are some useful statistics available.

 

2) Create some custom workload testing on the current environment . There are different approaches to 

TPC-H generate test data , test queries and sql database benchmark

SQL Server test data generation testing tools

3) Collect runtime information on ETL and other related SQL Server jobs. Save the data into a database and extract similar information when migration has completed , create some comparisons to identify any patterns requiring further analysis

 

 

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to Benchmark SQL Server for a hardware migration


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer