It is my belief that a good understanding of the system tables in SQL Server is one of the keys to understanding why the engine does what it does. For example, a good understanding of the system tables can help you figure out what query plan a sql statement is using. In addition system tables can be used to find out key information in your database, for instance, what table in a database is largest by rows and data size. In short, systems tables have a plethora of information that you can use to become a better DBA and DB Developer.
One of the most common questions I receive, on what seems to be a daily basis, is “My system is slow, what is going on”. Many DBAs and developers take the initial step of running the system procedures: sp_who2 and sp_locks to see what is running or active on their system. Both sp_who2 and sp_locks are helpful system procedures, but they only give you part of what you need to know. This is where system tables come to the rescue. Continue reading