Archive | Gathering Performance Information RSS for this section

Collect information from system metadata

May include but is not limited to: Dynamic Management Views (DMVs), catalog views

SQL Server always provided ways to access metadata describing your database objects. The SQL Server 2008 ways to do this is to query Dynamic Management Views and catalog views.

Before we move further, let’s describe metadata. Metadata is meta that describes data.

Catalog views provide a way to query database objects, and server configuration options in a very similar way you are doing it with actual tables. The obsolete method doing this was querying INFORMATION_SCHEMA views. Catalog views are far more superior, because they let you query results and use them in joins, offers server-specific information, and provides much more information.

We’ll query the following: table, column and index metadata. To query table metadata, use the sys.tables, sys.columns, sys.schemas and sys.types views. The syntax is the standard SQL:

SELECT * FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id

The code above retrieves all tables with their columns residing in the current database.

Information about indexes can be retrieved by using the sys.indexes, sys.index_columns catalog views.

Dynamic Management Views give you access to internal server state information. There are more than eighty of them, and you can easily identify them, because they are members of the sys schema, and their names start with the prefix dm_.

You can retrieve a great deal of information by using them, regarding session, connection, executing SQL, server resources, and unused indexes, just to name a few.

I don’t think it would do any good if I’d provide a list of these views, because MSDN did this job in a more effective way already. Just play around with these views, and you’ll gain enough experience on them for the exam, too.

Capture execution plans

May include but is not limited to: graphical execution plans, SHOWPLAN

I’m a bit confused about this one being a separate objective. As all of us know, SQL Server produces graphical (and textual) execution plans for your queries. You can watch these plans any time you’d like by pressing Ctrl+M.

A few things to know about execution plans:

  • They are read from left to right, and from top to bottom.
  • The thicker an arrow, the more data it returns.

I think that’s good enough. Another way to access graphical execution plans is the SHOWPLAN clause. When specifying it, SQL Server returns the execution plan in an XML format, without executing the commands. The syntax to use:


If you prefer plain text, use the SHOWPLAN_TEXT clause instead. But make sure you turn off these features, because you only get execution plans as a result.

You can get additional information about your queries when using the STATISTICS clause. The difference is that when you specify STATISTICS, you get a result after your query is executed. There are four things to query for: XML, PROFILE, IO, TIME. The syntax is:


Then you can view the results in the  Messages pane in SSMS.