Five useful dynamic management objects in SQL Server 2005

by Tim Chapman | Oct 02, 2006 7:00:00 AM

Tags: Databases, Enterprise software, Performance management, Tim Chapman, Microsoft SQL Server, management object, dynamic management object, server, Windows performance, Windows Performance Monitor, monitor, Microsoft SQL Server 2005, SQL Server Newsletter

Takeaway: SQL Server 2005 is packaged with new object types known as dynamic management objects. These objects can return the state of the database for a given point in time. Tim Chapman discusses how you can use five of the dynamic management objects to your advantage.

New to SQL Server 2005 are dynamic management objects, which are database views or functions that return state specific information for the given database instance at a given time. These objects allow the DBA or developer to monitor the database server in an efficient, controlled way. Dynamic management objects replace many of the former system tables available in previous versions of SQL Server, but give the DBA many more metrics to observe.

There are dynamic management objects for practically anything you need to monitor on your server. Because there are so many dynamic management objects, it would be impractical to review them all in this article. For instance, there are 12 categories of dynamic management objects, but I am only going to focus on five objects that I feel are quite useful from the SQL Server Operating System Related, Index Related, and Execution Related categories.

These objects are scoped two different ways: at the database and at the server level. The security architecture is different in SQL Server 2005, so you need to take some measures to ensure that you can use the views and functions I look at in this article. You need to make sure that the user you are using for these examples is able to view the SERVER STATE and DATABASE STATE of your objects. This can be done using the GRANT statement.

Before I begin looking at the dynamic management objects, you should create a table that you can use throughout the examples. Listing A contains the script to create the table.

SQL Server Operating System Related Dynamic Management Views

sys.dm_os_performance_counters: The information returned by this view lists performance statistics directly related to SQL Server. The information returned represents the internal performance counters as displayed through the Windows Performance Monitor. This data was represented in SQL Server 2000 through a system table named sysperfinfo, and that table remains in SQL Server 2005, but for backward compatibility only. It will likely be removed in future versions.

To take a look at how you can use this view to obtain system information rather than running Windows Performance Monitor, run the query in Listing B. This query will return the Buffer Cache Hit Ratio for the current moment on the server. The Buffer Cache Hit Ratio is the percentage of pages requested by SQL Server that were found in memory. If all is well on your server, you will typically see this value at over 90%. If this value is much lower than that, it means that your server is going to disk to retrieve data pages, and it may be a sign that your server needs more memory.

If you were to run the above query and compare the results with those found through running Windows Performance Monitor for the same object (SQL Server: Buffer Manager Buffer Cache Hit Ratio), you will find that these figures are practically identical. With a bit of research, you can develop your own library of scripts based on this table to use instead of constantly running Windows Performance Monitor.

Caveat: This view is limited to only SQL Server-related counters. You will still need to run Windows Performance Monitor if you need to capture non-SQL Server related data.

Index Related Dynamic Management Views and Functions

sys.dm_db_index_physical_stats: This dynamic management function returns information regarding data and index information for data tables and views.

View Listing C. The function accepts five parameters: the ID of the database, the ID of the table or view in question, the ID of a specific index on a table or view (which I have omitted), a partition number (which I have also omitted), and a mode, which I am specifying 'DETAILED' for. Passing these specific parameters to this function will allow you to view the detail for all of the indexes on the DMVTest table.

Running the query in Listing C on the table defined earlier in the article, you can see that there are no pages in the table and also no records in the table. By adding a record to DMVTest and rerunning the query, you can see that a page has been added to the table.

INSERT INTO DMVTest(C2, C3)

VALUES(1,1)

Another field of particular interest returned from this function is the avg_page_space_used_in_percent field, which tells how full the data pages are. From the query, you find that the data page is approximately .25 of 1% full. You can use this figure to develop routines to check the database tables and reindex them as necessary, depending upon the criteria set in the routines.

Execution Related Dynamic Management Views and Functions

sys.dm_exec_requests: This view shows information regarding each request occurring in the SQL Server instance. The information provided by this view is especially useful when you are investigating blocking on your server. The reads, writes, session settings, and blocking_session_id are some of the useful data returned from this view. The blocking_session_id column indicates the session that is blocking the database request on the system. Later in the article, I will take a look at an example of how to determine the statement the user is running that is blocking your request.

sys.dm_exec_sessions: This returns one row per authenticated session on SQL Server. This view is useful when you want to find out all of the users who are connected to the database server. It returns information such as the session_id, program from which the connection originated reads and writes executed by the session, along with some additional session settings. The view also contains a BIT field called is_user_process, with which you will probably want to use when querying this table. A value of 1 indicates that the session is a user session and not a system defined one.

sys.dm_exec_sql_text: This is a dynamic management function that returns the SQL Server statement based upon a sql handle. For certain dynamic management objects, SQL Server 2005 stores the executing sql statement as a hash value. This hash value can be deciphered by using the sys.dm_exec_sql_text dynamic management function. However, anytime you need to join a function with another object and intend to pass a field value from the table or view, you will need to use the new APPLY operator. This functionality was not available in versions previous to SQL Server 2005. (For a more thorough description of the APPLY operator in SQL Server 2005, read this article.)

Now let's take a look at how you can put these three dynamic management objects to work for you. The query in Listing D will list the session, blocking session, program, host, and the SQL Server statement being executed for all current user requests on the database.

Make note that I am using the function dm_exec_sql_text and passing in the sql_handle field from the sys.dm_exec_requests view. For each row returned by the join between the sys.dm_exec_requests view and the sys.dm_exec_sessions view, the sys.dm_exec_sql_text function will be invoked.

Give it a try

The best way to learn the plethora of new dynamic management objects is to test them out. SQL Server Books Online gives reasonable documentation regarding their use, but I always learn more through doing rather than by reading. With a bit of effort, you will find many different new ways to use not only the objects listed in this example, but also the dynamic management objects included in SQL Server 2005.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

1 sobella77@... | 10/09/06