SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifying Resources Hogs (Users)


Identifying Resources Hogs (Users)

Author
Message
montgomery johnson
montgomery johnson
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 73
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
Prakash Heda
Prakash Heda
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 416
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
montgomery johnson
montgomery johnson
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 73
When I query master..sysprocesses I only get 1 record.
Prakash Heda
Prakash Heda
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 416
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86484 Visits: 45239
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, MVP, M.Sc (Comp Sci)
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


Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39273 Visits: 32617
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Prakash Heda
Prakash Heda
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 416
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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12282 Visits: 8542
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 on googles mail service
John Kelly-334278
John Kelly-334278
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 5
Use SQL Profiler for the specified server where Trancount > 10000
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4609 Visits: 715
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search