Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Troubleshooting SQL Server: A Guide for the Accidental DBA

By Jonathan Kehayias,

High Performance SQL Server

SQL Server Team-based DevelopmentTroubleshooting SQL Server: A Guide for the Accidental DBA by Jonathan Kehayias and Ted Krueger


Download eBook (PDF): Free
Buy Printed Book: $29.99
Buy Kindle Edition: Coming Soon


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


Editorial Review

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…

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.


Publisher: Red Gate Books

View Related Books:

Total article views: 19621 | Views in the last 30 days: 232
 
Related Articles
FORUM

deadlock

deadlock

ARTICLE

Tracing Deadlocks

New Author! Deadlocks aren't the most common problem but they happen they can really degrade perform...

FORUM

DeadLock

DeadLock

FORUM

Deadlocks

How many deadlocks is normal?

FORUM

Deadlock - SQL Server 2000

Deadlocks

Tags
books    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones