SQL Server 2005性能调优实战指南

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

《SQL Server 2005性能优化与调优手册》是一本由Microsoft官方发布的指南，旨在帮助用户提升SQL Server 2005系统的性能和优化数据库管理。该手册由Ken England和Gavin Powell两位专家共同编撰，涵盖了针对Microsoft SQL Server 2005这一具体版本的深入分析和实用技巧。本书的核心内容包括但不限于以下几个方面： 1. **性能基础知识**：首先介绍了SQL Server 2005的基本架构、查询优化原理以及性能衡量指标，如CPU使用率、磁盘I/O、网络流量等，为后续的优化提供理论基础。 2. **查询优化**：详细讲解了如何编写高效的SQL查询语句，包括索引策略、统计信息的重要性、查询计划分析，以及如何利用SQL Server的内置工具（如SQL Profiler和Query Analyzer）进行性能诊断。 3. **存储过程和存储函数**：优化存储过程的执行效率，避免全表扫描，提高数据访问速度，以及如何设计和维护高效的数据存储结构。 4. **数据库设计优化**：包括规范化原则、分区和分片策略，以及如何根据业务需求选择合适的数据模型，减少数据冗余和提高查询性能。 5. **内存管理**：探讨了临时表、工作表、缓冲池管理等内存优化技术，以确保数据在内存中的有效缓存和快速访问。 6. **事务管理和并发控制**：针对多用户环境下的事务处理，讲解了锁机制、死锁检测以及如何设计可扩展的并发控制策略。 7. **硬件和配置优化**：建议针对硬件配置（如CPU、内存、磁盘I/O）进行调整，同时推荐最佳实践，如使用适当的硬件冗余和RAID配置。 8. **性能监控与调优**：提供了实时监控工具的使用方法，以及如何根据监控结果进行系统调优，持续改进SQL Server的性能。 9. **案例研究与实战经验**：书中包含实际项目中的性能问题解决案例，通过具体的实例演示优化策略的实施步骤和效果。版权信息显示，这本书受到严格的版权保护，未经许可，禁止任何形式的复制、存储或传输。读者如果需要获取更多内容或权限，可以通过Elsevier的官方渠道联系。《SQL Server 2005性能优化与调优手册》是一本实用的参考书籍，不仅适合SQL Server管理员和技术人员，对于追求高性能数据库应用的企业和个人而言，都是不可或缺的参考资料。

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页未读，继续阅读

sadxhy

粉丝: 21
资源: 17

SQL Server 2005性能调优实战指南

[SQL Server 2005性能调优]

SQLServer2005性能调优

SQL Server性能调优.pdf

如何在MySQL中利用information_schema查询特定表的大小和记录数，并根据存储引擎信息进行性能优化？

如何在MySQL数据库中查询特定表的大小和记录数，以及如何通过存储引擎进行性能优化？

如何利用MySQL的多用户、多线程特性优化数据检索性能？请结合API使用给出具体的操作步骤。

Oracle ERP系统中如何配置系统参数以优化性能？请提供详细步骤和注意事项。

如何在eMan系统中配置和优化Tomcat缓存以及处理Microsoft SQL Server数据库故障？

oracle dba管理手册

编写一份数据库管理员操作手册

最新资源