How to check active transactions in SQL Server 2014?

How to check active transactions in SQL Server 2014?

Note that, no exception in the SqlTransaction C#.

1. Query with sys.sysprocesses

   SELECT * FROM sys.sysprocesses WHERE open_tran = 1

2. DBCC OPENTRAN : helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log.

3. sys.dm_tran_active_transactions

Returns information about transactions for the instance of SQL Server. Syntax

Wondering about Transaction ?

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.

Find more at docs

How to find if Table/View is used in Stored Procedure in SQL Server

How to find if Table/View is used in Stored Procedure in SQL Server

In this post, we are going to learn how to find out if the table is used in a Stored Procedure. we have multiple ways to do that.

This System Stored Procedure will return you list of table/views used by the Stored procedure/View. You can pass the view name or Stored Procedure as parameter,it will return you tables/views which are used in the object.

One disadvantage of using sp_depends is , it will not show you tables/views which are not in current database. If you have used objects from other databases, this information might be misleading.

--By using sp_depends, You can provide Stored Procedure Name or View name as parameter
EXEC sp_depends '[dbo].[GetCustomer]'



The 2nd method is to use the sys.sysdepends view with combination of sys.sysobjects to get the information. This also works the same way. It will not show you cross database objects ( Tables,Views) if have used in Stored Procedure or view definition.

--Get Information from sys.sysdepends 
SELECT DISTINCT OBJECT_NAME(SD.id) AS StoredProcedureNameName,
 OB.name AS TableOrViewName
 FROM sys.sysdepends SD JOIN sys.sysobjects O 
ON SD.id=O.id
 INNER JOIN sys.sysobjects OB 
ON SD.depid=OB.id 
AND O.xtype='P'


3rd method is to use the sys.all_sql_modules system view. The definition column of this view has definition of Stored Procedure, Views, Functions etc.You can write query as given below to check if any of the Stored Procedure or object is using the table/view you are looking for.

--Use sys.all_sql_modules system view to get information if table is used
--in a Stored Procedure 
SELECT OBJECT_NAME(OBJECT_ID),definition 
 FROM sys.all_sql_modules 
WHERE definition LIKE '%vw_Employee%'

Recompile a Stored Procedure

Recompile a Stored Procedure

This topic describes how to recompile a stored procedure in SQL Server 2019 (15.x) by using Transact-SQL. There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure. This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. It also describes using the sp_recompile system stored procedure to recompile an existing procedure.

In This Topic

• Before you begin:

  Recommendations

  Security

• To recompile a stored procedure, using:

  Transact-SQL

Before You Begin

Recommendations

• When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes. This can improve the procedure's processing performance.

• There are times when procedure recompilation must be forced and other times when it occurs automatically. Automatic recompiling occurs whenever SQL Server is restarted. It also occurs if an underlying table referenced by the procedure has undergone physical design changes.

• Another reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

• SQL Server features statement-level recompilation of procedures. When SQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure.

• If certain queries in a procedure regularly use atypical or temporary values, procedure performance can be improved by using the RECOMPILE query hint inside those queries. Since only the queries using the query hint will be recompiled instead of the complete procedure, SQL Server's statement-level recompilation behavior is mimicked. But in addition to using the procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when you compile the statement. For more information, see Query Hint (Transact-SQL).

Security

Permissions

WITH RECOMPILE Option
If this option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure. Permissions are not required on the EXECUTE statement itself but execute permissions are required on the procedure referenced in the EXECUTE statement. For more information, see EXECUTE (Transact-SQL).

RECOMPILE Query Hint
This feature is used when the procedure is created and the hint is included in Transact-SQL statements in the procedure. Therefore, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

sp_recompile System Stored Procedure
Requires ALTER permission on the specified procedure.

Using Transact-SQL

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example creates the procedure definition.

   SQLCopy

   USE AdventureWorks2012;  
   GO  
   IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
       DROP PROCEDURE dbo.uspProductByVendor;  
   GO  
   CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
   WITH RECOMPILE  
   AS  
       SET NOCOUNT ON;  
       SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
       FROM Purchasing.Vendor AS v   
       JOIN Purchasing.ProductVendor AS pv   
         ON v.BusinessEntityID = pv.BusinessEntityID   
       JOIN Production.Product AS p   
         ON pv.ProductID = p.ProductID  
       WHERE v.Name LIKE @Name;  
   

To recompile a stored procedure by using the WITH RECOMPILE option

Select New Query, then copy and paste the following code example into the query window and click Execute. This executes the procedure and recompiles the procedure's query plan.

SQLCopy

USE AdventureWorks2012;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

To recompile a stored procedure by using sp_recompile

Select New Query, then copy and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.

SQLCopy

USE AdventureWorks2012;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO