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.

 

Quest – LiteSpeed – Large Log Files

Quest – LiteSpeed – Large Log Files

Quest LiteSpeed allows us to drastically reduce the size of our Microsoft SQL Server backup files.  And, so we use it quite a bit.

It runs well behind the scenes.  But, occasionally there is one setting you might want to keep an eye on.

That setting is the default “ErrorLogPath”.

On one of our machines, the system folder, kept filling up. We really could not find the culprit. Is it this or that:

  1. Is it Microsoft SQL Server Temp folder per the Service  Account  – Especially per hugh ETL jobs (Excel file loading, bcp, etc)
  2. Is it one of the other services – Backup

Nothing that easy to find…

Tried to list for “hugh” files on the system drive — Nothing too big.

Finally, limited our search to looking for *.log files.  And, then we found it:

So now we know that we have a couple of big files in the “C:\Documents and Settings\All Users\Application Data\Quest Software\LiteSpeed\SQL Server” folder.

Next thought is to determine how can we change the logging location from our system path (C:) to somewhere else less “strategic” to system uptime.

Of coure Googled it.

Can Litespeed log activity to a UNC path?
https://support.quest.com/Search/SolutionDetail.aspx?ID=SOL71230

So launched regedit and navigated to HKEY_LOCAL_MACHINE\SOFTWARE\Imceda\SQLLiteSpeed\Engine

Note that it is still under Imceda and not the new owner “Quest Software”.

Changed the folder listed in “ErrorlogPath” entry and stopped all running Quest Applications such as SqlLiteSpeedx32.  The original log files can not be deleted if the Application using\writing to them is not terminated.  Unless you ….

Anyways stopped the current SQL Backup Job and now orphaned “app” as noted in previous paragraph.

Will re-start the SQL backup job  and hope to have Quest use the new folder….

Only now if I know

  1. Why the Quest Log files are over 14 GB
  2. How to set a wrap around limit on the size of the Quest Log file
  3. How to disable logging

References:

  1. Can Litespeed log activity to a UNC path?
    https://support.quest.com/Search/SolutionDetail.aspx?ID=SOL71230
  2. ENHANCEMENT REQUEST – How to set default path for LiteSpeed log file during the Installation process?
    https://support.quest.com/search/SolutionDetail.aspx?id=SOL59970

Quest – LiteSpeed – Installation \ Uninstall – Error – 2203/-2147024629

Quest – LiteSpeed – Installation \ Uninstall – Error – 2203/-2147024629

Quest / LiteSpeed - Windows Installer - Error Number 2203 / -2147024629

To Repair:

  1. Start and Stop the “Windows Installer” Windows Service
  2. Check the Environment Variable “Temp”
    1. If missing, create temp
    2. If directory refer to is missing, create it or Change the value of the “Environment” Variable