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.  As of SQL Server 2005 all queries currently active or queries that have had their plans cached are stored in SQL Server system tables.  So to retrieve the sql running on your server can now be accomplished by running a simple query.  The system tables you need to query to accomplish this task are sys.dm_exec_requests, sys.dm_exec_sql_text and sys.dm_exec_sessions.  If you notice the name of the system tables they start with sys.dm..  The DM system tables are dynamic management views.  Dynamic management views hold statistics between each sql server restart.  With these views you can determine what sql is running on your server, on which database, by which user as well as information about how many reads and writes and how long the query has been running.   Here is a sample query (Note: These views contain more information than I am showing, refer to books on line for more detail)

SELECT

DB_NAME(dbid)

,s.login_name

,s.host_name

,s.program_name

,sqltext.text,er.session_id ‘spid’

,er.total_elapsed_time

,er.logical_reads

FROM sys.dm_exec_requests er

INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as sqltext;

This query retrieves the database name, the login name of the user running the query, the host name the query was executed from, the program name set in the connection string, the sql statement, the spid of the connection , the elapsed time of the query and the logical reads for the connection.   Special note, the database name can be null if the database cannot be determined, such as when using three part names in the query (i.e. one or more databases could be in play) or when a query crosses databases.  The cross apply is used because the  dm view sys.dm_exec_sql_text is similar to a table value function.  The sql handle being passed to sys.dm_exec_sql text is the unique identifier to the sql statement in cache.  The dm view sys.dm_exec_sessions contains login or session information for the connection running the sql.  Finally the dm view sys.dm_exec_requests holds all the statistical information about the query such as; reads, writes, elapsed time among others.

This is just a small example of the power of the system tables.  I encourage you to look at the 3 system views used in the query above to see what other gems of information you can get from them.