System Stored Procedures remain part of my troubleshooting toolbox. I use these familiar entry points before moving to more detailed views.
My working style has changed over the years, but these small entry points remain useful. The table keeps the original seven procedures and clarifies what their output can tell you.
| Procedure | Useful starting point |
|---|---|
sp_who | Sessions and current activity, including blocking session identifiers. |
sp_lock | Legacy lock details, not a captured deadlock graph. |
sp_spaceused | Database or table space usage. |
sp_helpdb | Database properties and file information. |
sp_monitor | Instance activity statistics since the previous invocation. |
sp_helpindex | Basic index descriptions for a table. |
sp_statistics | Index and statistics information exposed through the ODBC-oriented procedure. |
EXEC sys.sp_who;
EXEC sys.sp_spaceused;
EXEC sys.sp_helpdb;
-- In the database containing the table:
-- EXEC sys.sp_helpindex N'dbo.YourTable';
-- EXEC sys.sp_statistics @table_name = N'YourTable', @table_owner = N'dbo';I avoid treating this list as a complete performance diagnosis. For current lock investigation, sys.dm_tran_locks provides more detail than the deprecated sp_lock procedure. Blocking information is not the same as a deadlock graph, which records the participants and resources in a completed cycle. Permissions and the database context affect what you can see.
Which system procedure do you still reach for first? Community procedures are useful too, but they were outside the scope of this original list.
Reference: Legacy sp_lock and its replacement.
A familiar procedure is not a complete diagnosis, it is an entry point into the evidence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
6 Comments. Leave new
As Microsoft is warning people against using sp_lock, I wrote the following query which helps show currently executing queries – and where available, information about the blocking query.
SELECT
ES.login_name as [user],
er.session_id as [session_id],
BES.login_name as [blocking_username],
er.blocking_session_id as [blocking_session_id],
er.percent_complete,
BES.status as [blocking_status],
db_name(BES.database_id) as [blocking_db],
er.start_time,
datediff(SS, start_time, getdate()) as [run time],
er.status,
er.command,
er.database_id,
db_name(er.database_id) as [database], text,
query_plan,
query_plan.query(‘declare namespace MSBS=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”; //MSBS:ParameterList/*’) as xquery,
BES.host_name as [blocking_host],
BES.host_process_id as [blocking_host_pid]
FROM
sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
cross apply sys.dm_exec_query_plan(plan_handle) as qp
LEFT JOIN sys.dm_exec_sessions BES ON er.blocking_session_id = BES.session_id
LEFT JOIN sys.dm_exec_sessions ES ON er.session_id = ES.session_id
WHERE er.session_id @@SPID
Very true Brandon,
As I said these are my SP to get started in my early career.
That’s quite intersesting. By the way, thank you SO MUCH, Pinale Dave, for sharing your knowledge and your experience, this is priceless, and it’s always a pleasur to read your blog. Good job, dude ! :)
Thank you for your kind words.
Thanks for sharing,
using the helpindex procedure I have found that I have multiple indexes (up to 3) on a table that all seem to be structured around the same columns but the indexes have differing names
Is this the norm for indexes?
thanks