SQL Server性能调优：利用Dynamic Management Views

需积分: 50 39 浏览量更新于2024-07-24 收藏 7.18MB PDF 举报

"eBook_Performance_Tuning_Davidson_Ford" 是一本关于使用SQL Server动态管理视图进行性能调优的专业书籍，由Louis Davidson和Tim Ford撰写。这本书详细介绍了如何利用SQL Server 2005及后续版本中的动态管理对象（DMOs）来监控和分析数据库实例的执行情况，从而提升服务器性能。在SQL Server 2005引入的动态管理对象（DMO）是一个重要的里程碑，它极大地扩展了可用于暴露数据库实例上运行的连接、会话、事务、语句和进程的元数据范围和深度。这些DMOs使DBA能够深入了解服务器上的工作负载、其分布情况、压力点等，为性能问题的诊断提供了强大的工具。书中深入探讨了如何使用动态管理视图（DMV）进行性能调优，这是DMO的一个关键组成部分。DMV允许管理员查看服务器的实时状态，包括查询执行计划、锁和等待统计、内存使用情况、I/O性能等。通过分析这些信息，可以识别性能瓶颈，优化查询，调整数据库配置，以提高SQL Server的整体性能。 Louis Davidson和Tim Ford在书中详细讲解了如何利用DMV来监控和分析以下方面： 1. **查询性能**：通过分析执行计划和查询统计信息，可以找出低效的查询并优化它们。 2. **资源消耗**：监控CPU、内存、磁盘I/O和网络使用，以便在需要时调整资源分配。 3. **锁和事务**：了解锁定模式和事务处理对并发性能的影响，解决死锁问题。 4. **索引分析**：检查索引使用情况，创建或优化索引来改进查询速度。 5. **缓冲池行为**：理解内存管理，优化缓存命中率。 6. **服务器配置**：根据服务器的工作负载调整SQL Server的配置设置。 7. **性能计数器**：结合性能计数器数据，提供更全面的性能分析。此外，作者还可能提供了最佳实践、案例研究和实战技巧，帮助读者将理论知识应用到实际环境中。这本书对于任何希望提升SQL Server性能的DBA或开发人员来说，都是一份宝贵的参考资料。 "eBook_Performance_Tuning_Davidson_Ford" 提供了一个全面的方法来利用动态管理视图进行SQL Server性能调优，是数据库管理专业人士必备的工具书之一。通过学习和实践书中的内容，读者将能够更好地理解和解决SQL Server性能问题，实现高效的数据处理和存储。

Chapter 1: Using Dynamic

Management Objects

Dynamic Management Objects (DMOs) are a set of SQL Server objects, referenced

in the system (sys) schema, which provide the SQL Server DBA with a window into the

activities being performed on their SQL Server instances, and the resources that these

activities are consuming. In other words, these DMOs expose valuable information

concerning the connections, sessions, transactions, SQL statements, and processes that

are (or have been) executing against a database instance, the resultant workload generated

on the server, how it is distributed, where the pressure points are, and so on.

Having revealed a particular pressure point on their SQL Server, the DBA can then take

appropriate steps to alleviate the problem, perhaps by tuning a query, adding an index,

improving the disk subsystem, or simply "killing" a blocking session.

The term "dynamic" refers to the fact that the information reﬂected in these DMOs

is generated dynamically from a vast range of "instrumentation" points in memory

structures throughout the SQL Server engine. This data is then exposed in tabular

form via these DMOs, either in views, in which case they are referred to as Dynamic

Management Views (DMVs), or in table-values functions, in which case they are referred

to as Dynamic Management Functions (DMFs).

Too many acronyms

The objects, collectively, should be referred to as DMOs, but since this tends to cause some confusion with

the entirely unrelated "Distributed Management Objects," it's still very common for DBAs to refer to the

Dynamic Management Objects, collectively, as "DMVs."

So, DMVs and DMFs are simply system views and system functions and you use them just

like you use any other view and function within SQL Server: querying them, joining to

Chapter 1: Using Dynamic Management Objects

In releases prior to SQL Server 2005, SQL Server exposed metadata in a range of

physical system tables, stored or referenced in the master database (e.g. m aste r.d b o.

sysobjects or dbo.sysprocesses). Microsoft warned SQL DBAs and developers not

to code against the system objects, as they could not guarantee that they would not be

changed, or that they would even continue to be supported in future releases. However,

most DBAs did just that, and the sysprocesses system table, in particular, became the

"go-to" source of information when troubleshooting performance concerns in SQL

Server. This single table gave the DBA access to the spid (unique identiﬁer for the user

session), cumulative CPU usage, memory, and I/O consumption, client connectivity

information (host name, host program name, login information, and process id) and

a myriad of other important columns that identiﬁed, at a quick glance, what was

transpiring on the SQL instance at any given time. Many of us rolled our own

management solutions and queries, based on sysprocesses, allowing us to dig into the

guts of SQL Server.

Microsoft realized the importance of these scripts, and knew that many DBAs relied on

them but, at the same time, wanted to introduce a layer of abstraction between the user

and their metadata. So, starting in SQL Server 2005, they moved the system tables from

the master database into the new resource database that is inaccessible to the end-

user, and even the DBA (there are techniques to access the resource database, if it is

really necessary, but we won't cover them here).

In their place, in the master database, Microsoft created a set of compatibility views,

based on the data in these, now hidden, system tables. These compatibility views are

structured identically to the original system tables they were replacing (column names,

data types, and column order remain constant), but they are now owned by the sys

schema. They were provided purely for backwards compatibility purposes; they do not

reﬂect any new features and functionality added in SQL Server 2005 and later. However,

with a simple schema change in any applicable code, they allowed DBAs to keep using

their existing set of troubleshooting scripts and tools.

At the same time, with SQL Server 2005, Microsoft introduced a new set of system

catalog views, as well as the DMOs, both of which also live in the master database, but

Chapter 1: Using Dynamic Management Objects

source their actual data from the resource database. These catalog views and DMOs

would be fully supported and reﬂect all new functionality, and Microsoft began

encouraging DBAs to make the switch.

In some cases the switch is fairly subtle; for example, the sys.sysobjects compat-

ibility view becomes the sys.objects catalog view; sys.sysindexes becomes sys.

indexes, and so on. In other cases, the switch is slightly more complex; for example,

master.dbo.sysprocesses became the sys.sysprocesses compatibility view, but

was ocially superseded at the same time by the sys.dm _ exec _ requests DMV. In

reality, however, the rather complex sys.sysprocesses compatibility view has been

normalized to

deal with several new constructs such as support for MARS connections, and so on. The

net result is that the information previously found in the single sys.sysprocesses

view is now scattered across sys.dm _ exec _ connections and sys.d m _ exec _

sessions, as well as sys.dm _ exec _ requests.

With the introduction of the catalog views and DMOs, Microsoft took the opportunity

to simplify naming conventions and standardize them across both sets of new views.

So, for example, the uid column in the compatibility views is universally represented as

schema _ id in the catalog views and DMOs. Likewise, spid becomes session _ id;

dbid becomes database _ id; indid becomes index _ id, and so on. What DBAs

appreciate most is the consistency. For example, we know that if we wish to obtain the

name of an object, database, index, column, and so on, from a catalog view or DMO, we

need to query for the name column, regardless of the speciﬁc target view.

And so to the DMOs; rumor has it that Microsoft never intended to release them to

the general public. The story goes that they were created internally to allow developers,

technicians, and testers at Microsoft to analyze and troubleshoot issues during the design

phase of SQL Server 2005, and for post-deployment troubleshooting during support calls.

The exact truth of this story is open to debate, but ultimately, and thankfully, the

DMOs were exposed to the end-users, though one could argue that the sometimes

sparse documentation and the confusing interfaces that some of the objects provide,

Chapter 1: Using Dynamic Management Objects

hint strongly at an original intention to keep them a deep, dark secret, for use only by the

Microsoft CAT, CSS, and PSS teams.

The DMOs expose a sometimes dizzying array of information; the original

sysprocesses system view, for example, has essentially been normalized, many

new DMOs have been added, and many new columns made available. As the database

engine becomes better and better instrumented, so the amount of data available about

the engine and the work it is doing, will continue to grow.

The added complexity of stitching together data from a disparate array of DMOs,

coupled with initial confusion regarding which columns are exposed where, has led some

DBAs to liken querying DMOs to "collecting mystic spells." It's certainly true that DMO

queries look a lot more complex and tend to have many more joins than equivalent

queries against sysprocesses, but the problem with the latter was that, because of

changes to the engine, a huge number of rows could be returned for a single spid,

making the data very hard to interpret. The big payback with the DMO is that, for a

little added querying complexity, the "normalization" process has made the data that the

DMOs return much easier to analyze and understand, as we hope this book will prove.

Furthermore, catalog and compatibility views are generally siloed by object type: sys.

tables, for example, is the catalog view that pertains to information speciﬁcally (and

narrowly) restricted to table objects in a database. The DMOs, on the other hand, relate

to speciﬁc ranges or topics about which we may need to obtain information. Need to know

what sessions are currently active? Go to sys.dm _ exec _ sessions. Need to know

what requests they are currently running? Go to sys.dm _ exec _ requests.

In some ways, it helps to think of DMOs as "system views of system views." They pull

together into a logical unit columns sourced from multiple catalog and compatibility

views, plus others, in order to provide insight into speciﬁc activities that are occurring on

a SQL Server instance at any point in time.

剩余336页未读，继续阅读

qdjch

粉丝: 0
资源: 6

SQL Server性能调优：利用Dynamic Management Views

eBook_Workshop_v1.5

eBOOK_GSP_graphsignal_WithinRange_eBOOK_GSP.zip

SELE为我创建java实体类 CT ebook_category.id, ebook_category.`name` FROM ebook_category

用C++语言直接写一个代码不要复述文字可以创建一个派生类 EBook，继承自 Book 类，并增加一些新的成员变量，如电子书的文件格式等。这个类中也可以包含构造函数、析构函数等成员函数。

使用pandas库来读取E:\当当.xls文件，并使用matplotlib库进行可视化,画出电子书个数占总数数量的圆环图，并注明x,y轴，标题,标签

用python pansel读取E:\\爬取\\爬取.xls文件，画你们离开以前（一本治愈两代人的生命自度书）这本书的售价与电子书价格的占比圆环图，并注明标题，标签

写代码：试编写合适的类Ebook，实现一个电话通讯录，内含姓名和电话号码成员，可以按姓名拼音顺序将通讯录排序，设计必要的数据进行测试。

最新资源