Last Execution Date Time of a Stored Procedure

On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.

Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. Please note that this view has been introduced from SQL Server 2008.

The important thing to note is that this view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -

USE DBName

GO

SELECT 

  O.name,

  PS.last_execution_time

FROM 

  sys.dm_exec_procedure_stats PS 

INNER JOIN sys.objects O 

  ON O.[object_id] = PS.[object_id] 

P.S. Please replace the DBName with the actual name of the Database.

The above script will return the name of all the cached stored procedure of the current database with their last execution time.

For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)

How to hide Placeholder in SSRS 2008?

Introduction

For the ones who are working with SSRS, Placeholder is not new for them. So, today I will not go into explaining what is Placeholder, How and Where it is used, etc. I will straight away target the issue mentioned and try to explain a possible way to overcome that. However, for the ones who do not know about Placeholder, please visit this MSDN link.

Problem

Let’s first try to understand the problem I am talking about. Please have a look at Diag: 1-

Diag: 1

Here, as you have noticed, 4 different placeholders are used to make up the details for the Attribute column; 2 each for Labels & their respective values. Now, the requirement is such that the Labels & their Values for the Sub Category are to be shown only if the Value exists ELSE even the label should not be shown. This can be easily managed using the below expression for the Label & Value placeholders respectively -

Diag: 2

  Diag: 3

But the main issue here is the BLANK SPACE CREATED refer Diag: 1. There is not direct property available to Hide the Placeholder. Hence, the need arises to find out some work around.

Solution

The expected output is -

Diag: 4

To get the expected output, the main credit goes to the below Placeholder property -

Diag: 5

We just need to select HTML as the Markup type and then set the Value expression as under -

Diag: 6

And yes, we are done !

Conclusion

This was the solution I could manage to find out. There might exist other solutions as well. Please revert back if you are aware of any such solution by adding a comment below.

Fragmented Index – Rebuild or Reorganize ?

Introduction
In past, many times I have written on Fragmentation of Indexes and how to Defrag them. You can find some related articles at -

• T-SQL to find Fragmented Indexes
• What is the best value for Fill Factor in SQL Server?
• Rebuild all the indexes of a table & Indexed views in a Database
• Rebuild all the Indexes of a SQL Database in one go

After this, many of my readers have queried me for-

1. What is the basic difference between ReBuild & ReOrganizing an Index?
2. When to use what?

So, today I will address these two specific queries and also show a handy TSQL which could be used to remove Index fragmentation.
ReBuilding Index means the Index will be ReBuild from scratch using the same definition of its creation. It is same as using DBCC DBREINDEX. Please note that for new development do no use DBCC DBREINDEX as its deprecated from SQL Server 2005 onwards. It is generally preferred to rebuild OFFLINE. However, rebuild could be done ONLINE by specifying appropriate options.
ReOrganizing Index means the data at the Index leaf will be re-organized and the fragments will be removed to the max possible extent. It is same as using DBCC INDEXDEFRAG. Please note that for new development do no use DBCC INDEXDEFRAG as its deprecated from SQL Server 2005 onwards. ReOrganizing an Index is an ONLINE process.
When to use what – As per Microsoft, the recommendations are as under -
If Avg. Fragmentation < 5%, no action is required
else if 5% < Avg. Fragmentation <= 30%, reorganization is required
else if Avg. Fragmentation > 30%, rebuild is required
So, based on the above recommendations, the below TSQL could be used to serve the purpose -

USE DBName

GO

DECLARE @tsql NVARCHAR(MAX)  

DECLARE @fillfactor INT

SET @fillfactor = 70 

DECLARE @FragmentedIndexs TABLE (IndexID INT,

                                 IndexName VARCHAR(100),

                                 ObjectName VARCHAR(100),

                                 AvgFragmentationInPercent DECIMAL(6,2),

                                 FragmentCount INT,

                                 AvgFragmentSizeInPage DECIMAL(6,2),

                                 IndexDepth INT)

--Insert the Details for Fragmented Indexes.

INSERT INTO @FragmentedIndexs

SELECT 

  PS.index_id,

  QUOTENAME(I.name) Name,

  QUOTENAME(DB_NAME()) +'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,

  PS.avg_fragmentation_in_percent,

  PS.fragment_count,

  PS.avg_fragment_size_in_pages,

  PS.index_depth

FROM 

  sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, NULL) AS PS

INNER JOIN sys.indexes AS I 

  ON PS.[object_id]= I.[object_id]

  AND PS.index_id = I.index_id

WHERE

  PS.avg_fragmentation_in_percent > 5  

ORDER BY 

  PS.avg_fragmentation_in_percent DESC

--Select the details.

SELECT * FROM @FragmentedIndexs

--Prepare the Query to REORGANIZE the Indexes

SET @tsql = ''

SELECT @tsql = 

  STUFF(( SELECT DISTINCT 

           ';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REORGANIZE '

          FROM 

           @FragmentedIndexs FI

          WHERE

            FI.AvgFragmentationInPercent <= 30

          FOR XML PATH('')), 1,1,'')

SELECT @tsql

PRINT 'REORGANIZING START'

EXEC sp_executesql @tsql 

PRINT 'REORGANIZING END'

--Prepare the Query to REBUILD the Indexes

SET @tsql = ''

SELECT @tsql = 

  STUFF(( SELECT DISTINCT 

           ';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ') '

          FROM 

           @FragmentedIndexs FI

          WHERE

            FI.AvgFragmentationInPercent > 30

          FOR XML PATH('')), 1,1,'')

SELECT @tsql

PRINT 'REBUILD START'

EXEC sp_executesql @tsql

PRINT 'REBUILD END'

Conclusion

To keep the indexes in proper shape, a timely defragmentation is necessary. When to defragment has got no THUMB RULE, it depends on the environment. However, the above script could be scheduled as per ones need to make sure that the indexes gets defragmented when need.

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.