DBAmon
MSSQL Synthetic Transaction Monitoring

Home | Index/DBAmon Doc. | DBAmon Version/Change History | DBAmon Event/Error Doc. | What DBAmon Monitors | DBAmon Download | Free Oracle Tool: orastat | Request Support

MSSQL Synthetic Transaction Monitoring

Description

This feature of DBAmon allows you to define a typical, non-organic (i.e. synthetic) transaction and then to track the response time of that transaction historically.

How It Works:

High Level

DBAmon simply checks for the existence of a file in a certain location. If this file is found to exist, it is read and a certain string is looked for, followed by the response time value (in MS). If everything is found to be present, then the metric is saved in the DBAmon Repository.

The Details:

  1. DBAmon looks for the text file C:\temp\DBAmon_Synthetic_Trans1.txt
  2. If the file is found to exist, it is read.
  3. If the string (starting in column 1): DBAmon_Synthetic_Trans_MS: followed by at least 1 space is found, then the value following the space is read and saved as the response time.
For example: 
DBAmon_Synthetic_Trans_MS: 65
This would be read by DBAmon as a synthetic transaction response time of 65 milliseconds.

After you setup the job to collect the response time (below in this document), the entire file will look something like: 


Job 'DBAmon - Synthetic_Transaction_1' : Step 1, 'S1' : Began Executing 2012-06-03 21:30:02

Starting at: [SQLSTATE 01000]
Jun  3 2012  9:30PM [SQLSTATE 01000]
Executing Synthetic Transaction... [SQLSTATE 01000]
===================================== [SQLSTATE 01000]
                 
-----------------
23860003.16

(1 rows(s) affected)

SQL Server Execution Times:    CPU time = 63 ms,  elapsed time = 1594 ms. [SQLSTATE 01000]
===================================== [SQLSTATE 01000]
Done with Synthetic Transaction at [SQLSTATE 01000]
Jun  3 2012  9:30PM [SQLSTATE 01000]
e_time=1593       (MS) [SQLSTATE 01000]
DBAmon_Synthetic_Trans_MS: 1593 [SQLSTATE 01000]
Done [SQLSTATE 01000]

The highlighted line above is the only line that DBAmon cares about.

Setup:

You will be setting up an MSSQL Job which will run the syntethic transaction, and write the file that you see above.

First, you need to decide which application transaction will be your synthetic transaction. You will be running it via T-SQL, so it must be specified will all required parameters, etc.

Create an MSSQL Job with 1 step. The step T-SQL should look like: 


PRINT 'Starting at:'
 
declare @s_time  DATETIME
DECLARE @e_time  CHAR(10)
 
set @s_time=getdate()
print @s_time
 
PRINT 'Executing Synthetic Transaction...'
print '====================================='

set statistics time on
select max(value) from dbamon.dbamon.metrics_detail  -- THIS IS THE SYNTHETIC TRANSACTION
set statistics time off
 
print '====================================='
PRINT 'Done with Synthetic Transaction at'
PRINT GETDATE()
 
SET @e_time = RTRIM(CAST(DATEDIFF(MS, @s_time, GETDATE()) AS CHAR(10)))
 
PRINT 'e_time=' + @e_time + ' (MS)'
PRINT 'DBAmon_Synthetic_Trans_MS: ' + @e_time
 
PRINT 'Done'

In the definition of this step, click ADVANCED. For OUTPUT FILE, specify: c:\temp\DBAmon_Synthetic_Trans1.txt and click Append output to existing file. Also click Including Step Output in Job History .

Schedule the job to run at least every 30 minutes. If it runs more than once before the next DBAmon iteration, then the last (most recent) transaction response time will be recorded.

DBAmon.com
This Document: http://dbamon.com/config/mssql_synthetic_transaction.shtml