Sql-server – How to benchmark Microsoft SQL Server

benchmarksql server

In my company we have multiple virtual machines hosting SQL Server 2008 R2 and some of these machines perform differently than others, some because of very busy Vmware hosts some because of slower connections to NAS.

Is there a way I can run some SQL code in a test SQL database or other known best practices I could use to run some performance tests in each VM and baseline/benchmark performance so that I can understand which machine behaves better than others prior to moving those machines to Prod or UAT environments? Thanks, Davide.

Best Answer

You could use one of the free SQL benchmarking tools such as Dell's Benchmark Factory for Databases (BFD) or Hammerora. Benchmark Factory has a free trial and Hammerora is opensource. They both run industry-known TPC benchmarks, and you can do custom workloads with BFD. I have only used BFD and each test 'run' can be saved as a report which I found really useful for comparisons. However Hammerora is also well supported ( eg here and here ).

You could also record one of your own known workloads with a Replay trace via Profiler / server-side trace then restore the backup and replay it in each environment. There's a bit more work to this option but would make for a more familiar test.

I also have a soft-spot for ostress which is part of the RML Utility Suite which had an update in April 2014 and I still use all the time. More of a learning curve with this free Microsoft tool, but it's really powerful. For example you can do things like "run all the .sql scripts in this directory 20 times, simulating 50 concurrent users".

In summary, if you're after a free tool and a quick start look at one of the free tools I mentioned above. If you want to invest a bit more time, have a look at Replay traces, RML and ostress.

HTH

Related Solutions

SQL Server Space Usage – Different Results from sys.dm_db_file_space_usage with Database Prefix

It looks like that particular DMO was changed to work with all databases. This can be demonstrated by running the following scripts on a 2008r2 server.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM model.sys.dm_db_file_space_usage;

-- The "other" version
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

2008R2

As you can see the output will be the same. However, if you run the same scripts on a 2012 server the output will be different.

2012

Technet explains that in the 2008R2 the DMO only worked on tempdb http://technet.microsoft.com/en-us/library/ms174412(v=sql.105).aspx and in 2012 that restriction was removed http://technet.microsoft.com/en-us/library/ms174412(v=sql.110).aspx.

Hope That Helps.

Sql-server – CPU Time Slower in SQL Server 2014 than in SQL Server 2008

If you have not done so, I would highly recommend:

1.> Installing the right version of Integration Services for Hyper-V on the host and guest(like VMWare Tools). This alone can be a performance boost.

2.> Within Hyper V manager, disable compatibility mode on the processors.

3.> Consider using a SCSI controller for data volumes.

4.> Consider disk sizes that are fixed rather than expanding.

Should you implement any of these techniques, I'd be interested in knowing if any performance improvement was gained.

Best Answer

Related Solutions

SQL Server Space Usage – Different Results from sys.dm_db_file_space_usage with Database Prefix

Sql-server – CPU Time Slower in SQL Server 2014 than in SQL Server 2008

Related Question