SQL Server Performance

Identify Object Being Accessed

Discussion in 'SQL Server 2005 General DBA Questions' started by sql_jr, May 1, 2007.

  1. sql_jr New Member

    hi,

    I was hoping to find a way to capture/identify the object accessed that correlates to a particular spid.(sysprocesses)

    I know there are some new dynamic views in SQL2K5, but also need a method for 2000. (Please help w/both)

    For example, if I see a particular spid, I want to know what object it is accessing. Does this info exist in any system table or function? I know I can do a dbcc inputbuffer to get partial sql, but wanted to get the object name its running against (No profiler please) THX!!
  2. ndinakar Member

    do a DBCC INPUTBUFFER(spid) on the server when you see the spid. the spids change pretty quickly so you should be pretty fast. The DBCC INPUTBUFFER will tell you what the spid was doing. It will give you the T-SQL or the proc that was being executed. From there on you need to figure out what objects are being used.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. sql_jr New Member

    I appreciate the reply, but I wanted to find a way to do this programatically.
    I know I can get the spid and the sql info, but wanted to get the object id, so if for ex, there's an 'update table mytable' statement, somewhere there is a corresponding objectid in a sys table or view. Any suggestions? tx
  4. ndinakar Member

    no the information is not stored anywhere. you'd have to write queries to find it. You can get the objectid for a table as SELECT Object_id('mytable').

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  5. sql_jr New Member

    is there a way to join this info? That's still kind of manual. 1.sp_who 2.dbcc inputbuffer(spid) 3.SELECT Object_id('mytable').
    Anyway to automate this? Nothing that says an object is being accessed or last access date -something like that? Tx,again!
  6. ndinakar Member

    you'd have to be saving the information to some table constantly and then query against the table. the spid could be running a proc, or a TSQL or a begin tran or a rollback or a delete. There's no generic way to find the objectid direectly. you'd have to write a custom proc to run the trace with the events you want. If all your application access is through procs then perhaps you might have some luck since you can make some assumptions. If you have adhoc SQL queries coming in, then it makes it more difficult.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  7. sql_jr New Member

    I appreciate keeping up w/the thread. So, assuming I follow your suggestion, (saving and querying) what would the logic be?
    What info would i need to store and join - just at a high level.
  8. MohammedU New Member

    Trace is the best option in this case.... you can capture all the information if you are using sysprocesses/inpubuffer...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. sql_jr New Member

    thx mohammedu: Where in inputbuffer or sysprocesses is the object name/id being affected? I would have to do a search/parse of the inputbuffer?? Really trying to avoid trace/profiler. Tx
    Please advise.
  10. MohammedU New Member

    I don't think it is viable solution using inputbuffer...

    You can make use of the following code... and dump into a temp table and you can use charindex function to get the object name by looping through your sysobjects but it is hard for you to check the sysobjects of all dbs...

    create table #temp (EventTypeVarchar(50), Parametersint, EventInfo Varchar(256))
    declare @sql Varchar(1000), @spid Varchar(5)
    select @sql = 'DBCC INPUTBUFFER ', @spid = 52
    select @sql = @sql+'('+@spid+')'
    select @sql
    insert into #temp
    exec (@sql)
    select * from #temp


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  11. MohammedU New Member

    By the way if you are using sql server 2005 then you can make use of default trace which runs all the time and it is enabled by default...

    The trace is stored in the LOG directory for your SQL Server instance (i.e. C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG) at the root of where your SQL Server is installed.



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. satya Moderator

  13. sql_jr New Member

    Thx, M and S.<br />M: What is the default trace and how do you access it?<br /><br />S: This is very interesting, but does this require to put triggers on your table?<br /><br />Appreciate the brainpower here <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  14. MohammedU New Member

    You can't modify the default trace but you read the trace from the log folder as I mentioned in my previous posting....
    The trace is stored in the LOG directory for your SQL Server instance (i.e. C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG) at the root of where your SQL Server is installed.

    You can copy these trace files to different location for your use... MS keeps only 5 files I belive..


    S: This is very interesting, but does this require to put triggers on your table?
    Yes, you have to create the trigger on the source table and write it to a audit table...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  15. satya Moderator

    Either you have to trace the event using PROFILER (Logon audit) or use some sort of triggers.
    Either way the third party tool will also have similar approach to get such information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page