SQL Server Performance

OBJECT_NAME on SP_LOCK

Discussion in 'Performance Tuning for DBAs' started by cchitanu, Jun 5, 2004.

  1. cchitanu New Member

    I created a table with the output of sp_lock and I added a new column object_name.

    Is there a way to populate ALL the object_name rows based on the objid and dbid ?

    I can do this row by row using :

    use db_name(from sysdatabases)
    and
    select object_name(objid)

    but I would like to have a script to update all the rows.

    Thanks


  2. bambola New Member

    just run an update my_table set objectname = object_name(id)
    also, you could write your own sp_lock sproc and add object_name(id) directly there
  3. cchitanu New Member

    Hi Bambola,
    Your sugestion will not work. In the output of sp_lock I have different dbid and object id.
    DBID OBJID
    5 131242345
    5 456567787
    6 111111224
    6 235457658
    7 243454758

    Object_name(id) will give me the table names from master..sysobjects(I am running sp_lock from master). I still need a way to switch between databases.
  4. satya Moderator

    It will run if you could join sysobjects & sysdatabases together to get the table name.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. derrickleggett New Member

    This script is kind of a pain and I need to do a lot of work on it, but it works. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> You will need to hardcode in the name of your databases. On the good side, once you have it setup, it works.<br /><br />-- sp_who 226<br />-- dbcc inputbuffer(311) <br />-- sp_lock<br />-- kill 409<br />-- select * from sysprocesses where status &lt;&gt; 'sleeping' order by cpu desc<br />-- select * from sysprocesses where hostname = 'KCW2KSAPP8'<br /><br />-- select * from sysprocesses with(nolock) where waitresource like '2%' order by cpu<br />-- select * from sysprocesses with(nolock) where waitresource like '2%' order by physical_io<br />-- select * from sysprocesses with(nolock) where waitresource like '2%' order by memusage<br />-- <br />-- select * from tempdb..sysobjects where id = 1<br />-- dbcc outputbuffer(209)<br />-- select * from sysprocesses where spid = 209<br /><br />--dbcc inputbuffer(101)<br />--select * from sysprocesses where blocked &lt;&gt; 0<br /><br /><br /><br /><br />PRINT 'locked objects on ops'<br />PRINT ' '<br />SELECT <br />sd.name AS DatabaseName,<br />CASE<br />WHEN DB_NAME(sd.dbid) = 'PE' THEN (SELECT name FROM PE.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'chat_suite' THEN (SELECT name FROM chat_suite.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'complaint' THEN (SELECT name FROM complaint.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'lending_tree_leads' THEN (SELECT name FROM lending_tree_leads.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'los' THEN (SELECT name FROM los.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'master' THEN (SELECT name FROM master.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'metrics' THEN (SELECT name FROM metrics.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'model' THEN (SELECT name FROM model.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'msdb' THEN (SELECT name FROM msdb.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'nfi' THEN (SELECT name FROM nfi.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'nhm' THEN (SELECT name FROM nhm.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'oneloan_external' THEN (SELECT name FROM oneloan_external.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'ops' THEN (SELECT name FROM ops.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'ops_archive' THEN (SELECT name FROM ops_archive.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'ops_credit' THEN (SELECT name FROM ops_credit.dbo.sysobjects WHERE id = sl.rsc_objid)<br />--WHEN DB_NAME(sd.dbid) = 'ops_credit_archive' THEN (SELECT name FROM ops_credit_archive.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'ops_nsis' THEN (SELECT name FROM ops_nsis.dbo.sysobjects WHERE id = sl.rsc_objid)<br />-- WHEN DB_NAME(sd.dbid) = 'ops_nsis_archive' THEN (SELECT name FROM ops_nsis_archive.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'payment' THEN (SELECT name FROM payment.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'servicing' THEN (SELECT name FROM servicing.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'system_admin' THEN (SELECT name FROM system_admin.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'tempdb' THEN (SELECT name FROM tempdb.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'training' THEN (SELECT name FROM training.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> WHEN DB_NAME(sd.dbid) = 'web_home' THEN (SELECT name FROM web_home.dbo.sysobjects WHERE id = sl.rsc_objid) <br />WHEN DB_NAME(sd.dbid) = 'web_nhm' THEN (SELECT name FROM web_nhm.dbo.sysobjects WHERE id = sl.rsc_objid)<br />WHEN DB_NAME(sd.dbid) = 'web_retail' THEN (SELECT name FROM web_retail.dbo.sysobjects WHERE id = sl.rsc_objid)<br /> END AS ObjectName,<br />sl.req_spid AS LockSPID,<br />sp.blocked AS BlockedSPID,<br />sl.rsc_text AS LockText,<br />CASE<br />WHEN sl.rsc_type = 1 THEN 'Resource Not Used'<br />WHEN sl.rsc_type = 2 THEN 'Database'<br />WHEN sl.rsc_type = 3 THEN 'File'<br />WHEN sl.rsc_type = 4 THEN 'Index'<br />WHEN sl.rsc_type = 5 THEN 'Table'<br />WHEN sl.rsc_type = 6 THEN 'Page'<br />WHEN sl.rsc_type = 7 THEN 'Key'<br />WHEN sl.rsc_type = 8 THEN 'Extent'<br />WHEN sl.rsc_type = 9 THEN 'RID'<br />WHEN sl.rsc_type = 10 THEN 'Application'<br />END AS LockType,<br />CASE<br />WHEN sl.req_status = 1 THEN 'GRANTED'<br />WHEN sl.req_status = 2 THEN 'CONVERTING'<br />WHEN sl.req_status = 3 THEN 'WAITING'<br />END AS LockStatus,<br />CASE<br />WHEN sl.req_mode = 0 THEN 'No Access Granted'<br />WHEN sl.req_mode = 1 THEN 'Sch-S: Schema Stability - No Drops'<br />WHEN sl.req_mode = 2 THEN 'Sch-M: Schema Modification - No Other References Allowed'<br />WHEN sl.req_mode = 3 THEN 'S: Shared - Shared Access'<br />WHEN sl.req_mode = 4 THEN 'U: Update - May Eventually Update'<br />WHEN sl.req_mode = 5 THEN 'X: Exclusive - Exclusive Access To Resource'<br />WHEN sl.req_mode = 6 THEN 'IS: Intent Shared - Intent To Acquire S Locks On Subordinate Resource'<br />WHEN sl.req_mode = 7 THEN 'IU: Intent Shared - Intent To Acquire U Locks On Subordinate Resource'<br />WHEN sl.req_mode = 8 THEN 'IX: Intent Shared - Intent To Acquire X Locks On Subordinate Resource'<br />WHEN sl.req_mode = 9 THEN 'SIU: Shared Intent Update - Shared Access With Intent Of Acquiring Update Locks On Subordinate Resources'<br />WHEN sl.req_mode = 10 THEN 'SIX: Shared Intent Exclusive - Shared Access With Intent Of Acquiring Exclusive Locks On Subordinate Resources'<br />WHEN sl.req_mode = 11 THEN 'UIX: Update Intent Exclusive - Update Lock Held With Intent Of Acquiring Exclusive Locks On Subordinate Resources'<br />WHEN sl.req_mode = 12 THEN 'BU: Bulk Operation Lock'<br />WHEN sl.req_mode = 13 THEN 'RangeS_S: Shared Key-Range and Shared Resource Lock - Shared Locks For Serializable Range Scan.'<br />WHEN sl.req_mode = 14 THEN 'RangeS_U: Shared Key-Range and Update Resource Lock - Update Locks For Serializable Update Scan.'<br />WHEN sl.req_mode = 15 THEN 'RangeI_N: Insert Key-Range and Null Resource Lock - Shared Locks To Test Range For Insertion Of New Key In Index.'<br />WHEN sl.req_mode = 16 THEN 'RangeI_S: Key-Range Conversion Lock - Created by an overlap of RangeI_N and S Locks.'<br />WHEN sl.req_mode = 17 THEN 'RangeI_U: Key-Range Conversion Lock - Created by an overlap of RangeI_N and U Locks.'<br />WHEN sl.req_mode = 18 THEN 'RangeI_X: Key-Range Conversion Lock - Created by an overlap of RangeI_N and X Locks.'<br />WHEN sl.req_mode = 19 THEN 'RangeX_S: Key-Range Conversion Lock - Created by an overlap of RangeI_N and S Locks.'<br />WHEN sl.req_mode = 20 THEN 'RangeX_U: Key-Range Conversion Lock - Created by an overlap of RangeI_N and S Locks.'<br />WHEN sl.req_mode = 21 THEN 'RangeX_X: Exclusive Key-Range And Exclusive Resource Lock - Conversion Locks For Update A Key In A Range.'<br />END AS LockRequestMode,<br />CASE<br />WHEN sl.req_ownertype = 1 THEN 'Transaction'<br />WHEN sl.req_ownertype = 2 THEN 'Cursor'<br />WHEN sl.req_ownertype = 3 THEN 'Session'<br />WHEN sl.req_ownertype = 4 THEN 'ExSession'<br />END AS LockOwnerType,<br />CASE<br />WHEN (sp.blocked = 0 AND (SELECT DISTINCT blocked FROM master.dbo.sysprocesses WITH(NOLOCK) WHERE blocked = sl.req_spid) IS NOT NULL) THEN 3<br />WHEN (sp.blocked &lt;&gt; 0 AND (SELECT DISTINCT blocked FROM master.dbo.sysprocesses WITH(NOLOCK) WHERE blocked = sp.blocked) IS NOT NULL) THEN 2<br />ELSE 1<br />END AS LockOccurenceTypeOrder<br />FROM <br />master..syslockinfo sl WITH(NOLOCK) <br />INNER JOIN master..sysdatabases sd WITH(NOLOCK) ON sl.rsc_dbid = sd.dbid<br />INNER JOIN master..sysprocesses sp WITH(NOLOCK) ON sl.req_spid = sp.spid<br />WHERE<br />sl.rsc_objid IS NOT NULL<br />AND sl.rsc_objid &lt;&gt; ''<br />AND (sp.spid IN (SELECT DISTINCT blocked FROM master.dbo.sysprocesses WITH(NOLOCK))<br />OR blocked &lt;&gt; 0)<br />ORDER BY <br />LockOccurenceTypeOrder<br /><br />--Show all blcked processes<br />select * from master.dbo.sysprocesses WITH(NOLOCK) where blocked &lt;&gt; 0<br /><br />PRINT 'connections by host'<br />PRINT ' '<br />SELECT <br />hostname, <br />COUNT(*) AS HostConnections,<br />(SELECT COUNT(*) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />hostname <br />ORDER BY <br />2 DESC<br /><br />PRINT 'connections by database'<br />PRINT ' '<br />SELECT <br />sd.name, <br />COUNT(sp.spid) AS HostConnections,<br />(SELECT COUNT(spid) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses sp with(nolock) <br />INNER JOIN master.dbo.sysdatabases sd with(nolock) ON sp.dbid = sd.dbid<br />GROUP BY <br />sd.name <br />ORDER BY <br />2 DESC<br /><br />PRINT 'connections by loginame'<br />PRINT ' '<br />SELECT <br />loginame, <br />--hostname,<br />COUNT(*) AS HostConnection<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />loginame--,<br />--hostname<br />ORDER BY <br />2 DESC<br /><br />PRINT 'connections by loginame/hostname'<br />PRINT ' '<br />SELECT <br />loginame, <br />hostname,<br />COUNT(*) AS HostConnection<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />loginame,<br />hostname<br />ORDER BY <br />1,2 DESC<br /><br />PRINT 'connections by host/program'<br />PRINT ' '<br />SELECT <br />hostname,<br />program_name, <br />COUNT(*) AS HostConnections,<br />(SELECT COUNT(*) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY <br />program_name,<br />hostname<br />ORDER BY <br />1,2<br /><br />PRINT 'connections by Client PID'<br />PRINT ' '<br />SELECT<br />hostprocess,<br />COUNT(*) AS HostConnections,<br />(SELECT COUNT(*) FROM master.dbo.sysprocesses WITH(NOLOCK)) AS TotalConnections<br />FROM <br />master.dbo.sysprocesses with(nolock)<br />GROUP BY<br />hostprocess<br />ORDER BY<br />2 DESC<br /><br />--DBCC CACHESTATS<br />--DBCC MEMORYSTATUS<br />--DBCC SHOWCONTIG <br />--DBCC DBREINDEX ('ops_nsis.dbo.loan',idx_loan__broker_id__created_by__is_prequal__cur_status__cancelled__loan_id)<br />--DBCC SHOWCONTIG(loan) WITH TABLERESULTS, ALL_INDEXES, ALL_LEVELS<br />--DBCC INDEXDEFRAG(ops_nsis,loan,idx_loan__broker_id__created_by__is_prequal__cur_status__cancelled__loan_id)<br /><br />-- select count(*) from ops_credit..archivable_credit_reports where isArchived = 0<br /><br />-- select <br />-- count(*), <br />-- CAST(CAST(DATEPART(MM, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,pull_date)AS VARCHAR(4)) AS DATETIME)<br />-- from <br />-- ops_credit.dbo.credit_report<br />-- group by<br />-- CAST(CAST(DATEPART(MM, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, pull_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,pull_date)AS VARCHAR(4)) AS DATETIME)<br />-- order by<br />-- 2 desc<br />-- <br /><br />-- SELECT <br />-- COUNT(*),<br />-- is_prequal,<br />-- CAST(CAST(DATEPART(MM, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,create_date)AS VARCHAR(4)) AS DATETIME)<br />-- FROM <br />-- ops_nsis.dbo.loan<br />-- GROUP BY<br />-- is_prequal,<br />-- CAST(CAST(DATEPART(MM, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(DD, create_date)AS VARCHAR(4)) + '/' + <br />-- CAST(DATEPART(YYYY,create_date)AS VARCHAR(4)) AS DATETIME)<br />-- ORDER BY 3 desc<br />-- <br />-- <br />-- dbcc inputbuffer(86)<br />-- dbcc inputbuffer(75)<br />-- kill 75<br />-- dbcc outputbuffer(75)<br /><br /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  6. cchitanu New Member

    I think this is the question : what is the link between sysobjects and sysdatabases ?
  7. cchitanu New Member

    Derrickleggett,
    This is not an elegant solution :

    WHEN DB_NAME(sd.dbid) = 'PE' THEN (SELECT name FROM PE.dbo.sysobjects WHERE id = sl.rsc_objid)

    you still need to manually enter all database names on your server.
  8. bambola New Member

    Suppose @tbl_sp_who is the table with the output of sp_who

    declare @tbl_sp_who table (dbid int, objid int ....)

    you can hard code it

    select o.name
    from @tbl_sp_who t
    inner join my_db.dbo.sysobjects o
    on t.dbid = @dbid and o.id = t.objid

    or create a small table with all database names, loop over it and dynamically create your update statement.
    '...inner join ' + db_name(id) + '.dbo.' + 'sysobjects...'





  9. derrickleggett New Member

    I said it wasn't elegant. Read the post. It works though; and when you've had it around for years it takes time to change. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  10. cchitanu New Member

    Finally I created something like this :

    DECLARE test CURSOR FOR
    SELECT b.name,a.objid from #lock a inner join master..sysdatabases b on a.dbid=b.dbid
    OPEN test

    declare @dbname varchar(30)
    declare @id int

    FETCH NEXT FROM test into @dbname,@id
    WHILE @@FETCH_STATUS = 0
    BEGIN
    select @execstr =ISNULL(@execstr + CHAR(13), '')+'USE '+CAST(@dbname AS VARCHAR)+' Update #lock SET objname= (Select name from sysobjects where id='+CAST(@id AS VARCHAR)+')'
    exec(@execstr)

    FETCH NEXT FROM test into @dbname,@id
    END

    Regards

Share This Page