THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Who is Active: Default Columns (A Month of Activity Monitoring, Part 7 of 30)

THIS CONTENT HAS BEEN MOVED HERE.

Published Thursday, April 7, 2011 11:00 AM by Adam Machanic

Comments

 

Michael Zilberstein said:

You say that only sysprocesses shows number of open transactions. What about open_transaction_count column in sys.dm_exec_requests? I guess, we'll see the difference only for idle session which keeps open transaction(s) - dm_exec_requests would show nothing while sysprocesses would contain row.

April 11, 2011 7:27 PM
 

Adam Machanic said:

Michael, exactly. sysprocesses will show the value for a sleeping session. And that's really where it's interesting. Imagine that an app starts a bunch of nested transactions, then doesn't commit them all, but is done sending requests. This column is invaluable for debugging in that situation.

April 11, 2011 9:35 PM
 

Michael Zilberstein said:

Adam,

I actually don't have to imagine - two of my clients had exactly this problem when connection from application isn't closed properly after failing on timeout, thus leaving open transaction and locked resources. I don't use your procedure (strictly speaking, I can't according to the license terms - or at least that's questionable) but it was so bad (some of the main tables locked for quite long) that I've build my own monitoring for it 2 years ago that automatically killed stuck sessions and notified application developers. Here is first time I found somebody mentioning this issue:

http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx

(comment from mz1313 is mine)

April 12, 2011 9:57 AM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
 

Guest said:

Hi Adam,

I would have a question please.

You mentioned we should expect to see 0 on request_id almost always for active sessions. However, when I run the sproc, I receive 0,1 and 2 even when they are not asleep. The status is various for all of them (dormant,runnable etc.). I was just wondering what would that mean and whether it might point out to a problem?

Thanks a lot.

September 4, 2014 4:53 AM
 

Adam Machanic said:

Hi Guest,

Sounds like some app in your environment is using MARS. This is not a common feature, which is why I mentioned that request_id would almost always be 0. But it's certainly not indicative of a problem.

--Adam

September 4, 2014 9:40 AM
 

Guest said:

Hi Adam,

Thanks for your time, appreciated.

September 4, 2014 10:38 AM
 

SreeniJ said:

Hi Adam .I was wondering that there is a way to grab plan_handle using whoisactive stored procedure.I have encountered today a sitauation where whoisactive shows issue with execution plan but I cannot map it back to the statement (Adhoc).My plan was to clear existing plan from buffercache.

if we can add this functionality would be helpful in case of adhoc queries or Stored procedure.

February 17, 2015 6:19 PM
 

SreeniJ said:

The execution plans captured using whoisactive are actual execution plans or estimated? I believe they are actual but just making sure not missing important information

February 17, 2015 6:24 PM
 

Adam Machanic said:

@SreeniJ:

A) No, but that is a very common request. I will definitely add it to the next version.

B) They are "estimated" plans in that they don't include actual numbers for row counts, spool metrics, or batches. That's pretty much the only difference between "estimated" and "actual" plans -- the naming is confusing. What you need to know is that the plan you'll see from the requests DMV (which is where the proc sources its data) is what is actually running. So in that case, yes, it is "actual."

--Adam

February 18, 2015 10:22 AM
 

Lucullan said:

I usually have 40 to 100+ of these open_trans on my Database. The status on all of them is sleeping. Right now I have 13 over 10 minutes and 5 almost at 40 minutes. Is there an option where the application may begin a connection again? Is the kill the spid and let the developer know approach right?

Adam this proc is great!

September 9, 2015 10:33 AM
 

Adam Machanic said:

@Lucullan

What connection library is being used here? I'm not aware of anything like that in either Java or .NET but it's a big world outside of those two.

It sounds, on the face of it, like the developers are holding open connections and not properly utilizing connection pooling -- assuming that all of the connections are coming from the same login. If Windows authentication is being used then there could be some decent reasons for holding them open. But rarely are there decent reasons for holding open TRANSACTIONS. I think you need to have a chat with whomever is writing the code.

Glad the proc is helpful :-)

--Adam

September 10, 2015 9:13 AM
 

Muhammad Asam said:

Hi Adam,

x.statement_start_offset/2,

(x.statement_end_offset - x.statement_start_offset)/2

Whats the rational behind divided by 2. How you figured out this number 2?

Thanks

Asam

October 6, 2015 2:23 AM
 

Adam Machanic said:

@Muhammad:

The offsets are output from the DMVs based on number of bytes. And all of the string data is encoded as Unicode -- two bytes. The various string functions work with character positions, not byte offsets. So in order to use those string functions, we must convert byte offsets into character position offsets by dividing by by two.

Make sense? No magic numbers :-)

--Adam

October 6, 2015 11:26 AM
 

Lucullan said:

I've switched companies and the "sleeping" open transactions is over 150. I saw this at my old company and now here?  THE BIGGIE ISSUE is that...I have TWO sessions in the runnable state with 2 open transactions each. I think this is why that report won't run. I show no blocking or deadlocking? Any ideas?

I've upgraded to SQL Server 2014 standard from SQL Server 2008 r2  two weeks ago.

Old company was on SQL Server 2012 standard. Is isolation levels an issue?

December 15, 2015 10:53 AM
 

Adam Machanic said:

@Lucullan

Actually it's typical for a running query to show 2 open transactions, especially if it's doing something involving temp tables. There will be some implicit transactions involved in that processing. The "runnable" state actually means that the query is actively running; queries will go in and out of that state every 4ms or so as the query processor is working.

So is that the reason your report won't run? I guess by "won't run" you mean that it's slow. Sounds like CPU contention if you don't see any interesting waits. How are the procs looking on the box? Have you already made sure that the report is using the correct indexes?

As for the "sleeping" open connections, that is super annoying. Tell the devs to clean up after themselves :-)

--Adam

December 15, 2015 4:57 PM
 

Tom Moore said:

I know the tutorial above states all columns are in 8K pages, but a colleague and I have differing opinions on this. If our disk block sizes are formatted with 64K blocks (as per Microsofts recommendation) doesn't a one in the physical_reads column mean 64K was read from disk?

March 7, 2016 10:56 PM
 

Yad said:

- Why does "tempdb_current" and "blocking_session_id" columns value are sometimes the same ?

- We have seen a lot of ASYNC_NETWORK_IO on wait info and these same wait type is second most shown wait type next to CXPACKET and users are experiencing lots of timeouts.In addition, Can't see any of blocking session ids activity as database transaction. Any idea concerning user timeouts please? I am thinking the application is not committing or rolling back transactions and holding locks on resources.    

May 17, 2016 4:53 PM
 

Adam Machanic said:

@Yad

Those columns are not correlated; so I would assume this would be a simple coincidence. If you're seeing it a LOT, and we're talking random non-zero values, then maybe there's a bug. But I certainly haven't noticed it.

ASYNC_NETWORK_IO means that data is ready to be sent to the application, but the application isn't absorbing it fast enough. (It can also mean that SQL Server is waiting for data from the application, in the case of a stalled bulk load, but that's much rarer.) I would agree with you that this points directly at the application as being the problem.

--Adam

May 17, 2016 10:00 PM
 

@Red said:

Hi Adam,

48,000,000 tempdb_allocations means what is the MB amount used in tempdb.

September 23, 2016 1:04 PM
 

Adam Machanic said:

@Red

48,000,000 * 8192 bytes == 393,216,000 bytes == 375 MB

--Adam

September 26, 2016 1:52 PM
 

Mike said:

Those figures are missing a bunch of zeroes

February 15, 2017 3:10 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Privacy Statement