<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Identifying Resources Hogs (Users)
13 posts, Page 1 of 2
1
2
»»
Identifying Resources Hogs (Users)
Rate Topic
Display Mode
Topic Options
Author
Message
montgomery johnson
montgomery johnson
Posted Monday, December 03, 2007 5:20 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 12, 2011 2:58 PM
Points: 30,
Visits: 68
Does anyone have a script that would help me identify users that are resource hogs? I need to know who is a resource when I run my script to check.
Thank you
Post #429067
Prakash Heda
Prakash Heda
Posted Monday, December 03, 2007 5:38 PM
Old Hand
Group: General Forum Members
Last Login: Sunday, February 17, 2013 2:38 AM
Points: 369,
Visits: 392
check master..sysprocesses table , you can filter based on your criteria based on session status /cputime /diskIO / OpenTarn....
is that what u r looking for?
Prakash Heda
Lead DBA Team -
www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Post #429069
montgomery johnson
montgomery johnson
Posted Monday, December 03, 2007 5:45 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, December 12, 2011 2:58 PM
Points: 30,
Visits: 68
When I query master..sysprocesses I only get 1 record.
Post #429070
Prakash Heda
Prakash Heda
Posted Monday, December 03, 2007 6:57 PM
Old Hand
Group: General Forum Members
Last Login: Sunday, February 17, 2013 2:38 AM
Points: 369,
Visits: 392
run sp_who also....
ideally there should be many records......
try running with nolock clause also
still same then post ur queries and sql servre versions details or anything which u think can help debugging
Prakash Heda
Lead DBA Team -
www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Post #429081
GilaMonster
GilaMonster
Posted Monday, December 03, 2007 11:59 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640,
Visits: 29,895
montgomery johnson (12/3/2007)
When I query master..sysprocesses I only get 1 record.
You need view server state permissions to see anything other than your own connection. I assume you're not sysadmin.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #429123
Grant Fritchey
Grant Fritchey
Posted Tuesday, December 04, 2007 5:32 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371,
Visits: 25,143
montgomery johnson (12/3/2007)
Does anyone have a script that would help me identify users that are resource hogs? I need to know who is a resource when I run my script to check.
Thank you
If you're just checking ad hoc through tsql, sp_who is the answer. Otherwise, since you're in 2005, use one of the Standard Reports such as Activity - Top Sessions or Performance -Top Queries by Total CPU Time.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #429226
Prakash Heda
Prakash Heda
Posted Tuesday, December 04, 2007 11:17 AM
Old Hand
Group: General Forum Members
Last Login: Sunday, February 17, 2013 2:38 AM
Points: 369,
Visits: 392
GilaMonster (12/3/2007)
montgomery johnson (12/3/2007)
When I query master..sysprocesses I only get 1 record.
You need view server state permissions to see anything other than your own connection. I assume you're not sysadmin.
this was a good catch....
Prakash Heda
Lead DBA Team -
www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Post #429472
TheSQLGuru
TheSQLGuru
Posted Wednesday, December 05, 2007 10:50 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 3,572,
Visits: 5,106
1) Build your own recording mechanism which inserts records from sysprocesses or one of several DMVs on a scheduled basis and do analysis on that table over time.
2) Set up a profile run to log profiler information to disk on a schedule (watch out for overhead on busy systems). Import disk file to table on (different server?) and analyze.
3) Purchase one of numerous 3rd party packages which track resource utilization.
4) Built in reports as someone else suggested.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #429867
John Kelly-334278
John Kelly-334278
Posted Friday, December 07, 2007 12:25 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, August 27, 2008 8:10 AM
Points: 7,
Visits: 5
Use SQL Profiler for the specified server where Trancount > 10000
Post #430838
colin.Leversuch-Roberts
colin.Leversuch-Roberts
Posted Monday, December 10, 2007 11:03 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:10 PM
Points: 2,668,
Visits: 688
In general terms you can only capture the information after the event, you can of course capture open transactions ( although they might not be doing much ) and you can sometimes capture the details of running processes - the main issue is that values returned are nearly always cumulative so don't really show what the current query is doing, and it's difficult to know if this is a totally new query - and - if you're using client side cursors it's almost impossible. Apparently Oracle has been able to do this since version blah blah blah ... however sql server can only tell you afterwards.
There's a number of white papers written by microsoft which may help. you're unlikley to get anywhere with sysprocesses or sp_who2 ( these are really sql 2000 ) you'll need to understand the dmvs to extract this information. I have a number of queries i use linked to reports etc. I will put all these on my web site in due course but I can't promise if it'll be before christmas. ( www.grumpyolddba.co.uk )
The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #431443
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
