Quest Software – Benchmark Factory – v7


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 @

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


Restore Database

USE [master]
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'
  , 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.


use [AdventureWorks2014]

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

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

    set nocount on;

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






Here is SP 2, it uses the case clause.

use [AdventureWorks2014]

if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null

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

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

    set nocount on;

    select *
    from   [Person].[Person]

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


                        Lastname = 
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname


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.




Data Source Name and SQL Server



Database Connection Detail



Quest Benchmark Factory





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




Transaction SQL



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:


Userload 20 Results




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





Actual Plan


To get the Actual Plan I entered the query.


 Test Query:

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

      @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







Metric SP-1 SP-2
Estimated Cost 6.2% 93.8%
Duration 14 20
CPU 15 32
Reads 124 128
Estimated I/O  5.5%  94.5%
Est Rows  1  632
Actual 124 128
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.




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?

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


  1. Can Litespeed log activity to a UNC path?
  2. ENHANCEMENT REQUEST – How to set default path for LiteSpeed log file during the Installation process?

Quest – Performance Analysis – Client Installs Causing Havoc

A while ago, we installed Quest / Performance Analysis and left it dormant.  The server has since being decomission.  But, unfortunately a few client installs are still orphaned out there.

It seems that client caches old authentication (username \ password) data which at times raises their old head.

I suppose we could back track a bit.  Really was not sure that the problem was Quest.  But, a “good” search through Event Viewer \ Security Log revealed the “Process ID” of the Application that was causing the failed login…


Logon Failure:
Reason: Unknown user name or bad password
User Name: joe
Domain: LAB
Logon Type: 2
Logon Process: Advapi
Authentication Package: Negotiate
Workstation Name: LABCOMP1
Caller User Name: LABCOMP1$
Caller Domain: LAB
Caller Logon ID: (0x0,0x3E7)
Caller Process ID: 4008
Transited Services: –
Source Network Address: –
Source Port: –


Thank Goodness the process was still running; as we were able to use “Task Manager” to match Process ID 4008 to quest_sc_mw.exe

And so, I started searching for a way to uninstall Quest or the very least disable it.

So looked through at the “Start up” folder and the “Services” Applet, but no luck.

Lauched SysInternals “Autoruns”  and ordered by ImagePath.  Viola found it…\

Once found, still do not know how to un-install it as it is not showing up “Add\Remove” programs – Probably the install was done via deamon.  Whatever the case, will change start-up mode form Automatic to Disabled using Services Applet.

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