Call me old school, but there are certain things in SQL Server that I believe in strongly. Over the years, my working style has evolved, but there are a few techniques that have stood the test of time. Today, I want to talk about one such technique – the use of system stored procedures.

Working with SQL Server has its basics, and these fundamentals have remained unchanged for decades. While performance techniques have become more refined, the core principles remain the same. In this blog post, I want to share with you some of the system stored procedures that I have been using consistently throughout my career.

Even though Dynamic Management Views (DMVs) have become more popular, I find myself relying on these system stored procedures because they have proven to be reliable and effective. Let’s dive into the top system stored procedures that I use in various situations:

• sp_who: This stored procedure provides information on the current SQL Server users and processes. It helps me identify any active sessions and troubleshoot performance issues.
• sp_lock: When it comes to managing locks, blocking, and deadlock information, sp_lock is my go-to stored procedure. It allows me to understand the current lock status and resolve any conflicts.
• sp_spaceused: To determine the amount of disk space used by a table or a database, I rely on sp_spaceused. It helps me analyze storage requirements and optimize space allocation.
• sp_helpdb: This stored procedure provides detailed information about databases and their objects. It is particularly useful when I need to understand the structure and dependencies of a database.
• sp_monitor: When it comes to monitoring SQL Server statistics, such as processing time, reads and writes, and connections, sp_monitor is my preferred choice. It gives me a comprehensive overview of the server’s performance.
• sp_helpindex: To examine the indexes on a table, I turn to sp_helpindex. It provides valuable insights into the indexing strategy and helps me optimize query performance.
• sp_statistics: When I need information about all the indexes on a specific table, sp_statistics comes to the rescue. It allows me to analyze index usage and identify potential areas for improvement.

These system stored procedures have been my trusted companions throughout my career as a SQL Server professional. They have helped me troubleshoot performance issues, optimize storage, and monitor server statistics. However, I am always open to learning new techniques.

If there are any other system stored procedures that you find indispensable in your DBA toolbox, I would love to hear about them. Feel free to share your insights and experiences in the comments section below. Together, we can continue to enhance our SQL Server skills and knowledge.

PS: There are also a number of community-built procedures that people use, which I haven’t covered in this blog post. But that’s a topic for another day.