Jeff Moden (1/20/2014)
smitty-1088185 (1/20/2014)
There is so many variables to your question I'm not sure where to start. How do you know it's SQL verses some other process?How much available memory?
How much paging?
What are the processors doing?
Is there any blocking going on?
Are you running any Indexing maintenance jobs
etc, etc, etc......
You are in a bad situation my friend.
That's why sp_WhoIsActive is such a valuable tool. It shows a lot of that in a nice, tight result set.
it was just a hypothetical question to find out what other people are doing.
If you need to know, lets imagine that some advanced users have access to run queries via SSMS and one nasty analyst has a query that is known to be awful due to select * from super massive table, bad indexing, terrible coding practices etc etc.
So someone on the team a while would see the username and know the story, but someone new might not, but a head DBA casually says, "its just a rogue query", can you identify it.