Recently I had an engagement where I needed to collect DBCC SQLPERF data at 15 second intervals and wanted to write the results to a file. I’ve always used OSQL for this type of thing but wanted to see if there was another way so I searched the Internet to see if DBA’s were using any different methods to accomplish this task. In my search I found some articles relating to the sp_OACreate and the sp_OAMethod stored procedures and thought I would see if using these stored procedures would be better for performance than my old method.

The tests below were used to gauge performance for the sp_OACreate stored procedures versus the osql.exe application.

Test 1: Query Analyzer Baseline

This T-SQL script will print @i 100,000 times. This script was used to get a duration baseline for the same statements below while using different options. This script took 37 seconds to complete on my test machine.

DECLARE @i INT
SET @i = 1
WHILE @i <= 100000
 BEGIN
  PRINT @i
  SET @i = @i + 1
 END

Test 2: OSQL – From Query Analyzer

This T-SQL script uses xp_cmdshell to utilize osql.exe to print @i 100,000 times. This script took only 5.86 seconds to complete. It looks like the additional overhead of returning results for display in Query Analyzer cost me 32.6 seconds.

DECLARE @cmd VARCHAR(255)
SET @cmd = 'osql -E /Q "SET NOCOUNT ON SELECT GETDATE() DECLARE @i INT SET @i = 1 WHILE @i <= 100000
            BEGIN PRINT @i SET @i = @i + 1 END SELECT GETDATE()" /o c:\test2.csv' 
EXEC xp_cmdshell @cmd

Test 3: OSQL – From a Command Prompt

This script uses osql.exe and is executed from a command prompt to print @i 100,000 times and save the output to a csv file. This script only took 5.69 seconds to complete. It seems as though the additional overhead for running the xp_cmdshell from T-SQL is very small.

osql -E /Q "SET NOCOUNT ON SELECT GETDATE() DECLARE @i INT SET @i = 1
           WHILE @i <= 100000 BEGIN PRINT @i SET @i = @i + 1 END 
  SELECT GETDATE()" /o c:\test3.csv

Test 4: VB Script

This script is a VB script that creates a COM object, creates a csv file and loops through “i” for 100,000 iterations and appends the writeline for “i” to the end of the file for each iteration. This script creates a file COM object and destroys it only once for the duration of the script but is executed from the OS and not T-SQL. This script completed in 5.8 seconds which is about as quick as the

osql.exe command.

Dim objFS
Dim objText
Dim i
i = 1
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objText = objFS.OpenTextFile("c:\test4.csv", 8, True)
For i = 1 To 100000
    If i = 1 Then
      objText.Writeline("Start:  " & Date & " " & Time())
      objText.Writeline(i)
    ElseIf i = 100000 Then
      objText.Writeline(i)
      objText.Writeline("End:  " & Date & " " & Time())
    Else
      objText.Writeline(i)
    End If
Next
objText.Close()

Test 5: sp_OA – From Query Analyzer

This script uses the sp_OA stored procedures and a user stored procedure to create a COM object and destroy it for each @i value for 100,000 iterations. This approach took 487 seconds for this script to complete (obviously not a likely option for production systems).

Code for the usp_UseOA Stored Procedure

IF NOT EXISTS (SELECT (1) FROM master..sysdatabases WHERE name = 'SE')
BEGIN
CREATE DATABASE SE
END
GO
USE SE
GO
IF EXISTS (SELECT (1) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_UseOA]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[usp_UseOA]
END
GO
CREATE PROCEDURE usp_UseOA (
   @File varchar(1000)
 , @Str varchar(1000)
) 
AS
DECLARE @FS int
        , @OLEResult int
        , @FileID int
EXECUTE @OLEResult = sp_OACreate 
 'Scripting.FileSystemObject'
 , @FS OUT
IF @OLEResult <> 0 
BEGIN
PRINT
'Error: Scripting.FileSystemObject'
END
-- Opens the file specified by the @File input parameter 
execute @OLEResult = sp_OAMethod 
   @FS
   , 'OpenTextFile'
, @FileID OUT
, @File
, 8
, 1
-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution 
IF @OLEResult <> 0 
BEGIN
PRINT 'Error: OpenTextFile'
END
-- Appends the string value line to the file specified by the @File input parameter
execute @OLEResult = sp_OAMethod 
   @FileID
, 'WriteLine'
, Null
, @Str
-- Prints error if non 0 return code during sp_OAMethod WriteLine execution 
IF @OLEResult <> 0 
BEGIN
PRINT 'Error : WriteLine'
END
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Execution Script

DECLARE
  @file VARCHAR(255)
, @i INT
SET @i = 1
SET @file = 'c:\test5.csv'
WHILE @i <= 100000
 BEGIN
   -- executes this stored procedure for each @i value
   EXEC SE..usp_UseOA @file, @i
   SET @i = @i + 1
 END

Test 6: sp_OACreate – From Query Analyzer

This script uses the sp_OA stored procedures to create a file object and destroys it once. This script completed in 67 seconds. It is apparent that the additional overhead of the sp_OACreate, sp_OAMeathod and sp_OADestroy are significant enough to cost the execution 61 seconds compared to the VB script.

DECLARE 
 @i INT
 , @File VARCHAR(1000)
 , @FS INT
 , @RC INT
 , @FileID INT
 , @Date DATETIME
SET @File = 'c:\test6.csv'
SET @i = 1
EXEC @RC = sp_OACreate 
  'Scripting.FileSystemObject'
, @FS OUT
IF @RC <> 0 
 BEGIN
PRINT 'Error:  Creating the file system object'
 END
-- Opens the file specified by the @File input parameter
EXEC @RC = sp_OAMethod 
 @FS
 , 'OpenTextFile'
 , @FileID OUT
 , @File
 , 8
 , 1
-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution 
IF @RC <> 0 
 BEGIN
PRINT 'Error:  Opening the specified text file'
 END
-- Printing start time in file
SET @Date = GETDATE()
EXEC @RC = sp_OAMethod 
 @FileID
 , 'WriteLine'
 , Null
 , @Date
-- Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @RC <> 0 
BEGIN
PRINT 'Error:  Writing string data to file'
END
WHILE @i <= 100000
 BEGIN
   -- Appends the string value line to the file specified by the @File input parameter
EXEC @RC = sp_OAMethod 
      @FileID
, 'WriteLine'
, Null 
      , @i
   -- Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @RC <> 0 
BEGIN
PRINT 'Error:  Writing string data to file'
END
SET @i = @i + 1
END
-- Printing completion time in file
SET @Date = GETDATE()
EXEC @RC = sp_OAMethod @FileID
, 'WriteLine'
, Null
, @Date
IF @RC <> 0 
 BEGIN
PRINT 'Error:  Writing string data to file'
 END
EXECUTE @RC = sp_OADestroy @FileID
EXECUTE @RC = sp_OADestroy @FS

Conclusion

The conclusion to these tests dramatically show the performance benefits of using OSQL over the sp_OA stored procedures. The overhead associated to the use of sp_OA stored procedures comes from the additional operations required for looking up the PROGID, finding the path and name of the dll or exe in the registry, then testing for a set of base methods for validity and so on. The performance implications can also be very bad for the rest of the SQL Server system. The sp_OA stored procedures run in the SQL Server memory space and they use SQL Servers resources, more specifically in the mem-to-leave memory area. This can also induce memory pressure to the areas of SQL Server also running in the virtual address space such as the procedure cache and other vital caches. Also each use of the sp_OA stored procedures cost a context switch to the system costing at lease 1000 ticks in CPU processing.

When comparing OSQL running from a command prompt to a VB Script running from the OS, there is no real performance gain either way with these simple operations tested in this document.

OSQL seems to be a much better choice for performance when attempting to save output to a file. The sp_OA series of stored procedures allow for a great deal of flexibility but should not be used when performance is a factor, more to the point, they should probably be avoided altogether unless they are used for administrative tasks or other one off operations.

Zach Nichter

DBA Consultant

znichter@scalabilityexperts.com