|
|
 |
|
|
|
Microsoft® SQL Server 2000™ Performance Tuning Technical Reference
|
|
|
Author
|
|
Edward Whalen, Marcilina Garcia, Steve Adrien DeLuca, Dean Thompson
|
|
|
Pages
|
464
|
|
Disk
|
N/A
|
|
Level
|
Int/Adv
|
|
Published
|
07/03/2001
|
|
ISBN
|
9780735612709
|
|
Price
|
$49.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
A
accessing
I/Os, 78
objects, 116
ACID (atomicity, consistency, isolation, durability) property transactions, 218–219
Add Counters dialog box, 72, 110, 114, 206
Add Trendline dialog box, 211
adding
columns, 100, 135–136
counters, 107, 110, 114, 206
events, 134–135
physical memory, 76
tape devices, 281
users, 154
virtual memory, 19
Address Windowing Extensions. See AWE
administration
database maintenance, 97
engine events, 122
log shipping, 98
SQL Profiler, 98–99
affinity mask option, 29
after images, 30
aggregations, 243, 306
alerts, creating, 104
allocation. See also configuration; management
connection context, 15
data structures, 15
memory, 13, 17, 77, 117
AllowPageLocks option, 231
ALTER DATABASE command, 24
Analysis Services, 235, 239–240
analyzing data
correlative, 213
historic data reporting, 209–210
performance tuning, 9
predictive, 208, 210–212
profile, 141–143
queries, 296, 354–355
service chain reporting, 214
traces, 143–144
anatomy, transactions, 182
anonymous users, 207
application servers
application logic, 222
clients, 220
counters, 207
multitier systems, 180
OLTP systems, 219
service chain reporting, 214
single-tier systems, 179–180
stored procedures, 220
applications
caches, 328–329
critical transactions, 321
designing, 182–183, 317–321
developers, 183
instances, 90
processes, 28
stored procedures, 322–326
threads, 28
three-tiered architecture, 222
troubleshooting, 8
tuning, 4
variables, 327–328
architecture
executable code, 14
memory pool, 14
processors, 67–68
three-tiered, 222
two-tiered, 220–221
archiving data, 227–228
arrays. See RAID
arrival rates
capacity planning, 162–163
transactions, 169
ascending sorts, indexes, 341
Assert operator, 300
asychronous read operation, 16
asymptotic point, 157
asynchronous write operations, 16
atomic demand modeling, 159
atomicity, transactions, 218
audits
security, 135
trails, 191
auto update statistics option, 333
automatic growth increment
log files, 25, 33
OLTP systems, 225–226
average seek time, 43
AWE (Address Windowing Extensions), 19, 84, 89
B
B-tree structure, indexes, 339
Backup Device Properties-New Device dialog box, 286
backups
capacity planning, 283
data, 275
differential, 97, 279, 291
disks, to, 281–282
downtime, 228
filegroups/files, 24, 279
full, 275, 278, 290
I/Os, 280–282
incremental, 275
local, 287
log files, 35
multiple devices, 282
networks, 282, 288–290
performance, 278, 283–284
processes, 280
scheduling, 291
storage area networks (SANs), 289
tape devices, 281
text, 277
threads, 282
transaction logs, 31, 275, 279
write operations, 48
base level, server options, 362
batches
running, 127
statements, 117
Transact-SQL, 122
BCP threads, 261
bcp utility, 277, 368
before images, 30
benchmarking, 154–155, 181
bigint data type, 98
bits. See parity
blocking transactions, 116
Bookmark Lookup operator, 300
bottlenecks
CPUs, 69
hardware, 4
I/Os, 280
OLTP systems, 229
processors, 110–111
removing, 4
troubleshooting, 72–75
branch nodes, 339–340
buffer cache, 14–15
hit ratio, 77
memory pool, 75
size, 16
Buffer Manager object, 77, 112–113
build input, hash join, 363
Bulk Changed Map pages, 26
bulk copying, 277, 368–369
Bulk Update node, 27
BULK_LOGGED recovery mode, 276
busy time, capacity planning, 160–161
C
Cache Manager object, 205
caches
applications, 328–329
buffer cache hit ratio, 77
controllers, 48
data, 16
database processing area, 191
hit ratio, 77, 112, 192, 205
levels, 67
calculated measures, 241–242
calculations
I/Os, disk drives, 195
indexes, 344
memory, 192
RAID, 59, 60
read/write operations, 184
transactions, 184, 189
calling stored procedures, 131, 227
Cancel Query Execution toolbar button, 298
capacity planning, 3, 5–6. See also sizing
arrival rates, 162–163
atomic demand modeling, 159
backups, 283
benchmarks, 154–155, 181
busy time, 160–161
completions, 160–161
components, 291
counters, 200
CPU utilization, 199
disk drives, 47
disk utilization, 199
graphs, 202–204
hardware, 7
independent variables, 160–161
memory utilization, 199
observation time, 160–161
postconfiguration, 154
preconfiguration, 153
decision support system (DSS), 168
multitier systems, 180
OLTP systems, 168
page faults, 171
queries, 176–178
single-tier systems, 179–180
predictive analysis, 154
queues, 156–159, 162
response time, 163–164
servers, 155–156
service chains, 159–160
system simulation, 154–155
transactions, 167–168
utilization, 161–162
workloads, 201
capturing SQL statements, 147
change tracking, 97
chart views, 104, 105
CHECK constraints, 93, 369
checkpoints, 32, 35–37
chunks. See striping
clauses, 363, 367
Clear Window toolbar button, 297
clients
application logic, 220
presentation, 220
service chain reporting, 214
three-tiered architecture, 222
clustered indexes, 95–96, 300–301, 341–344
code faults, 171
Collapse operator, 301
collection data, 202–207
color coding, SQL syntax, 295
columns
adding, 100
calculated, 344
deleting, 100
editing, 135–136
grouping, 136
indexes, 98, 341, 348–349
permissions, 138
primary key constraints, 343
values, 137–138
commands
ALTER DATABASE, 24
CREATE INDEX, 350
DBCC SHOW_STATISTICS, 348
RECONFIGURE, 37
ROLLBACK, 276
commit batch size, 265–266
committed data, 360
completions, capacity planning, 160–161
components
applications, 222
capacity planning, 291
hardware, 7
software, 9
composite indexes, 341
Computer Scalar operator, 302
computing, parity, 55–56
CONCAT hit, 367
Concatenation operator, 302
conceptual modeling, service chains, 164–165
conditional statements, 307
configuration. See also allocation
clusters, 95–96
distributors, 252–255
editing, 9
hardware, 9
I/Os, 62, 286–287
locks, 28
measurement, 200
memory, 18, 90
merge replication, 270
networks, 284–285
OLTP systems, 217
options, 83–87
parameters, 3, 5
processors, 29
publishers, 260
recovery interval option, 38
snapshot replication, 257–261
tables, 353
traces, 124
transaction logs, 31
users, 135
workload, 352
Connect To SQL Server dialog box, 296
connections
context, 14–15
current, 207, 299
instances, 90
SQL Query Analyzer, 296–297
users, 114, 205
consistency, 218
Constant Scan operator, 302
constraints
CHECK, 93
foreign key, 97
primary key, 343
context switches, 29, 112
controller caches, 48–49
correlative analysis, 213
cost
benchmark sizing, 181
hard paging, 119–120
servers, 155
counters
adding, 107
applications, 207
Buffer Manager object, 112–113
Cache Manager object, 205
capacity planning, 200
Databases object, 113–114, 205–206
enabling/disabling, 71
explosion, 234
General Statistics object, 114, 205
I/Os, 71–72, 205
Latches object, 115
LogicalDisk object, 118
measurement configuration, 200
Memory object, 119–120, 204
monitoring, 75–76
Network Interface, 204
objects, 103
performance, 200
PhysicalDisk object, 118
Processor object, 111, 204
read/write operations, 71–72
Server object, 205
System object, 111–112
Web servers, 207
covering queries, 332, 349
CPU Log dialog box, 107
CPUs. See also processors
bottlenecks, 69
cache levels, 67
counters, 204
single-processor systems, 68
sizing, 182–183
support, 69
symmetric multiprocessing (SMP), 68
utilization, 110–111, 156–158, 161–162, 183–187, 199, 209, 212
creating
alerts, 104
filegroups, 80–83
indexes, 30, 277, 346–350
profiles, 266
traces, 124–125, 140–141
critical transactions, 321
cubes, partitions, 243–245
Current Connection Properties toolbar button, 207, 299
Cursors event, 134
customers. See users
cylinders, 40
D
data
accessing, 4
analyzing, 141–143, 208–213
archiving, 227–228
backups, 228, 275
bulk copying, 368–369
caches, 15–16, 75
collection, 202–207
columns, 136–138
correlative analysis, 213
disk distribution, 78–82
filegroups, 223–226
files, 206
grouping, 126–128
historic data reporting, 209–210
integrity, 114, 218–219, 359
OLTP systems, 219, 227
pages, 14, 26
predictive analysis, 208, 210–212
primary, 20
redundancy, 227
restoring, 276
retrieving, 330–332, 342, 346
secondary, 21
service chain reporting, 214
sorting, 342, 348
sparse, 240
storing, 20–21, 339
striping, 23–24, 50, 173
structures, 15
tables, 341–342
traces, 133, 143–144
Data Manipulation Language (DML) statement, 307
Data Transformation Services (DTS), 235
data types, 98
database servers
CPUs, 182
disk drives, 174–175
fault tolerance, 172–174
queries, 177–178
RAID 5, 183
service chain reporting, 214
tables, 176
data warehouses
aggregations, 243
calculated measures, 241–242
cube partitions, 243–245
Data Warehousing Framework, 235
disk drives, 246
fact tables, 236
hierarchies, 239–240
HOLAP, 235, 242
memory, 246
MOLAP, 235, 242
OLTP systems, 233–234
processors, 245–246
query logs, 247
ROLAP, 235, 242
semi-additive measures, 240–241
snowflake schema, 238–239
sparse data, 240
star schema, 237
databases. See also indexes; tables
administration, 122
data files, 21
designers, 182–183
disk drives, 194–195
disk farms, 178–179
location, 81
locks, 27, 115–116
maintenance, 97
multiple users, 221
options, 362
processing area, 191
restoring, 32
tables, 91–93
transaction logs, 30
workloads, 223
Databases object, 113–114, 134, 205–206
DBAs (database administrators). See administration
DBCC SHOW_STATISTICS command, 348
DBCC USEROPTIONS statement, 362
deadlocks
detection, 145
transactions, 115–117
Debit/Credit benchmarks, 155
debugging statements, 123, 127, 146–149
decision support system (DSS), 223. See also transactions
OLAP, 235
preconfiguration capacity planning, 168
throughput, 168
default configurations, 24, 90
Deleted Scan operator, 302
deleting, 27, 341
columns, 100, 136
events, 134–135
rows, 300, 303–306
text, 297
dependent variables, 160–161
descending sorts, 341
designing applications, 317–321
detecting deadlocks 145
devices, backup, 282
differential backups, 97, 279, 291
Differential Changed Map pages, 26
dimesion tables, 236–237
dirty pages, 16
checkpoints, 36
read/write operations, 360
recovery, 32
DisAllowPageLocks option, 231
DisAllowRowLocks option, 231
disk drives, 39
backups, 281–282
bottlenecks, 72–75
capacity, 47
data files, 79–80
data warehouses, 246
databases, 174–175, 194–195
fault tolerance, 52–53
I/Os, 70–71, 195
latency, 41–42, 70
log files, 193–194
logical, 50
mean time between failure (MTBF), 44
optimizing, 70
platters, 40
RAID, 47, 59–60, 194, 197
requirements, 193
seek time, 42–43
sizing, 196–197
specifications, 43
tracks, 40
utilization, 175, 199
volumes, 47
disk farms, 178–179
diskperf utility, 70–72
Display Estimated Execution Plan toolbar button, 298
displaying
queries, 295, 298
stored procedures, 123, 128–129
Transact-SQL statements, 130
distribution
disk drives, 80
proportional fill strategy, 81–82
random I/Os, 47
Distribution Agent Profiles dialog box, 266
distributors, 271
BCP threads, 261
commit batch size, 265–266
configuration, 252–255
I/Os, 265
log readers, 267–268
monitoring, 255–256
performance, 251
snapshot folders, 252–253
tuning, 256
DLLs (dynamic link libraries), 14
DML (Data Manipulation Language) statement, 307
documentation, performance tuning, 9–10
downtime
data backups, 228
failover clustering, 95–96
DTS (Data Transformation Services), 235
durability, transactions, 219
dynamic link libraries (DLLs), 14
dynamic memory, 17–18
E
Edit menu (SQL Profiler), 123
editing. See also configuration
configuration, 9
filegroups, 24
SQL statements, 295
stored procedures, 220
traces, 98–99, 127, 132–140
elevator sorting, 46
enabling/disabling
AWE, 19
counters, 71
fiber mode scheduling, 29
memory pool, 19
show advanced options, 18
end-to-end response time, 163–164
engine events, 122
Enterprise Manager
distributors, 253–254
memory, 18
recovery interval option, 37
equality clause, 363
errors, 134
escalation, locks, 28
events. See also traces
adding/deleting, 134–135
engines, 122
Exclusive node, 27
exclusive OR (XOR) operation, 56
Execute Mode toolbar button, 298
executing
code, 14
graphical plans, 4, 307–308
queries, 298, 300–306
EXISTS clause, 319
exponential growth, CPU utilization, 157
extending memory, 89
Extensible Markup Language. See XML
extents, 23, 26–27
external RAID controllers, 48–50
F
fact tables, 236–237
failback, 95
failover clustering, 95–96
FASTFIRSTROW hint, 366
fault tolerance
database servers, 172–174
disk drives, 52–53
log files, 78
logical disks, 47
RAID 1, 53
striping, 50–51
federations, servers, 90–95
fibers, 29
fields, user input, 227
File menu (SQL Profiler), 123
files/filegroups. See also log files
backups, 24, 279
creating, 80–83
data, 206, 223–226
files, 22–24
I/Os, 23
naming, 20
pages, 20, 25–26
primary, 20
secondary, 21
sizing, 25
fill factor, 349–350
Filter operator, 302
filters, traces, 134–135, 138–140
FIRE_TRIGGERS hint, 369
flags, memory, 20
flushing log files, 114
foreign key contraints, 97, 237
free pages, 112
free-form user input fields, 227
full backups, 275, 278, 290
full-disk seek time, 43
full-text indexes, 97, 341, 345–346
G
General Statistics object, 114, 205
Global Allocation Map pages, 26
graphical user interfaces, 155
graphs
capacity planning, 202–204
CPU utilization, 209
performance, 200–201
query execution, 307–308
grouping
columns, 136
data, 126–128, 363, 367
growth increment, 26
H
hard disks. See disk drives
hard paging, 119–120
hardware
bottlenecks, 4
capacity planning, 153–154
components, 7
configuration, 9
disk drives, 70
interrupts, 111
tuning, 5, 67
hash joins, 4, 363, 367
Hash operator, 302–303
HBAs (host bus adapters), 49
headers, pages, 16
Help menu (SQL Profiler), 124
hierarchies, data warehouses, 239–240
hints
bulk copying, 368–369
joins, 363–364
queries, 366–368
statement level, 362
tables, 364–366
histograms, 104–105
historic data reporting, 209–210
hit ratio caches, 112, 205
HOLAP. See hybrid OLAP
HOLDLOCK hint, 365
host bus adapters (HBAs), 49
hybrid OLAP (HOLAP), 235, 244
I
I/Os
backups, 280–282
bottlenecks, 280
configuration, 62, 286–287
counters, 205
CPU utilization, 183
data storage, 223–226
disk drives, 59–60, 70–71, 195
distributors, 265
elevator sorting, 46
filegroups, 23
latency, 39, 61–62
logical disks, 71–72, 184
merge replication, 270–271
monitoring, 70–74
OLTP systems, 231
performance, 223–226
physical disks, 71–72, 171, 184
queuing, 45
RAID, 60–61, 74
random, 45, 47
RDBMSs, 39
read/write operations, 118–119, 207
requests, 70, 79
response time, 188
seek time, 43
sequential, 44, 47
sizing, 182–183
snapshot replication, 258
throughput, 187
transactions, 182, 264–265
utilization, 175
images, 30, 97
incomplete transactions, 32
incremental backups, 275
independent variables, 160–161
Index Tuning Wizard, 313–315
indexes, 366. See also tables
allocation, 26
branch nodes, 339–340
clustered, 341–343
columns, 341, 344
composite, 341
computed columns, 98
creating, 346–348
data, 339, 348
DELETE statement, 341
factors, 177–178
filegroups, 22
fill, 349–350
full-text, 341, 345–346
joins, 348
key values, 341, 344, 348–349
leaf nodes, 339–340
levels, 339–340
memory allocation, 26
narrow, 347
nonclustered, 343–344
OLTP systems, 228–229
operators, 303–304
pages, 26, 349–350
parallel scanning, 98
queries, 347, 349
root node, 339–340
rows, 339–340
SELECT statement, 341
selectivity, 348
simple, 341
sorting, 98, 341
SQL Query Analyzer, 350–357
structure, 339
tables, 228–229
unique, 345
updating, 341, 349–350
views, 344
wide, 347
input tables, 363–364
Insert Template toolbar button, 297
Inserted Scan operator, 304
inserting rows, 27, 301–305
instances, 90
integrity. See data, integrity
Intent node, 27
interaction, user transactions, 116
internal RAID controllers, 48–49
interrupts, 111
interviews, CPUs, 182–183
isolation
locking, 359
queries, 359
read/write operations, 360–361
sequential I/Os, 47
serializability, 359
sessions, 361–363
transactions, 218–219
J–K
joins, 367
hash, 363
indexes, 348
loops, 304, 363
merge, 363–364
operations, 4
queries, 336
tables, 176, 363–364
key values
indexes, 341, 344, 348–349
row locks, 27
knee of the curve, 157–158
L
L1/L2 caches, 67
Latches object, 115
latency. See also response time
disk drives, 41–42, 70
I/Os, 39, 44–45, 61–62
RAID 10, 57
transactions, 189–190
lazywriter, 16
leaf nodes, indexes, 339–340, 343
levels
indexes, 339–340
RAID, 60–61
lightweight pooling option, 29, 84–85, 112
limitations
Index Tuning Wizard, 314–315
performance tuning, 3
linear growth, 157
Load SQL Script toolbar button, 297
local backups, 287
location
database data files, 81
references, 227
snapshot replication, 259–260
system tables, 81
locks
databases, 27
escalation, 28
extents, 27
isolation levels, 359
Locks object, 115–116
locks option, 28, 85
management, 27
monitorings, 135
multigranular, 27
OLTP systems, 230–231
pages, 27
system, 85
tables, 27, 364–366
log files, 21
automatic growth increment, 25, 33
backups, 35
caches, 14–15
checkpoints, 35–37
data, 20–21
database processing area, 191
disk distribution, 78
disk drives, 50, 193–194
fault tolerance, 78
filegroups, 24
flushes, 114
networks, 106
performance tuning, 7, 9
records, 25
recovery, 35
shipping, 98
system, 145–146
System Monitor, 104–110
trace, 104
tracking, 206
transactions, 30–31, 53
truncation, 33, 35
viewing, 108–110
Windows 2000, 106–108
Log Reader Agent Profiles dialog box, 268
Log Row Scan operator, 304
logical I/Os
counters, 71–72
read/write operations, 184, 189, 195
logical logs, 33–34
logical names, 20
logical operators, 300–306
LogicalDisk object, 118–119
loops, 4, 363, 367
M
mainframe computers, 170
maintenance. See administration
management
dynamic, 17
locks, 27
memory, 13
threads, 28
max server memory option, 17–18, 76–77, 85–86
max worker threads option, 30
maximum file size option, 26
mean time between failure (MTBF), 44
measurement configuration, 200
member tables, 91–93
memory. See also RAM
allocation, 17, 77, 117
buffer pool, 75
caches, 15, 67
connection context, 15
counters, 75
data warehouses, 246
enabling, 19
extending, 89
files, 22–25
flags, 20
indexes, 26
instances, 90
log cache, 15
management, 13
minimum, 192
OLTP applications, 192, 229–230
operating systems, 19–20
optimizing, 75–77
page faults, 14, 171–172
pool, 14
procedure cache, 15
sizing, 190–191
tables, 26
threads, 76
traces, 132
utilization capacity, 199
virtual, 13
working sets, 76, 86
memory management, 13–20
Memory Manager object, 117
Memory object, 119–120, 204
menus, SQL Profiler, 123–124
Merge Agent Profiles dialog box, 271
merge operations
joins, 4, 304, 363–364, 367
replication, 270–274
Microsoft Cluster Server (MSCS), 95
Microsoft Repository, 235
Microsoft SQL Query Analyzer, 99–100, 350
analysis, 354–355
rows, 351–352
running, 356–357
tables, 353
Microsoft SQL Server Personal Edition, 30
min server memory option, 17–18, 77, 86
minimum memory calculations, 192
mirroring disks, 52–53, 56–57, 173
modeling
atomic demand, 159
independent variables, 160–161
queue, 159
service chains, 159–160, 164–165
modes, 276–278
modifying. See editing
MOLAP (multidimensional OLAP), 235, 242
monitoring
buffer cache hit ratio, 77
counters, 75–76, 103
distributors, 255–256
engine events, 122
I/Os, 70–74
locks, 135
merge replication, 273
objects, 75–76, 135
paging, 17, 76
performance, 135
scans, 135
service chains, 207
snapshot replication, 262
stored procedures, 128–129, 135
systems, 66
transaction replication, 269
user sessions, 205
MSCS (Microsoft Cluster Server), 95
MTBF (mean time between failure), 44
multidimensional OLAP (MOLAP), 235, 242
multigranular locks, 27
multiprocessing
OLTP systems, 229
symmetric (SMP), 68
multitier systems, 170, 180
Next
Last Updated: Friday, July 6, 2001 |
