July 19, 2010 at 12:38 pm
In SSMS 2008 it has some very nice new features, such as the activity monitor.
However, I can't seem to figure out how to track back a query to the session/spid that initiated it.
Am I missing something blindingly obvious here?
It seems like I should be able to click on the query and see who owns it /ran it/ etc...?
Thanks!
Every now and again we will notice our development server slows down and I would like to be able to pop open SSMS and get a quick look at what may be the culprit.
July 19, 2010 at 1:11 pm
The spid is available in the activity monitor. You should even see the account.
We use SQL Profiler though to see what's going on. Activity Monitor is only for the current information at the type the data was rendered, whereas SQL Profiler allows you to trace on going. You can also query a DMV for the info, but I find that SQL Profiler is much more powerful.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 19, 2010 at 1:26 pm
I don't see the spid or account...
🙁
I go to Recent Expensive Queries, and I see a nice listing of various queries.
The columns I have available are:
----
Query
Executions/min
CPU
Physical Reads
Logical Writes
Logical Reads
Average Duration
Plan Count
Database Name
----
I don't seem to have any way to tie that back to who or what executed said query.
Am I missing some options somewhere?
Processes shows me their session ID, login, etc... but I don't see any way to tie that to "recent expensive queries".
I get that recent queries is a sort of aggregate view (at least I assume it must be), but I expected some way to break it down and see who was running said queries.
July 19, 2010 at 1:27 pm
Usually I grab the SPID from Activity Monitor and then use dbcc inputbuffer to get the offending code!
July 19, 2010 at 1:30 pm
Twinsoft SME (7/19/2010)
Usually I grab the SPID from Activity Monitor and then use dbcc inputbuffer to get the offending code!
But how would I determine who is using the most resources?
It looks like memory use is the only real indicator there? Otherwise it shows me data about the state of a task, or if it is waiting on something, but nothing about resources used other than memory.
Processes just has:
SID, User Processes, Login, Dataase, Task State, Command, Application, Wait Time, Wait Type, Wait Resources, Blocked By, Head Blocker, Memory Use, Host Name, Workload Group.
July 19, 2010 at 1:32 pm
Sorry Maxer that data isn't available in that part of the Activity Monitor. It's available in the Processes report, which is what I thought you meant until I read your post more clearly.
I use SQL Profiler to find expensive queries. Here's some of the things I look at:
ORDER BY Duration DESC
ORDER BY Reads DESC
WHERE Duration > 500
WHERE Reads > 5000
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 19, 2010 at 1:43 pm
Tara Kizer (7/19/2010)
Sorry Maxer that data isn't available in that part of the Activity Monitor. It's available in the Processes report, which is what I thought you meant until I read your post more clearly.I use SQL Profiler to find expensive queries. Here's some of the things I look at:
ORDER BY Duration DESC
ORDER BY Reads DESC
WHERE Duration > 500
WHERE Reads > 5000
Wasn't there a way to view that in SQL 2005? I thought using one of the standard reports, or perhaps it was an additional download of some customized SSRS reports you could tie into SSMS.
Thought they had a sort of click-through report where you could expand out queries or CPU usage by query and then drill down to see who/what executed the query?
Anyway, I take it the best option is to just open profiler like you said, and then watch what queries fly by (filtering by whatever appropriate values may apply to the situation)?
July 19, 2010 at 1:44 pm
You can use the Performance Dashboard report and the DMVs, however I find that SQL Profiler is much more powerful.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 19, 2010 at 1:47 pm
2005 Activity Monitor gave you that data. 2008 Activity Monitor does not. The new Activity Monitor in many ways is much worse than its predecessor.
September 15, 2010 at 8:54 am
Select spid,hostname,program_name,nt_username,loginame,cmd, t.text from sys.sysprocesses
CROSS APPLY( select text from sys.dm_exec_sql_text(sql_handle))t
This query will list all the queries currently executing along with SPID's.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply