Indexed View Overhead on DML - Part 1 (Non-aggregrated view)

Starting from SQL Server 2000, Indexed View is a powerful tool to speed up complicated SELECT queries. In this series, I would like to dive into the indexed view maintenance overhead on DML statements. In this blog post, I will first focus on indexed view with no any aggregation, i.e. no any group by clause.
Like indexes, every time we modify data in the underlying tables then not only must SQL Server maintain the index entries on those tables, but also the index entries on the indexed view. This can affect write performance. SQL Server maintains the indexed view by embedding extra operations into the execution plan of the DML statement.
Let's say we have two tables, the Account table stores account id, player name, and the current balance; and the PlayerBetLog table stores the players' betting records.

USE [TESTDB]
GO
CREATE TABLE [dbo].[Account] (
 [accid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [name] [varchar](50) NOT NULL,
 [balance] [int] NOT NULL DEFAULT(0)
)
GO
CREATE TABLE [dbo].[PlayerBetLog] (
 [dividendId] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [amount] [decimal](18, 2) NOT NULL,
 [playerId] [int] NOT NULL,
 [gameCat] [int] NOT NULL
);


The balance column in the Account table will be frequently updated, but new records will be seldomly added, and existing records never be deleted. On the other hand, new records will be frequently inserted into the PlayerBetLog table, but never change.
Let's insert some dummy data into the two tables:

Before adding an indexed view, let's run a typical DML workload, to see the execution plan, statistics I/O, and locked resources:


SET NOCOUNT ON;
SET ARITHABORT OFF;
SET STATISTICS IO ON;
BEGIN TRAN
INSERT PlayerBetLog (playerId, gameCat, amount) SELECT 1, 2, 10 UNION ALL SELECT 1, 2, 20 UNION ALL SELECT 2, 2, 100;
UPDATE Account SET balance += 10 WHERE accid = 1;
SET STATISTICS IO OFF;
-- check locked resources
SELECT
 t1.request_session_id AS spid
 ,t1.resource_type
 ,DB_NAME(t1.resource_database_id) AS DB
 ,(CASE resource_type
  WHEN 'DATABASE' THEN ''
  WHEN 'FILE' THEN ''
  WHEN 'OBJECT' THEN OBJECT_NAME(t1.resource_associated_entity_id, t1.resource_database_id)
  WHEN 'EXTENT' THEN ''
  WHEN 'APPLICATION' THEN ''
  WHEN 'METADATA' THEN ''
  WHEN 'ALLOCATION_UNIT' THEN (
   SELECT TOP 1 OBJECT_NAME(p.[object_id])
   FROM sys.partitions AS p JOIN sys.allocation_units AS a
   ON a.container_id = (CASE a.[type] WHEN 0 THEN NULL WHEN 2 THEN p.partition_id ELSE p.hobt_id END)
   WHERE a.allocation_unit_id = t1.resource_associated_entity_id
  )
  ELSE (SELECT TOP 1 OBJECT_NAME([object_id]) FROM sys.partitions WHERE hobt_id = t1.resource_associated_entity_id)
  END) AS [object_name]
 ,i.name AS index_name
 ,t1.request_mode
 ,t1.request_status
FROM sys.dm_tran_locks AS t1 LEFT JOIN sys.dm_os_waiting_tasks AS t2
 ON t1.lock_owner_address = t2.resource_address
LEFT JOIN sys.partitions AS s ON s.hobt_id = t1.resource_associated_entity_id
LEFT JOIN sys.indexes AS i on i.[object_id] = s.[object_id] and i.index_id = s.index_id
LEFT JOIN (SELECT t.[text], p.*
FROM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) AS t
) AS t3 ON t1.request_session_id = t3.spid
WHERE t3.spid = @@spid
ORDER BY index_name, resource_type
ROLLBACK;

Below is the execution plan:

As you can see in the above picture, the main operator on each DML statement only the Clustered Index Insert / Update operator.



Let's also check the statistics I/O by using http://statisticsparser.com/

Table 'PlayerBetLog'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Account'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Below are the locked resources:

 

Now let's add an indexed view, which joins the Account table and the PlayerBetLog table, but excluding the frequently updated balance column:
USE [TESTDB]
GO
-- View
CREATE VIEW [dbo].[VwTranReport] WITH SCHEMABINDING AS
 SELECT A.accid, A.name, B.dividendId, B.amount, B.gameCat FROM dbo.Account A JOIN dbo.PlayerBetLog B ON A.accid = B.playerId;
GO
-- Unique Clustered Index on View
CREATE UNIQUE CLUSTERED INDEX [VUX_VwTranReport] ON [dbo].[VwTranReport] ([dividendId]);
GO
-- another Nonclustered Index on View
CREATE NONCLUSTERED INDEX [NCI_VwTranReport_accid] ON [dbo].[VwTranReport] ([accid]);
GO

Let's run again the DML workload, and compare the new execution plan, statistics I/O, and locked resources:

By comparing to the old plan on the left hand side, the new plan on the right hand side added an extra operation branch for the INSERT PlayerBetLog statement, which includes an index seek to get the Account name column value, and Index Insert to maintain the indexes on the indexed view.
On the other hand, as the indexed view excludes the balance column, the UPDATE statement on this column didn't incur any extra operation.

Below is the statistics I/O for the DML statements after indexed view added:

Table 'VwTranReport'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Account'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PlayerBetLog'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Account'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Extra logical read are placed on the Account table in order to get the name column for populating the indexed view, and extra I/O on the indexed view and Worktable in order to maintain the indexed view.

Below picture compares the locked resources between the old plan (left) and the new plan (right), extra locks are being placed on the indexed view in the new plan:

Final thought:
This blog post only investigates the overhead of Non-aggregated indexed view, for aggregated one, much more complicated operations will be added into the DML execution plan. If you find adding an indexed view can really boost up a reporting query, you must do load test first to see whether your production database can afford the extra indexed view maintenance cost on the DML workload.

An Overlooked Stored Procedure since SQL2012 - sp_describe_first_result_set

As a database developer, we create stored procedures for application developers to call them in their application code, most of the time they ask us to provide a document which list out the input parameters and result columns. Where listing stored procedure parameters are very easy by copying the parameters from the stored procedure definition, extracting result columns from the stored procedure body may be not that easy, especially there may be a SELECT * and joining multiple tables in the output statement. Starting with SQL Server 2012, there's a built-in stored procedure, sp_describe_first_result_set, which returns the metadata for the first possible result set of a Transact-SQL batch. Here is an example:


USE StackOverflow2010
GO
-- =============================================
-- Author:  Peter Lee
-- Create date: 20180102
-- Description: SP Get User Posts
-- =============================================
CREATE OR ALTER PROCEDURE uspGetUserPosts
 @DisplayName nvarchar(40)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @DisplayName AS UserName, U.Id AS UserId, U.CreationDate AS UserCreationDate,
  U.Reputation, P.CreationDate AS PostCreationDate, P.Title
 FROM Users AS U JOIN Posts AS P ON U.Id = P.OwnerUserId
 WHERE U.DisplayName = @DisplayName
 ORDER BY PostCreationDate DESC, Title
END
GO
EXEC dbo.uspGetUserPosts 'Anonymous User';
GO
EXEC sp_describe_first_result_set N'EXEC dbo.uspGetUserPosts ''Anonymous User''';
GO


The sp_describe_first_result_set stored procedure will tell you the metadata for the result set of your stored procedure, including the column name, data type, ordinal, columns in ORDER BY list, etc.

Export Query Result in CSV File using T-SQL

Let's say you have a program or sql agent job, which runs a stored procedure, extract data from some tables, join them, and do some calculations. Then you want to export the result into a csv file, into a local drive or shared folder somewhere in your network. You can NOT fire BCP utility by xp_cmdshell because your company security administrator not allow your program spawning Windows command shells, and if that stored procedure is to be executed per each user button click, it will impact your sql server hardware performance.
In this post, I will show you how to do this by using OLE Automation Procedures in SQL Server. The prerequisites are you must turn on this server option, and the owner of your user database must be the same as that of the master database (using sa account as the owner of all your databases actually is the best practice).
USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
USE TESTDB
ALTER AUTHORIZATION ON DATABASE::[TESTDB] TO sa
GO
-- =============================================
-- Author:  Peter Lee
-- Description: Convert local temp table #TempTblCSV into a CSV string variable
-- =============================================
CREATE OR ALTER PROCEDURE ConvertLocalTempIntoCSV
 @csvOutput nvarchar(max) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;
 -- get list of columns
 DECLARE @cols nvarchar(4000);
 SELECT @cols = COALESCE(@cols + ' + '', '' + ' + 'CAST([' + name + '] AS nvarchar(4000))', 'CAST([' + name +'] AS nvarchar(4000))')
  FROM tempdb.sys.columns WHERE [object_id] = OBJECT_ID('tempdb..#TempTblCSV') ORDER BY column_id;
 CREATE TABLE #TempRows (r nvarchar(4000));
 EXEC('INSERT #TempRows SELECT ' + @cols + ' FROM #TempTblCSV');
 SELECT @csvOutput = COALESCE(@csvOutput + CHAR(13)+CHAR(10) + r, r) FROM #TempRows;
END
GO
/****** Object:  StoredProcedure [TLB].[WriteToFile] ******/
CREATE OR ALTER PROC [WriteToFile]
 @file varchar(2000),
 @text nvarchar(max)
WITH EXECUTE AS 'dbo'
AS  
 DECLARE @ole int;
 DECLARE @fileId int;
 DECLARE @hr int;
 EXECUTE @hr = master.dbo.sp_OACreate 'Scripting.FileSystemObject', @ole OUT;
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d creating object.', 16, 1, @hr)
  RETURN
 END
 EXECUTE @hr = master.dbo.sp_OAMethod @ole, 'OpenTextFile', @fileId OUT, @file, 2, 1;  -- overwrite & ALTER if not exist
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d opening file.', 16, 1, @hr)
  RETURN
 END
 EXECUTE @hr = master.dbo.sp_OAMethod @fileId, 'WriteLine', Null, @text;
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d writing file.', 16, 1, @hr)
  RETURN
 END
 EXECUTE @hr = master.dbo.sp_OADestroy @fileId;
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d closing file.', 16, 1, @hr)
  RETURN
 END
 EXECUTE master.dbo.sp_OADestroy @ole;
GO
-- Create a local temp table, name must be #TempTblCSV
-- you can define any columns inside, e.g.
CREATE TABLE #TempTblCSV (pk int, col1 varchar(9), dt datetime2(0));
-- Fill the local temp table with your query result, e.g.
INSERT #TempTblCSV VALUES (1, 'a', '20180112 12:00');
INSERT #TempTblCSV VALUES (2, 'b', '20180113 13:00');
INSERT #TempTblCSV VALUES (3, 'c', '20180113 14:00');
-- convert the local temp table data into a string variable, which is the CSV content
DECLARE @csv nvarchar(max);
EXEC ConvertLocalTempIntoCSV @csv OUTPUT;
-- write the CSV content into a file
EXEC WriteToFile 'H:\TEMP\PETER\output.csv', @csv;
-- Not a must if to drop the temp table, especially if you're writing a stored proc
DROP TABLE #TempTblCSV;

And here's the sample output:

Another Way to Changing String Columns Collation

Let's say you have a database table, which contains millions rows of string data. Someday you need to change the collation, for example originally it's case sensitive, now you want to change it to case insensitive. According to SQL Server BOL, you can change the column collation, but first you need to drop all the reference objects referring to that column, including computed column, indexes, statistics, CHECK constraints, and FOREIGN KEY constraints. It's not a simple task, and if you do this way in your production database, you need a prolonged maintenance period. In this blogpost, I want to show you another way to do this, but it needs a schema comparison tool and a data comparison tool, such as Red Gate® SQL Compare and Data Compare.

1. Create a new database as the destination database, specify the new collation.
2. From the Source database, generate the creation script for all Tables, clear all options EXCEPT "Schema qualify object names". In this step, you just need to create the table, without any index nor constraints.
3. Change the current query context database to the destination database, execute the table creation script.
4. Generate the data copy script (INSERT…SELECT statements) for all tables, using below script:
   USE <source DB>GO
   DECLARE @destDb varchar(50) = '<destination DB>';
   SELECT CASE WHEN I.column_id IS NULL THEN '' ELSE 'SET IDENTITY_INSERT ' + @destDb + '.' + S.name + '.' + O.name + ' ON; ' END + 'INSERT ' + @destDb + '.' + S.name + '.' + O.name + ' (' + RTRIM(LTRIM(STUFF((SELECT ', [' + name + ']' FROM sys.columns WHERE object_id = O.object_id FOR XML PATH ('')), 1, 1, ''))) + ') SELECT ' + RTRIM(LTRIM(STUFF((SELECT ', [' + name + ']' FROM sys.columns WHERE object_id = O.object_id FOR XML PATH ('')), 1, 1, ''))) + ' FROM ' + S.name + '.' + O.name + '; ' + CASE WHEN I.column_id IS NULL THEN '' ELSE 'SET IDENTITY_INSERT ' + @destDb + '.' + S.name + '.' + O.name + ' OFF; ' END FROM sys.objects O JOIN sys.schemas S ON O.schema_id = S.schema_id LEFT JOIN sys.identity_columns I ON I.object_id = O.object_id WHERE O.[type] = 'U' ORDER BY S.name, O.name;
5. Execute generated the INSERT…SELECT statements.
6. Backup the Destination database.
7. Run Red Gate® SQL Compare to synchronize table schema (PK, FK, check, index, etc.) and objects (view, stored proc, function, etc.) from source database to destination database.
8. Freeze the source database, e.g. ALTER DATABASE <source DB> SET READ_ONLY;
9. Run Red Gate® SQL Data Compare to compare and synchronize the source database and destination database.
10. Rename the source database to XXX_OLD, and destination database to the source database original name.

Using Service Broker to implement Asynchronous Trigger

Suppose you have an OLTP system, which lets users to place purchase orders (PO), the UI response reply to the user must be fast, but there's a time consuming background process need to undergo for each PO placed by the users. Such system can be implemented by using triggers and Service Broker service. A trigger queues a message that requests work from a Service Broker service. The trigger does not actually perform the requested work. Instead, the trigger creates a message that contains information about the work to be done and sends this message to a service that performs the work. The trigger then returns. When the original transaction commits, Service Broker delivers the message to the destination service. The program that implements the service performs the work in a separate transaction. By performing this work in a separate transaction, the original transaction can commit immediately. The application avoids system slowdowns that result from keeping the original transaction open while performing the work.

Below is a demonstration. There's a main table named DepartmentMaster. It has an insert trigger, which sends a message to a service broker service. The service broker service is implemented by internal activation stored procedure on the target queue, with MAX_QUEUE_READERS = 5, meaning the maximum number of instances of the activation stored procedure that the queue starts at the same time. When you insert a row into DepartmentMaster table, finally an XML file will be created inside a folder C:\TEMP\PETER\ in your testing database server. For the sake of simplicity, the example uses OLE Automation Stored Procedures for SQL Server to write the XML file.

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

CREATE DATABASE [SBTEST];

GO

ALTER DATABASE [SBTEST] SET TRUSTWORTHY ON;

ALTER DATABASE [SBTEST] SET ENABLE_BROKER;

GO

USE [SBTEST]

GO

CREATE PROCEDURE [dbo].[WriteToFile]
  @file varchar(2000),
  @text nvarchar(max)
AS  
BEGIN
  DECLARE @ole int;  
  DECLARE @fileId int;
  EXECUTE sp_OACreate 'Scripting.FileSystemObject', @ole OUT;
  EXECUTE sp_OAMethod @ole, 'OpenTextFile', @fileId OUT, @file, 8, 1;
  EXECUTE sp_OAMethod @fileId, 'WriteLine', Null, @text;
  EXECUTE sp_OADestroy @fileId;
  EXECUTE sp_OADestroy @ole;
END
GO
CREATE MESSAGE TYPE [TestMessage] VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT [TestContract] ([TestMessage] SENT BY INITIATOR)
GO
CREATE QUEUE [dbo].[TestQueue] WITH STATUS = OFF
GO
CREATE SERVICE [TestServiceInitiator]  ON QUEUE [dbo].[TestQueue] ([TestContract])
GO
CREATE SERVICE [TestServiceTarget]  ON QUEUE [dbo].[TestQueue] ([TestContract])
GO
CREATE TABLE [dbo].[auditlog](
 [xmlstring] [xml] NULL,
 [logTime] [datetime2](2) NOT NULL,
 [isSuccess] [bit] NOT NULL,
 [err_num] [int] NULL,
 [err_msg] [nvarchar](4000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-- DEMO TABLE
CREATE TABLE [dbo].[DepartmentMaster](
 [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [Description] [varchar](50) NULL,
 CONSTRAINT [PK_DepartmentMaster1] PRIMARY KEY CLUSTERED
(
 [DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE  TRIGGER [dbo].[Trg_DepartmentMaster_INSERT]
ON  [dbo].[DepartmentMaster]
FOR INSERT
AS
BEGIN
            SET NOCOUNT ON;
            DECLARE @MessageBody XML 
            DECLARE @TableId int 

            --get relevant information from inserted/deleted and convert to xml message 
            SET @MessageBody = (SELECT DepartmentId,Name,Description FROM inserted 
            FOR XML AUTO)               

            If (@MessageBody IS NOT NULL) 
            BEGIN 
      -- **** INSERT TRIGGER SEND a SB request message, then return immediately ****
                        DECLARE @Handle UNIQUEIDENTIFIER;  
                        BEGIN DIALOG CONVERSATION @Handle  
                        FROM SERVICE [TestServiceInitiator]  
                        TO SERVICE 'TestServiceTarget'  
                        ON CONTRACT [TestContract]  
                        WITH ENCRYPTION = OFF;  
                        SEND ON CONVERSATION @Handle  
                        MESSAGE TYPE [TestMessage](@MessageBody);
            END
END
GO
/* **** HANDLER SP, automatically fired by Service Broker **** */
CREATE PROCEDURE [dbo].[spMessageProcTest]
AS
SET NOCOUNT ON;

DECLARE @message_type varchar(100), @dialog uniqueidentifier, @message_body XML;
 
BEGIN TRY
   
 BEGIN TRAN;

 WAITFOR (
  RECEIVE TOP(1)
   @message_type = message_type_name,
   @message_body = CAST(message_body AS XML),
   @dialog = conversation_handle
  FROM dbo.TestQueue
 ), TIMEOUT 500
 ;

 -- Received an message
 IF (@@ROWCOUNT != 0 AND @message_body IS NOT NULL)
 BEGIN
  IF @message_type = 'TestMessage'
  BEGIN
   -- **** PROCESSING HERE, e.g. Export To File ****
   DECLARE @fileNum int = @message_body.value('(/inserted/@DepartmentId)[1]', 'int');
   DECLARE @fileContent nvarchar(max) = CAST(@message_body AS nvarchar(max));
   DECLARE @filePath varchar(2000) = 'C:\TEMP\PETER\' + CAST(@fileNum AS varchar(99)) + '.xml';
   EXEC dbo.WriteToFile @filePath, @fileContent;

   -- Success Log
   INSERT INTO auditlog (xmlstring, isSuccess) values(@message_body, 1);
  END

  END CONVERSATION @dialog;
 END

 COMMIT;

END TRY
BEGIN CATCH

 DECLARE @error int, @message nvarchar(4000);
 SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE();

 -- Uncommittable tx.
 IF XACT_STATE() = -1
 BEGIN
  ROLLBACK;
 END

 -- Error but Commitable tx.
 IF XACT_STATE() = 1
 BEGIN
  COMMIT;
 END

 -- FAILED Audit
 INSERT INTO auditlog (xmlstring, isSuccess, err_num, err_msg)
  VALUES (@message_body, 0, @error, @message);

 -- End Conversion with Error
 END CONVERSATION @dialog WITH error = @error DESCRIPTION = @message;

END CATCH

GO

ALTER QUEUE [dbo].[TestQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[spMessageProcTest] , MAX_QUEUE_READERS = 5 , EXECUTE AS OWNER  ), POISON_MESSAGE_HANDLING (STATUS = ON);

GO

-- TESTING --
INSERT DepartmentMaster (Name, Description) VALUES ('IT Dept', 'IT DEAPRTMENT');

SELECT TOP (1000) *, casted_message_body =
CASE message_type_name WHEN 'X'
  THEN CAST(message_body AS NVARCHAR(MAX))
  ELSE message_body
END
FROM [SBTEST2].[dbo].[TestQueue] WITH(NOLOCK)
;
SELECT xmlstring.value('(/inserted/@DepartmentId)[1]', 'int'), * FROM auditlog;
SELECT * FROM DepartmentMaster;

Estimating SQL Server Recovery Time

Every time when an SQL Server instance is restarted, no matter it is a planned restart or cluster failover, SQL Server undergoes the recovery process, which takes time to rollback uncommitted transactions, and roll-forward committed transactions (hardened to log file, but not yet applied to data file) during the server restart. Depending on the workload on your databases and how many databases are there, recovery may take a long time to run. There is no any exact way to calculate the time a recovery will take, however, you can get an estimation based on the last recovery that you SQL Server took. Below sql script employs the undocumented system stored procedure, xp_readerrorlog, to get the latest recovery start time and completion time, and calculates the last recovery time:
SET NOCOUNT ON;
DECLARE @RecoveryStart datetime, @RecoveryStop datetime;
DECLARE @tbl_RECOVERY_START table (LogDate datetime, ProcessInfo nvarchar(max), [Text] nvarchar(max));
DECLARE @tbl_RECOVERY_STOP table (LogDate datetime, ProcessInfo nvarchar(max), [Text] nvarchar(max));
INSERT @tbl_RECOVERY_START EXEC xp_readerrorlog 0, 1, N'(c) Microsoft Corporation.', NULL, NULL, NULL, N'ASC';
INSERT @tbl_RECOVERY_STOP EXEC xp_readerrorlog 0, 1, N'Recovery is complete. This is an informational message only. No user action is required.', NULL, NULL, NULL, N'DESC';
SELECT @RecoveryStart = LogDate FROM @tbl_RECOVERY_START;
SELECT @RecoveryStop = LogDate FROM @tbl_RECOVERY_STOP;
SELECT DATEDIFF(second, @RecoveryStart, @RecoveryStop) AS RecoveryTimeSeconds;

ClearTrace - Free Tool to summarize SQL Trace Results

Let's say you capture the workload on your production SQL Server, by using SQL Profiler or SQL Trace to gather the events of query executions. In order to determine the most expensive queries within the workload, you need to summarize the trace results, group the events by query text, and order the summarized items by CPU or disk IO or duration. You also want to normalize the query text, such that common statements can be grouped together. ClearTrace is a free tool that can help, it can imports SQL Profiler and SQL Trace results, from SQL Server 2000 to the latest SQL Server version. Below are the steps demonstrating how to use ClearTrace:

1. Download ClearTrace from scalesql.com. You don't need to set it up on the database server, it can be executed in any computer which able to access the trace result files.
2. Extract the downloaded ClearTrace.XX.zip compressed file.
3. Execute the ClearTrace.exe executable file, which shows the ClearTrace GUI.
4. When you first time start ClearTrace in your computer, you will be asked to set the database in the Tools -> Options menu. If the database doesn’t exist you will be given the option to create it. If it does exist ClearTrace will create the necessary objects in the database. All tables and views are prefixed with “CT” so they are easy to identify.
    
   
    
   
5.  Select the first trace result file to import. Trace files in the same directory will also be imported in sequence. ClearTrace only processes RPC:Completed and SQL:BatchCompleted events. You can create the trace by yourself, including the required event columns: EventClass, TextData, HostName, ApplicationName, LoginName, SPID, Duration, EndTime, Reads Writes, and CPU. Or you can download the sql script from ClearTrace website. Anyway, I prefer creating it by myself.
    
   
   
    
   
6. Press Import Files button. ClearTrace will display the status. After the process completed, the summarized result will be shown. Then you can change the grouping and ordering of it.
    
   
7. You can also get the summarized data from the ClearTrace database, by querying the view CTTraceSummaryView.

Please be reminded that ClearTrace is still in BETA. But it still a handy tool for database performance tuning, and it's free.