Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Identifying Resources Hogs (Users) Expand / Collapse
Author
Message
Posted Monday, December 03, 2007 5:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, December 03, 2007 5:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:03 AM
Points: 371, Visits: 407
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
Posted Monday, December 03, 2007 5:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, December 03, 2007 6:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:03 AM
Points: 371, Visits: 407
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
Posted Monday, December 03, 2007 11:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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
Posted Tuesday, December 04, 2007 5:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
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
Posted Tuesday, December 04, 2007 11:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:03 AM
Points: 371, Visits: 407
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
Posted Wednesday, December 05, 2007 10:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:07 PM
Points: 4,128, Visits: 5,840
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
Posted Friday, December 07, 2007 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 10, 2007 11:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695
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 »

Add to briefcase 12»»

Permissions Expand / Collapse