An attendee at my PASS Summit precon on virtualized SQL Server performance tuning on Monday asked a great question. “Do you have a list of the tools and scripts that you’re using in this precon?” Now I do! This list is an abbreviated list of the utilities and scripts that you can use to help evaluate and validate your virtualized SQL Server data platform.

Microsoft DiskSpd

DiskSpd is a synthetic disk benchmarking utility. A spiritual successor to the SQLIO utility, I use it to simulate certain SQL Server-like I/O patterns on different storage subsystems so I can see how certain storage devices respond to various disk access patterns.

The scripts that are used to generate a random workload for evaluation are as follows.

80/20 Read/Write test, 100GB workload file, 4KB disk access, 4 worker threads and 8 operations per thread – to simulate activity but not trying to overwhelm the storage

80/20 Read/Write test, 100GB workload file, 64KB disk access, 8 worker threads and 8 operations per thread – to simulate a high demand I/O workload pattern

DVDStore Database Benchmarking Utility

HammerDB is not the only database benchmarking utility in town. The DVDStore benchmarking suite is useful for repetitive database benchmarking tests where I can script it out and run it via a PowerShell/Batch file to make for very quick database activity. I have a walk-through for how to use this tool out at my company site here. The command that I used to simulate the moderate SQL Server load is as follows.

Tuning Cost Threshold of Parallelism

Jonathan Kehayias has a great query out at SQLskills that I use to help identify any and all commands in the execution plan cache that have either gone parallel or stay single threaded. With derivatives of this query, I’m able to find key queries that have a query cost to replay and validate where the Cost Threshold of Parallelism setting should be dialed in at for a given SQL Server workload.

DBATools PowerShell Module

It goes without saying how important the DBATools PowerShell module is to all DBAs around the world. I used this to help deploy and manage all of the SQL Servers that I demoed during my precon. If you’ve not yet explored it, I implore you to dig in and learn how you can use this tool to make your life better. 

Perfmon Setup for 24×7 Collection

Windows Perfmon is a fantastic means to collect and record ongoing performance metrics from your key SQL Servers. I’ve got a PDF and video set here to show you how to set up Perfmon for ongoing 24×7 collection.

Import Perfmon Data into a SQL Server Database

In addition to collecting it, we now need to load it into a database so that we can start to mine the data and extract the meaning from the data. We’ve released a PowerShell module at GitHub that will help you extract the BLG file and load the data into a SQL Server database, as well as sample mining queries to start to access key metrics within the data. We also have a video here that will show you how to use it and extract meaning from the data.

CPU-Z

CPU-Z is a wonderful free tool to help you identify all the key components and specifications of your processors underneath your most critical servers. 

Geekbench

Geekbench is my go-to choice for performing relative CPU performance comparisons. It’s per-core benchmarking metrics help me understand the performance differences that you can expect when moving to new hardware, either on-premises or in the cloud. The best part is that the database of results is searchable so you can mine the info to see the performance of other CPUs that you might be interested in moving to.

iperf

Iperf is a great free utility to help you performance network throughput testing. You download a copy, deploy it to multiple servers, and then set up one as a server and one as the test client. More details on how to use this utility is found here.

CoreInfo (Microsoft Sysinternals)

The CoreInfo utility from the Microsoft Sysinternals suite is a great utility to help you determine the in-guest CPU topology, as seen and used by your SQL Server workloads. NUMA, hyperthreading, and alignment tasks can be validated through the use of this free utility.

SQL Server Disk Stall Collector

SQL Server keeps track of the ‘stall’ rates of how quickly it can access the database data and log files on the file system. These are different metrics than what your underlying operating system tracks, as the disk stall rates can show indications of metadata contention inside SQL Server, even if the underlying operating system doesn’t see it. DMVs inside SQL Server do track this information, and while aggregate information is usually what is stored, the law of averages might mean that you lose the importance of the data. This script, documented on my other site, mines this information and helps you track this information over time so you can understand if you have hot-spots during the day or night that are causing your databases to run slow.

I really hope that these scripts and utilities helps you test, validate, and stress your infrastructure underneath your critical SQL Servers so that you can help to have the best possible platform for your data! Enjoy the rest of the PASS Summit conference, and do not hesitate to reach out if you have any additional questions on the content in my precon!