amachanic / sp_whoisactive Goto Github PK
View Code? Open in Web Editor NEWsp_whoisactive
License: GNU General Public License v3.0
sp_whoisactive
License: GNU General Public License v3.0
I installed the newest release of sp_WhoIsActive, but it broke an existing Capture WhoIsActive Data job triggered by the CheckCPUPercent Alert.
I can see that you are querying original_login_name, but it never gets bubbled up to the output (unless I am just missing how to do that)
The sys.dm_exec_connections DMV contains an encrypt_option column. I would like to add that into the output.
We plan to use the sp_WhoIsActive procedure to collect and log activity information from Azure databases across multiple Azure SQL servers into a single table in the administrative database. It would be great if sp_WhoIsActive could return @@ServerName in the output, so that you can determine not only a database where the procedure was executed, but also a server name.
New XML clickable pulling info from dm_exec_query_memory_grants
, where available.
Hi Adam,
I recently noticed that sp_whoisactive seems not displaying the real value of [Percent_Complete] for a restore session.
But if I run my own query
select session_id, command, percent_complete
from sys.dm_exec_requests
where command like 'restore%',
I can observe [percent_complete] has values keeping changing my environment. I am using SQL Server 2016 SP2+CU7 13.0.5366.0 (X64)
My sp_whoisactive is ver 11.33.
Do you know why?
Thanks a lot Adam for your great sp_whoisactive.
Kind regards,
Jeffrey Yao
would it be possible to have job_name & step_name in new columns instead of having it buried in the additional_info? Maybe with a new parameter? The program_name for SQLAgent jobs is pretty useless when trying to find what job is actually running, and it would be nice to have that broken out.
On servers using the JDBC driver and other smelly unfortunates, implicit transactions can be quite an annoying problem. I wrote code into sp_BlitzWho to identify them, and I'd love to get it in sp_WhoIsActive so I don't need to use another proc for it.
It looks something like this:
SELECT
des.session_id,
CASE
WHEN
EXISTS
(
SELECT
1/0
FROM sys.dm_tran_active_transactions AS tat
JOIN sys.dm_tran_session_transactions AS tst
ON tst.transaction_id = tat.transaction_id
WHERE tat.name = 'implicit_transaction'
AND des.session_id = tst.session_id
) THEN 1
ELSE 0
END
FROM sys.dm_exec_sessions AS des;
While working on some Great Posts™, I found a bit of odd behavior:
It appears to be a case sensitivity issue, because this works:
EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] desc', @show_own_spid = 1;
Easily fixed by doing this:
WHEN LOWER(tokens.next_chunk) LIKE '%asc%' THEN ' ASC'
WHEN LOWER(tokens.next_chunk) LIKE '%desc%' THEN ' DESC'
PR incoming!
Would be great to have the docs in this repo too.
Maybe I can start with a PR?
I've gone through and updated the SP with the Synapse DMVs, but I keep on getting this error:
Msg 103010, Level 16, State 1, Line 12
Parse error at line: 528, column: 36: Incorrect syntax near 'r'.
I've attached my work in progress because I couldn't open a pull request.
Thank you
The below paramenters doesn't work correct. Procedure simply retrives all rows even if no blocking.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_WhoIsActive')
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@get_plans = 1,
@sort_order = '[blocked_session_count] DESC'
GO
We've got following error when execute sp_WhoIsActive:
Executed as user: *******\sqlservice. Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153) The target database, '*****', is participating in an availability group and is
currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled
for read access. To allow read-only access to this and other databases in the availability group, enable read access
to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY
GROUP statement in SQL Server Books Online. [SQLSTATE 42000] (Error 976, Level 14, State 1, Line 38)
Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Error 8625).
The step failed.
Version: Who Is Active? v11.35 (2020-10-04)
Additional context:
This error occurs when we try to additionally use Telegraf plugin for monitoring SQL Server.
We have Always On Availability Groups.
This error occurs on healthy cluster.
My idea that Telegraf
exec some queries on not available db and sp_WhoIsActive
tries to get additional information about this query and fails.
Could you assist us to solve this issue? Any ideas?
Thank you!
Hi Adam, when running the script on Azure SQL Data Warehouse it runs into
Msg 103010, Level 16, State 1, Line 12
Parse error at line: 531, column: 36: Incorrect syntax near 'r'.
Is sp_whoisactive SQL Server only?
Thank you.
I'm executing the store procedure periodically and writing it to a table in tempdb using:
EXEC tempdb.dbo.sp_WhoIsActive @get_plans = 1, @get_task_info = 2, @get_locks = 1,
@get_full_inner_text = 1, @get_transaction_info = 1, @format_output = 0,
@DESTINATION_TABLE = 'tempdb.dbo.whoisactive_table'
I've recently started getting this error about once an hour:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
A google search seems to indicate I should add a try/catch around the sp and then rollback if there is an error... is that something I should be expected to do when executing this? Or is there something else that might be going on?
For reference I am on SQL Server 2017 Standard Edition14.0.3281.6 (X64) on AWS RDS.
While not available through sys.sysprocesses
, original_login_name
is a very handy column e.g. in sys.dm_exec_sessions
(or sys.dm_pdw_nodes_exec_sessions
in warehouse). We use application roles frequently and some legacy code uses EXECUTE AS
with dynamic SQL. Especially in the later case original_login_name
offers invaluable information.
Observed on SQL Server 2017:
Seems to be a DMV bug, but is there some workaround?
Msg 1934, Level 16, State 1, Line 463
SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Hello, Adam!
Thank you for inviting me to visit your blog.
I've been using this procedure since 2017. I think that it's very useful for DB admin's. Also I use your DOC's for sp_WhoIsActive.
Good luck in project's development!
I have the lastest version of sp 11.33.
I am runing the proc on SQL Server 2019 CU4 Enterprise version.
I am executing proc with the following parameters:
EXEC DBATools.dbo.sp_WhoIsActive @get_plans = 1 , @get_outer_command = 1
If I run a trace on the server while executing sp_whoisactive, I see a number of errors being produced by a single call to the proc.
Invalid object name '#sessions'.
Invalid object name '@buffer_results'.
Invalid object name '#locks'.
Invalid object name '#blocked_requests'.
Hi All
Hope someone is able to provide some insight and potential resolution for a problem we're experiencing
This problem has showed up on three servers now. Each occurrence has started with an exception being generated by the SPID running sp_WhoIsActive. There isn't any specific timeline (runtime, time of day, etc...) and the workloads on each of the servers is very different. On two occasions the SQL Server process was terminated, and another the service had to be manually restarted as it was in a CPU spin. Other occasions have left the server generally unresponsive until the offending SPID was killed off.
Running
sp_WhoIsActive is being run in a SQL Agent job to capture any blocking activity occurring on the server. The job runs every minute and has two steps, 1) Capture sp_WhoIsActive data, 2) Analyse and raise an alert if blocking is detected.
The command to capture the data is as follows,
EXEC [dbo].[sp_WhoIsActive]
@get_additional_info = 1
, @get_full_inner_text = 1
, @get_plans = 2
, @find_block_leaders = 1
, @output_column_list = '[collection_time][session_id][blocking_session_id][open_tran_count][status][dd hh:mm:ss.mss][start_time][login_time][login_name][database_name][host_name][program_name][sql_text][query_plan][wait_info][reads][writes][CPU][additional_info]'
, @DESTINATION_TABLE = 'dbo.workBlockingCapture';
This is the structure of the workBlockingCapture table,
CREATE TABLE [dbo].[workBlockingCapture](
[collection_time] [datetime] NOT NULL,
[session_id] [smallint] NOT NULL,
[blocking_session_id] [smallint] NULL,
[open_tran_count] [varchar](30) NULL,
[status] [varchar](30) NOT NULL,
[dd hh:mm:ss.mss] [varchar](8000) NULL,
[start_time] [datetime] NOT NULL,
[login_time] [datetime] NULL,
[login_name] [nvarchar](128) NOT NULL,
[database_name] [nvarchar](128) NULL,
[host_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[sql_text] [xml] NULL,
[query_plan] [xml] NULL,
[wait_info] [nvarchar](4000) NULL,
[reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[CPU] [varchar](30) NULL,
[additional_info] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The following the latest exception event that occurred,
2021-08-15 12:41:00.41 spid62 CImageHelper::Init () Version-specific dbghelp.dll is not used
2021-08-15 12:41:00.43 spid62 Using 'dbghelp.dll' version '4.0.5'
2021-08-15 12:41:02.17 spid62 ***Stack Dump being sent to D:\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump10000.txt
2021-08-15 12:41:02.18 spid62 SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2021-08-15 12:41:02.18 spid62 * *******************************************************************************
2021-08-15 12:41:02.18 spid62 *
2021-08-15 12:41:02.18 spid62 * BEGIN STACK DUMP:
2021-08-15 12:41:02.18 spid62 * 08/15/21 12:41:02 spid 62
2021-08-15 12:41:02.18 spid62 *
2021-08-15 12:41:02.18 spid62 *
2021-08-15 12:41:02.18 spid62 * Exception Address = 00007FF82A47FCBD Module(sqllang+000000000003FCBD)
2021-08-15 12:41:02.18 spid62 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2021-08-15 12:41:02.18 spid62 * Access Violation occurred reading address 0000021EDB971288
2021-08-15 12:41:02.19 spid62 * Input Buffer 510 bytes -
2021-08-15 12:41:02.20 spid62 * EXEC [dbo].[sp_WhoIsActive] @get_additional_info = 1 , @g
2021-08-15 12:41:02.20 spid62 * et_full_inner_text = 1 , @get_plans = 2 , @find_block_leaders = 1
2021-08-15 12:41:02.20 spid62 * , @output_column_list = '[collection_time][session_id][blocking_session
2021-08-15 12:41:02.20 spid62 * _id][open_tran_count][status][dd hh:mm:ss.mss][start_time][login_time][l
2021-08-15 12:41:02.20 spid62 * ogin_name][database_name][host_name][program_name][sql_text][query_plan]
2021-08-15 12:41:02.20 spid62 * [wait_info][reads][writes][CPU][additional_info]' , @DESTINATION_TABL
2021-08-15 12:41:02.20 spid62 * E = 'dbo.workBlockingCapture';
If any further details are required, I'm happy to provide what I'm able to.
Cheers
Phil
The current version of sp_whoisactive
reports wait duration as part of the text value in the wait_info
column, which is human-oriented and unamenable to sorting, filtering, or any other automated, machine-based analysis. This is a request for a dedicated numeric column to store this important metric—wait_duration
or wait_duration_ms
(for milliseconds)—that shall be usable in MSSQL predicates and operators and shall facilitate quantitative lock analysis and automated gathering of wait statistics.
Whereas the numeric value of lock duration is already used internally inside sp_whoisactive
, this request should not be hard to implement.
We are have scheduled automatic capture job using sp_whoisactive and storing result into table. and this action is filling up default trace with below events. This job is scheduled to run every one minute.
Missing Join Predicate
Object:Created
Missing Column Statistics
NO STATS:([s1].[recursion], [s2].[recursion])
NO STATS:([#sessions].[recursion])
NO STATS:([s1].[recursion])
Below code stores sp_WhoIsActive output into table.
DECLARE
@destination_table VARCHAR(4000) ,
@msg NVARCHAR(1000) ;
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;
DECLARE @numberOfRuns INT ;
SET @numberOfRuns = 10 ;
WHILE @numberOfRuns > 0
BEGIN;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,@get_plans = 1,
@destination_table = @destination_table ;
SET @numberOfRuns = @numberOfRuns - 1 ;
IF @numberOfRuns > 0
BEGIN
SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' +
'Logged info. Waiting...'
RAISERROR(@msg,0,0) WITH nowait ;
WAITFOR DELAY '00:00:05'
END
ELSE
BEGIN
SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' + 'Done.'
RAISERROR(@msg,0,0) WITH nowait ;
END
END ;
GO
For connections made from other MS services using managed identities, the login_name is two guids separated by an @. The first is the guid of the application ID , and the second is the guid of the tenant ID. Would be cool to pull the name of the managed identity instead. I'm not sure how to get the tenant id, but to get the application ID from converting the sid from sys.database_principals:
DECLARE @tenantID uniqueidentifier = '<guid>' SELECT Name, LOWER(CONCAT(CAST([sid] AS uniqueidentifier), '@', @tenantID)) AS GUID FROM sys.database_principals WHERE type = 'E'
We have a few servers that are used as multi-tenant instances and we setup resource governor workload groups, etc. It would be nice to see Workload Group as a column like you can in the Activity Monitor...mostly so we can ensure that the session is being classified properly and is using the correct amount of resources.
I ran sp_whoisactive on a read-only secondary replica in an availability group but no results were returned. I double checked with sp_who2 and there are 100 rows with spids over 50.
Is there a parameter I need to set to get results this way or something else I should look at?
using v12.00
I am trying to persist data into a temp table and it is working properly, except for blocked_session_count. It appears that this value always shows 0 when outputting into a temp table.
I also noticed that when using the @return_schema = 1 parameter it seems to just skip over this column in the table create, which is when I tried to add it manually and noticed it was always 0.
EXEC sp_WhoIsActive
@destination_table = '#temp',
@Find_Block_leaders = 1,
@output_column_list = '[session_id][login_name][blocked_session_count][reads][writes][database_name][program_name][start_time]',
@sort_order = '[blocked_session_count] DESC'
So it seems like this is not possible, but please correct me if I am wrong as I would really like to capture this value.
DETAILS:
ISSUE:
Hello Adam. My name is Bradley Biera, a database administrator for the software company Infor. We have been running in a recent issue with sp_WhoIsActive where it does not successfully collect and return results. This is due to:
Error 6841, Severity 16
FOR XML could not serialize the data for node 'Lock/@resource_description' because it contains a character (0x0001) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
We have found that by not using the @get_locks=1 parameter, sp_WhoIsActive would then return. The issue must be coming from the locks column. Here is the SQL we are running:
EXEC CODBAProcedures.dbo.sp_WhoIsActive
@get_full_inner_text = 1,
@get_plans = 2,
@get_outer_command = 1,
@get_task_info = 2,
@get_locks = 1,
@get_additional_info = 1,
@delta_interval = 10,
@destination_table = 'CODBALogs.dbo.monitor_WhoIsActive'
This is indicating that CHAR(1) is being picked up in the locks and the XML is having an issue converting. Is there a way around this? I would strongly assume you already use plenty of REPLACE() functions, as I see them sprinkled through your code. How could we get this fixed? Is it possible you could fix it?
Thank you for your time,
Iv downloaded the latest, and the previous version. Both are giving the same error:
Msg 9455, Level 16, State 1, Procedure ddl_objects_log, Line 31 [Batch Start Line 14]
XML parsing: line 5152, character 32, illegal qualified name character
database:
--SELECT @@Version as Ver
Ver
Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 12 2019 22:46:48 Copyright (C) 2019 Microsoft Corporation
--SELECT SERVERPROPERTY('EngineEdition') as 'Azure_EngineEdition'
Azure_EngineEdition
5
I was wondering if we can get the duration in seconds (for easy filtering or comparison) instead of dd hh:mm:ss.mss e.g. 00 00:00:26.250
It is the elapsed_time that I am after I think. Is it a glitch that it is returned for the evarage but not the normal column?
Formatted: [dd hh:mm:ss.mss] varchar NULL
Non-Formatted:
For an active request, time the query has been running
For a sleeping session, time since the last batch completed
Formatted: [dd hh:mm:ss.mss (avg)] varchar NULL
Non-Formatted: [avg_elapsed_time] [int] NULL
(Requires @get_avg_time option)
How much time has the active portion of the query taken in the past, on average?
Running the creation script on the new Azure Synapse (aka Data Warehouse) gets
Msg 103010, Level 16, State 1, Line 12 Parse error at line: 528, column: 36: Incorrect syntax near 'r'.
Running
SELECT * FROM sys.dm_exec_requests
gets
Msg 104385, Level 16, State 1, Line 1 Catalog view 'dm_exec_requests' is not supported in this version.
@@Version = Microsoft Azure SQL Data Warehouse - 10.0.10665.0 Oct 6 2019 00:45:12 Copyright (c) Microsoft Corporation
Hi @amachanic ,
I have deployed sp_WhoIsActive (v11.35 (2020-10-04)) baselining with below combinations of parameters in my environment. Most of the time it is successful. But once in a while, below statement fails with following error message -
Arithmetic overflow error converting expression to data type int
EXEC dbo.sp_WhoIsActive @get_outer_command=1, @get_task_info=2, @find_block_leaders=1, @get_plans=1, @get_avg_time=1, @get_additional_info=1, @delta_interval = 10
,@output_column_list = @output_column_list
,@destination_table = @staging_table;
Attaching the @destination_table definition I have.
CREATE TABLE [dbo].[who_is_active_staging](
[collection_time] [datetime] NOT NULL,
[dd hh:mm:ss.mss] [varchar](8000) NULL,
[session_id] [smallint] NOT NULL,
[program_name] [nvarchar](128) NULL,
[login_name] [nvarchar](128) NOT NULL,
[database_name] [nvarchar](128) NULL,
[CPU] [varchar](30) NULL,
[CPU_delta] [varchar](30) NULL,
[used_memory] [varchar](30) NULL,
[used_memory_delta] [varchar](30) NULL,
[open_tran_count] [varchar](30) NULL,
[status] [varchar](30) NOT NULL,
[wait_info] [nvarchar](4000) NULL,
[sql_command] [xml] NULL,
[blocked_session_count] [varchar](30) NULL,
[blocking_session_id] [smallint] NULL,
[sql_text] [xml] NULL,
[dd hh:mm:ss.mss (avg)] [varchar](15) NULL,
[physical_io] [varchar](30) NULL,
[reads] [varchar](30) NULL,
[physical_reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[tempdb_allocations] [varchar](30) NULL,
[tempdb_current] [varchar](30) NULL,
[context_switches] [varchar](30) NULL,
[physical_io_delta] [varchar](30) NULL,
[reads_delta] [varchar](30) NULL,
[physical_reads_delta] [varchar](30) NULL,
[writes_delta] [varchar](30) NULL,
[tempdb_allocations_delta] [varchar](30) NULL,
[tempdb_current_delta] [varchar](30) NULL,
[context_switches_delta] [varchar](30) NULL,
[tasks] [varchar](30) NULL,
[query_plan] [xml] NULL,
[percent_complete] [varchar](30) NULL,
[host_name] [nvarchar](128) NULL,
[additional_info] [xml] NULL,
[start_time] [datetime] NOT NULL,
[login_time] [datetime] NULL,
[request_id] [int] NULL
)
GO
Please let me know if I missed anything here. I created above table with CREATE TABLE statement generated by stored procedure itself.
Hey Adam - It looks like we have a typo on line 5130. It says blocked_session_count
instead of percent_complete
?
sp_whoisactive/who_is_active.sql
Line 5130 in f2ef875
Thanks.
At least since ADS's latest stable release, each launch results in this warning popping up:
Some of the loaded extensions are using obsolete APIs, please find the detailed information in the Console tab of Developer Tools window
Developer Tools's Console tab details:
The extension "Microsoft.whoisactive" is using sqlops module which has been replaced by azdata module, the sqlops module will be removed in a future release.
console.(anonymous function) @ c:\Program Files\Azure Data Studio - Insiders\resources\app\node_modules.asar\zone.js\dist\zone-node.js:2280
Is this really a bug within your extension (not sp_WhoIsActive.sql
, but the "stuff" around it) or should I open an issue over at microsoft/azuredatastudio/issues?
An upcoming change in Azure SQL and future versions of SQL Server will change the format of the waitresource column in sysprocesses and several DMVs to include additional latch diagnostics for all latch waits. For example, instead of 13:1:24323201
for a PAGEIOLATCH_EX wait, we will see 13:1:24323201 (LATCH 0x0000020114CC5FD8: CLASS: BUF_LATCH KP: 0 SH: 0 UP: 0 EX: 1 DT: 0 Sublatch: 0 HasWaiters: 1 Task: 0x000001FDF5F6FC28 AnyReleasor: 1)
.
On MSSQL builds with this change, sp_whoisactive v11.35 fails with Msg 245, Level 16, State 1, Line 331 Conversion failed when converting the nvarchar value ' 1)' to data type int.
A crude but straightforward fix for this would be to replace RTRIM(sp2.waitresource)
on lines
sp_whoisactive/who_is_active.sql
Line 1993 in 459d2bc
sp_whoisactive/who_is_active.sql
Line 2074 in 459d2bc
CASE WHEN CHARINDEX('' (LATCH '', sp2.waitresource) > 0 THEN LEFT(sp2.waitresource, CHARINDEX('' (LATCH '', sp2.waitresource) - 1) ELSE RTRIM(sp2.waitresource) END
.
A separate future improvement could make use of the new latch info.
If the proposed fix is acceptable, I can send a PR.
sys.sysprocesses is deprecated and so it is causing "complaints" in our database project as it cannot resolve the reference: SQL71502
I am planning to write a parser to handle what is currently explained in the documentation at:
http://whoisactive.com/docs/16_morewaits/
Depending on how many tasks are waiting for each wait type, Who is Active breaks out the times as follows:
- One waiting task: (1x: MINms)[wait_type] where MINms is the number of milliseconds that the task has been waiting
- Two waiting tasks: (2x: MINms/MAXms)[wait_type] where MINms is the shorter wait duration between the two tasks, and MAXms is the longer wait duration between the two tasks
- Three or more waiting tasks: (Nx: MINms/AVGms/MAXms)[wait_type] where Nx is the number of tasks, MINms is the shortest wait duration of the tasks, AVGms is the average wait duration of the tasks, and MAXms is the longest wait duration of the tasks
I propose instead of returning wait_info as a string, we return wait_info as an XML object, something like:
<wait_info type="OneWaitingTask">
<task wait_type="ASYNC_NETWORK_IO" milliseconds="MINms" />
</wait_info>
<wait_info type="TwoWaitingTasks">
<taskSummary wait_type="ASYNC_NETWORK_IO" minMilliseconds="MINms" maxMilliseconds="MAXms" />
</wait_info>
<wait_info type="ThreeOrMoreWaitingTasks">
<taskSummary wait_type="ASYNC_NETWORK_IO" minMilliseconds="MINms" avgMilliseconds="AVGms" maxMilliseconds="MAXms" />
</wait_info>
Also... It is not clear to me what Two waiting tasks does when the wait_types are different (I think this is possible but likely a ultra rare scenario and thus has never been reported as a bug). I think this is probably a documentation descriptive issue more than a logic issue, but I plan to dive into the logic to see if it's also logically buggy.
We are seeing below error periodically happen since we upgraded to SQL Server 2017.
Warning: The join order has been enforced because a local join hint is used.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type money.
Warning: Null value is eliminated by an aggregate or other SET operation.
Just wondering the possibility of returning the job name in the top level column [program_name] instead of its [job_id] in hex?
At the top level its nice to see at a glance what jobs are colliding without having to dig down into the additional_info XML. I find myself toggling back and forth often which can get cumbersome when there are a lot of jobs involved in a block chain.
Hi, Adam. Great thanks to move in open source club your awesome stored procedure. Could you rename who_is_active.sql
to dbo.sp_WhoIsActive.sql
(prefered for me ) or just sp_WhoIsActive.sql
?
As discussed via email! PR is ready.
Dear Developer,
Thank you for this project.
Although, the procedure might return trimmed query text sometimes.
Please, check StackOverflow post for more information.
Regards
Team,
I am had Who Is Active of Version v11.17 (2016-10-18) and while i tried to plan for Migration and went with DMA got the error as Unqualified Join(s) detected.
Impact shown as
Starting with database compatibility level 90 and higher, in rare occasions, the 'unqualified join' syntax can cause 'missing join predicate' warnings, leading to long running queries.
What could be the solution for this ?
Hey Guys!
Look at this case using sp__whoisactive v11.34 (happens v11.00 also)
I guess the numbers showing in reads and CPU are very high... This environment is not able to read 200+ millions of pages in 180ms... 1.5TB in less than 1 second...
This seems a problem in whoisactive when multiple requests run in the same session very fast (complete in some milliseconds).
The environment is a Hyper-V with Windows Server 2019
The Azure DMA tool flags some of the cross joins used in sp_whoisactive as invalid.
Unqualified Join Detected
Object [dbo].[sp_WhoIsActive] uses the old-style join syntax which can have poor performance at database compatibility level 90 and higher.
The comma-style cross joins should be changed to use the CROSS JOIN syntax in order to eliminate this report.
Work based off of where spaces are rather than hard-coded lengths. PR ready.
Please add context_info from sys.dm_exec_requests to the output.
Hey Adam,
When using sp_WhoIsActive @Get_outer_command = 1
, I am wondering if it is possible to include the parameter values in the returned text assuming there are any. I know that sp_WhoIsActive @Get_plans = 1
returns the plan XML, which allows you to open the plan and right-click properties on the top-leftmost node to grab the parameter list:
Current result of Sql_Command:
Desired result of Sql_Command:
If this is something you think is possible and worth adding I'd be happy to try and contribute.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.