Performance Tuning using SQL Server Dynamic Management Views

Topics: Microsoft SQL Server, SQL, Microsoft Pages: 326 (79421 words) Published: October 27, 2014
High Performance SQL Server

Performance Tuning
with SQL Server Dynamic
Management Views
Louis Davidson and Tim Ford

Performance Tuning
using SQL Server Dynamic
Management Views
By Louis Davidson and Tim Ford

First published by Simple Talk Publishing 2010

Copyright Louis Davidson and Tim Ford 2010
ISBN 978-1-906434-46-5
The right of Louis Davidson and Tim Ford to be identified as the authors of this work has been asserted by them in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act in relation to this publication may be liable to criminal prosecution and civil claims for damages. This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold, hired out, or otherwise circulated without the publisher's prior consent in any form other than that in which it is published and without a similar condition including this condition being imposed on the subsequent publisher.

Editor: Tony Davis
Technical Review and Additional Material: Glenn Berry
Cover Image: Tim Ford
Typeset & Designed: Matthew Tye & Gower Associates

Table of Contents
Introduction.................................................................................... 12 Code Examples........................................................................................................................ 15

Chapter 1: Using Dynamic Management Objects........................ 16 Compatibility Views, Catalog Views, and DMOs............................................................... 17 DMO Security and Permissions............................................................................................ 21 Performance Tuning with DMOs.........................................................................................22 Navigating through the DMOs...................................................................................... 24 Point-in-time versus cumulative data........................................................................... 26 Beware of the watcher effect.......................................................................................... 29 Using DMOs with other performance tools................................................................ 29 Summary................................................................................................................................... 32

Chapter 2: Connections, Sessions and Requests......................... 33 Sysprocesses versus DMOs.................................................................................................... 35 Connections and Sessions..................................................................................................... 37 sys.dm_exec_connections...............................................................................................38 sys.dm_exec_sessions......................................................................................................40 Who is connected?........................................................................................................... 42 Who is connected by SSMS?........................................................................................... 44 Session-level settings........................................................................................................45 Logins with more than one session.............................................................................. 47 Identify sessions with context switching..................................................................... 48 Identify inactive...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Essay about Sql Server
  • A performance Management Essay
  • Back and Restore Using Sql Server Essay
  • Strategic Management And Organisational Dynamics Essay
  • Microsoft SQL server Essay
  • sql server notes Essay
  • Essay about Performance Management systems
  • Support performance management Research Paper

Become a StudyMode Member

Sign Up - It's Free