Call me an old school or whatever – there are certain things I believe in strongly. I have a working style and these in my opinion have evolved over the years. I keep innovating myself as I learn on my way, but there are few things I do that always makes me think I have not changed much. Let us learn about System Stored Procedures I Use to Get Started.
The basics of working with SQL Server in my opinion have not changed for decades now. The performance techniques I used more than a decade still holds good and it has taken a more refined state though. But the basics don’t change. They don’t challenge me anymore.
Consider this blog as old wine in a new bottle – rather I would like to say this as old wine in the same old bottle. The reason is simple, I have rarely changed some of my troubleshooting techniques for ages. So let us get to the meat here. What am I talking about?
Invariably in all the performance troubleshooting consultations, I have seen that I use a number of system stored procedures. Even though the DMV’s have come a long way, I tend to use them often because they have stuck to me for ages. In this blog, I wanted to highlight those top System Stored procedures that I have been using invariably given any situation. I am sure you have used most of them, but a recap was required.
Though these SPs have been there for ages and helped me in multiple assignments, I would like to know are there some SPs that I have missed in the above list that fall into your DBA toolbox? Let me know via comments as this would help us all.
| System stored procedure | Reports information on |
sp_who | Current SQL Server users and processes |
sp_lock | Active locks, as well as blocking and deadlock information |
sp_spaceused | The amount of disk space that a table or a database uses |
sp_helpdb | Databases and their objects |
sp_monitor | SQL Server statistics, such as total processing time, number of reads and writes, and connections |
sp_helpindex | Indexes on a table |
sp_statistics | All indexes on a specific table |
PS: There are a number of other community built procedures that people use that I have added. But that would be reserved for a different blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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