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://darkslategrey-bat-805937.hostingersite.com)
