SQL SERVER – System Stored Procedures I Use to Get Started

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


Current SQL Server users and processes


Active locks, as well as blocking and deadlock information


The amount of disk space that a table or a database uses


Databases and their objects


SQL Server statistics, such as total processing time, number of reads and writes, and connections


Indexes on a table


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)

SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Network Servers List is Empty in SQL Server Management Studio
Next Post
SQL SERVER – Becoming a More Effective DAX Developer with DAX Studio – Notes from the Field #134

Related Posts

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.

    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],
    BES.status as [blocking_status],
    db_name(BES.database_id) as [blocking_db],
    datediff(SS, start_time, getdate()) as [run time],
    db_name(er.database_id) as [database], text,
    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]
    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

  • 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 ! :)

  • 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?


Leave a ReplyCancel reply

Exit mobile version