How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.

How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.