stored-procedures

Find locks in SQL Server

dm_tran_locks contains info about all current locks, granted or pending.

select *
from sys.dm_tran_locks

Use stored procedures instead of embedded T-SQL

Use stored procedures whenever a client application needs to send Transact-SQL to SQL Server. Stored procedures offer many benefits:

  • Reduces network traffic and latency, boosting application performance.
  • Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  • Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
  • Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access.

Performance statistics for stored procedures

By looking at the results returned by dm_exec_procedure_stats DMV, we can gather info about how a stored procedure is performing:

SELECT
        DB_NAME(database_id), p.name as SPName, execution_count
        , total_physical_reads, total_physical_reads / execution_count as avg_physical_reads
        , total_logical_reads, total_logical_reads / execution_count as avg_logical_reads
        , total_elapsed_time, total_elapsed_time / execution_count as avg_elapsed_time
        , QP.query_plan
FROM
        sys.procedures P
        JOIN sys.dm_exec_procedure_stats S ON S.object_id = P.object_id
        CROSS APPLY [sys].[dm_exec_query_plan] (S.[plan_handle]) QP
WHERE
        S.database_id = DB_ID()
        AND P.is_ms_shipped = 0

Versioning of stored procedures

SQL Server provides a built-in mechanism for versioning of stored procedures.
You can create more versions for the same stored procedures by adding “;” and version number at the end of stored procedure name. If version number is not specified, SQL Server presumes it to be 1. Let’s have a look on the following sample:

CREATE PROCEDURE myproc;1
AS
PRINT 'You have run version 1 of myproc stored procedure'
GO

CREATE PROCEDURE myproc;2
AS
PRINT 'You have run version 2 of myproc stored procedure'
GO

Running a specific version of the procedure is straightforward:

EXEC myproc;1

or
EXEC myproc;2

One very interesting thing I should remark is you are allowed to use different signatures for each version. Among of drawbacks I would like to point out you can’t drop just a specific version using DROP PROCEDURE statement (you can delete all versions at a time).

Code template for transactions

Code template for correctly handling transactions in SQL Server:

SET XACT_ABORT ON;

BEGIN TRY
        BEGIN TRANSACTION
                -- Your script here

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        IF XACT_STATE() != 0  
                ROLLBACK TRANSACTION

        -- Re-throw error
        EXEC ThrowError
END CATCH
GO

Sample of ThrowError function:

CREATE PROCEDURE ThrowError
AS
BEGIN
   DECLARE
     @ErrorNumber INT
     , @ErrorMessage NVARCHAR(4000)
     , @ErrorSeverity INT
     , @ErrorState INT
     , @ErrorProcedure NVARCHAR(128)
     , @ErrorLine INT
     
   SELECT @ErrorNumber = ERROR_NUMBER()    
        , @ErrorMessage = ERROR_MESSAGE()
        , @ErrorSeverity = ERROR_SEVERITY()
        , @ErrorState = ERROR_STATE()
        , @ErrorProcedure = ERROR_PROCEDURE()
        , @ErrorLine = ERROR_LINE()

    SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
   
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )
END
GO

Subscribe to RSS - stored-procedures