Check logspace on multiple servers

In SSMS setup all your servers in a group, see https://www.brentozar.com/archive/2008/08/sql-server-2008s-new-central-management-server/ for a splendid howto.

Now connect to them all by right-clicking on the group and choosing New Query, this new query window will have its status list at the bottom in a pinkish/red hue instead of the usual yellow to show that, yes we are connected to a group.

I often use dbcc sqlperf(logspace) to quickly get logspace usage for all databases on the instance, and we can do this for all servers at once. When I wrote this I was only interested in those servers and databases that had used more than 10% of its log, and no system databases. Came up with the following

CREATE TABLE #logspace
( [dbname] sysname
, logSizeMB float
, logSpaceUsedPct float
, Status int);

INSERT INTO #logspace
EXEC ('DBCC SQLPERF(LOGSPACE);')

SELECT dbname
, logSizeMB
, CONVERT(INT, logSpaceUsedPct)
, logSizeMB - (logSizeMB * logSpaceUsedPct / 100) AS LogSpaceUnusedMB
FROM #logspace
WHERE logSpaceUsedPct > 10
AND dbname not in ('master','msdb','model','tempdb','SSISDB')
ORDER BY logSpaceUsedPct DESC;   

DROP TABLE #logspace

As always, run this on a testserver first and when you have verified it to work, go for prod.

Have a good day!

Measure disk IO with TSQL

There is a WAITFOR DELAY after the first SELECT, default is 2 seconds, change to whatever you want.

 SELECT 1 AS x
     , a.database_id
     , b.[name]
     , b.[type_desc]
     , a.size_on_disk_bytes
     , a.io_stall_read_ms
     , a.num_of_reads
     , a.num_of_bytes_read
     , b.[type]
     , a.io_stall_write_ms
     , a.num_of_writes
     , a.num_of_bytes_written
     , b.physical_name
     , GETDATE() AS ts
 INTO #data
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
 INNER JOIN sys.master_files AS b ON a.file_id = b.file_id AND a.database_id = b.database_id;
  
 WAITFOR DELAY '00:00:02';
  
 INSERT #data
     SELECT 2
         , a.database_id
         , b.[name]
         , b.[type_desc]
         , a.size_on_disk_bytes
         , a.io_stall_read_ms
         , a.num_of_reads
         , a.num_of_bytes_read
         , b.[type]
         , a.io_stall_write_ms
         , a.num_of_writes
         , a.num_of_bytes_written
         , b.physical_name
         , GETDATE()
     FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
     INNER JOIN sys.master_files AS b ON a.file_id = b.file_id AND a.database_id = b.database_id;
  
 SELECT DB_NAME(d1.database_id) AS [database_name]
     , d1.[name] AS logical_file_name
     , d1.[type_desc] AS logical_file_type
     --, d1.[type]
     , d1.physical_name AS physical_file_name
     , d2.size_on_disk_bytes - d1.size_on_disk_bytes AS size_on_disk_bytes
  
     , d2.io_stall_read_ms - d1.io_stall_read_ms AS io_stall_read_ms
     , d2.num_of_reads - d1.num_of_reads AS num_of_reads
     , d2.num_of_bytes_read - d1.num_of_bytes_read AS num_of_bytes_read
     , CASE WHEN (d2.num_of_reads - d1.num_of_reads) > 0 THEN (d2.io_stall_read_ms - d1.io_stall_read_ms) / (d2.num_of_reads - d1.num_of_reads) ELSE NULL END AS read_stall_ms_average
     , CASE 
         WHEN d1.[type] = 0 THEN 30 /* data files */
         WHEN d1.[type] = 1 THEN 5 /* log files */
         ELSE 0
       END AS read_stall_ms_avg_max
     , CASE
         WHEN d1.[name] = 'tempdb' THEN 'N/A'
         WHEN d1.[type] = 1 THEN 'N/A' /* log files */
         ELSE 'PAGEIOLATCH*'
       END AS read_related_wait_stat
     , d2.io_stall_write_ms - d1.io_stall_write_ms AS io_stall_write_ms
     , d2.num_of_writes - d1.num_of_writes AS num_of_writes
     , d2.num_of_bytes_written - d1.num_of_bytes_written AS num_of_bytes_written
     , CASE WHEN (d2.num_of_writes - d1.num_of_writes) <> 0 THEN (d2.io_stall_write_ms - d1.io_stall_write_ms) / (d2.num_of_writes - d1.num_of_writes) ELSE NULL END AS write_stall_ms_avg
     , CASE 
         WHEN d1.[type] = 0 THEN 30 /* data files */
         WHEN d1.[type] = 1 THEN 2 /* log files */
         ELSE 0
       END AS write_stall_ms_avg_max
     , CASE
         WHEN d1.[type] = 1 THEN 'WRITELOG' /* log files */
         WHEN d1.[name] = 'tempdb' THEN 'xxx' /* tempdb data files */
         WHEN d1.[type] = 0 THEN 'ASYNC_IO_COMPLETION' /* data files */
         ELSE 'xxx'
       END AS write_related_wait_stat
     , DATEDIFF(MILLISECOND, d1.ts, d2.ts) / 1000.0 AS sample_time_second
 FROM #data d1
 INNER JOIN #data d2 ON d1.database_id = d2.database_id
     AND d1.[name] = d2.[name]
     AND d1.[type_desc] = d2.[type_desc]
     AND d1.[type] = d2.[type]
     AND d1.physical_name = d2.physical_name
     AND d1.x = 1
     AND d2.x = 2
 WHERE (d2.io_stall_read_ms - d1.io_stall_read_ms) + 
     (d2.num_of_reads - d1.num_of_reads) + 
     (d2.num_of_bytes_read - d1.num_of_bytes_read) + 
     (d2.io_stall_write_ms - d1.io_stall_write_ms) + 
     (d2.num_of_writes - d1.num_of_writes) + 
     (d2.num_of_bytes_written - d1.num_of_bytes_written) <> 0;
  
 
 DROP TABLE #data; 

Extended event to find failing stored procedure

-- Drop if it exists
DROP EVENT SESSION [failing_queries] ON SERVER 
GO

-- Create extended event session
CREATE EVENT SESSION failing_queries ON SERVER
ADD EVENT sqlserver.error_reported (
    ACTION (sqlserver.sql_text
        , sqlserver.tsql_stack
        , sqlserver.database_id
        , sqlserver.username
        )
    WHERE ([severity] > 10)
    )
ADD TARGET package0.asynchronous_file_target (
    SET filename = 'C:\XEventSessions\failing_queries.xel'
    , metadatafile = 'C:\XEventSessions\failing_queries.xem'
    , max_file_size = 5
    , max_rollover_files = 5
    )
    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

-- Start the session
ALTER EVENT SESSION failing_queries ON SERVER STATE = START
GO

-- Test it
CREATE PROCEDURE [dbo].[FailProc]
AS
BEGIN
    SET NOCOUNT ON;
        SELECT 1/0;
END

-- Run it
Exec [FailProc];


-- Get info from trace
;WITH events_cte
AS (
    SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [err_timestamp]
        , xevents.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity]
        , xevents.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number]
        , xevents.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message]
        , xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
        , xevents.event_data
    FROM sys.fn_xe_file_target_read_file('C:\XEventSessions\failing_queries*.xel', 'C:\XEventSessions\failing_queries*.xem', NULL, NULL)
    CROSS APPLY (
        SELECT CAST(event_data AS XML) AS event_data
        ) AS xevents
    )
SELECT *
FROM events_cte
ORDER BY err_timestamp DESC;

Track progress of create index command

My normal SQL for checking long running procedures do not work with the CREATE INDEX command, I usually run:

SELECT     db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
         SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
         (CASE WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
         ELSE 
                 r.statement_end_offset
         END -r.statement_start_offset)/2) AS stmt_executing,
     s.login_name, r.percent_complete, r.start_time, 
     CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                   + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                   + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
             dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
     ,CASE s.transaction_isolation_level
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadComitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
     END as 'Isolation level'
     ,   s.HOST_NAME,    s.PROGRAM_NAME, s.host_process_id, 
     r.status,   r.wait_time,    wait_type,  r.wait_resource,
     r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
     --Comment out below if you don't need the plan
     , (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
 FROM sys.dm_exec_requests r
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
             sys.dm_exec_sessions s
 WHERE r.session_id > 50 
 and r.session_id=s.session_id
 and r.session_id <> @@SPID
 ORDER BY r.cpu_time desc

But this will not print out the percent complete value we want, need to pick it up in another way. What you need to do is the following, in the same SSMS Query window that you later will run CREATE INDEX you start with finding your SPID by running:

SELECT @@SPID

Save that SPID, we’ll use it later.

To be able to pick up the CREATE INDEX progress we need to add:

SET STATISTICS PROFILE ON;
SET STATISTICS XML ON;

So the full CREATE INDEX command would be something like:

SET STATISTICS PROFILE ON;
SET STATISTICS XML ON;

CREATE NONCLUSTERED INDEX [IX_mycolumn] ON [dbo].[mycolumn]
(
    [mycolumn] ASC
)
GO

Then in another window we run we run some code checking that SPID we found earlier, say that the SPID was 42, then it would be:

DECLARE @SPID INT = 42;
 ;WITH agg AS
 (
      SELECT SUM(qp.[row_count]) AS [RowsProcessed],
             SUM(qp.[estimate_row_count]) AS [TotalRows],
             MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
             MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
                     [physical_operator_name],
                     N'')) AS [CurrentStep]
      FROM sys.dm_exec_query_profiles qp
      WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan',
                                            N'Index Scan',  N'Sort')
      AND   qp.[session_id] = @SPID
 ), comp AS
 (
      SELECT *,
             ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
             ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
      FROM   agg
 )
 SELECT [CurrentStep],
        [TotalRows],
        [RowsProcessed],
        [RowsLeft],
        CONVERT(DECIMAL(5, 2),
                (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
        [ElapsedSeconds],
        (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
        DATEADD(SECOND,
                (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
                GETDATE()) AS [EstimatedCompletionTime]
 FROM   comp;

Now we get an ETA and stuff, neat huh? 🙂

None of this was my original work, I’m just building upon giants here, that’s what’s so nice about the SQL Server community!

Snapshot replication with AlwaysOn

A client of mine was upgrading a 2008R2 system to 2016, they previously had database mirroring as HA solution, I suggested they go with AlwaysOn instead as mirroring is a deprecated, although simple to implement, solution. In addition to mirroring they had snapshot replication running once a day replicating a number of tables to a development environment. The old solution demanded the database was primary on one specific server, they could not change the application to use the FAILOVER PARTNER option, so at patching they always had to leave the primary running on one particular server. That wouldn’t do!

So when they decided to upgrade we went with SQL Server 2016 on Windows 2016 as that was the latest and greatest at the time. We set up Server1 and Server2 which were part of a new AlwaysOn cluster and Server3 which was a DEV server that would get some tables updated via snapshot replication each night. Simple setup, I won’t go into detail about AO, that can be found everywhere. What I was interested in was how to setup snapshot replication pushing data to a dev env. Turned out it was quite easy.

DISTRIBUTION
First we decided on where to setup the distribution database, I chose one of the AO servers, Server1 to be exact as replication was not a part of HA or DR, just copying some data and it didn’t matter if we missed a day rebuilding a server if it had crashed miserably. So on Server1 under the Replication tab we right click and choose to create a new distribution database and we also choose to have all agent jobs running on this server.

PUBLISHER
Set Server1 as PRIMARY AO server and start the New Publisher wizard, choose snapshot replication, set a schedule and choose to run a snapshot to disk right away. You will probably, depending on your setup, get an error that it can’t write to the replication catalog, easily fixed, just give the service account rights to do so and start the snapshot agent job again. Which one is the snapshot job? Well, look at the properties of each replication agent job and choose the one that says it’s the snapshot job. If it works you will get a new catalog called unc and the job will succeed, you can always check more with the Replication Monitor program.

SUBSCRIBER
Still on Server1 we right click on the published replication and choose New Subscriber, choose your security setup and create the subscription. After this first replication has been pushed out we should see the changes made on Server3.

AG CHANGES
We have to change the publisher from Server1 to the AG, so still on Server1 that is still Primary we run something similar to:

USE distribution;   
GO   
EXEC sp_redirect_publisher    
    @original_publisher = 'server1',   
    @publisher_db = 'db_to_replicate',   
    @redirected_publisher = 'YOUR-AG-NAME'
GO

This can give an error message that the secondary is not in readable mode, not sure if this is a real problem or not as I only tested on Enterprise Edition, I set the AG to Readable Secondaries=Yes and ran it again and it worked. I’ll try on Standard later. Next we run a verification, I got an error on Linked Server saying it can’t link to itself:
Warning: A linked server that refers to the originating server is not a supported scenario.  If you wish to use a four-part name to reference a local table, please use the actual server name rather than an alias.

 USE distribution;  
 GO  
 DECLARE @redirected_publisher sysname;  
 EXEC sys.sp_validate_replica_hosts_as_publishers  
     @original_publisher = 'server1',  
     @publisher_db = 'db_to_replicate',  
     @redirected_publisher = @redirected_publisher output;
GO 

Ok, so yes, this it it, I did some changes on the tables I wanted to replicate, ran the snapshot agent job again and it replicated nicely. Next I failed over to Server2 and again did some changes and ran the snapshot job on Server1 and voila! Everything still worked!!

One thing to remember, I only chose to replicate a subset of all the tables so after my first change and snapshot replication it didn’t replicate my changes, weird, but the change I did was to add another table, DUH, of course it didn’t replicate that, I had to go in under Properties of the Publication and choose that new table to have it replicated, ran the snapshot again and now it worked just fine 🙂

SQL Server unused indexes

Updating an index that is never used for searching is just a waste of cpu and diskspace, the query below can be run in a database and shows you which are not used and the size of the index.

Note, this is from the last instance reboot, if you have recently restarted than maybe you see wrong data. Or maybe you have an index that is only used for monthly reporting…or something, don’t just drop the indexes without carefully checking why it is created and its purpose.

Anywho, here’s the code…

SELECT d.name + '.' + s.name + '.' + o.name AS objectName
, i.name AS indexName
, MAX(ius.user_updates) AS userUpdates
, MAX(ius.user_seeks) AS userSeeks
, MAX(ius.user_scans) AS userScans
, MAX(ius.user_lookups) AS userLookups
, CASE
    WHEN SUM(a.used_pages) * 8 < 1024 THEN CONVERT(VARCHAR(32), SUM(a.used_pages) * 8) + ' kB'
    WHEN SUM(a.used_pages) * 8 / 1024 < 1024 THEN CONVERT(VARCHAR(32), SUM(a.used_pages) * 8 / 1024) + ' MB'
    ELSE CONVERT(VARCHAR(32), SUM(a.used_pages) * 8 / 1024 / 1024) + ' GB'
END AS indexSize
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_index_usage_stats ius ON ius.[object_id] = i.[object_id]
                             AND i.index_id = ius.index_id 
INNER JOIN sys.databases d ON d.database_id = ius.database_id
INNER JOIN sys.partitions AS p ON i.[object_id] = p.[object_id]
                             AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON p.[partition_id] = a.container_id
WHERE ius.user_updates > 0
AND ius.user_seeks + ius.user_scans + ius.user_lookups <= 0
AND i.is_primary_key = 0
GROUP BY d.name, s.name, o.name, i.name
ORDER BY MAX(ius.user_updates) DESC;
GO

Should backups be run here?

Needed a quick way to see which databases should be backed up on a node, regardless of member of the AG or not. There is a function one can use and send the database name to it, see https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-backup-is-preferred-replica-transact-sql?view=sql-server-ver15 for how to use it.

It works but can be very slow, and I wanted to roll my own anyway, so a quick hack later I came up with the below code. It checks if we are on the node where AG backups should be done, if so prints out all the databases, even those not in the AG. If we are on the not preferred backup node then it only lists the non-AG databases. Works good enough for most circumstances.

DECLARE @SNAME VARCHAR(200)
SET @SNAME = @@SERVERNAME

--Primary and non-mirrored db should be backed up here
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
BEGIN
	SELECT dcs.database_name
	FROM sys.dm_hadr_database_replica_states AS drs
	JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id
	JOIN sys.availability_groups AS g ON g.group_id = drs.group_id
	JOIN sys.dm_hadr_database_replica_cluster_states AS dcs ON dcs.group_database_id = drs.group_database_id AND dcs.replica_id = drs.replica_id
	WHERE r.replica_server_name = @SNAME
	AND   g.automated_backup_preference_desc = 'primary'
	AND drs.database_state_desc = 'ONLINE'
	UNION
	select d.name
	from sys.databases d
	where d.database_id not in(select database_id from sys.dm_hadr_database_replica_states)
END

--Secondary and non-mirrored db should be backed up here
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'SECONDARY')
BEGIN

	SELECT dcs.database_name
	FROM sys.dm_hadr_database_replica_states AS drs
	JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id
	JOIN sys.availability_groups AS g ON g.group_id = drs.group_id
	JOIN sys.dm_hadr_database_replica_cluster_states AS dcs ON dcs.group_database_id = drs.group_database_id AND dcs.replica_id = drs.replica_id
	WHERE r.replica_server_name = @SNAME
	AND   g.automated_backup_preference_desc = 'secondary'
	AND drs.database_state_desc = 'ONLINE'
	UNION
	select d.name
	from sys.databases d
	where d.database_id not in(select database_id from sys.dm_hadr_database_replica_states)
END

Sort SSMS Multi Server Result Set

Hi,

A little known really good SSMS functionality is what is called Registered Servers, for more info on that specifically, look here

What I want to talked about today is the problem when using it against a bunch of servers and wanting to get the result set back ordered. Thing is the servers send back their respective result set when they are done and there is no way in SSMS you can sort it. My problem I had was that I wanted the result sets back in the order of the server name, I was coding a simple script to check the Always-On and Mirroring health against a whole bunch of servers, the output from them unsorted was very cluttered, I wanted the result sets sorted.

What I ended up doing, and you can see it in the script below, is that I converted the server name to UNICODE and took the first number as seconds to wait and the next 3 as milliseconds to wait before returning the result set. The result set I wanted was the health of the database synchronization, I know this should be monitored but sometimes you just want to do a quick check, and the wait part can be used for whatever you like.

It’s so not perfect and may not work in your environment, run it on some test servers first, you may have to adapt the code a bit but it’s quite easy.

A quick hack but it made my day 🙂

USE master
GO

DECLARE @name VARCHAR(20),@sum VARCHAR(5), @ss VARCHAR(2), @ms VARCHAR(3)
DECLARE @position int
DECLARE @delay VARCHAR(12) 
SET @name =  @@SERVERNAME
SET @position = 1

WHILE @position <= LEN(@name)
BEGIN
    SELECT @sum = CONCAT(@sum, UNICODE(SUBSTRING(@name, @position, 1)))
    SELECT @position = @position + 1;  
END
SET @ss = SUBSTRING(@sum,1,1)
SET @ms = SUBSTRING(@sum,2,3)
SET @delay = '00:00:'+@ss+':'+@ms
WAITFOR DELAY @delay

--PRIMARY AG
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
SELECT 'AO Primary' as HA, role_desc, operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc FROM msdb.sys.dm_hadr_availability_replica_states WHERE role_desc = 'PRIMARY'

--SECONDARY AG
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'SECONDARY')
SELECT 'AO Secondary' as HA, role_desc, operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc FROM msdb.sys.dm_hadr_availability_replica_states WHERE role_desc = 'SECONDARY'

--OLD SCHOOL MIRRORING
IF EXISTS(select * from sys.database_mirroring where mirroring_guid is not null)
SELECT 'Mirroring' AS HA, DB_NAME(database_id) AS 'DB name', mirroring_state_desc, mirroring_role_desc from sys.database_mirroring where mirroring_guid is not null

Stop SQL Server backups from cluttering up your SQL Server error log and the Windows event log

If you do not want all the backup database or backup log messages to show up in the Windows event viewer and SQL Server error log, the only thing to do is to add a trace flag to the SQL Server instance start up parameters. It is called trace flag 3226 and can be added by opening the SQL Server Configuration Manager, choosing Properties on the instance and under Startup Parameters add the string: -t3226

Then restart the instance, now the messages should not be logged to event viewer anymore.

See https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?redirectedfrom=MSDN&view=sql-server-2017

Why do this at all you ask? Well one example can be a client of mine that has 2000 databases in an instance, the logs are filled up with messages of successfull database and log backups, can’t see the trees!

“What is currently running in my instance?”

I have a script I turn to first, just to get a quick estimate of what is happening in my SQL Server instance.

It works in most versions 2008+ but you might find something lacking, if so just comment out that part.

The slowest part of the script is getting the graphical sql plan, so if you need real fast results then, yup, comment it out until you need it.

It’s a good script to have around, even better is  sp_blitz from Brent Ozar, you can find it and a lot of other tools here https://www.brentozar.com/blitz/

SELECT 	db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
	s.login_name, r.percent_complete, r.start_time, 
	CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
	,CASE s.transaction_isolation_level
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'ReadUncomitted'
		WHEN 2 THEN 'ReadComitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
	END as 'Isolation level'
	,	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
	r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
	r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
	--Comment out below if you don't need the plan
	, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
WHERE r.session_id > 50 
and r.session_id=s.session_id
and r.session_id <> @@SPID
ORDER BY r.cpu_time desc