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.
Running the tool
Once installed, execute the tool with the database connection string used by SQL Server endpoints.
If the tool was installed as a .NET tool:
throughput-counter sqlserver [options] --connectionString "Server=SERVER;Database=DATABASE;User=USERNAME;Password=PASSWORD;"
Or, if using the self-contained executable:
Particular.EndpointThroughputCounter.exe sqlserver [options] --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 identity value for each queue table.
Options
Either the --connectionString or --connectionStringSource must be used to provide the tool with connection string information.
| Option | Description |
|---|---|
--connectionString | A single database connection string1 that will provide at least read access to all queue tables. |
--addCatalogs | When the --connectionString parameter points to a single database, but multiple database catalogs on the same server also contain NServiceBus message queues, the --addCatalogs parameter specifies additional database catalogs to search. The tool replaces the Database or Initial Catalog parameter in the connection string with the additional catalog and queries all of them. With this option, only a single database server is supported.Example: --connectionString |
--connectionStringSource | 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. Example: --connectionStringSource |
--queueNameMasks | Masks sensitive information in the generated report. See masking private data. |
--customerName | Sets the customer name. If not provided, the tool will prompt for the information. Example: --customerName "Particular Software" |
--unattended | Will not prompt for user input, so that the tool can be used from environments such as a continuous integration system. |
--skipVersionCheck | Will not perform the check, for use in environments when outgoing network access creates alerts. |
1 See examples of SQL Server connection strings. Authentication is often via username/password User Id=myUsername;Password=myPassword, integrated security Integrated Security=true, or active directory with MFA Authentication=ActiveDirectoryInteractive;UID=user@domain..
In recent versions of Microsoft's Sql Server drivers encryption has been enabled by default. When trying to connect to a Sql Server instance that uses a self-signed cerftificate, the tool may display an exception stating The certificate chain was issued by an authority that is not trusted. To bypass this exception update the connection string to include ;Trust Server Certificate=true.
What the tool does
The tool executes the following SQL queries on the database connection strings provided.
Find queues
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 C.TABLE_SCHEMA as TableSchema, C.TABLE_NAME as TableName
FROM [INFORMATION_SCHEMA].[COLUMNS] C
WHERE
(C.COLUMN_NAME = 'Id' AND C.DATA_TYPE = 'uniqueidentifier') OR
(C.COLUMN_NAME = 'CorrelationId' AND C.DATA_TYPE = 'varchar') OR
(C.COLUMN_NAME = 'ReplyToAddress' AND C.DATA_TYPE = 'varchar') OR
(C.COLUMN_NAME = 'Recoverable' AND C.DATA_TYPE = 'bit') OR
(C.COLUMN_NAME = 'Expires' AND C.DATA_TYPE = 'datetime') OR
(C.COLUMN_NAME = 'Headers') OR
(C.COLUMN_NAME = 'Body' AND C.DATA_TYPE = 'varbinary') OR
(C.COLUMN_NAME = 'RowVersion' AND C.DATA_TYPE = 'bigint')
GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME
HAVING COUNT(*) = 8
Get snapshot
The tool uses this query to get a snapshot of the identity value for each queue table. It is executed once per queue table when the tool is first run, then again at the end of the tool execution. The snapshots are compared to determine how many messages were processed in that table while the tool was running.
select IDENT_CURRENT('[SCHEMA_NAME].[TABLE_NAME]')