The Particular throughput tool can be installed locally and run against a production system to discover the throughput of each endpoint in a system over a period of time.
This article details how to collect endpoint and throughput data when the system uses the SQL transport. Refer to the throughput counter main page for information how to install/uninstall the tool or for other data collection options.
Once installed, execute the tool with the database connection string used by SQL Server endpoints, as in this example:
throughput-counter sqlserver --connectionString "Server=SERVER;Database=DATABASE;User=USERNAME;Password=PASSWORD;"
The tool will run for slightly longer than 24 hours in order to capture a beginning and ending
RowVersion value for each queue table. A value can only be detected when a message is waiting in the queue to be processed, and not from an empty queue, so the tool may execute multiple SQL queries for each table. The tool will use a backoff mechanism to avoid putting undue pressure on the SQL Server instance.
--connectionStringSource must be used to provide the tool with connection string information.
|A single database connection string that will provide at least read access to all queue tables.|
|When the |
|Provide a file containing database connection strings (one per line) instead of specifying a single connection string as a tool argument. The tool will scan the databases provided by all connection strings in the file for NServiceBus queue tables. With this option, multiple catalogs in multiple database servers are supported.|
|Masks sensitive information in the generated report. See masking private data.|
|Sets the customer name. If not provided, the tool will prompt for the information.|
|Will not prompt for user input, so that the tool can be used from environments such as a continuous integration system.|
The tool executes the following SQL queries on the database connection strings provided.
The tool uses this query to discover what tables in a SQL database catalog have the table structure that matches an NServiceBus queue table. This query is executed only once when the tool is first run.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DISTINCT t.TABLE_SCHEMA AS TableSchema, t.TABLE_NAME as TableName FROM [INFORMATION_SCHEMA].[TABLES] t WITH (NOLOCK) INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cId WITH (NOLOCK) ON t.TABLE_NAME = cId.TABLE_NAME AND cId.COLUMN_NAME = 'Id' AND cId.DATA_TYPE = 'uniqueidentifier' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cCorrelationId WITH (NOLOCK) ON t.TABLE_NAME = cCorrelationId.TABLE_NAME AND cCorrelationId.COLUMN_NAME = 'CorrelationId' AND cCorrelationId.DATA_TYPE = 'varchar' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cReplyToAddress WITH (NOLOCK) ON t.TABLE_NAME = cReplyToAddress.TABLE_NAME AND cReplyToAddress.COLUMN_NAME = 'ReplyToAddress' AND cReplyToAddress.DATA_TYPE = 'varchar' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cRecoverable WITH (NOLOCK) ON t.TABLE_NAME = cRecoverable.TABLE_NAME AND cRecoverable.COLUMN_NAME = 'Recoverable' AND cRecoverable.DATA_TYPE = 'bit' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cExpires WITH (NOLOCK) ON t.TABLE_NAME = cExpires.TABLE_NAME AND cExpires.COLUMN_NAME = 'Expires' AND cExpires.DATA_TYPE = 'datetime' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cHeaders WITH (NOLOCK) ON t.TABLE_NAME = cHeaders.TABLE_NAME AND cHeaders.COLUMN_NAME = 'Headers' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cBody WITH (NOLOCK) ON t.TABLE_NAME = cBody.TABLE_NAME AND cBody.COLUMN_NAME = 'Body' AND cBody.DATA_TYPE = 'varbinary' INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] cRowVersion WITH (NOLOCK) ON t.TABLE_NAME = cRowVersion.TABLE_NAME AND cRowVersion.COLUMN_NAME = 'RowVersion' AND cRowVersion.DATA_TYPE = 'bigint' WHERE t.TABLE_TYPE = 'BASE TABLE'
The tool uses these queries to discover the current
ROWVERSION in each queue table. The start query is used at the beginning of tool execution, and the end query is used at the end of tool execution roughly 24 hours later.
These queries will be executed at minimum once per queue table. Because it is impossible to get a value if the queue is empty (there are no rows in the table) the tool may need to retry several times in order to get a value for each queue. The retries are time-limited to a 15 minute collection window at the beginning and end of the tool execution, and uses a backoff mechanism to avoid putting any undue stress on the SQL Server instance.
select min(RowVersion) from [SCHEMA_NAME].[TABLE_NAME] with (nolock);
select max(RowVersion) from [SCHEMA_NAME].[TABLE_NAME] with (nolock);