Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs, and stalwarts of the SQLServerCentral and MSDN community forums, provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".

1365-Troubleshooting_SQLServer_200px.gif

Troubleshooting SQL Server: A Guide for the Accidental DBA

Jonathan Kehayias and Ted Krueger

Available now from Amazon.com.

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.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , , , , , ,

  • 80642 views

  • Rate
    [Total: 69    Average: 4.1/5]
  • calin.oprea

    whooohaaa
    Now this IS THE MUST READ for me, at least.

  • vkshah212

    For PDF file
    Please send your PDF file for Troubleshooting SQL Server: A Guide for the Accidental DBA
    in my address
    vkshah212@yahoo.co.in

    Thanks ,

  • Nadreck

    SAMPLE CODE MISSING
    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:

    http://www.simple-talk.com/RedGateBooks/JonathanKehayias/TroubleshootingSQL-Server_Code.zip

    simple come up with File Not Found.

    Any idea when this will be made avaliable?

  • Tony Davis

    re: Sample Code
    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:

    http://www.simple-talk.com/RedGateBooks/JonathanKehayias/TroubleshootingSQLServer_Code.zip

    Cheers,
    Tony

  • dominichart88

    i want it
    i want it this book

  • genety2004@gmail.com

    About The FIFO Stock Inventory SQL
    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

  • JenMidnightDBA

    Errata?
    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?

  • smitp11

    Permission to print the ebook
    Hi,

    Please could you give me permission to print the ebook?

    Thanks,

    Patrick Smith

    • Dave Convery

      Re: Permission to print the ebook
      Hi Patrick,

      Are you just looking to print a copy for personal use?