May 21, 2013 at 7:56 am
So it is hard for me to run sp_who3 on these servers because they are in production, and if I make any permanent changes at all, I have to go through an entire Change Managment process, get permission, go before a committee, etc. No, really. It's a nightmare.
But I can run ad-hoc queries. So the sp_who3 that is here I can run just by removing the CREATE PROCEDURE bits http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3. Unfortunatley running that variation of sp_who3 does not answer the question at hand -- it only shows you what is running at the very instant you execute this query, therefore not very useful.
The best-looking variant on sp_who3 is here http://www.sqlservercentral.com/scripts/SP_who3/69906/. Unforunately I don't see any easy way to run this without creating a stored procedure, which I can't do without going to hell and back.
Running sp_who2 gives over 1200 lines. That doesn't seem to be helping me!
Run Profiler, choosing TSQL_SPs for only 5 seconds gives over 100,000 rows. Not sure what to do with that!
May 21, 2013 at 8:05 am
sp_who2 can be run with no problems at all. You shouldn't need to ask anyone for help to run it. The fact you have so many lines returned in such a short time is indicative of some serious activity going on! Have you looked at what is being run during these times of high activity?
May 21, 2013 at 8:05 am
jpSQLDude (5/21/2013)
Right now my CPUs are at 100%... so what is causing this? Someone must have come up with a way to figure this out!
Query sys.dm_exec_requests and see what has high CPU time, bearing in mind that said CPU time is since they logged in. Basically, run it twice a few seconds apart and see which processes have their CPU usage increasing fast.
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
May 21, 2013 at 8:43 am
If your running Win 2K8 Start up Task Manager, go to the Performance Tab, click the Resource Monitor button and have a look around (or you can use PerfMon but the Resource monitor is very easy to use). If you are on Win2k3 download ProcessMonitor to do the same thing.
If I/O is the issue look in the Disk tab you can see what files (DB containers etc) are getting hammered (expand the Disk Activity and click on the Total column to sort by the highest file I/O.
I agree and use WhoisActive myself.
May 21, 2013 at 9:01 am
It sounds to me as if the OP does not have the required permissions to do a lot of things.
That being said they can't use remotely connect to the machine and they will not be able to run Taskmgr and examine Resource Manager.
Their DBA's have permissions should have permissions to remotely connect.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2013 at 4:12 pm
jpSQLDude (5/21/2013)
But I can run ad-hoc queries. So the sp_who3 that is here I can run just by removing the CREATE PROCEDURE bits http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3. Unfortunatley running that variation of sp_who3 does not answer the question at hand -- it only shows you what is running at the very instant you execute this query, therefore not very useful.
I disagree, sp_who3 is very useful to a DBA.
I use it every day, several times a day.
You just need to know when and how to use it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply