December 3, 2007 at 5:20 pm
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
December 3, 2007 at 5:38 pm
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
December 3, 2007 at 5:45 pm
When I query master..sysprocesses I only get 1 record.
December 3, 2007 at 6:57 pm
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
December 3, 2007 at 11:59 pm
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
December 4, 2007 at 5:32 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2007 at 11:17 am
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
December 5, 2007 at 10:50 am
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
December 7, 2007 at 12:25 pm
Use SQL Profiler for the specified server where Trancount > 10000
December 10, 2007 at 11:03 am
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. ( http://www.grumpyolddba.co.uk )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 10, 2007 at 12:28 pm
Nice site name colin! 😀
I note, however, that your links on the 2005 page end in .mht for URL as opposed to htm. I tried to hit the pages by changing to htm with no luck. Various mangles didn't work either. Some other links are mht too. Perhaps you just don't want anyone to access stuff yet?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2007 at 3:22 am
some of the docs are quite large and the mht files avoid having lots of sub folders, although they do take longer to load. Not all the 2005 pages are there yet , parts 3 5 and 6 of analysing indexes have yet to be put up to the site - bit confusing I guess - but I'm working on it.
All my work is in word so I had to find the easiest way to convert to web pages, I may drop the mht and revert to htm - bear with me. without doubt word 2007 seems to be better at publishing but stuff still gets moved about so it's a long process! ( web site design isn't one of my strong points as you can probably guess )
web site links to my blog - the link to the grumpy old is probably more relevant to UK unless grumpy old men and grumpy old women have made it worldwide.
cheers
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 11, 2007 at 3:26 am
I would suggest the deployment of the performance dashboard if you're running sp2, if you're not then I also suggest you get sp2 and at least 3186 rollup.
There's also dmv stats warehouse available, although you need to understand what you're looking at if you deploy this - both these tools are free, which is good, and they're both pretty useful.
You don't need SSRS for the performance dashboard and you could run dmvstats without with a few changes to the code.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply