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

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)

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.

    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

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

    Reply
  • Thanks for sharing,

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

    Reply

Leave a Reply

Menu
Exit mobile version