Stirk I. - SQL Server DMVs in Action + Code


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



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


Chapter 2 Common patterns

Reducing blocking


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


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


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


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


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


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


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


Chapter 9 Database-level DMVs

Space usage in tempdb

Session usage in tempdb

Task usage in tempdb

Tempdb recommendations

Index contention


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


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



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