没有合适的资源?快使用搜索试试~ 我知道了~
首页《意外DBA的SQL Server故障排除指南》:提升技能与解决问题
《意外DBA的SQL Server故障排除指南》:提升技能与解决问题
需积分: 3 1 下载量 100 浏览量
更新于2024-07-18
收藏 2.3MB PDF 举报
"《SQL Server故障排除指南》是一本专为那些因意外或非自愿成为数据库管理员(DBA)的人而编写的实用手册,目标读者是那些在工作中突然接手SQL Server管理职责,但缺乏专业背景的系统管理员。这本书涵盖了广泛的故障排除概念和方法,无论是经验丰富的DBA还是新手,都能从中受益匪浅。 作者Jonathan Kehayias和Ted Krueger深入浅出地探讨了如何识别和解决SQL Server常见问题,提供了一系列实用技巧,旨在帮助读者提高SQL Server的管理和维护效率。书中内容不仅针对初学者,也适合于那些希望扩展技能、提升故障处理能力的专业人员。例如,章节可能包括性能优化(如HighPerformanceSQLServer部分)、故障诊断技术、集群和高可用性(AGuidefortheAccidentalDBA)等方面的知识。 该书由Paul Randal撰写序言,由Gail Shaw进行技术审阅,确保了内容的专业性和准确性。ISBN号码为978-1-906434-77-9,版权信息表明作者享有本书的权益,并受1988年版权、设计和专利法保护。未经出版商事先书面许可,任何未经授权的复制、存储、检索或传播行为都将被视为违法行为,可能会导致法律诉讼和民事赔偿责任。 《SQL Server故障排除指南》于2011年9月首次由Simple Talk Publishing公司出版,所有权利保留。购买者需遵守不得以任何形式(电子、机械、复印、录音等)未经许可进行复制或再销售的条款,以尊重作者的知识产权和市场规则。这是一本既能提升读者技术能力,又能避免因错误操作引发问题的重要参考资料。"
资源详情
资源推荐
![](https://csdnimg.cn/release/download_crawler_static/10827850/bg10.jpg)
16
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
offer 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 offers, 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 first 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.
![](https://csdnimg.cn/release/download_crawler_static/10827850/bg11.jpg)
17
The art of taming an unruly SQL Server is the art of gathering the various pieces of
information that you need, and then assembling the "puzzle" so that you have a complete
picture of what is going on inside of a server, and a true understanding of the real root
cause of the problem.
The following eight chapters cover the areas in which I see problems arising with
alarming regularity: disk I/O, high CPU, memory mismanagement, missing indexes,
blocking and deadlocking, full transaction logs, and accidentally lost data.
In each case, I describe the most common problems, why they occur, how they can be
diagnosed, using tools such as the Performance Monitor counters, Dynamic Management
Views, server-side tracing, and so on, and how to fix them.
By applying the basic steps that I use daily to troubleshoot these performance problems,
I hope that you, too, can begin to solve the performance problems in your environment,
faster and more accurately.
Who is this book for?
The primary audience for this book is anyone who has found themselves in charge of
SQL Server "by accident;" in other words, the accidental/involuntary DBAs and system
administrators who have had guardianship of SQL Server databases added to their roles,
because there is a SQL Server in their environment, and someone has got to look after it.
However, the troubleshooting concepts and methodologies covered in this book can be
applied by anyone, including seasoned database administrators, to identify and resolve
common problems and learn new tricks for managing and maintaining SQL Server.
Anyone who has ever posted questions on one of the community forums will probably
learn something new from this book.
![](https://csdnimg.cn/release/download_crawler_static/10827850/bg12.jpg)
18
Code Examples
Throughout this book are scripts demonstrating various ways to gather diagnostic data,
and there are a few places where presentation of the full code was deferred, due to space
restrictions. All the code you need to try out the examples in this book can be obtained
from the following URL:
:.-.RGBJKT-
SQLS_C.
All examples should run on all versions of SQL Server from SQL Server 2005 upwards,
unless specified otherwise.
![](https://csdnimg.cn/release/download_crawler_static/10827850/bg13.jpg)
19
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 specific methodology for troubleshooting problems with SQL Server is
hard because, of course, the exact route taken to solve the problem will depend on the
specific 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 Effect 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 five 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 offer 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,
![](https://csdnimg.cn/release/download_crawler_static/10827850/bg14.jpg)
20
Chapter 1: A Performance Troubleshooting Methodology
including wait statistics, virtual file statistics, SQL Server-related performance counters,
and plan cache analysis.
Defining a Troubleshooting Methodology
As I noted in the introduction to this chapter, defining a troubleshooting methodology
is hard, because the actual methodology that I apply depends entirely on the specific
problem that I am trying to troubleshoot for a specific 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 first 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
buffer 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 buffer pool (and so physical
I/O is needed to retrieve them). SQL Server must allocate a buffer 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 suffering. 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页未读,继续阅读
![epub](https://img-home.csdnimg.cn/images/20210720083646.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://profile-avatar.csdnimg.cn/default.jpg!1)
sc020512
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
我的内容管理 展开
我的资源 快来上传第一个资源
我的收益
登录查看自己的收益我的积分 登录查看自己的积分
我的C币 登录后查看C币余额
我的收藏
我的下载
下载帮助
![](https://csdnimg.cn/release/wenkucmsfe/public/img/voice.245cc511.png)
最新资源
- 新型矿用本安直流稳压电源设计:双重保护电路
- 煤矿掘进工作面安全因素研究:结构方程模型
- 利用同位素位移探测原子内部新型力
- 钻锚机钻臂动力学仿真分析与优化
- 钻孔成像技术在巷道松动圈检测与支护设计中的应用
- 极化与非极化ep碰撞中J/ψ的Sivers与cos2φ效应:理论分析与COMPASS验证
- 新疆矿区1200m深孔钻探关键技术与实践
- 建筑行业事故预防:综合动态事故致因理论的应用
- 北斗卫星监测系统在电网塔形实时监控中的应用
- 煤层气羽状水平井数值模拟:交替隐式算法的应用
- 开放字符串T对偶与双空间坐标变换
- 煤矿瓦斯抽采半径测定新方法——瓦斯储量法
- 大倾角大采高工作面设备稳定与安全控制关键技术
- 超标违规背景下的热波动影响分析
- 中国煤矿选煤设计进展与挑战:历史、现状与未来发展
- 反演技术与RBF神经网络在移动机器人控制中的应用
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
![](https://img-home.csdnimg.cn/images/20220527035711.png)
![](https://img-home.csdnimg.cn/images/20220527035711.png)
![](https://img-home.csdnimg.cn/images/20220527035111.png)
安全验证
文档复制为VIP权益,开通VIP直接复制
![](https://csdnimg.cn/release/wenkucmsfe/public/img/green-success.6a4acb44.png)