Performance Tuning with SQL Server Dynamic Management Views



Download free course Performance Tuning with SQL Server Dynamic Management Views, pdf file on 326 pages by Louis Davidson, Tim Ford.
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 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 : PDF
Downloads: 84
Created: 2022-02-03
License: For personal or educational use
Author(s): Louis Davidson, Tim Ford
Performance Tuning with SQL Server Dynamic Management Views

Others sql Tutorials

Protecting SQL Server Data

MySQL – Create Database free PDF

MySQL tutorial for professionals

Download PLSQL tutorial in pdf

Structured Query Language

Others related eBooks about Performance Tuning with SQL Server Dynamic Management Views

How To Manage a Redis Database

This book aims to provide an approachable introduction to Redis concepts by outlining many..., download free Redis Database tutorial in PDF (95 pages) created by Mark Drake ....

Oracle Advanced PL/SQL Developer Professional Guide

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural ..., download free PL/SQL tutorial in PDF (440 pages) created by Saurabh K. Gupta ....

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...

RavenDB in Action

The data you encounter in the real world is usually easier to think of as objects or docum..., download free RavenDB tutorial in PDF (221 pages) created by Itamar Syn-Hershko ....

Guide to NoSQL with Azure Cosmos DB

Download free course Guide to NoSQL with Azure Cosmos DB, pdf file on 205 pages by Gaston C. Hillar , Daron Yöndem....

PostgreSQL for Beginners

PostgreSQL for Beginners book is intended for those who only start getting acquainted with..., download free PostgreSQL tutorial in PDF (142 pages) created by Pavel Luzanov ....

MySQL – Create Database free PDF

Download free course MySQL – Create Database free PDF, pdf file on 3 pages by tutorialkart.com....

Learning MySQL

Download free course intituled Learning MySQL, a PDF document created by StackOverflow Documentation, a 300-page tutorial on the basics of this language to learn and manipulate databases created with MySQL....

Tutorial Oracle Database 11g: SQL Fundamentals in PDF

Download tutorial in PDF about Oracle Database 11g: SQL Fundamentals, free training document for beginners....

Introduction to SQL Server 2008

This pdf course is designed to teach you Microsoft SQL Server. This is a practical training to learn the basics of the Microsoft Database Management System, in its 2008 version....