Reading the transaction log in SQL Server

To see transactions for inserted rows:

SELECT [Current LSN],
  Context, 
  [Transaction ID], 
  [Begin time],
  [Operation],
  [Transaction Name],
  [Transaction SID],
  [SPID],
  [Begin Time],
  [AllocUnitName],
  [Page ID],
  [Slot ID],
  [End Time],
  [Number of Locks],
  [Lock Information]
  FROM sys.fn_dblog(NULL, NULL)
  WHERE operation IN('LOP_INSERT_ROWS');

Alternatively, to view additional transaction operations, you can use:

-- OR
WHERE Operation IN('LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

To check for transactions related to a split page, use the following query:

-- Or for a split page
WHERE [Transaction Name]='SplitPage'

To see transactions for deleted rows:

-- To see transactions for deleted rows
SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog(NULL, NULL)
  WHERE operation IN('LOP_DELETE_ROWS');

If you need to read native transaction log backups, use the following query:

-- To read native transaction log backups
SELECT [Current LSN], Context, [transaction name],
       Operation, [Transaction ID], Description
FROM fn_dump_dblog(NULL,NULL,N'DISK',1,
  N'F:\\Mybackups\\InventoryDatabase_4589725r.trn', 
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT);

To recover a dropped object from SQL Server log, use the following:

-- Can recover the dropped object from SQL Server log
SELECT Convert(varchar(Max), Substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM fn_dblog(NULL,NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
AND [Context] = 'LCX_MARK_AS_GHOST'
AND [AllocUnitName] = 'sys.sysobjvalues.clst'

SELECT Convert(varchar(Max), Substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM fn_dblog(NULL,NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
AND [Context] = 'LCX_MARK_AS_GHOST'
AND [AllocUnitName] = 'sys.sysobjvalues.clst'
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) 
WHERE Context IN ('LCX_NULL') 
AND Operation IN ('LOP_BEGIN_XACT')  
AND [Transaction Name] = 'DROPOBJ'  
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
GO

To restore the log with a specific LSN number, use the following:

-- Restore log with LSN number
RESTORE LOG InventoryDatabase
FROM DISK = N'F:\\Mybackups\\InventoryDatabase_4589725r.trn'
WITH STOPBEFOREMARK = 'lsn:112000000001700001',
    NORECOVERY;

Finally, to check for databases with more than 100 VLFs, run this query:

-- Returns the names of the databases with more than 100 VLFs
SELECT [name], COUNT(l.database_id) AS 'vlf_count'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
HAVING COUNT(l.database_id) > 100;

How to write a merge statement in SQL Server

SQL Server provides the MERGE statement that allows you to perform three actions at the same time. If you use the INSERT, UPDATE, and DELETE statements individually, you have to construct three separate statements to update the data to the target table with the matching rows from the source table.

The following shows the syntax of the MERGE statement:

MERGE  USING 
ON  -- join condition base on matched column by foreign key relation or primary key and unique key
WHEN MATCHED --When records are matched, update the records if there is any change
    THEN 
WHEN NOT MATCHED --When no records are matched, insert the incoming records from source table to target table
    THEN 
WHEN NOT MATCHED BY SOURCE -- delete from target when record not matched with source table **
    THEN DELETE
OUTPUT $action, --$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns
[], --list of deleted column separated by comma
[], --list of inserted column separated by comma

The statement specifies the target table and the source table in the MERGE clause.

The merge_condition determines how the rows from the source table are matched to the rows from the target table. It is similar to the join condition in the join clause.

The merge_condition results in three states: MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

Finding a SQL Server running Task with percentage complete using DMVs

 

Monitoring Query Progress in SQL Server Using Live Query Statistics

When working with SQL Server, it's common to encounter long-running operations. At times, it becomes difficult to determine whether these operations are making progress or are stuck. Fortunately, SQL Server provides tools to help monitor such tasks in real-time.

One such tool is the Live Query Statistics feature, which can be enhanced with a custom query to track the percentage completion of various operations. These include:

• Database backup and restore processes
• DBCC commands like SHRINKFILE, SHRINKDATABASE, CHECKDB, CHECKTABLE, etc.
• Index reorganizations
• Transaction rollbacks

The following T-SQL script provides real-time insights into these operations:

SELECT r.session_id AS [Session_Id],
       r.command AS [Command],
       CONVERT(NUMERIC(6, 2), r.percent_complete) AS [%Complete],
       DATEADD(ms, r.estimated_completion_time, GETDATE()) AS [EstimatedCompletionTime],
       r.total_elapsed_time / 1000.0 / 60.0 AS [ElapsedMin],
       r.estimated_completion_time / 1000.0 / 60.0 AS [EstimatedMin],
       r.estimated_completion_time / 1000.0 / 60.0 / 60.0 AS [EstimatedHours],
       r.wait_time,
       (SELECT SUBSTRING(TEXT, r.statement_start_offset / 2,
               CASE WHEN r.statement_end_offset = -1
                    THEN 1000
                    ELSE (r.statement_end_offset - r.statement_start_offset) / 2
               END) AS TSQL_text
        FROM sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id;

This query taps into Dynamic Management Views (DMVs) to provide current execution details and the progress of each request.

---

Key Columns in sys.dm_exec_requests

The query above pulls data from the sys.dm_exec_requests DMV. Below are some important columns that provide valuable insights:

Column Name	Data Type	Description
session_id	smallint	Identifies the session related to the request.
request_id	int	Unique ID of the request within the session.
start_time	datetime	Time when the request started.
status	nvarchar(30)	Current status: Running, Suspended, Sleeping, etc.
sql_handle	varbinary(64)	Identifies the SQL batch or procedure.
statement_start_offset / statement_end_offset	int	Byte offset positions to isolate the running T-SQL statement.
plan_handle	varbinary(64)	Token for the query execution plan.
database_id	smallint	Database context in which the query is executing.
user_id	int	ID of the user who initiated the request.
blocking_session_id	smallint	ID of any blocking session, if applicable.
wait_type	nvarchar(60)	Type of wait the request is experiencing.
wait_time	int	Duration (in milliseconds) the request has been waiting.
last_wait_type	nvarchar(60)	The last type of wait, if any.
wait_resource	nvarchar(256)	Resource the request is currently waiting for.
percent_complete	real	Percentage of task completion.
estimated_completion_time	bigint	Time remaining in milliseconds (internal).
cpu_time	int	CPU time used in milliseconds.
total_elapsed_time	int	Total time elapsed since the request started.
reads / writes / logical_reads	bigint	Counts of reads and writes performed by the request.
transaction_isolation_level	smallint	Isolation level used (e.g., ReadCommitted, Serializable).
row_count	bigint	Number of rows returned to the client.
granted_query_memory	int	Number of pages granted for query execution.
query_hash / query_plan_hash	binary(8)	Hash values for identifying similar queries and plans.
parallel_worker_count	int	Number of parallel workers, applicable in newer versions.

For a full reference, see Microsoft’s documentation on sys.dm_exec_requests.

---

By using this method, DBAs can proactively monitor long-running processes and gain better visibility into SQL Server's performance and behavior during critical operations.

How to write a select with values in SQL Server

Using the SELECT Statement Without a FROM Clause with Multiple Rows

This is an example of a SELECT statement used without a FROM clause, returning multiple rows.

VALUES ( <row value expression list> ) [ ,...n ]   

<row value expression list> ::=  
    {<row value expression>} [ ,...n ]  

<row value expression> ::=  
    { DEFAULT | NULL | expression }

This code creates a dataset with two columns named c1 and c2, and two rows.


SELECT *
  FROM (VALUES (1,2),
               (3,4)
       ) t1 (c1, c2)


Another example with string and numeric values:


SELECT * FROM (
  VALUES ('Helmet', 25.50),  
         ('Wheel', 30.00)  
) ab(a, b);

Tables with more than 5 indexes

 

Query to Retrieve Index Table List

In this blog post, we'll look at a query that returns a list of tables in a database that have more than a specified number of indexes. This can be helpful for identifying tables with an unusually high number of indexes, which may be a sign of inefficiency.

The query is as follows:

DECLARE @threshold INT;
SET @threshold = 5;

SELECT 
   [Table] = s.[Name] + N'.' + t.[Name] 
FROM [sys].tables t
   INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
   SELECT 1 FROM [sys].indexes i
   WHERE i.[object_id] = t.[object_id]
   GROUP BY i.[object_id]
   HAVING COUNT(*) > @threshold
);

Explanation:

1. The query uses a threshold value (@threshold) that you can customize. In this case, the threshold is set to 5.
2. The SELECT statement retrieves the table names along with their schema name.
3. The WHERE EXISTS condition filters out tables that have fewer than the specified number of indexes.

This can be particularly useful in cases where you are analyzing database performance or trying to optimize query speed by reviewing indexing strategies.

---

Additional References:

• Unused Indexes
• Missing Indexes
• Working Without Clustered Indexes

How to Recompile All Database Objects

This is an example of using a cursor to recompile all stored procedures in the current database.

-- Declare a cursor to loop through all stored procedures
DECLARE sps CURSOR FOR
    SELECT ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'PROCEDURE';

OPEN sps;

DECLARE @RoutineName VARCHAR(128);
DECLARE @SQLString NVARCHAR(2048);

FETCH NEXT FROM sps INTO @RoutineName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Prepare the RECOMPILE statement
    SET @SQLString = 'EXEC sp_recompile ''' + @RoutineName + '''';
    
    -- Optional: Print the statement being executed
    PRINT @SQLString;

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQLString;

    FETCH NEXT FROM sps INTO @RoutineName;
END;

-- Clean up
CLOSE sps;
DEALLOCATE sps;

Top Procedures By Total Logical Reads

 Top  Procedures By Total Logical Reads

Are your SQL Server stored procedures running slower than a snail in molasses? Pinpointing the culprits can be a challenge, but with the right query, you can quickly identify those resource-hungry procedures that are slowing down your database. This post provides a powerful SQL script to help you analyze your stored procedure performance by focusing on logical reads, execution counts, and even potential missing indexes.

The following SQL query is designed to help you proactively monitor and optimize your SQL Server stored procedures. It provides insights into their performance, allowing you to prioritize your optimization efforts effectively.

SELECT TOP (25) p.name AS [SP Name]
 ,qs.total_logical_reads AS [TotalLogicalReads]
 ,qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads]
 ,qs.execution_count
 ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,qs.total_elapsed_time
 ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
 ,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
 AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_reads DESC
OPTION (RECOMPILE);

Understanding the Query's Output:

• SP Name: The name of your stored procedure.
• TotalLogicalReads: The total number of logical reads performed by the stored procedure since it was last cached. This is a key indicator of I/O activity.
• AvgLogicalReads: The average logical reads per execution, helping you understand the typical I/O cost of a single run.
• Execution_Count: How many times the stored procedure has been executed.
• Calls/Minute: Provides an idea of how frequently the procedure is being called.
• total_elapsed_time & avg_elapsed_time: Total and average execution time, crucial for identifying slow-running procedures.
• Has Missing Index: A very important flag! If this is '1', it means the SQL Server query optimizer identified a missing index that could significantly improve performance for this procedure.
• Last Execution Time & Plan Cached Time: Helpful timestamps for understanding the recency of activity and when the execution plan was last put into cache.

By using this script, you can easily identify your top 25 stored procedures based on total logical reads, which often correlate directly with performance bottlenecks. Pay close attention to procedures with a high "Has Missing Index" flag, as these represent immediate optimization opportunities.

Remember to always test any changes in a development environment before deploying them to production. Happy optimizing!

Top Procedures By Total Worker time

 Top  Procedures By Total Worker time

This SQL query is a handy tool for database administrators to identify the top 25 most resource-intensive stored procedures in their SQL Server database. It helps pinpoint performance bottlenecks by showing which procedures consume the most worker time, their average execution time, and even if they're suffering from missing indexes.

Here's a breakdown of what the query provides:

• SP Name: The name of the stored procedure.
• TotalWorkerTime: The cumulative CPU time (in microseconds) consumed by the procedure across all its executions. This is a key indicator of how much work a procedure is doing.
• AvgWorkerTime: The average CPU time per execution, giving you an idea of the typical cost of a single run.
• Execution_count: The total number of times the procedure has been executed since it was last cached.
• Calls/Minute: How frequently the procedure is being called, calculated by dividing the execution count by the minutes since it was cached.
• Total_elapsed_time: The total wall-clock time (in microseconds) spent executing the procedure.
• avg_elapsed_time: The average wall-clock time per execution.
• Has Missing Index: A flag (1 for yes, 0 for no) that indicates if the procedure's query plan suggests that a missing index could improve performance. This is crucial for optimization!
• Last Execution Time: When the procedure was last run.
• Plan Cached Time: When the execution plan for the procedure was cached. This is useful for understanding how fresh the statistics are.
• Query Plan: (Commented out by default) If uncommented, this would show the XML-formatted execution plan, which provides detailed information about how SQL Server executes the procedure.

The query joins `sys.procedures` with `sys.dm_exec_procedure_stats` to get performance metrics and uses `sys.dm_exec_query_plan` to check for missing indexes. It filters for procedures in the current database and those with a cached plan that's not brand new, ordering the results by `TotalWorkerTime` in descending order to show the heaviest hitters first. The `OPTION (RECOMPILE)` hint ensures that the query itself gets a fresh execution plan each time it's run, which can be helpful for queries used in performance monitoring.

SELECT TOP (25) p.name AS [SP Name]
 ,qs.total_worker_time AS [TotalWorkerTime]
 ,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
 ,qs.execution_count
 ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,qs.total_elapsed_time
 ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
 ,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
 AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);

This script is an essential tool for any SQL Server DBA looking to proactively identify and address performance issues within their stored procedures. By regularly running and analyzing its output, you can ensure your database remains optimized and responsive.