SQL Server 2005性能优化与调优手册

5星 · 超过95%的资源需积分: 10 43 浏览量更新于2024-07-29 收藏 7.33MB PDF 举报

"Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook" 本书《Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook》由Ken England和Gavin Powell合著，是针对SQL Server 2005性能优化的重要参考资料。在SQL Server性能优化领域，这本书提供了深入的洞见和实用技巧，帮助读者理解和解决数据库性能问题。 SQL Performance是本书的核心关注点。SQL Server的性能优化涉及到多个方面，包括查询优化、索引设计、存储过程的优化、资源管理以及数据库架构的设计。以下是一些关键知识点： 1. **查询优化**：书中详细介绍了如何通过编写高效的SQL查询来提升性能，包括理解执行计划、使用恰当的JOIN类型、避免全表扫描、使用索引来加速查询，并探讨了如何利用查询提示和统计信息来优化查询执行。 2. **索引策略**：索引是提升SQL Server性能的关键。书中的内容涵盖了单列索引、复合索引、唯一索引、非聚集索引和覆盖索引的使用，以及何时创建和重建索引以保持最佳性能。 3. **存储过程优化**：书中讨论了存储过程的优势和潜在的性能瓶颈，如何编写高效的存储过程，以及如何利用存储过程参数化来提高性能。 4. **资源管理**：包括内存管理、CPU调度、I/O子系统优化等，这些都是影响SQL Server性能的重要因素。书中会解释如何配置SQL Server以优化这些资源的使用。 5. **数据库设计与架构**：良好的数据库设计可以预防许多性能问题。书中会介绍如何设计正常化的表结构，如何减少数据冗余，以及如何设计灵活且可扩展的数据模型。 6. **监控和调优工具**：书中也会涵盖SQL Server自带的监控工具，如SQL Server Profiler、Database Engine Tuning Advisor，以及如何利用这些工具识别性能瓶颈并进行调优。 7. **备份与恢复策略**：备份和恢复策略对数据库性能也有影响。书会讨论最佳实践，如差异备份、事务日志备份和即时恢复技术。 8. **性能计数器和性能监视**：了解如何解读和利用操作系统及SQL Server的性能计数器来诊断和优化性能问题。 9. **安全性和权限管理**：合理的权限设置可以防止不必要的性能损耗。书中会讲解如何设置用户权限，以及安全策略对性能的影响。 10. **高可用性和灾难恢复**：探讨高可用性解决方案，如镜像、集群和复制，以及如何规划灾难恢复策略，以确保业务连续性。通过深入学习这本书的内容，读者不仅可以理解SQL Server 2005的性能优化原理，还能获得实际操作的指导，从而提升数据库系统的整体性能。

Introduction

What is the goal of tuning an SQL Server database? The goal is to improve

performance until acceptable levels are reached. Acceptable levels can be

deﬁned in a number of ways. For a large online transaction processing

(OLTP) application the performance goal might be to provide sub-second

response time for critical transactions and to provide a response time of less

than two seconds for 95 percent of the other main transactions. For some

systems, typically batch systems, acceptable performance might be mea-

sured in throughput. For example, a settlement system may deﬁne accept-

able performance in terms of the number of trades settled per hour. For an

overnight batch suite acceptable performance might be that it must ﬁnish

before the business day starts.

Whatever the system, designing for performance should start early in

the design process and continue after the application has gone live. Per-

formance tuning is not a one-off process but an iterative process during

which response time is measured, tuning performed, and response time

measured again.

There is no right way to design a database; there are a number of possi-

ble approaches and all these may be perfectly valid. It is sometimes said that

performance tuning is an art, not a science. This may be true, but it is

important to undertake performance tuning experiments with the same

kind of rigorous, controlled conditions under which scientiﬁc experiments

are performed. Measurements should be taken before and after any modiﬁ-

cation, and these should be made one at a time so it can be established

which modiﬁcation, if any, resulted in an improvement or degradation.

What areas should the database designer concentrate on? The simple

answer to this question is that the database designer should concentrate on

those areas that will return the most beneﬁt. In my experience, for most

database designs I have worked with, large gains are typically made in the

area of query and index design. As we shall see later in this book, inappro-

xvi Introduction

priate indexes and badly written queries, as well as some other contributing

factors, can negatively inﬂuence the query optimizer such that it chooses an

inefﬁcient strategy.

To give you some idea of the gains to be made in this area, I once was

asked to look at a query that joined a number of large tables together. The

query was abandoned after it had not completed within 12 hours. The

addition of an index in conjunction with a modiﬁcation to the query meant

the query now completed in less than eight minutes! This magnitude of

gain cannot be achieved just by purchasing more hardware or by twiddling

with some arcane SQL Server conﬁguration option. A database designer or

administrator’s time is always limited, so make the best use of it! The other

main area where gains can be dramatic is lock contention. Removing lock

bottlenecks in a system with a large number of users can have a huge impact

on response times.

Now, some words of caution when chasing performance problems. If

users phone up to tell you that they are getting poor response times, do not

immediately jump to conclusions about what is causing the problem. Circle

at a high altitude ﬁrst. Having made sure that you are about to monitor the

correct server, use the System Monitor to look at the CPU, disk subsystem,

and memory use. Are there any obvious bottlenecks? If there are, then look

for the culprit. Everyone blames the database, but it could just as easily be

someone running his or her favorite game! If there are no obvious bottle-

necks, and the CPU, disk, and memory counters in the System Monitor are

lower than usual, then that might tell you something. Perhaps the network

is sluggish or there is lock contention. Also be aware of the fact that some

bottlenecks hide others. A memory bottleneck often manifests itself as a

disk bottleneck.

There is no substitute for knowing your own server and knowing the

normal range of System Monitor counters. Establish trends. Measure a set

of counters regularly, and then, when someone comments that the system is

slow, you can wave a graph in front of him or her showing that it isn’t!

Also there are special thanks to be made to Craig Mullins for his work

on technical editing of this book.

So, when do we start to worry about performance? As soon as possible,

of course! We want to take the logical design and start to look at how we

should transform it into an efﬁcient physical design.

Gavin Powell can be contacted at the following email address:

ezpowell@ezpowell.com

2 1.3 Transact SQL improvements

1.2 Building indexes online

Building an index online allows the table indexed against to be accessed

during the index creation process. Creating or regenerating an index for a

very large table can consume a considerable period of time (hours, days).

Without online index building, creating an index puts a table ofﬂine. If that

is crucial to the running of a computer system, then you have down time.

The result was usually that indexes are not created, or never regenerated.

Even the most versatile BTree indexes can sometimes require rebuilding

to increase their performance. Constant data manipulation activity on a

table (record insert, update and deletions) can cause a BTree index to deteri-

orate over time. Online index building is crucial to the constant uptime

required by modern databases for popular websites.

1.3 Transact SQL improvements

Transact SQL provides programmable access to SQL Server. Programmable

access means that Transact SQL allows you to construct database stored

code blocks, such as stored procedures, triggers, and functions. These code

blocks have direct access to other database objects—most signiﬁcantly

tables where query and data manipulation commands can be executed

directly in the stored code blocks; and code blocks are executed on the data-

base server. New capabilities added to Transact SQL in SQL Server 2005

are as follows:

 Error handling



Recursive queries



Better query writing capabilities

There is also something new to SQL Server 2005 called Multiple Active

Result Sets (MARS). MARS allows for more than a single set of rows for a

single connection. In other words, a second query can be submitted to a

SQL Server while the result set of a ﬁrst query is still being returned from

database server to client application.

The overall result of Transact SQL enhancements to SQL Server 2005 is

increased performance of code, better written code, and more versatility.

Better written code can ultimately make for better performing applications

in general.

1.4 Adding the .NET Framework 3

Chapter 1

1.4 Adding the .NET Framework

You can use programming languages other than just Transact SQL and

embed code into SQL Server as .NET Framework executables. These pro-

graming languages can leverage existing personnel skills. Perhaps more

importantly, some tasks can be written in programming languages more

appropriate to a task at hand. For example a language like C# can be used,

letting a programmer take advantage of the enormous speed advantages of

writing executable code using the C programming language.

Overall, you get support for languages not inherently part of SQL Server

(Transact SQL). You get faster and easier development. You get to use Web

Services and XML (with Native XML capabilities using XML data types).

The result is faster development, better development, and hopefully better

over database performance in the long run.

The result you get is something called managed code. Managed code is

code executed by the .NET Framework. As already stated, managed code

can be written using all sorts of programming languages. Different pro-

gramming languages have different beneﬁts. For example, C is fast and efﬁ-

cient, where Visual Basic is easier to write code with but executes slower.

Additionally, the .NET Framework has tremendous built-in functionality.

.NET is much, much more versatile and powerful than Transact SQL.

There is much to be said for placing executable into a database, on a

database server such as SQL Server. There is also much to be said against

this practice. Essentially, the more metadata and logic you add to a data-

base, the more business logic you add to a database. In my experience, add-

ing too much business logic to a database can cause performance problems

in the long run. After all, application development languages cater to num-

ber crunching and other tasks. Why put intensive, non-data access process-

ing into a database? The database system has enough to do just in keeping

your data up to date and available.

Managed code also compiles to native code, or native form in SQL

Server, immediately prior to execution. So, it should execute a little faster

because it executes in a form which is amenable to best performance in

SQL Server.

SQL Server 2005 includes a new management object model called SQL

Management Objects (SMO). The SMO has a basis in the .NET Frame-

work. The new graphical, SQL Server Management Studio, is written using

the SMO.

剩余516页未读，继续阅读

kangminghui

粉丝: 0
资源: 13

SQL Server 2005性能优化与调优手册

Microsoft.Press.Inside.Microsoft.SQL.Server.2005.Query.Tuning.and.Optimization.Sep.2007

Inside.Microsoft.SQL.Server.2005

Java.EE.7.Performance.Tuning.and.Optimization.178217642X

Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook

Handbook.of.Graph.Theory.Combinatorial.Optimization.and.Algorithms.158

Sams-C.Plus.Plus.Footprint.and.Performance.Optimization.eBook-EEn.chm

高清彩版 oracle sql performance tuning and optimization

Footprint.and.Performance.Optimization

Inside SQL Server 2005: Query Tuning and Optimization

Oracle Performance Tuning and Optimization

最新资源