Back in May 2014, Kevin Stern of Quest reached out to me and let me in on the fact that they have released a new version of Quest Benchmark Factory.
This was through a comment he posted @ http://danieladeniji.wordpress.com/2014/05/10/technical-quest-software-benchmark-factory-for-databases/.
Since then I have wanted to download the new version and kick it around a bit.
In this post, I will talk a bit about my experience.
Database – Preparation
Database – Sample
We will use the AdventureWorks Database. Unfortunately, our target database is SQL Server Express and that product version does not ship with sample databases. And, so we downloaded a version from Microsoft; specifically https://msftdbprodsamples.codeplex.com/releases/view/125550.
USE [master] GO
RESTORE DATABASE [AdventureWorks2014] FROM DISK = N'C:\Downloads\AdventureWorks2014.bak' WITH FILE = 1 , MOVE N'AdventureWorks2014_Data' TO N'E:\DATA\AdventureWorks2014_Data.mdf' , MOVE N'AdventureWorks2014_Log' TO N'F:\LOG\AdventureWorks2014_Log.ldf' , NOUNLOAD , STATS = 5
Database – Objects
As we have an entire pre-written database for our use, we will need very minimal modifications.
Actually, we will simply add two stored procedure that serve same purpose; which is to query the person table.
The Stored Procedures will accept two arguments firstname and lastname and will filter against the person.person table based on the argument contents.
Here is SP 1, it exclusively uses the or clause.
Here is SP 2, it uses the case clause.
Database – Sample Data
To get representative test data, we will use bcp to get data out of our database.
ODBC Data Source
System Data Source
We created a System ODBC Data Source.
Data Source Name and SQL Server
Database Connection Detail
Quest Benchmark Factory
Here is a table that shows our Benchmark Factory (BF) Script:
Here is our Transaction Mix
Transaction Bind Parameters
Here is what our User-load looks like.
We will start with a single user, and move on to 5, 10, 15, and 20 users.
Here is what the Agent Screen looks like initially.
The change between the Before and After is that we checked our computer name to indicate it is part of our test.
If you do not make that test, you will see a warning that reads
Missing Agents To Use For Testing
Our test result is pasted below:
- The second stored procedure is a bit slower
- SP 1 executed 1771 times, while SP2 executed a slightly smaller 1766; so when allocated identical time scope we were able to accomplish slightly more with SP1
- As we iterated a bit more with SP1, we returned a bit more records
SQL Sentry Plan Explorer
In all truth, though second Stored Procedure is a tad bit slower, I still could not tell why.
And, so I installed and launched Huntersville’s own SQL Sentry Plan Explorer. See how I little know it took a young lady on Bart to tell me that Huntersville is a suburb of Charlotte, North Carolina.
To get the Actual Plan I entered the query.
In summary, SQL Server thinks the second query is much worse than it ended up been.
There are so many tools one can use to dig a bit deeper into queries. Quest Software certainly earns its honorable role and belong in the tool-bag of SQL aficionados.