Performance Tuning with SQL Server Dynamic Management Views (Paperback)

Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions.

Why, then, aren't all DBAs using them? Why do many DBAs continue to ignore them in favour of "tried and trusted" tools such as sp_who2, DBCC OPENTRAN, and so on, or make do with the "ready made" reports built into SSMS? Why do even those that do use the DMVs speak wistfully about "good old sysprocesses"?

There seem to be two main factors at work. Firstly, some DBAs are simply unaware of the depth and breadth of the information that is available from the DMvs, or how it might help them troubleshoot common issues. This book investigates all of the DMVs that are most frequently useful to the DBA in investigating query execution, index usage, session and transaction activity, disk IO, and how SQL Server is using or abusing the operating system.

Secondly, the DMVs have a reputation of being difficult to use. In the process of exposing as much useful data as possible, sysprocesses has been normalized, and many new views and columns have been added. This fact, coupled with the initially-baffling choices of what columns will be exposed where, has lead to some DBAs to liken querying DMVs to "collecting mystic spells".

In fact, however, once you start to write your own scripts, you'll see the same tricks, and similar join patterns, being used time and again. As such, a relatively small core set of scripts can be readily adapted to suit any requirement. This book is here to de-mystify the process of collecting the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own systems, including how to:

• Root out the queries that are causing memory or CPU pressure on your system
• Investigate caching, and query plan reuse
• Identify index usage patterns
• Track fragmentation in clustered indexes and heaps
• Get full details on blocking and blocked transactions, including the exact commands being executed, and by whom.
• Find out where SQL Server is spending time waiting for resources to be released, before proceeding
• Monitor usage and growth of tempdb

The DMVs don't make existing, built-in, performance tools obsolete. On the contrary, they complement these tools, and offer a flexibility, richness and granularity that are simply not available elsewhere. Furthermore, you don't need to master a new GUI, or a new language in order to use them; it's all done in a language all DBAs know and mostly love: T-SQL.

動態管理視圖（DMV）是資料庫管理員（DBA）疑難排解工具中重要且有價值的補充，揭示了以往無法獲取的關於資料庫會話和交易的底層活動的資訊。

那麼，為什麼不是所有的DBA都在使用它們呢？為什麼許多DBA繼續忽視它們，而偏好於“經過驗證和可靠”的工具，如sp_who2、DBCC OPENTRAN等，或者只使用SSMS中內建的報告？為什麼即使那些使用DMV的人也會對“好舊的sysprocesses”表示留戀呢？

似乎有兩個主要因素在起作用。首先，一些DBA對DMV提供的深度和廣度的資訊根本不知情，也不知道它們如何幫助他們解決常見問題。本書將探討對DBA在查詢執行、索引使用、會話和交易活動、磁碟IO以及SQL Server如何使用或濫用操作系統方面最常用的DMV。

其次，DMV被認為很難使用。為了盡可能公開有用的資料，sysprocesses已被規範化，並添加了許多新的視圖和列。這一事實，再加上最初令人困惑的列在哪裡公開的選擇，導致一些DBA將查詢DMV比作“收集神秘咒語”。

事實上，一旦您開始撰寫自己的腳本，您將看到相同的技巧和類似的連接模式一次又一次地被使用。因此，一個相對較小的核心腳本集可以輕鬆地適應任何需求。本書旨在揭示收集解決SQL Server問題所需的資訊的過程。它將強調您需要掌握的核心技術和“模式”，並提供一組核心腳本，供您在自己的系統中使用和適應，包括如何：

- 找出導致系統內存或CPU壓力的查詢
- 調查緩存和查詢計劃的重複使用
- 識別索引使用模式
- 追蹤叢集索引和堆積的碎片化情況
- 獲取有關阻塞和被阻塞交易的詳細信息，包括正在執行的確切命令和執行者
- 查明SQL Server在等待資源釋放之前花費的時間
- 監控tempdb的使用和增長情況

DMV並不使現有的內建性能工具變得過時。相反，它們補充了這些工具，並提供了一種靈活性、豐富性和細粒度，其他地方根本無法獲得。此外，您無需掌握新的GUI或新的語言來使用它們；一切都在所有DBA都熟悉且大多喜愛的T-SQL語言中完成。