Managing Timeouts while using System.Transactions in .Net

Problem
While working with System.Transactions in .net 2.0 or higher versions, we often come across a situation where we need to execute some long running processes inside Transactions and our process gets aborted after few seconds. This demands for some long running transactions. So, lets see how this can be achieved -

• Setting the CommandTimeout for the Command object – Command object is used to execute raw TSQL/ Stored procedures though .net. This object has got a getter/setter property CommandTimeout which holds the wait time before terminating the attempt to execute the command. By default, the value is 30 seconds. So, a long running process will certainly get aborted after 30 seconds. In order to extend it over this time, we can set an appropriate timeout value. We need to set it to 0 (zero) to set it to infinite. For more information, please refer this msdn article.
• Next important factor in this sequence is setting the timeout value for the TransactionScope. The TransactionScope class has got an overloaded constructor, where we can specify the timeout TimeSpan

  TimeSpan timeout = TimeSpan.FromSeconds(30);

  using(TransactionScope scope = new 8 TransactionScope(TransactionScopeOption.Required, timeout))

  {...}

or we can also make use of the TransactionOptions.Timeout property and set its value.

TransactionOptions options = new TransactionOptions();

options.IsolationLevel = IsolationLevel.ReadCommitted;

options.Timeout = TransactionManager.DefaultTimeout;

using(TransactionScope scope = new 8 TransactionScope(TransactionScopeOption.Required, options))

{...}

By default, the value is 60 seconds. We can an appropriate value for this property or can also set it to infinite the same way as we did for the command object. We need to set it to 0 (zero) to set it to infinite.

This is the point where most of the developers think they are done after setting the timeout values for the command & the transaction objects. But this is not 100% correct. It holds true only to a time limit of 10 minutes. If you want your transaction to continue after that, setting only the above two values is not the solution. And here comes into play the TransactionManager settings.

TransactionManager sits on top of Transaction & Command. The default timeout value of TransactionManager is 10 minutes and it is defined in the machine.config file. The main problem in re-setting this value is -

1. It can not be changed programmatically.
2. And due to Point 1, we have to make manual changes in the machine.config file. This results in a machine wide change; i.e. all the applications running on that box will be affected by this change. So, one needs to take utmost care in doing so and should properly assess the consequences before attempting this change.

Please note that we can not specify the machinesettings in our own configuration file. But we need to actually add/change the machinesettings\timeout in the machine.config file. Below shows a timeout of 30 minutes after manual change.

<configuration>

 <system.transactions>

  <machineSettings maxTimeout="00:30:00" />

 </system.transactions>

</configuration>

Conclusion
The timeout of a transaction is decided by MIN(TransactionManager Timeout value, Transaction timeout value, command timeout value); i.e. a minimum value of these 3 settings is the deciding factor.

References

1. http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/250b40b9-0838-4142-a8ff-d9d26690083b/
2. http://social.msdn.microsoft.com/Forums/en-US/windowstransactionsprogramming/thread/7863f7e5-ec86-4f77-90e2-dc212ff099a2/

Happy New Year !

Wishing all my followers a very happy & prosperous new year !

Dynamic Sorting in SSRS

Introduction
Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -
 Implementation
Assuming that the sorting is to be done on 3 columns – Col1,Col2 & Col3. But the ORDER is undecided until runtime. Now, what we can do is -

1. Define 3 Report parameters. Say - @SortCol1Name,@SortCol2Name & @SortCol3Name of STRING type.
2. Pass the name of the columns which need to sorted in the required order. Say -
   • @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
   • OR @SortCol1Name = “ColumnY”,@SortCol2Name = “ColumnZ” and @SortCol3Name = “ColumnX”
   • OR any preferred order
3. In the Sorting Option of the Properties dialog box of Table or Group, set the sort expression as under -
   • =Fields(Parameters!SortCol1Name.Value).Value
   • =Fields(Parameters!SortCol2Name.Value).Value
   • =Fields(Parameters!SortCol3Name.Value).Value

This finally gets converted to –Fields!ColumnX.Value,Fields!ColumnY.Value & Fields!ColumnZ.Value if we have the passed the following values for the parameters - @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”

Conclusion
With this trick, we can easily set the sort expression dynamically. However, there is a limitation that we can not set the Sort Direction dynamically. I could not find out a way for it. If you have any trick to get it done with this, please leave your suggestion as a comment to this post.

How many times the T-SQL inside the CTE is executed?

Introduction

Common Table Expression (CTE) has become very popular these days. However, many users of CTE still have a myth that the t-sql written inside the CTE will be executed only once irrespective of the number of times it is referred in the subsequent CTEs or the related query.
However, this is not correct. It is a pure misconception. And let’s try to prove this with a simple example.

Proof
The Query

;WITH CTE1 AS (

SELECT

* 

FROM

dbo.Employees

),CTE2 AS (

SELECT

*

FROM

CTE1

)

SELECT

C1.EmployeeID,

C1.Name,

C2.EmployeeID,

C2.Name,

C3.EmployeeID,

C3.Name

FROM

CTE1 C1

INNER JOIN CTE2 C2

ON C2.EmployeeID = C1.EmployeeID

INNER JOIN CTE1 C3

ON C3.EmployeeID = C1.EmployeeID

Execution Plan



Please have a look at the execution plan. It clearly shows that even though the t-sql to fetch the data from the table is written only once inside CTE1, the Scan is done thrice. This is because CTE1 is referred thrice in the final query -

1. Two times CTE1 is directly used.
2. Once CTE1 is used indirectly via CTE2.

Conclusion
The CTE should be thought of a view that is defined for the current query only. At the time of execution, the query optimizer will replace all the direct/indirect use of CTE with the actual query in the same way as it does for Views.

Using OFFSET and FETCH

 

Introduction

Many times while developing our applications we feel the need of pagination, where our User Interface (UI) has to list a number of records and fetching them all at once and listing is not a feasible option because of the following reasons -

1. High utilization of the network bandwidth which if on a higher side might even choke up the bandwidth.
2. User is not guaranteed to see the latest details in a multi user environment.
3. High need of RAM on local machine for caching/processing.

So, the solution which is generally implemented in this situation was to fetch only the relevant records from the backend. Until Denali the following were the options used to counter this situation -

1. Before SQL 2005 – ORDER BY clause in combination with TOP
2. From SQL 2005 onwards – ROW_NUMBER() function with a WHERE clause

And from Denali, we can use ORDER BY clause in combination with OFFSET and FETCH

Implementation

Let’s see how we can get the same output using all of the 3 ways explained above and try to fetch records from 3 to 4 assuming page size to be 2.

Total Records are as under

1. ORDER BY + TOP

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

SELECT

  * 

FROM (SELECT

        TOP (@PageSize) *

      FROM (SELECT 

              TOP (@PageNo * @PageSize) *

            FROM 

              dbo.DemoTable DT

            ORDER BY

              ID ASC) X

      ORDER BY

        X.ID DESC) Y

ORDER BY

  Y.ID ASC

Output

 

2. ROW_NUMBER() + WHERE

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

;WITH Data AS (  

SELECT

  *,

  ROW_NUMBER()OVER(ORDER BY DT.ID ASC) Rno

FROM

  dbo.DemoTable DT

)

SELECT 

  ID,NAME,CITY

FROM

  Data

WHERE

  Rno BETWEEN ((@PageNo - 1) * @PageSize) + 1 AND ((@PageNo - 1) * @PageSize) + @PageSize

Output

 

3. FETCH + OFFSET

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

SELECT 

  *

FROM 

  dbo.DemoTable DT

ORDER BY 

  DT.ID

OFFSET ((@PageNo - 1) * @PageSize)) ROWS

FETCH NEXT @PageSize ROWS ONLY

Output

Performance

I did a small test using all the 3 ways and have found the Denali (OFFSET and FETCH) way the best performing one followed by the ROW_NUMBER().

 

Conclusion

I would prefer using the Denali way just for 2 simple reasons -

• Simplicity of code


• Better performance

Remarks

1. The Denali code is based on SQL Server Denali CTP 1 and might change after further releases.

Workarounds for SSRS 2008 Font Rendering Issue in ReportViewer 10.0

Problem
A report developed using SSRS 2008 either through Visual Studio 2010 or BIDS 2008 does not renders properly when viewed in the Report Viewer 10.0 via Remote Desktop or any 3rd party applications using Terminal Services in the background.
Remote Rendering

Local Rendering

Workarounds

1. Change the Screen Resolution to 1024 X 768 or any 4:3 aspect ratio for both the Remote machine as well as the local machine.
   
2. Export the report as PDF and then take the print outs if required.
   
3. If the above two solutions does not work for you, unfortunately you will have to look back to SSRS 2005.

Hope, this saves you some time of searching.

How to find out which Table is not having rows in SQL Server?

 

Introduction

Many times while tuning our production databases we might try to find out the list of tables not having even a single row of data. Today, I am going to show a simple script which could be used to get a list of tables having ZERO rows.

Script

USE DBName --Change this to the DB Name you want to script for.

GO

DECLARE @TableRowCount TABLE

( 

    TableName VARCHAR(255), 

    RowCnt INT 

) 

INSERT @TableRowCount 

  EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1),COUNT(*) FROM ?' 

SELECT 

    * 

FROM 

  @TableRowCount 

WHERE

    RowCnt = 0     

ORDER BY 

  RowCnt