Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the object of SQLServer2005 dynamic management". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the object of SQLServer2005 dynamic management".

Almost all the contents that need to be monitored on the server have corresponding dynamic management objects. Since there are so many dynamic management objects, it is impractical to discuss them one by one in this article. For example, there are 12 types of dynamic management objects, but here I focus on five objects that I think are quite important, each from classes related to the SQLServer operating system, directories, and execution.

These objects are divided into two categories: the database level and the server level. The security architecture of SQLServer2005 is somewhat different, so you must take some steps to ensure that you can use the views and functions discussed in this article. You must make sure that the users who use these examples can view the SERVERSTATE and DATABASESTATE of the object. You can do this by applying the Grant statement.

Before I start introducing dynamic management objects, you should create a table that can be used in all examples. Listing An is the code that creates this table.

Dynamic Management objects related to SQLServer operating system

Sys.dm_os_performance_counters: the information returned by this view lists performance statistics directly related to SQLServer. The information returned represents the external performance counters displayed through the Windows performance monitor. In SQLServer2000, this data is represented in a systematic table called sysperfinfo, which is also available in SQLServer2005, but only for backward compatibility. This table may be deleted in future versions.

To learn how to apply this view instead of running the Windows performance monitor to get system information, run the query code in listing B. This query will return the cache hit ratio (BufferCacheHitRatio) of the server at the current time. Cache hit ratio is the percentage of SQLServer request pages found in memory. If the server is working properly, this value is generally more than 90%. If this value is less than 90%, it means that the server will go to the hard drive to recover data pages, which may be a sign that the server needs more memory.

What is the dynamic management object of SQLServer2005

If you are going to run the above query for the same purpose and compare the results with those displayed by running the Windows performance monitor (SQLServer: buffer manager cache hit ratio), you will find that the data is almost the same. With a little research, you can develop your own script library based on this table without having to run the Windows performance monitor all the time.

Caveat: this view is limited to SQLServer-related counters. If you want to get data other than SQLServer, you still need to use the Windows performance monitor.

Directory-related dynamic management views and functions

Sys.dm_db_index_physical_stats: this dynamic management function returns relevant data and catalog information for data tables and views. View list C. This function takes five parameters: the ID of the database, the ID of the table or view in question, the ID of a specific directory on the table or view (ignored by me), the partition number (also ignored by me), and a pattern, which I specify as "DETAILED". Submitting these parameters to this function allows you to view all the directory details in the DMVTest table.

Run the query in list C in the table defined in the last article, and you will find that there are neither pages nor records in the table. Add a record to the DMVTest, and then run the query, and you will find that a page has been added to the table.

INSERTINTODMVTest (C2 ~ C3)

VALUES (1 dint 1)

Another particularly interesting field returned by this function is the avg_page_space_used_in_ pages field, which indicates how big the data page is. It is found from the query that the data page is about 0.25%. You can use this data to develop programs, view database tables, and recatalog them according to the standards set in the program.

Thank you for your reading, the above is the content of "what is the object of SQLServer2005 dynamic management". After the study of this article, I believe you have a deeper understanding of what the object of SQLServer2005 dynamic management is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.