Databases Reference
In-Depth Information
Determining long-running transactions
Long-running transactions block the other transactions and in turn introduce new long-running
transactions! This affects the performance of the database server.
As a DBA, you should regularly monitor your database transactions and should take necessary
remedial steps whenever you identify such long-running transactions, as they can degrade the
performance of the application drastically.
In this recipe, you will see how you can monitor the transactions by looking at their time
duration. If you frequently find some transactions running for a long time, you may probably
want to find if they are blocked by other transactions. You may also probably look into the
query to investigate which statements of the transaction are taking more time and why, so
that you can know which part of the T-SQL code should be modified accordingly.
Getting ready
This will be a very simple recipe that will show you how to track the transaction time. With the
script that has been provided in this example, you can see all the current running transactions
along with the time duration for which they have been executing.
The following are the prerequisites for this recipe:
F
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.
F
A sample
AdventureWorks2012
database on the SQL Server instance. For more
details on how to install the
AdventureWorks2012
database, please refer to the
Preface
of this topic.
How to do it...
Follow the ensuing steps to perform this recipe:
1.
Start SQL Server Management Studio and establish a connection to the SQL Server
hosting the
AdventureWorks2012
database.
2.
In the query window (Connection-1), type the following T-SQL statements and then
execute them to begin a sample transaction from Connection-1:
--Beginning a sample transaction
USE AdventureWorks2012
GO
BEGIN TRANSACTION
SELECT * FROM Sales.SalesOrderHeader
Search WWH ::
Custom Search