Performance Tuning with SQL Server Dynamic Management Views
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 de-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.
Table of contents
- Using Dynamic Management Objects
- Connections, Sessions and Requests
- Query Plan Metadata
- Transactions
- Indexing Strategy and Maintenance
- Physical Disk Statistics and Utilization
- OS and Hardware Interaction
| Pages | 326 |
|---|---|
| Size | 28.3 MB |
| File type | |
| Downloads | 272 |
| Created | 2022-02-03 |
| License | For personal or educational use |
| Author(s) | Louis Davidson, Tim Ford |
Others sql tutorials
Others related eBooks about Performance Tuning with SQL Server Dynamic Management Views
-
Microsoft Access course
This PDF tutorial shows how to construct a complex application in Microsoft Access ,free training lesson under 154 pages by Soren Lauesen designated to the intermediate level-user....
-
Graph Databases
Download free course Graph Databases, pdf file on 238 pages by Ian Robinson, Jim Webber, Emil Eifrem....
-
SQL queries Tutorial
Download free SQL tutorial course in PDF, training file in 62 chapters and 166 pages. Free unaffiliated ebook created from Stack OverFlow contributor....
-
Quick Guide to SQL Server Management Studio
This PDF tutorial will teachs you how to install and use SQL Server Management Studio from Home using VPN,it's a free training courses for beginners...
-
Introduction to Access 2010
Start to learn Microsoft Access 2010 with this free PDF tutorial and more about new useful features insides including designing databases and queries,training document for download under 24 ....
-
Graph Databases For Beginners
Download free course Graph Databases For Beginners, pdf file on 46 pages by Bryce Merkl Sasaki, Joy Chao, Rachel Howard....
-
PostgreSQL tutorial
Download free PostgreSQL tutorial course in PDF, training file in 29 chapters and 93 pages. Free unaffiliated ebook created from Stack OverFlow contributor....
-
MySQL Notes for Professionals
MySQL's popularity has brought a flood of questions about how to solve specific problems, and that's where this MySQL Notes for Professionals is essential. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out exampl...
-
Oracle Database Notes for Professionals
This book is the definitive guide to undocumented and partially-documented features of the Oracle Database server. It helps you learn to apply the right solution at the right time, about avoiding risk, about making robust choices related to Oracle databases. It is packed with of experience over deca...
-
Graph Databases, 2nd Edition
Discover how graph databases can help you manage and query highly connected data. With thi..., download free Graph Databases tutorial in PDF (238 pages) created by ....