August 31, 2009 at 1:57 pm
I am trying to check user activity for a particualr user from activity monitor, I see application as " Micorsoft SQL Server " so that means it is not a MSMS query and the database is seen as " master" .
this user is using too much CPU but i cudnt find what databases he is querying, how can i find that.
I tried sql profiler filetring on this user but i dont see any records. How can i track this guy.
thanks
August 31, 2009 at 2:17 pm
If you know the SPID of that particular user then use DBCC INPUTBUFFER or fn_get_sql as follows:-
------------------------------------------------
DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid =
SELECT text FROM ::fn_get_sql(@handle)
-------------------------------------------------
to get the text of the query, and then by that text u can know the database. How many user databases are there on that server?
August 31, 2009 at 2:49 pm
I am getting blank.
August 31, 2009 at 2:53 pm
I think if the application is MSMS Query then i can get some text result for the above query but for this user ther application used says " Microsoft SQL Server" what does it mean?
August 31, 2009 at 3:11 pm
Tara,
Is the SPID getting changed?
Is the SPID value is less than 50?
What is the Version of your SQL Server?
Thanks,
Raj
August 31, 2009 at 4:36 pm
Does that particular user have any other processes running?
I have seen in the past where a user was connected via SSMS and the Application showed as Microsoft SQL Server. The user ended up with a couple of spids, 1 in master and 1 in the user database. When the user disconnected and logged off - the user database spid went away but the spid in master remained.
What is the last batch time stamp of that user? How long ago did the user login to the db? Can you go and ask the user, or is it a service account or generic account?
I like to use the following query in conjunction with sp_who2 to find a little more detail on the running queries.
select percent_complete, estimated_completion_time, cpu_time/60000 as CPUMinutes, total_elapsed_time/60000 as TotalMinutes,session_id
,start_time,command, st.*,sql_handle,plan_handle,database_id,wait_time,last_wait_type
,wait_resource,transaction_id,reads,writes,logical_reads
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) AS st
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 31, 2009 at 5:48 pm
Thanks Mayank, JAson for all your response..
I have noticed one more thinj..
(1) While executing the query as:: DBCC inputbuffer(77)--SPID,
I'm getting one result.
---------------------------------
Output::
Exec ImportComponents '459087',''
(2) DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 77
SELECT text FROM ::fn_get_sql(@handle)
Executing the above query, getting other result.
--------------
Output::
UPDATE Material WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917' , LastModifiedBy = 459087 , ChangeID = 2151042 FROM Material WHERE MaterialID = 1466288 UPDATE MaterialComponentDetail WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917', LastModifiedBy = 459087 , ReplacedByPartNumber = 'X15-05816-02' FROM MaterialComponentDetail WHERE MaterialID = 1466288
Can you please suggest why is this so??
Regards
Sourav
Thanks.
September 1, 2009 at 5:53 am
Better than sp_who2, take a look at the DMV sys.dm_exec_requests. That will show you currently running queries. You can combine it with other DMV/DMF to arrive at the query being run, the execution plan, statistics, locks, etc. It's so much more elegant than using sp_who2.
"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
September 1, 2009 at 7:13 am
Tara (8/31/2009)
I am getting blank.
If you are getting blank using fn_get_sql then probably your query has zero cost plan, hence not stored in cache. If you turn on flag 2861(By default its off) then SQL text with zero cost plan will be stored in ur memory and hence fn_get_sql will print something. But after you are done, I think u should turn off this flag.
September 1, 2009 at 7:19 am
Sourav (8/31/2009)
Thanks Mayank, JAson for all your response..I have noticed one more thinj..
(1) While executing the query as:: DBCC inputbuffer(77)--SPID,
I'm getting one result.
---------------------------------
Output::
Exec ImportComponents '459087',''
(2) DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 77
SELECT text FROM ::fn_get_sql(@handle)
Executing the above query, getting other result.
--------------
Output::
UPDATE Material WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917' , LastModifiedBy = 459087 , ChangeID = 2151042 FROM Material WHERE MaterialID = 1466288 UPDATE MaterialComponentDetail WITH ( ROWLOCK ) SET LastModifiedDate = '2009-08-31 12:07:19.917', LastModifiedBy = 459087 , ReplacedByPartNumber = 'X15-05816-02' FROM MaterialComponentDetail WHERE MaterialID = 1466288
Can you please suggest why is this so??
Regards
Sourav
Either your ImportComponents '459087' stored procedure is equivalent of the UPDATE script shown by running fn_get_sql or your SPID was changed when you ran the first INPUTBUFFER and the second fn_get_sql(@handle)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply