Jul 10, 2025

fn_my_permissions

 fn_my_permissions ( securable , 'securable_class' )

Value Description
DATABASE   Permissions on the current database
OBJECT Table, View, Stored Procedure, etc.
SCHEMA Database schema
SERVER Server-level permissions

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions('dbo.YourTableName', 'OBJECT');

Jul 3, 2025

.

truncate table LongRunningQueries

 

INSERT INTO LongRunningQueries

 

(

 

    session_id,

 

    status,

 

    start_time,

 

    command,

 

    cpu_time,

 

    total_elapsed_time,

 

    blocking_session_id,

 

    query_text,

 

    capture_time,

 

    tempdb_allocation_kb,

 

    tempdb_current_kb,

 

    host_name,

 

    database_name,

 

    program_name,

 

    used_memory_kb,

 

    login_name,  -- Added login_name

 

    server_name  -- Added server_name

 

)

 

SELECT
    r.session_id,
    r.status,
    r.start_time,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.blocking_session_id,
    s.text AS query_text,
    GETDATE() AS capture_time,
    t.user_objects_alloc_page_count * 8 AS tempdb_allocation_kb,
    t.user_objects_dealloc_page_count * 8 AS tempdb_current_kb,
    p.host_name,
    DB_NAME(r.database_id) AS database_name,
    p.program_name,
    m.used_memory_kb,
    p.login_name,  -- Fetching login_name from dm_exec_sessions
    @@SERVERNAME AS server_name  -- Fetching server_name using system function
FROM
    sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
    LEFT JOIN sys.dm_exec_sessions p ON r.session_id = p.session_id
    LEFT JOIN sys.dm_db_task_space_usage t ON r.session_id = t.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants m ON r.session_id = m.session_id
WHERE
    r.total_elapsed_time > 2400000  -- 40 minutes = 2,400,000 milliseconds
    AND s.text NOT LIKE '%sp_server_diagnostics%';  -- Exclude sp_server_diagnostics

 

--------------------------------

 

  


CREATE PROCEDURE dbo.SendQueryPerformanceEmail    

AS    

BEGIN    

    DECLARE @tableHTML NVARCHAR(MAX), @mailSubject NVARCHAR(255), @recipients NVARCHAR(500), @senderEmail NVARCHAR(255)    



    -- Initialize the recipients, sender email, and subject    

    SET @recipients = 'dba@clotatechnology.com'  -- Add your recipient email(s)    

    SET @senderEmail = 'db.alerts@wylth.com'          -- Add your sender email address    

    SET @mailSubject = 'Query Performance Report'    



    -- Check if data exists in the table    

    IF EXISTS (SELECT 1 FROM LongRunningQueries)    

    BEGIN    

        -- Start the HTML table structure with modern CSS    

        SET @tableHTML =    

        '<!DOCTYPE html>    

<html lang="en">    

<head>    

<meta charset="UTF-8">    

<meta name="viewport" content="width=device-width, initial-scale=1.0">    

<style>    

                    body {    

                        font-family: Arial, sans-serif;    

                        margin: 0;    

                        padding: 0;    

                        background-color: #f4f4f4;    

                        color: #333;    

                    }    

                    .container {    

                        width: 100%;    

                        max-width: 800px;    

                        margin: 0 auto;    

                        background-color: #ffffff;    

                        padding: 20px;    

                        border-radius: 10px;    

                        box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);    

                    }    

                    h2 {    

                        color: #2C3E50;    

                        text-align: center;    

                    }    

                    table {    

                        width: 100%;    

                        border-collapse: collapse;    

                        margin-top: 20px;    

                    }    

                    th, td {    

                        border: 1px solid #dddddd;    

                        text-align: left;    

                        padding: 12px;    

                    }    

                    th {    

                        background-color: #2C3E50;    

                        color: #ffffff;    

                        font-weight: bold;    

                    }    

                    tr:nth-child(even) {    

                        background-color: #f2f2f2;    

                    }    

                    .footer {    

                        margin-top: 20px;    

                        text-align: center;    

                        font-size: 0.9em;    

                        color: #777;    

                    }    

                    .powered-by {    

                        margin-top: 10px;    

                        font-size: 0.8em;    

                        color: #aaa;    

                    }    

</style>    

</head>    

<body>    

<div class="container">    

<h2>Query Performance Report</h2>    

<table>    

<thead>    

<tr>    

<th>Session ID</th>    

<th>Status</th>    

<th>Start Time</th>    

<th>Command</th>    

<th>CPU Time (ms)</th>    

<th>Total Elapsed Time (ms)</th>    

<th>Blocking Session ID</th>    

<th>Query Text</th>    

<th>Capture Time</th>    

<th>TempDB Allocation (KB)</th>    

<th>TempDB Current (KB)</th>    

<th>Host Name</th>    

<th>Database Name</th>    

<th>Program Name</th>    

<th>Used Memory (KB)</th>    

<th>Login Name</th>    

<th>Server Name</th>    

</tr>    

</thead>    

<tbody>'    



        -- Add table rows with data from LongRunningQueries table    

        SELECT @tableHTML = @tableHTML +    

            '<tr>' +    

            '<td>' + COALESCE(CAST(session_id AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(status, '') + '</td>' +    

            '<td>' + COALESCE(CONVERT(NVARCHAR(30), start_time, 121), '') + '</td>' +    

            '<td>' + COALESCE(command, '') + '</td>' +    

            '<td>' + COALESCE(CAST(cpu_time AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(CAST(total_elapsed_time AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(CAST(blocking_session_id AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(query_text, '') + '</td>' +    

            '<td>' + COALESCE(CONVERT(NVARCHAR(30), capture_time, 121), '') + '</td>' +    

            '<td>' + COALESCE(CAST(tempdb_allocation_kb AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(CAST(tempdb_current_kb AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(host_name, '') + '</td>' +    

            '<td>' + COALESCE(database_name, '') + '</td>' +    

            '<td>' + COALESCE(program_name, '') + '</td>' +    

            '<td>' + COALESCE(CAST(used_memory_kb AS NVARCHAR(10)), '') + '</td>' +    

            '<td>' + COALESCE(login_name, '') + '</td>' +    

            '<td>' + COALESCE(server_name, '') + '</td>' +    

            '</tr>'    

        FROM LongRunningQueries    



        -- Close the HTML table and add footer    

        SET @tableHTML = @tableHTML +    

            '</tbody>    

</table>    

<div class="footer">    

<p>Generated on ' + CONVERT(NVARCHAR(30), GETDATE(), 121) + '</p>    

<div class="powered-by">    

<p>Powered by <a href="https://clotatechnology.com" target="_blank">Clota Technology</a></p>    

</div>    

</div>    

</div>    

</body>    

</html>'    



        -- Send the email with the constructed HTML body    

        EXEC msdb.dbo.sp_send_dbmail    

            @profile_name = 'A3TechAccount',      -- Replace with your Database Mail profile name    

            @recipients = @recipients,    

            @subject = @mailSubject,    

            @body = @tableHTML,    

            @body_format = 'HTML',    

            @from_address = @senderEmail      -- Specify the sender's email address    

    END    

    ELSE    

    BEGIN    

        PRINT 'No data found in LongRunningQueries table. Email will not be sent.'    

    END    

END

May 6, 2025

Monitoring SQL Server Deadlocks using Extended Events


Extended Events were first introduced in SQL Server 2008, and therefore you will not face any problem implementing them in our environment.

Extended Events is a lightweight performance monitoring feature that enables users to collect data to monitor and troubleshoot problems.

A deadlock is a situation where two or more processes or threads are blocked indefinitely, waiting for each other to release resources that they need to proceed.

Here we are going to observe how to gather deadlock information through Extended Events :

Creating SQL Server Extended Events to Capture Deadlocks using Management Studio

Step 1: First, open SQL Server Management Studio (SSMS) and navigate to Management > Extended Events > Sessions.



 

Step 2: Right-click on Sessions and select New Session.




Step 3: In the new window, provide a name for your event, we will call it SQLWATCH_deadlock in this caseSelect the event to start at the beginning of the server and when the session is created, you can change these settings if necessary.



Step 4:  Go to the Events tab and in the event library textbox type “deadlock” to filter deadlock events:


Step 5: We will select 2 events: Lock_Deadlock (Raised when a request to acquire a lock is cancelled for the victim of a deadlock) and Lock_deadlock_chain (Raised when a request to acquire a lock results in a deadlock. This event is raised for all members of the deadlock).




Step 6: Having both the events chosen, click on the configure button, and a new window will be opened, in this window we will take a snapshot of the sql_text field so that we can view the query that generated the deadlock


Step 7: In the Data Storage tab, select where you want to store the Extended Event data use a file.



Step 8: After that, click on OK to save the Event configuration, we can see that the event is created and is already running.