May 9, 2011 at 8:49 am
Hopefully an easy question from a newbie to sp_WhoIsActive.
Q: I keep getting 0 rows returned? What am I doing wrong? The default columns do appear starting with dd:hh:mm:mss, session_id, etc. I just can't get any real data returned. I'm logged into Studio as sa.
My test box is: SQL 2008 R2 SP1 X64 Standard. The SQL service is working fine.
I've tried: Running sp_WhoIsActive under various SQL load conditions. I've tried various combination of parameters, such as @get_transaction_info=1, @get_plans=1, etc.
Can anyone please give me an example returns anything?
Hints: It would be great to have some basic usage hints to introduce this SP to those of us who are not full-time DBA's. I was overwhelmed trying to read thru the 30 blogs. Maybe a Top 5 usage examples to help get us started.
May 9, 2011 at 11:49 am
Partially answering my own question, for the benefit of the next person. Edit the query, change at least the following two lines from a 0 to a 1: @show_system_spids BIT = 1 and @show_sleeping_spids TINYINT = 1. This will at least return data rows to get you started. I had read in one of the 30 blogs that newbies could simply load the the procedure and press F5 to get a initial peek into the power of the SP. Well that wasn't actually the case, editing of the SP is required.
May 9, 2011 at 11:53 am
John I downloaded the proc and put it on my dev server that i know is fairly busy, and even with those two bits turned on, I couldn't get the results i was looking for
I ran an app that hit's half a dozen tables on startup,a nd could not find a trace of it in the results.
Lowell
May 9, 2011 at 12:16 pm
Adam did a great series about using whoisactive .
If you find any problems contact him directly.
He'll appreciate your feedback and he reacts fairly quick.
I use the (also free) WhoIsActiveUI plugin for ssms available at http://www.schemasolutions.com/WhoIsActiveUI.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 10, 2011 at 2:20 am
funny enough I just posted about this, sort of, on my blog. I never use procs for this type of thing - far too restrictive and not flexible in a support/incident scenario - have a look at some queries you can run instead.
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2011/05/09/dmvs-what-they-can-do-for-you.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 10, 2011 at 6:46 pm
JohnSQLServerCentral (5/9/2011)
Partially answering my own question, for the benefit of the next person. Edit the query, change at least the following two lines from a 0 to a 1: @show_system_spids BIT = 1 and @show_sleeping_spids TINYINT = 1. This will at least return data rows to get you started. I had read in one of the 30 blogs that newbies could simply load the the procedure and press F5 to get a initial peek into the power of the SP. Well that wasn't actually the case, editing of the SP is required.
John, why would you want to see sleeping SPIDs that aren't doing anything? Or system SPIDs, for that matter?
Might want to read this:
By the way, set @show_sleeping_spids = 2 if you want to see ALL of the sleeping SPIDs. 1 is the default value -- show only sessions that are actually doing something, or have open transactions.
And by the way, you don't need to edit the stored procedure to make this change--and you shouldn't. These options are parameters for a good reason. Change them when you make a call if you want different behavior.
--
Adam Machanic
whoisactive
May 10, 2011 at 6:48 pm
Lowell (5/9/2011)
John I downloaded the proc and put it on my dev server that i know is fairly busy, and even with those two bits turned on, I couldn't get the results i was looking forI ran an app that hit's half a dozen tables on startup,a nd could not find a trace of it in the results.
Perhaps you ran the proc a bit too slowly and your app was already finished? Try @show_sleeping_spids = 2 and see if the sessions show up that way.
--
Adam Machanic
whoisactive
May 10, 2011 at 6:51 pm
colin.Leversuch-Roberts (5/10/2011)
funny enough I just posted about this, sort of, on my blog. I never use procs for this type of thing - far too restrictive and not flexible in a support/incident scenario - have a look at some queries you can run instead.http://sqlblogcasts.com/blogs/grumpyolddba/archive/2011/05/09/dmvs-what-they-can-do-for-you.aspx
Colin, have you actually tried Who is Active? It's not the usual sp_who variant. It supports filters, column list inclusion/exclusion/reordering, a sort order option, and various other customizations. Plus it delivers a lot more information than any other sp_who* ever created.
...and in addition it properly handles various situations that your queries don't get quite right, such as correlating active requests with transactions (session_id via sys.dm_tran_session_transactions is not enough -- you also need to use the transaction_id from sys.dm_exec_requests).
--
Adam Machanic
whoisactive
May 10, 2011 at 7:14 pm
Thanks Adam. I was initially just looking for any signs of life from running your sp, i.e. records>0. Sleeping SPIDs were a welcomed sight, when I finally figured how, which most importantly told me I wasn't going crazy. I was testing out your sp on a test server with little or no activity most of the time. I'm sure you can understand that I didn't want to run it on a prod box without some playing in a sandbox first.
As I mentioned I did eventually figure out that i needed to manually modify some of the settings in the top of your sp code. If I where you, I'd default the script to show the sleeping SPIDs. Too much info is better then none at all. At least newcomers like me would see something returned. I spent way too much time thinking that maybe my test box sa account was underprivileged or something else wrong internally.
sp_WhoIsActive is a great Stored Procedure!
And ALZDBA's hint about WhoIsActiveUI plugin was very informative too. Thanks ALZDBA.
Thanks for replying Adam, I really respect your work for the community. Great stuff.
May 10, 2011 at 7:21 pm
I understand that it's a bit confusing at first, but once you get used to it it works much more nicely as a default. After a while you'll no longer want to see all of the useless information.
The solution is going to be shipping the proc with documentation. Now that it's finally mostly written, I just need to find the time to edit it and turn it into a nice PDF. Hopefully that won't take me another four years 😀
Enjoy, and let me know if you have any more questions, comments, feature requests, etc.
--
Adam Machanic
whoisactive
May 11, 2011 at 3:27 am
Hi Adam - no not used your proc and I was in no way suggesting that it shouldn't be used. I was just observing that usually in my scenario I find scripts far more adaptable than the usual sp_who and sp_who2. The examples I blogged are illustrations of what you can do, I use these and others when things become "problematic". It's always difficult to generalise and applications tend to differ, I seriously do have an app where connections ( up to 1000) are all sa and they don't pool very well, in this case the queries I need are different to say another app where I have connections from web servers, ssrs servers and app servers - here I'm likely to be interested in the host for example. Scripts allow me to craft individual queries for individual problems on diverse servers.
For the record your blog is one of my favourites.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 27, 2012 at 3:47 pm
How do you get the UI setup once you've downloaded it?
June 27, 2012 at 11:54 pm
just extract it and run the msi.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 29, 2012 at 6:06 am
Hi,
I executed at development server as below script but result is nothing.
please, Let me know what is the correct parameter using for whoisactive V11.?
use master
go
exec dbo.sp_WhoIsActive
thanks
June 29, 2012 at 7:01 am
Please read this, and let me know if you have any followup questions:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/05/less-data-is-more-data-a-month-of-monitoring-part-5-of-30.aspx"> http://sqlblog.com/blogs/adam_machanic/archive/2011/04/05/less-data-is-more-data-a-month-of-monitoring-part-5-of-30.aspx
--
Adam Machanic
whoisactive
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply