SQL Transactions Test

This test reports the number of transactions active in an instance of the Database Engine, and the effects of those transactions on resources such as the snapshot isolation level row version store in tempdb.

Transactions are logical units of work; a set of operations that must either all succeed or all be erased from a database in order to maintain the logical integrity of the data. All modifications of data in SQL Server databases are made in transactions. When a database is set to allow snapshot isolation level, SQL Server must maintain a record of the modifications made to each row in a database. Each time a row is modified, a copy of the row as it existed before the modification is recorded in a row version store in tempdb. Many of the measures of the MsSqlTransTest monitor the size and rate of growth of the following row version stores in tempdb:

The online index build version store is used for online index builds in all databases.
The common version store is used for all other data modification operations in all databases.

Note:

This test again, is specific to Microsoft SQL Server 2005 (or above).

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL Server 2005 (or above) that is being monitored

Configurable parameters for the test
TEST PERIOD - How often should the test be executed Host – The IP address of the Microsoft SQL server. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter. USER – If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server. password - The password of the specified user. confirm password - Confirm the password by retyping it. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server. useperfmon – By default, this flag is set to Yes, indicating that this test uses the Windows Perfmon utility by default to pull out the metrics of interest. To instruct the test to use queries for metrics collection and not Perfmon, set this flag to No. Typically, when monitoring a Microsoft SQL server in an agent-based manner, its best to go with the default setting – i.e., use Perfmon for metrics collection. However, when monitoring the Microsoft SQL server in an agentless manner, its ideal to use queries instead of Perfmon to collect the required metrics. In such cases, set this flag to No. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host. ssl - By default, the SSL flag is set to No, indicating that the target Microsoft SQL server is not SSL-enabled by default. To enable the test to connect to an SSL-enabled Microsoft SQL server, set the SSL flag to Yes. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Free space in tempdb:	Indicates the amount of space that is currently free in tempdb.	KB	There must be enough free space in tempdb to hold both the snapshot isolation level version store and all new temporary objects created in this instance of the Database Engine. When the value of this measure decreases, the Database Engine forces the version stores to shrink. During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. A message 3967 is generated in the error log for each victim transaction. If a transaction is marked as a victim, it can no longer read the row versions in the version store. When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. If the shrinking process succeeds, space becomes available in tempdb. Otherwise tempdb runs out of space and the following occurs: Write operations continue to execute but do not generate versions. An information message (3959) appears in the error log, but the transaction that writes data is not affected. Transactions that attempt to access row versions that were not generated because of a tempdb full rollback terminate with an error 3958.
Version store size:	Indicates the amount of space in tempdb that is currently being used to store snapshot isolation level row versions.	KB	This information helps determine the amount of space needed in the tempdb database for the version store. Monitoring this measure over a period of time provides a useful estimate of additional space needed for tempdb.
Version generation rate:	Indicates the rate at which new row versions are added to the snapshot isolation version store in tempdb.	KB/Sec	The values of the Version generation rate and Version cleanup rate measures can be used to predict tempdb space requirements.
Version cleanup rate:	Indicates the rate at which row versions are removed from the snapshot isolation version store in tempdb.	KB/Sec	Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. A long-running transaction prevents space in the version store from being released if it meets any of the following conditions: It uses row versioning-based isolation It uses triggers, MARS, or online index build operations It generates row versions
Version store units:	Indicates the number of allocation units currently active in the snapshot isolation version store in tempdb.	Number
Version store units creation:	Indicates the number of new version store units created in the version store since the last measurement period.	Number
Version store units deletion:	Indicates the number of version store units that were truncated since the last measurement period.	Number	A version store unit is truncated when SQL Server determines that none of the version rows stored in the version store unit are needed to run active transactions.
Update conflict ratio:	Indicates the percentage of transactions using the snapshot isolation level that have encountered update conflicts within the last second.	Percent	An update conflict occurs when a snapshot isolation level transaction attempts to modify a row that last was modified by another transaction that was not committed when the snapshot isolation level transaction started.
Longest transaction running time:	Indicates the length of time since the start of the transaction that has been active longer than any other current transaction.	Secs	Row versions are stored in tempdb for as long as an active transaction needs to access it. If the value of this measure is very high, then it indicates that a transaction has been running for an unreasonable period of time, and is thus preventing the database engine from freeing space from tempdb. If the Free space in tempdb measure touches alarmingly low levels, then you might have to identify the long running transaction and terminate it. Use fn_transactions() to identify the transaction.
Active transactions:	Indicates the number of currently active transactions.	Number	This measure is a good indicator of the current workload on the database server.
Snapshot transactions:	Indicates the number of currently active transactions using the snapshot isolation level.	Number	The value of this measure changes when the first data access occurs, not when the BEGIN TRANSACTION statement is issued. Also, note that this measure does not include system transactions.
Update snapshot transactions:	Indicates the number of currently active transactions using the snapshot isolation level that perform update operations.	Number	The sum of Update snapshot transactions and Non snapshot version transactions represents the total number of transactions that participate in version generation. The difference of Snapshot transactions and Update snapshot transactions reports the number of read-only snapshot transactions.
Non snapshot version transactions:	Indicates the total number of currently active non-snapshot transactions that generate version records.	Number
Temp tables creation rate:	Indicates the number of temporary tables/table variables created per second.	Creations/sec	Temporary tables are created in tempdb. They are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. You can create indexes and statistics on temporary tables. You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys. You can also add and drop columns from temporary tables. The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL. Unlike temporary or regular table objects, table variables have certain clear limitations. Table variables can not have Non-Clustered Indexes You can not create constraints in table variables Statistics can not be created against table variables You can not create default values on table variable columns Temporary tables are usually preferred over table variables for a few important reasons: they behave more like physical tables in respect to indexing and statistics creation and lifespan.