Click here to monitor SSC
  • Av rating:
  • Total votes: 66
  • Total comments: 9

Troubleshooting SQL Server: A Guide for the Accidental DBA

29 September 2011
Troubleshooting SQL Server

Troubleshooting SQL Server: A Guide for the Accidental DBA

Jonathan Kehayias and Ted Krueger

Available now from

Get your free eBook download here.

We spend much of our working life helping solve SQL Server-related performance problems, hands-on, during consulting, or on online forums. We've seen a few weird-and-wonderful issues but, mainly, it’s the same problems and misconceptions time-and-again. This is our attempt to describe, diagnose, and solve the most common problems with SQL Server 2005, 2008, and 2008 R2.

First, we explain a basic approach to troubleshooting, the essential tools, and how rare it is that a problem can be diagnosed by looking at a single data point. The art of troubleshooting SQL Server is the art of first gathering various pieces of information and then assembling the "puzzle" to reveal a complete picture of what is going on inside a server, and so the root of the problem. Next, we explore the areas in which problems arise with alarming regularity:

  • High Disk I/O –RAID misconfiguration, inadequate I/O throughput, poor workload distribution, SAN issues, disk partition misalignment and more
  • High CPU usage –insufficient memory, poorly written queries, inadequate indexing, inappropriate configuration option settings, and so on
  • Memory mismanagement – the advent of 64-bit SQL Server removes the memory allocation "shackles" placed on its 32-bit predecessors, but issues arising from incorrect memory configuration are still common
  • Missing indexes – arguably the number one cause of wasteful resource usage in SQL Server
  • Blocking – caused mainly by poorly designed databases that lack proper keys and indexing, and applications that apply needlessly restrictive transaction isolation levels
  • Deadlocking – covering the Bookmark Lookup deadlock, the Serializable Range Scan deadlock, the Cascading Constraint deadlock and more
  • Full transaction logs – lack of log backups, hefty index maintenance operations, long running transaction, problems with replication and mirroring environments, and more.
  • Accidentally-lost data – "oops, wrong database!" Let's hope you've got backups%hellip;

In each case, the book describes the most common problems, why they occur, and how they can be diagnosed using tools such as the Performance Monitor, Dynamic Management Views, server-side tracing, and more. Finally, it provides practical solutions for removing root causes, rather than "papering over the cracks".

The steps and techniques described are ones we use day-to-day to troubleshoot real SQL Server performance problems. With them, we hope you can solve performance problems quickly and accurately, and tame your unruly SQL Servers.

Jonathan Kehayias

Author profile:

Jonathan Kehayias is currently employed as a Principal Consultant and Trainer for SQLskills, one of the best-known and most respected SQL Server training and consulting companies in the world. Jonathan is a SQL Server MVP and one of the few Microsoft Certified Masters for SQL Server 2008, outside of Microsoft. Jonathan frequently blogs about SQL Server, presents sessions at PASS Summit, SQLBits, SQL Connections and local SQL Saturday events, and has remained a top answerer of questions on the MSDN SQL Server Database Engine forum since 2007. Jonathan is a performance tuning expert for both SQL Server and hardware, and has architected complex systems as a developer, business analyst, and DBA. He also has extensive development (T-SQL, C#, and ASP.Net), hardware and virtualization design expertise, Windows expertise, Active Directory experience, and IIS administration experience. Outside of SQL Server, Jonathan is also a Drill Sergeant in the US Army Reserves and is married with two young children. On most nights he can be found at the playground, in a swimming pool, or at the beach with his kids. Jonathan can be found online as @SQLPoolBoy on Twitter, or through his blog (

Search for other articles by Jonathan Kehayias

Rate this article:   Avg rating: from a total of 66 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: whooohaaa
Posted by: calin.oprea (view profile)
Posted on: Monday, October 3, 2011 at 12:31 AM
Message: Now this IS THE MUST READ for me, at least.

Subject: For PDF file
Posted by: vkshah212 (view profile)
Posted on: Monday, October 3, 2011 at 2:54 AM
Message: Please send your PDF file for Troubleshooting SQL Server: A Guide for the Accidental DBA
in my address

Thanks ,

Posted by: Nadreck (view profile)
Posted on: Saturday, October 15, 2011 at 11:41 PM
Message: Book was released and SOLD at the 2011 Pass Conferance in Seattle

Not only is the website not been updated but the code samples which are listed to be at:

simple come up with File Not Found.

Any idea when this will be made avaliable?

Subject: re: Sample Code
Posted by: Tony Davis (view profile)
Posted on: Monday, October 17, 2011 at 5:59 AM
Message: Nadreck,

Sorry about the mistake in the URL. We'll get that corrected. However, if you simply omit the hyphen from the ZIP file name, it will work:


Subject: i want it
Posted by: dominichart88 (view profile)
Posted on: Tuesday, January 17, 2012 at 2:48 PM
Message: i want it this book

Subject: About The FIFO Stock Inventory SQL
Posted by: (view profile)
Posted on: Tuesday, April 17, 2012 at 6:09 AM
Message: I tried to copy the code posted about Fifo Stock inventory SQl , to MySQL database but it doesn't work for me, I think some of the key words like "NONCLUSTERED INDEX" dosn't work in MySQL it brings an error message. So is there any fifo code suitable for MYSQL database

Subject: Errata?
Posted by: JenMidnightDBA (view profile)
Posted on: Monday, June 4, 2012 at 3:43 PM
Message: I've been unable to find an errata page for this book. I'm specifically interested in the (possible) correction to the query in Listing 1.4, as the formula for avg_total_latency only takes writes into account. Wouldn't total latency be a combination of reads and writes?

Subject: Permission to print the ebook
Posted by: smitp11 (view profile)
Posted on: Tuesday, November 26, 2013 at 6:39 AM
Message: Hi,

Please could you give me permission to print the ebook?


Patrick Smith

Subject: Re: Permission to print the ebook
Posted by: Dave Convery (view profile)
Posted on: Wednesday, November 27, 2013 at 10:27 AM
Message: Hi Patrick, Are you just looking to print a copy for personal use?


Recommended site is a user-driven wiki which provides .NET developers with native method signatures, so they don't have to spend time writing them from scratch.

Check out!

Top Rated

SQL Server Source Control Basics
 For efficient team-based database development, and reliable and repeatable database deployments, source... Read more...

Tribal SQL
 Tribal SQL, is a reflection of how a DBA's core and long-standing responsibilities sit alongside new... Read more...

Book Review: Optimizing And Troubleshooting Hyper V Networking 2013
 If you want to learn how to optimize and troubleshoot Hyper-V, then this book, written by Microsoft... Read more...

Don't Just Roll the Dice - eBook Download
 Neil Davidson has created a short handbook with the theory, practical advice and case studies, to... Read more...

Captain Biplane: Interplanetary Airman
 Simple-Talk caters for the lighter side with its publication of 'Captain Biplane: Interplanetary... Read more...

Most Viewed

Troubleshooting SQL Server: A Guide for the Accidental DBA
 Three SQL Server MVPs, and stalwarts of the SQLServerCentral and MSDN community forums, provide... Read more...

Exchange 2010 - A Practical Approach
 Jaap's Practical Guide to Exchange Server 2010 draws upon all that experience to deliver an easy-to-use... Read more...

Brad's Sure Guide to SQL Server Maintenance Plans
 Brad's Sure Guide to Maintenance Plans shows you how to use the Maintenance Plan Wizard and Designer to... Read more...

SQL Server Hardware
 SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent... Read more...

Inside the SQL Server Query Optimizer
 This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.