Stirk I. - SQL Server DMVs in Action [2011, PDF, ENG] + Code torrent

Книги и журналы » Компьютерная литература » СУБД

SQL Server DMVs in Action
Better Queries with Dynamic Management Views

Год: 2011 (May)
Автор: Ian W. Stirk
Издательство: Manning Publications
ISBN: 9781935182733
Язык: Английский
Формат: PDF
Качество: Изначально компьютерное (eBook)
Интерактивное оглавление: Да
Количество страниц: 355
Описание: SQL Server DMVs in Action is a practical guide that shows you how to obtain, interpret, and act on the information captured by DMVs to keep your system in top shape. The samples provided in this book will help you master DMVs and also give you a tested, working, and instantly reusable SQL code library.

SQL Server DMVs in Action shows you how to obtain, interpret, and act on the information captured by DMVs to keep your system in top shape. The over 100 code examples help you master DMVs and give you an instantly reusable SQL library. You'll also learn to use Dynamic Management Functions (DMFs), which provide further details that enable you to improve your system's performance and health.

What's Inside:
•Many practical solutions
•How to correct missing indexes
•What's slowing down your queries
•What's compromising concurrency
•Much more
[spoiler="Примеры страниц"]

[/spoiler]
[spoiler="Оглавление"]
preface
acknowledgements
about this book

Part 1 Starting the journey

Chapter 1 The Dynamic Management Views gold mine
What are Dynamic Management Views?
The problems DMVs can solve
DMV examples
Preparing to use DMVs
DMV companions
Working with DMVs
Summary

Chapter 2 Common patterns
Reducing blocking
Using CROSS APPLY
Restricting output to a given database
Restricting output by using the TOP command
Creating an empty temporary table structure
Looping over the databases on a server
Retrieving a query’s cached plan and SQL text
Extracting the Individual Query from the Parent Query
Identifying the database used by ad hoc queries
Calculating DMV changes
Reading cached plans
Building dynamic SQL
Printing the content of large variables
Common terms and acronyms
Known problems that may affect the scripts
Summary

Part 2 DMV discovery

Chapter 3 Index DMVs
The importance of indexes
Costly missing indexes
Unused indexes
High-maintenance indexes
Most-frequently used indexes
Fragmented indexes
Indexes used by a given routine
Databases with most missing indexes
Completely unused indexes
Your statistics
A holistic approach to managing indexes
Summary

Chapter 4 Improving poor query performance
Understanding executed queries
Finding a cached plan
Finding where a query is used
Long-running queries
Queries that spend a long time being blocked
CPU-intensive queries
I/O-hungry queries
Frequently executed queries
The last run of a query
Summary

Chapter 5 Further query improvements
Queries with missing statistics
Finding queries that have missing indexes
Finding queries that have implicit data type conversions
Finding queries that have table scans
Slower-than-normal queries
Unused stored procedures (2008 only)
Looking for SQL queries run during a specific interval
Relationships between DMV snapshot deltas
Currently running queries
Recompiled queries
Summary

Chapter 6 Operating system DMVs
Understanding server waits
Identifying your most common waits
Identifying your most common waits—snapshot version
Identifying why queries wait
Queries that are waiting
Finding what’s blocking running SQL
SQL Server performance counters
Effect of running SQL queries on the performance counters
How performance counters and wait states relate
SQL queries and how they change the performance counters and wait states
Correlating wait states and performance counters
Capturing DMV data periodically
Summary

Chapter 7 Common Language Runtime DMVs
Introducing the CLR
A simple CLR example
.NET Framework performance concerns
Time-consuming CLR queries
Queries spending the most time in the CLR (snapshot version)
Relationships between CLR DMVs and other DMVs
Getting information about SQL Server CLR integration
Getting information about your SQL CLR assemblies
Summary

Chapter 8 Resolving transaction issues
Transaction overview
A simple transaction-based case study
Locks, blocks, and deadlocks
The ACID properties of transactions
Transaction isolation levels
Sessions, connections, and requests
Finding locks
Identifying the contended resources
Identifying inactive sessions with open transactions
Waiting due to transaction locks
Queries waiting for more than 30 seconds
Lock escalation
How to reduce blocking
How to reduce deadlocks
Summary

Chapter 9 Database-level DMVs
Space usage in tempdb
Session usage in tempdb
Task usage in tempdb
Tempdb recommendations
Index contention
Summary

Chapter 10 The self-healing database
Self-healing database
Recompiling slow routines
Automatically rebuild and reorganize indexes
Intelligently update statistics
Automatically updating a routine’s statistics
Automatically implement missing indexes
Automatically disable or drop unused indexes
Summary

Chapter 11 Useful scripts
Viewing everyone’s last-run SQL query
A generic performance test harness
Determining the impact of a system upgrade
Estimating the finishing time of system jobs
Get system information from within SQL Server
Viewing enabled Enterprise features (2008 only)
Who’s doing what and when?
Finding where your query really spends its time
Memory usage per database
Memory usage by table or index
Finding I/O waits
A simple lightweight trace utility
Some best practices
Where to start with performance problems
Summary

index
[/spoiler]
Доп. информация: Who Should Read It: This book is written for DBAs and developers.