Sql Server

High Performance SQL Server

Troubleshooting SQL Server
A Guide for the Accidental DBA

Jonathan Kehayias and Ted Krueger

Foreword by Paul Randal
Technical review by Gail Shaw
ISBN: 978-1-906434-77-9

Troubleshooting SQL Server
A Guide for the Accidental DBA
By Jonathan Kehayias and Ted Krueger

With a Foreword from Paul S. Randal

First published by Simple TalkPublishing September 2011

Copyright Jonathan Kehayias and Ted Krueger 2011
ISBN 978-1-906434-77-9
The right of Jonathan Kehayias and Ted Krueger 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 retrievalsystem, 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 tradeor otherwise, be lent, re-sold,
hired out, or otherwise circulated without the publisher's prior consent in any form other than which it is
published and without a similar condition including this condition being imposed on the subsequent
publisher.
Edited by Tony Davis
Technical Review and Additional Material: Gail Shaw
Cover Image by Andy Martin
Typeset & Designed by Matthew Tye& Gower Associates

Table of Contents
Introduction ................................................................................... 16
Who is this book for? ............................................................................................................. 17
Code Examples....................................................................................................................... 18

Chapter 1: A Performance Troubleshooting Methodology.......... 19
Defining a Troubleshooting Methodology........................................................................ 20
Wait Statistics: the Basis for Troubleshooting .................................................................. 23
Virtual File Statistics..............................................................................................................28
Performance Counters ..........................................................................................................30
Plan Cache Usage ...................................................................................................................38Summary................................................................................................................................. 40

Chapter 2: Disk I/O Configuration ................................................42
Disk Configuration: Basic Considerations .........................................................................43
Disk size vs. disk throughput........................................................................................... 44
Random versus sequential I/O..........................................................................................45
Choosing the Right RAID Level .......................................................................................... 46
A brief overview of RAID configurations....................................................................... 48
Disk size and throughput considerations .......................................................................54
Workload considerations ...................................................................................................57
Direct Attached Storage vs. Storage Area Networks......................................................... 61
Direct Attached Storage...