March 15, 2011 at 6:03 am
Hi All,
I have this wired problem where I tried to find out which query is taking up the most CPU, there is a query that runs every other day under a particular login but the actual user is not actually logged in, which I find very strange.It is not as SQL Agent job but it is a standard query.
I am really confused about this, would someone please let me know how to resolve this problem.
Thank you
March 15, 2011 at 6:13 am
What do you mean 'the actual user is not logged in'?
Check sysprocesses, it will give login, host name (machine the query is running from) and program name (what app is executing it) that should help you track down where the query is coming from.
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
March 15, 2011 at 6:21 am
Thank you for the reply.
The user that is suppose to have run the particular query is not running anything at the time.
Thank you
March 15, 2011 at 6:29 am
tt-615680 (3/15/2011)
The user that is suppose to have run the particular query is not running anything at the time.
Then someone's using his login.
Did you check the things I mentioned?
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
March 15, 2011 at 7:48 am
SELECT GetDate() as Run_Date,
rtrim(Cast(@@ServerName as varchar(100))) as Server,
spid,
blocked,
waittime,
sb.name,
lastwaittype,
sp.cpu,
sp.login_time,
sp.last_batch,
sp.status,
sp.hostname,
sp.program_name,
sp.cmd,
sp.loginame,
getdate() - last_batch as duration
FROM master..sysprocesses sp
INNER JOIN master..sysdatabases sb ON sp.dbid = sb.dbid
ORDER BY
cpu DESC
then when I found the query that is not suppost to be running, I run the following query to find out what that particular SP id is currently running:
dbcc inputbuffer(82)
March 15, 2011 at 7:57 am
Great. That query gives you the machine name and program name that the query was coming from. That should be enough information to track down the source, though you might need to work with your network people to identify the machine from its name.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply