Tag: SQL Server System Tables

Understand your system tables in SQL Server

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