Quest Software – Benchmark Factory – v7

Prelude

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 @ https://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.

 

Restore Database

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.

 

 

dbo,usp_PersonFetchFilteredOnName_1

 

Here is SP 1, it exclusively uses the or clause.

 

use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_1') is null
begin
    exec('create procedure dbo.usp_PersonFetchFilteredOnName_1 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_1
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin

    set nocount on;

    select *
    from   [Person].[Person]
    where
                (
                       (@Firstname is null)
                    or (@Firstname = '')
                    or (Firstname = @Firstname)
                )
            and
                (
                       (@Lastname is null)
                    or (@Lastname = '')
                    or (Lastname = @Lastname)
                )


end
go

 

 

dbo.usp_PersonFetchFilteredOnName_2

 

Here is SP 2, it uses the case clause.

use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_2 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_2
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    select *
    from   [Person].[Person]
    where
            (

                    (
                        Firstname = 
                                    case
                                        when (@Firstname is null) then Firstname
                                        when (@Firstname = '') then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

Database – Sample Data

To get representative test data, we will use bcp to get data out of our database.

 

bcp "select '\"' + Firstname + '\"' as Firstname, '\"' + Lastname + '\"' as Lastname, Count(*) as Cnt from [AdventureWorks2014].[Person].[Person] group by Firstname, Lastname order by count(*) desc " queryout person.txt -S.\SQLEXPRESS_V2014  -E -T -c -t","

if not exist c:\sqlserver\data mkdir c:\sqlserver\data
xcopy C:\Personal\DanielAdeniji\Blog\QuestSoftware\BenchmarkFactory\v7\DatabaseScripts\GenerateTestData\person.txt  c:\sqlserver\data /Q /y /D

 

 

ODBC Data Source

System Data Source

We created a System ODBC Data Source.

 

SelectADriver

 

Data Source Name and SQL Server

 

DatasourceNameAndSQLInstance

Database Connection Detail

 

DatabaseConnectionSettings

Quest Benchmark Factory

 

Script

 

Introduction

Here is a table that shows our Benchmark Factory (BF) Script:

 

Scenario Parameter  Value
Transaction 1
 SQL exec AdventureWorks2014.dbo.usp_PersonFetchFilteredOnName_1  ?,?
 Param1=@Firstname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,1)
 Param2=@lastname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,2)
Transaction 2
 SQL exec AdventureWorks2014.dbo.usp_PersonFetchFilteredOnName_2  ?,?
 Param1=@Firstname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,1)
 Param2=@lastname  $BFFileArray(“C:\sqlserver\data\person.txt”,SEQUENTIAL,2)

 

 

 

Transaction Mix

Here is our Transaction Mix

 

EditJob

 

Transaction SQL

StoredProcedure-SQL

 

Transaction Bind Parameters

 

StoredProcedure-BindParameters

 

 

 

 

User-load

 

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.

 

userload

 

 

Agent

 

Before

Here is what the Agent Screen looks like initially.

 

Agent-Before

 

 

After

 

Agent-After

 

 

The change between the Before and After is that we checked our computer name to indicate it is part of our test.

 

Warning

If you do not make that test, you will see a warning that reads

 

Missing Agents To Use For Testing

 

MissingAgentsToUseForTesting

 

 

 

Results

 

Picture

Our test result is pasted below:

 

Userload 20 Results

 

Tabulated

 

Metric SP-1 SP-2
 Avg Response Time  0.014  0.018
 Avg. Transaction Time  0.014  0.018
 Executions  1771  1766
 Rows  8712  8707
 Errors  0  0

 

 

Quick Explanation

 

  • 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.

 

Estimated Plan

 

EstimatedPlan

 

 

Actual Plan

 

To get the Actual Plan I entered the query.

 

 Test Query:

declare
      @Firstname nvarchar(50) 
    , @Lastname  nvarchar(50) 

select 
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

exec dbo.usp_PersonFetchFilteredOnName_1
      @Firstname = @Firstname
    , @Lastname  = @Lastname

exec dbo.usp_PersonFetchFilteredOnName_2
      @Firstname = @Firstname
    , @Lastname  = @Lastname

 

Actual Plan

Actual Plan

ActualPlan

 

 

 

Tabulated

 

Metric SP-1 SP-2
Estimated Cost 6.2% 93.8%
Duration 14 20
CPU 15 32
Reads 124 128
Writes
Estimated I/O  5.5%  94.5%
Est Rows  1  632
Actual 124 128
Writes
Estimated I/O  5.5%  94.5%
Est Rows  1  632
Actual Rows 5 5
Key Look up 1 1
Index Scan  1 1

 

 

In summary, SQL Server thinks the second query is much worse than it ended up been.

 

 

Conclusion

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.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s