《意外DBA的SQL Server故障排除指南》：提升技能与解决问题

需积分: 3 100 浏览量更新于2024-07-18 收藏 2.3MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源推荐

Introduction

I've spent much of the last six years of my working life trying to help people solve SQL

Server-related performance problems, either hands-on, at client sites in my capacity as a

consultant, or on various online community forums, answering people's questions.

Over that time I've been exposed to a few weird and wonderful SQL Server issues but,

mainly, have seen the same problems, and the same confusions, crop up time and again. It

was only a matter of time before I leapt to the foolhardy conclusion that I ought to write

a book to explain what the most common problems were, why they occurred, and to

oﬀer sensible, practical solutions, aimed squarely at removing the root cause of the issue,

rather than simply "papering over the cracks."

It turned out to be a much more daunting task than I imagined, for a number of reasons.

Firstly, this is, by its very nature, a fairly broad-ranging book, covering topics stretching

from CPU issues, to memory management, to missing indexes, and full transaction logs.

Secondly, it is amazing how complex some of the simplest topics become when you try

to put them down in text. Thirdly, technology changes rapidly, and the troubleshooter,

and the advice he or she oﬀers, must adapt accordingly. There were several cases in

the writing of this book, where either a technology change meant I had to go back and

adapt my original advice, or where my own opinion of the content and troubleshooting

methodologies had evolved to the point where certain sections had to be completely

rewritten.

Despite the occasionally bumpy road, what I hope I've achieved is a book that covers

clearly and concisely the most common problems associated with the currently supported

versions of SQL Server, namely SQL Server 2005, 2008, and 2008 R2. The ﬁrst chapter

explains my basic approach to performance troubleshooting, and the tools I use. It

stresses, in particular, how rare it is that a problem can be diagnosed by looking at just a

single data point.

Chapter 1: A Performance

Troubleshooting Methodology

Knowing where to start is the toughest part of solving a problem. As a Senior Database

Administrator, I prided myself on being able to pinpoint the root cause of problems in

my servers, and quickly restore services to normal working order. The ability to do this is

partly down to a sound knowledge of your SQL Server environment, partly to having the

right tools and scripts, and partly to what you learn to look out for, based on hard-earned

lessons of the past.

Nailing down a speciﬁc methodology for troubleshooting problems with SQL Server is

hard because, of course, the exact route taken to solve the problem will depend on the

speciﬁc nature of the problem and the environment. One of the keys to accurate trouble-

shooting is not only collecting and examining all of the relevant pieces of information,

but also working out what they are telling you, collectively. There is a famous old proverb,

recorded in John Heywood's Dialogue Containing the Number in Eﬀect of All the Proverbs in

the English Tongue, that sums this up very well: I see, yet I cannot see the wood for the trees.

If you collect and examine individually ﬁve separate pieces of performance data, it's

possible that each could send you down a separate path. Viewed as a group, they will

likely lead you down the sixth, and correct, path to resolving the issue. If there is one

take-away from this chapter, as well as this book, it should be that focusing on a single

piece of information alone can often lead to an incorrect diagnosis of a problem.

What I attempt to oﬀer in this chapter is not a set of stone tablets, prescribing the exact

steps to take to resolve all SQL Server problems, but rather a basic approach and set of

tools that have served me well time and again in the six years I've spent working with

SQL Server, troubleshooting performance problems. It covers a high-level description

of my basic approach, followed by more detailed sections on each of my areas of focus,

Chapter 1: A Performance Troubleshooting Methodology

including wait statistics, virtual ﬁle statistics, SQL Server-related performance counters,

and plan cache analysis.

Deﬁning a Troubleshooting Methodology

As I noted in the introduction to this chapter, deﬁning a troubleshooting methodology

is hard, because the actual methodology that I apply depends entirely on the speciﬁc

problem that I am trying to troubleshoot for a speciﬁc environment. However, my basic

approach, and the tools I use, remain constant, regardless of whether the problem is

users complaining of slow performance, or if I am just performing a standard server

health check.

When I am examining a server for the ﬁrst time, I need to establish a picture of its general

health, and there are a number of items on which I'll focus in order to obtain this picture.

For each piece of information I collect, I'll be examining it in relation to the previous

data points, in order to validate or disprove any previous indicators as to the nature of

the problem.

Fairly early on in any analysis, I'll take a look at the wait statistics, in the sys.d m _os_

wait_stats Dynamic Management View (DMV), to identify any major resource waits in

the system, at the operating system level. Let's say I identify very high PAGEIOLATCH_SH

waits, which indicates that sessions are experiencing delays in obtaining a latch for a

buﬀer page. This happens when lots of sessions, or maybe one session in particular, are

requesting a lot of data pages that are not available in the buﬀer pool (and so physical

I/O is needed to retrieve them). SQL Server must allocate a buﬀer page for each one, and

place a latch on that page while it's retrieved from disk. The bottleneck here is disk I/O;

the disk subsystem simply can't return pages quickly enough to satisfy all of the page

requests, and so sessions are waiting for latches, and performance is suﬀering. However,

this does not necessarily mean that a slow disk subsystem is the cause of the bottleneck; it

may simply be the victim of excessive I/O caused by a problem elsewhere in the system.

剩余346页未读，继续阅读

sc020512

粉丝: 0
资源: 1

《意外DBA的SQL Server故障排除指南》：提升技能与解决问题

sql问题

MySQL Troubleshooting

Troubleshooting Performance Problems in SQL Server 2008

SQL Server Query Store

SQL Profiler

500 : Internal Server Error

no server profilers registerd

cannot execute request on any known server

Failed to load resource: the server responded with a 9527/api/users/login:1 status of 500 (Internal Server Error)

Cannot write historyserver pid /tmp/hadoop-HX-historyserver.pid

businessId = 1002exchange data with server error

500 Server Error for HTTP GET "/"

troubleshooting是什么意思

unable to verify your captcha response. please visit https://docs.github.com/articles/troubleshooting-connectivity-problems/#troubleshooting-the-captcha for troubleshooting information.

Unable to start LiveReload server

Could not create connection to database server. Attempted reconnect 3 times

The connection to the server localhost:8080 was refused - did you specify the right host or port?

mount.nfs: access denied by server while mounting

Number of retries on next server exceeded max 1 retries

WebSocketServer java.io.EOFException

最新资源