Monitoring SQL Sessions with necessary parameters

Hello Readers !

Below is a useful T-SQL query having necessary monitoring parameters to do a live monitoring of SQL server sessions in SSMS. You can put more filters in the where clause to make it more relevant to your environment.

It includes isolation levels used by the sessions along with popular columns that are monitored like hostname, wait time, program name etc.

use master
go
SELECT   s.session_id,
            r.status,
            r.blocking_session_id as BlockedBy,                                 
            r.wait_type,
            wait_resource,
DB_name(s.database_id) as DBName,
            s.host_name,
CASE r.transaction_isolation_level
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unspecified' END AS transaction_isolation_level,
            r.wait_time / (1000.0) as [WaitSec],
r.total_elapsed_time / (1000.0) as [ElapSec],
            r.cpu_time,
            r.logical_reads,
            r.reads,
            r.writes,

            Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
                    ((CASE r.statement_end_offset
                        WHEN -1
                        THEN Datalength(st.TEXT)
                        ELSE r.statement_end_offset
                        END - r.statement_start_offset) / 2) + 1) AS SQL_text,
            Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)),
                    '') AS command_text,
            r.command,
            s.login_name,
            s.program_name, 
dateadd(HOUR, 3, S.last_request_end_time) as last_request_end_time,
            dateadd(HOUR, 3,s.login_time) as Login_Time,
            r.open_transaction_count
FROM     sys.dm_exec_sessions AS s
            JOIN sys.dm_exec_requests AS r
            ON r.session_id = s.session_id
            CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE    r.session_id != @@SPID 
ORDER BY r.cpu_time desc, r.status,
            r.blocking_session_id,
            s.session_id

Please let me know in comment section if you find any other columns that are useful to amend and improve this.

[Solved]: Replication monitor could not retrieve Publisher information from the distributor (In azure managed instances)

Hi Readers,

I am writing this blog to document a workaround for a connectivity issue I was facing in my Replication monitor (in SSMS).I am hoping those who are facing this issue, would be benefited from this blog.

 I was configuring MI-MI  replication with T-SQL scripts and at the end when I was trying to launch Replication monitor by right clicking on the publication , I have encountered the below error, which was basically telling me that the replication monitor is unable to connect to distributor or cannot retrieve the publisher information from the distributor.

Error message:

Clicking on the show details button above got me the below text

Message details:

===================================

Replication Monitor could not retrieve Publisher information from Distributor 'tcp:xxxxxx.xxxxxxxx.database.windows.net,1433'. (Replication Monitor)

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18131.0&EvtSrc=Microsoft.SqlServer.Management.UI.ReplicationMonitorErrorSR&EvtID=CantGetPublisherInformation&LinkId=20476

===================================

Could not refresh properties of this object from server. Check if this object still exists on the server.  (Microsoft.SqlServer.Rmo)

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

Program Location:

   at Microsoft.SqlServer.Replication.ReplicationObject.Refresh()

   at Microsoft.SqlServer.Management.UI.ReplMonitorMenu.Invoke()

<End of the error message>

Looking at the error message, I thought this must have something to do with the publisher details maintained in the distributor. I went and right-clicked on the Replicationàselected Distributor propertiesàWent further for the ‘publishers’ page in that window and could see everything was perfect there. And moreover I could successfully test the data inserted at the publication DB being sent successfully to the subscriber too. This cannot be anything to do with the permissions, as I have used SQL accounts with sysadmin access everywhere.

After browsing through some online forums and having found no solutions, I opened a case with Microsoft as I felt that the replication monitor is important for us if we are ever going to use replication in the Prod setup and we didn’t have the luxury of time to keep exploring solutions online.

The gentle man from azure team tried a few options like downloading the latest SSMS and trying to remove the publisher from the replication monitor and adding it back. Unlucky for both of us, nothing really worked.

On the very next day, I stumbled upon a workaround that saved time for both of us which was removing publisher from the replication monitor and adding it back using the third option.

The option that I used was the one in the screenshot

I have supplied the distributor server name and could successfully use the replication monitor to insert tracer tokens, check latency data, look at the agent profiles etc with no issue whatsoever.

I am hoping you would find this blog content useful or Microsoft fixing this issue in their next SSMS release. Thanks for visiting my blog.