false
MCSE Readiness Review-- Exam 70-028: Administering Microsoft SQL Server 7.0 by Jill Spealman
BUY IT NEW
- Limited Time Offer! Everyone receives the Member Price on books.
See Details - This item is currently out of stock.
- Add To List uiAction=GetAllLists&page=List&pageType=list&ean=9780735606722&productCode=BK&maxCount=100&threshold=3
Product Details
- Pub. Date: October 1999
- Publisher: Microsoft Press
- Format: Hardcover, 416pp
- Series: MCSE Series
- ISBN-13: 9780735606722
- ISBN: 0735606722
Synopsis
Here's a smart, inexpensive way to prepare for MCP exam 70-028, Administering Microsoft SQL Server 7.0-a core credit for the new Microsoft Certified Database Administrator (MCDBA) credential. This READINESS REVIEW book-and-CD set helps users practice answering real exam objectives-sharpening their test-taking skill and identifying areas for further study. Randomly generated practice tests on CD deliver questions that map to actual MCP exam objectives. Users can test and retest with different question sets each time. The companion text offers pointers for understanding correct and incorrect responses, along with valuable test-prep suggestions to shore up performance.
About The Author
Founded in 1975, Microsoft (Nasdaq 'MSFT') is the worldwide leader in software for personal and business computing. The company offers a wide range of products and services designed to empower people through great software-any time, any place and on any device.
From the Publisher
If you took Microsoft Certified Professional (MCP) Exam 70-028 today, would you pass? With the READINESS REVIEW MCP exam simulation on CD-ROM, you get a low-risk, low-cost way to find out! Developed exclusively for Microsoft Press by Self Test Software, this electronic assessment tool delivers a set of randomly generated, 60-question practice exams covering real MCP objectives. You can test and retest with different question sets each time—and with automated scoring, you get immediate Pass/Fail feedback. More importantly, you’ll get answers to these four critical questions:
•What do MCP exam questions look like?
• In what topic and skill areas am I proficient/deficient?
• How should I focus my studies?
• Am I ready for the real exam?
Use this READINESS REVIEW to evaluate your proficiency in the following SQL Server 7.0 skills and knowledge areas:
• Planning
• Installation and configuration
• Configuring and managing security
• Managing and maintaining data
• Monitoring and optimization
• Troubleshooting
After completing your practice sessions, use the companion book to review helpful explanations for all responses—right and wrong—and to identify areas for further study. Save valuable time and money—and build your confidence for the real exam—with READINESS REVIEW, the smart way to prep!
Product Details
- Pub. Date: October 1999
- Publisher: Microsoft Press
- Format: Hardcover, 416pp
- Series: MCSE Series
- ISBN-13: 9780735606722
- ISBN: 0735606722
Table of Contents
Special Characters
and Numbers
@@IO_BUSY function, 157-58
@@SERVICENAME function, 303-4
% Privileged Time counters, 270, 277-80
% Processor Time counters, 270, 277-78
% Total Processor Time counters, 270, 277-78
% User Time counters, 271, 277-80
6.x (SQL Server version)
replicate to, from 7.0, 329-30
upgrading from, 297, 299-307
A
Access. See Microsoft Access
access
add NT group to SQL, 111-12
assignment, 103-4, 117-25
Access Methods objects, 256
accounts, login, 103-5, 107
access assignment, 117-25
assignment, 107, 123-24
BUILTIN/Administrators, 108
dbo, 117
dropping, 131-32
groups, 107
guest, 117, 121-22
sa (System Administrator), 107
security folder, 119-20
sp_addlogin, 109-10
sp_grantdbaccess, 110, 119-20
SQLAgentCmdExec, 122
SQL login, 111-12, 119-20
upgrading from 6.x, 303-6
user, 119-20
administration, 163-64
accounts, 103
alerts, 163
backup strategy, 31-42
jobs, 163
logins, 135-36
minimizing, 25-26
operators, 164
replication, 215-25
SQLAgentMail, 164
SQL Mail, 89-90
SQL Server Agent, 227-37
transaction log maintenance, 55-56
user access, 9-10
Administrators local group, 6, 11-12
Affinity Mask options, 271
Agent, 227-37
alerts, 227, 231-32
error log, 335-36
History dialog box, 321
jobs, 227
notifications, 228, 232-33
replication troubleshooting, 321
SQLAgentMail, 227, 229
alerts, 163, 227, 231-32
failing to fire, 337-38
history values, 337-38
notification, 335-36, 339-40
transaction logs, 335-36, 339-40
troubleshooting, 333-41
aliases, 133-34
ALTER DATABASE statement, 161, 166-67, 175-77
ANSI database options, 70, 83
NULL default, 83-84, 173-74
sp_dboption, 91-92
warnings, 84, 91-92
ap_add_jobstep, 227
ap_addapprole, 129
AppleTalk network library, 19
application log, Windows NT, 235-36
applications
custom data input, 131-32
roles, 7, 104-5
security, 5
audits, 105, 151-59
@@IO_BUSY function, 157-58
connections, 158
DBCC, 157-58
events, 153-54
Index Tuning Wizard, 151, 155-56
page allocation, 154
Profiler, 151, 155-56
queries, 153-56
audits (continued)
Query Analyzer, 151, 155-56
SHOWCONTIG, 157-58
system stored procedures, 153-54
Transact-SQL, 157-58
Transaction-log space, 158
user, 153-54
Windows NT Server, 151
xp_logevent, 154
xp_trace_setqueryhistory, 153-54
authentication, 1, 5, 347-48
Mixed mode, 5, 9-10
network libraries, 19
SQL Server Authentication login, 5
Web site, 15
Windows NT, 5, 9-10
automatic growth, 166
Available Bytes counters, 269, 273-74
B
BACKUP DATABASE Transact-SQL statement, 162, 190-91, 195-98
Backup Device objects, 256
BACKUP LOG Transact-SQL statement, 162, 190, 199-200
BACKUP statement, 35-38
backups, 2, 31-33, 35-38, 162, 189-92
appending, 37-38, 195-96
BACKUP DATABASE statement, 162, 190-91, 195-98
BACKUP LOG statement, 162, 190, 199-200
BACKUP statement, 35-38
complete database backups, 189-90, 193-94, 197-98
Create Backup Wizard, 162, 190
creating file, 39-40
db_backupoperator, 128
devices, 189, 197-98, 203
differential database, 39-42, 191, 197-98
error messages, 309, 311-12
filegroup backups, 191
full database backup, 31, 39-40
header information, 207-8
INIT option, 37-38
jobs, 227-28
master databases, 169-70
media header information, 195-96
media sets, 35-36
MEDIANAME option, 35-36
minimizing time, 39-40
multiple files, 209-10
names, display, 207-8
naming devices, 189
NOINIT option, 37-38, 196-97
NOUNLOAD option, 37-38
point of failure, 195-96
RECOVERY option, 163
Restart option, 196-97
restoring, 41-42, 162-63, 203-13
roles, 193-94
sp_add_job, 40
sp_addumpdevice, 39-40
speed, 35-36
tape, 196-97, 301-2
tape devices, 31, 37-38
testarch, 40
transaction log, 39-42, 190-91, 197-200, 203, 209-10, 317-18
troubleshooting, 309-19
Truncate Log On Checkpoint option, 317-18
Upgrade Wizard, 301-4
USE function, 39-40
verifying, 203, 207-8
Banyan VINES network libraries, 19
bcp utility, 162, 179, 185-86
command, 46
sort order restore, 311-12
bd_backupoperator, 131-32
Binary order, 74
Books Online Web site, 15
Buffer Cache Hit Ratio counters, 257, 269, 275-76
Buffer Manager objects, 256-57
buffers, 269-70
bulkcopy. See bcp utility
bulk copy libraries, 46
BULK INSERT statements, 46, 162, 179, 187, 311-12
C
caches
hit ratio, 269
write, 27-28
Cache Manager objects, 256
capacity strategy, 2
catalogs, 98, 239
character sets, 71, 73-74
Client Network utility, 71, 88, 298, 355, 361-62
clients
configuration, 71, 355-63
connections, 355-63
installing, 69
lock-out, 85-86
Windows 98, 359-62
clustering, 2, 32
Clustering Service, 32
Microsoft Search service, 100
columns
error messages, 349-50
linked server, 239-40
NOT NULL, 173
communication topologies, 59
complete database backups, 31, 39-40, 162, 189-90,
193-94, 197-98
restoring, 163, 203-4
components run as NT services, 6
configuration, 69-81
ANSI, 83, 91-92
Client Network, 71, 88
CPU optimization, 253
databases, 185-86
data files, 161
log files, 161
memory optimization, 253
network libraries, 87-90
profiles, 89-90
remote connections, 87-88
replication, 163
Server Network, 88
sp_configure Allow Updates option, 86
sp_dboption, 83, 91-92
sp_processmail, 89-90
SQLAgentMail, 70, 83, 89-90
SQL Mail, 70, 83, 89-90
Configure Publishing and Distribution Wizard, 163, 215
connections
audit, 158
Client Network utility, 355, 361-62
groups, 371-72
IPX/SPX, 359-62
MAKEPIPE utility, 357-58
Multiprotocol Net-Library, 359-60
Named Pipes Net-Library, 355, 357-60
Net Library, 355, 359-62
NET VIEW, 357-58
protocol stack, 359-60
READPIPE utility, 357-58
Server Alias Configurations list, 355
Server Network utility, 361-62
TCP/IP, 359-60
testing, 357-58
troubleshooting, 298, 355-63
Windows 95, 361-62
Windows 98, 359-60
consolidate databases, 79-80
constraint, 134
Cost Threshold for Parallelism, 271
counters, 151, 253, 257
Agent polling of, 337-40
CPU, 254
CPUs
counters, 254, 270-71
Processor Queue Length counters, 278-80
usage, 255
usage rates, 277-80
Create Backup Wizard, 162, 190
CREATE DATABASE statements, 161, 165, 171-72, 177
Create Job Wizard, 227, 231-32
Create Publication Wizard, 163, 216
Create Trace Wizard, 263-65
creating
backup devices, 162
data files, 165
databases, 161, 165, 169-74
filegroups, 161, 165
indexes, 244-45
publications, 163
transaction logs, 161, 165
cross-database dependencies, 51-52
Current Activity windows, 151, 255, 260
custom sort order, upgrading, 53-54
D
damaged database recovery, 85-86. See also restoring; troubleshooting
data
availability, 2, 31-43
file types, 17
automatic file growth, 161
creating files, 165
fragmentation, 157-58
loading, 46, 162, 179-86
types changing, 55-56
Database Creation Wizard, 161, 165
databases
access assignment, 104, 117-20, 123-24, 173-74, 203-4
adding data files, 175-76
backups. See backups
bulk copying, 185-86
columns, 173
configuring, 185-86
creating, 161, 165, 169-74
DBCC (Database Consistency Checker), 255
DBO Use Only option, 203-4
defaults, 165
filegroups, 161
Filegrowth option, 171-72
files, 17
growth increment, 171-72, 176
guest user accounts, 121-22
jobs, 227-28
linked, 164, 239
master, 111-12, 117
master database backup, 169-70
model, 112
name, display, 207-8
objects, permission to modify, 11-12
options, 161, 165, 173-74
primary data files, 22, 165-66
primary filegroups, 167, 171-72
Properties window, 185-86
recovering, 169-70
restoring, 162-63, 203-13
roles, fixed, 11-12
secondary data files, 21-22, 165-66
Select Into/Bulk Copy checkbox, 185-86, 191
size, 18, 165-66, 171-72, 176
sp_dboption, 161
standby, 310-11
SYSDATABASES table, 170
tempdb, 112, 117
transaction log files, 165-66
Truncate Log On Checkpoint option, 317-18
Databases objects, 256
Data Transformation Services. See DTS (Data Transformation Services)
db_accessadmin, 128
db_backupoperator, 128, 193-94
db_datareader, 11-12, 128
db_datawriter, 11-12, 128
db_ddladmin, 11-12, 128, 133-34
db_denydatareader, 128
db_denydatawriter, 128
db_owner, 14, 128-29
backups, 193-94
pull subscriptions, 223-24
push subscriptions, 221-22
db_securityadmin, 11-12, 128-29
DBCC (Database Consistency Checker), 151, 157-58, 255
dbcreator role, 9-10, 127
DBO Use Only option, 173-74, 203-4
dbo user accounts, 104
deadlocks, 263-64, 369-70
deadlocks/sec, 259-60
default filegroups, 18, 167
deleting devices, 51-52
DENY permission state, 140, 147-48
devices
backups, 189, 197-98, 203
deleting, 51-52
Dictionary order, 74
differential database backups, 31, 39-42, 162, 191, 197-98
restoring, 163, 205
disk
devices, 31
mirroring, 18, 23-24
space, upgrade requirements, 301-2
striping, 18
striping with parity, 25-26
diskadmin role, 9-10, 127
distributed file system, 17
distributed queries. See queries, distributed
Distributors, 163, 215
Distributor servers, 59
domains
remote systems communications, 21-22
user accounts, 11-12
DTS (Data Transformation Services), 46, 162, 187
Designer, 162, 180
Wizards, 162, 179, 181-84
loading packages, 183-84
security, 181-82
sort order restore, 311-12
DTS Designer, 180
E
encryption, passwords, 145-46
Enterprise Manager, 161-64
access assignment, 117
account assignment, 107
application roles, 129
complete database backups, 190
Configure Publishing and Distribution Wizard, 215
creating databases, 165, 173-74
Current Activity, 151
differential database backups, 191
enable full-text indexing, 99-100
filegroup backups, 191
fixed database roles, 128
fixed server roles, 127
group access assignment, 119-20
job alerts, 228
linked database login mappings, 239
login creation, 104
migrating Access objects, 55-56
naming backup devices, 189
optimization options, 270-71
options, changing, 173-74
query cost limits, 285
remote administration, 85-86
restricting database access, 203-4
server registration, 85-86
size of databases, 166
truncating transaction logs, 190
user-defined database roles, 129
verify backup devices, 203
error messages
ANSI, 84
backups, 309, 311-12
columns, 349-50
jobs, 228
Msreplication_subscriptions invalid object, 329-30
notification failures, 335-36
provider, 343
queries, 343-53
restoring, 309, 311-12
send to EventLog service, 235-36
sp_addmessage, 154
storage, 233-34
transaction log backup not allowed, 317-18
warm-standby state, 311-12
EventLog service, 235-36, 337-38
Event Viewer, 151
events
auditing, 153-54
notifications, 233-34, 335-40
EXEC statement, 145-46
Executive settings
transferring, 49-50
upgrading, 77-78
exporting data. See data, loading
F
-f option, 86
failover, 33
fault tolerance, 23-26
file/filegroup backups, 31
filegroups, 17-18, 166-67
administration, 26
backups, 162, 191
creating, 161, 165
distribution on disks, 18
primary, 167, 171-72
restoring backups, 163
size, 175-76
files, 17, 161
fixed database roles, 6, 11-12
fixed server roles, 6, 9-10, 127, 135-36
fragmentation, 157-58
Free Buffers counters, 270
Full-Text Indexing Wizard, 95
full-text searching. See Microsoft Search service
functions, Transact-SQL, 151
G
General Statistics objects, 256-57
GRANT permission state, 140, 143-44, 147-48
groups, 9-10. See also roles
access assignment, 117-20, 131-32
accounts, 107, 371-72
Administrators local, 6, 11-12
connections, 371-72
names, determining, 113-14
organization, 13-14
roles as, 123-24
security ID numbers, 113-14
SQL login accounts, 111-12
growth, automatic, 166
guest accounts, 104
H
hackers, unauthorized login attempts, 261-62
history values, alerts, 337-38
I
I/O requests, maximum, 271
importing data. See data, loading
Index Create Memory option, 270
indexes, 154. See also Microsoft Search service
creating, 244-45
fragmentation, 157-58
Index Tuning Wizard, 151, 155-56, 253, 256, 262-63
linked servers, 240, 243-44
memory, 270
Index Tuning Wizard, 151, 155-56, 253, 256, 262-63
INIT option, 37-38
INSERT statement, 46, 162, 179, 187
installation, 69-81
character sets, 71, 73-74
client network connection, 71
custom, 97
full-text searching, 70, 95
instancing, 71
Management Tools, 71
Microsoft Search service, 95-100
services account, 71-72
setup initialization files, 72, 75-76
SMS (Systems Management Server), 72
sort order, 73-74
unattended, 69, 71-72, 75-76
Unicode, 69, 71
Upgrade Wizard, 70
instancing
installation, 71
object, 257
IPX/SPX, 19, 359-60
ISQL utility, 358
J
jobs, 134, 163, 227-28
defining, 227-28, 231-32
scheduling, 228, 231-32
SQLAgentMail, 229
troubleshooting, 333-41
K
keys, linked servers, 240
KILL command, 368
L
latches, 256
Level 0 RAID, 18
Level 1 RAID, 18, 23-24
Level 5 RAID, 19, 25-26
libraries. See network libraries
linked servers, 1, 7, 9-10, 164, 239-51
default mapping, 239
logins, creating, 245-46
OLE DB data source initialization failure, 345-46
Oracle, 247-48
registration, 351-52
remote servers, 249-50
security, 245-46
troubleshooting, 345-52
loading
bcp utility, 185-86
data, 42, 162, 179-86
Use a Query option, 183-84
locks, 154, 255-56
lock-out clients, 85-86
objects, 256
wait time, 339-40
log files, 2. See also transaction logs
automatic growth, 161
upgrade, 297, 299-300, 305-6
xp_logevent, 154
LoginFailed event class, 261-62
logins
accounts, 103-4, 107-8, 119-20
administration, 135-36
default database, 111-12
error messages, 347-48
folders, 119-20
group, 371-72
guest, 121-22
linked servers, 245-46
LoginFailed event class, 261-62
modes available, 5
object, 119-20
remove entries, 110
logins (continued)
sp_addlogin, 109-10, 120
sp_change_users, 110
sp_grantbaccess, 110
sp_grantlogin, 109-10
sp_revokelogin, 110
specified hours, 11-12
SQL Server, 107, 109-12
unauthorized attempts, 261-62
upgrades, 303-6
Windows NT, 9-10
M
-m option, 85-86
Macintosh clients, AppleTalk network library, 19
mail. See SQL Mail; SQLAgentMail
MAKEPIPE utility, 357-58
Management Tools, installation, 71
master databases, 111-12
backup, 169-70
user-defined error message storage, 233-34
master domain, remote systems communications, 21-22
Max Async IO options, 271
Max Degree of Parallelism, 271
Max Server Memory options, 270, 276
Max Worker Threads options, 270-71
MEDIANAME option, 35-36
media sets, 35-36
memory
Available Bytes counters, 273-74
Buffer Cache Hit Ratio counters, 275-76
counters, 254, 269-70
distribution database, 221-22
indexes, 270
optimization, 281-83
Page Faults/sec counters, 281-82
queries, 270
shortage, 273-76
Total Server Memory (KB) counters, 257, 270
Memory Manager, 256-57
merge replication, 59-62, 325-26, 329-30
metadata, linked servers, 164
Microsoft Access, migrating to SQL Server 7.0, 55-56
Microsoft Exchange Server, 89-90
Microsoft Search service, 6, 95-101
clustering, 100
index repopulation, 96
installation, 70, 95-100
sp_fulltext_database, 97-98
sp_fulltext_table, 97-98
Microsoft Transaction Server, 7
Microsoft Windows 95
connections, 361-62
replication, 327-28
Microsoft Windows 98, connections, 359-62
Microsoft Windows NT
Event Viewer, 151, 337-38
login, 5
remote systems communications, 21-22
SID (security identifier), 371-72
migrating data, 45-57. See also upgrading
methods available, 46
Microsoft Access, 55-56
named pipe method, 45
settings, transfer, 49-50
strategy, 3
tape method, 45
Upgrade Wizard, 49-50
upgrading, 45, 49-52
Web sites, 57
Min Memory Per Query option, 270
Min Server Memory option, 270
monitoring. See audits; performance
MSDTC (Microsoft Distributed Transaction Coordinator) service, 6
MSSQLServer service, 6, 11-12, 83-86
Multiprotocol network libraries, 19, 359-60
N
Named Pipes, 19
devices, 31
method, 45
Net-Library, 355, 357-60
Net-Libraries. See network libraries
net start mssqlserver -m, 85-86
NET VIEW, 357-58
network libraries, 19, 355, 359-62
configuration, 87-90
installing, 69
nodes, 32
NOINIT option, 37-38
nonlogged operations, 173-74
bulk copy, 185-86
notifications, 228, 233-34, 237
delays, 335-36, 339-40
failures, 335-36
multiple operators, 339-40
operator’s on-duty schedule, 335-36
NOUNLOAD option, 37-38
Novell NetWare, 19
NWLink IPX/SPX network library, 19
O
object creation scripts, 55-56
objects, permissions, 133-34, 139, 143-44
ODBC procedures, 92
OLE DB
data source, initialization failure, 345-46
linking servers, 247-50
procedures, 92
operators, 164, 237
fail-safe, 229, 231-32
job notifications, 228
multiple, 339-40
notifications, 228, 233-34
on-duty schedule, 335-36
optimization, 253, 269-83
CPUs, 253, 270-71
Enterprise Manager, 270-71
memory, 253, 269-70, 281-83
queries, 254, 285-95
servers, 270-71
options, database, 91-92
Oracle, linking servers, 247-48
orphaned sessions, 373
OSQL utility, 358
P
Page Faults/sec counters, 269, 281-82
Page Reads/sec counters, 257
pages, allocation, 154
Pages/sec counters, 269
Page Writes/sec counters, 257
paging, 281-82
Password Never Expires attribute, 11-12
passwords
DTS packages, 181-82
encryption, 145-46
linked servers, 245-46, 345-46
performance, 154, 253, 255-67
CPUs, 255
Create Trace Wizard, 264-65
Current Activity window, 255, 260
DBCC (Database Consistency Checker), 255
deadlocks, 259-60, 263-64
filegroups, 18, 166
file placement, 17
Index Tuning Wizard, 253, 262-63
objects, 253
Performance Monitor, 253, 255-56
Profiler, 255-56
reads, 23-26
servers, 270-71
types of, 167
Performance Monitor, 151, 253, 255-56
Available Bytes counters, 273-74
Buffer Cache Hit Ratio counters, 275-76
deadlocks/sec, 259-60
memory, 281-82
Page Faults/sec counters, 281-82
replication, 321
SQL counters, 253
permissions, 6, 139-49
application roles, 141
assignment, 105
database-level, 11-12
dbcreator, 139
deleting data, 147-48
DENY, 140, 147-48
distributed queries, 239, 349-50
Distributors, 215
encrypted passwords, 145-46
EXEC statement, 145-46
GRANT, 140, 143-44, 147-48
guest accounts, 117
ignore existing, 311-132
implied, 139
objects, 139, 143-44
public role, 121-22, 128
replication, 323-24
REVOKE, 140, 147-48
roles, 6, 127-29, 143-44
SELECT, 143-44
Select, 369-70
server-level, 9-10
sp_setapprole, 145-46
permissions (continued)
statement, 139, 144
states, 105, 140
sysadmin, 139
table columns, 145-46
types, 105, 139
user, 369-70
views, 145-46
physical disks
backup, 31
failure, recovery from, 190-91
fault tolerance, 23-26
primary database files, 22
RAID, 23-26
secondary database files, 21-22
space used, monitoring, 255
planning, 1-3
migrating data, 45-57
replication strategy, 59-67
security, 5-16
server capacity, 17-29
primary database files, 17, 22
primary data files, 165-66
primary filegroups, 18, 167, 171-72
Priority Boost options, 271
processadmin, 127
processes
memory use, 269
monitoring, 255
Processor Queue Length counters, 271, 278-80
Profiler, 151, 155-56, 253, 255-56, 260
Create Trace Wizard, 263-65
Index Tuning Wizard, 253, 256, 262-63
OLE DB errors, 343
unauthorized login attempts, 261-62
profiles, configuration, 89-90
Properties window, 173-74
protocols, installing, 69
protocol stacks, 359-60
publications, 163, 216
public roles, 6. See roles, public
Publishers, 53-54, 163, 215
Publisher servers, 59
Publishers and Subscribers, upgrading, 53-54
pull subscriptions, 163, 223-22, 225, 327-28
push subscriptions, 163, 221-22, 329-30
Q
queries
cost values, 254, 285-95
distributed, 46, 239, 241-42, 343-53
error messages, 343-48
Governor option, 289-90, 293-94
measuring, 155-56
memory, 270
OLE DB data source initialization failure, 345-46
permissions, 349-50
Query Analyzer, 151, 155-56
registration errors, 349-50
response time, 255
SET QUERY_GOVERNOR_COST_LIMIT statement, 285-88, 291-92
sp_configure system, 285, 289-90
syntax errors, 349-50
tracing, 153-54
troubleshooting, 343-53
Query Analyzer, 151, 155-56, 321
adding data files, 175-76
traces, 264-65
Query Governor options, 289-90, 293-94
R
RAID (redundant array of independent disks), 2, 166
disk mirroring, 18
disk striping, 18
Level 0, 18
Level 1, 18, 23-24
Level 5, 19, 25-26
levels supported, 18-19
RAISERROR statement, 235-36
read-only data, replication model for, 63-64
READPIPE utility, 357-58
recovering databases, 85-86, 169-70. See also restoring
Recovery option, 163, 315-16
Recurring schedule option, 231-32
registration, linked servers, 239
registry, restore corrupted entries, 358
REGREBLC utility, 358
remote connections, configuring, 87-88
remote servers
linking, 249-50
security, 11-12
Windows NT systems, 21-22
replication, 46, 59-67, 163, 215-25
Central Publisher/Distributor model, 59-60, 63-64
Central Subscriber/Multiple Publishers model, 60, 65-66
Configure Publishing and Distribution Wizard, 215
configuring, 163
Create Publication Wizard, 216
data validation, 321
Distributor servers, 59
Distributors, 215
replication (continued)
from 7.0 to 6.5, 329-30
jobs, 227-28
merge, 59-62, 216, 219-20, 223-24, 325-26, 329-30
models, 59-60
Monitor, 321, 325-26
Multiple Publishers/Multiple Subscribers model, 60
objects for monitoring, 257
permissions, 323-24
publications, 216
Publishers, 215
Publisher servers, 59
pull subscriptions, 327-28
push subscriptions, 329-30
Query Analyzer, 321
QueryTimeOut value, 329-30
read-only data, 63-64
registering Subscribers, 329-30
REPLP70.SQL script, 329-30
settings, 49-50
snapshot, 59-62, 216, 219-20, 327-28
strategy, 3
Subscribers, 59, 63-64, 215
Subscriber servers, 59
subscriptions, 216, 221-23
timeout failure, 329-30
transactional, 59-64, 216, 219-20
troubleshooting, 321-31
upgrading settings, 77-80
Windows 95, 327-28
Replication Agents, 256
Replication Dist. object, 257
Replication Logreader object, 257
Replication Merge object, 257
Replication Monitor, 321, 325-26
Replication Snapshot object, 257
Replicator Distributor server, upgrading, 53-54
REPLP70.SQL script, 329-30
RESTORE DATABASE statements, 163, 204-5, 209-10
RESTORE FILELISTONLY statement, 207-8
RESTORE LOG statements, 205, 209-12
RESTORE statements, 203-4, 207-10, 315-16
restoring
backups, 41-42
complete database backups, 203-4
databases, 162-63, 203-13
differential backups, 163, 205
error messages, 309, 311-12
filegroup backups, 163, 205
gap in transaction logs, 313-14
multiple files, 209-10
NORECOVERY option, 205, 207-12, 315-16
RECOVERY option, 204-5, 207-10, 315-16
sort order, 311-12
Stopat option, 163, 205, 211-12
transaction logs, 163, 204-5, 209-12, 313-16
troubleshooting, 309-19
verifying devices, 203, 207-8
REVOKE permission state, 140, 147-48
roles, 1, 5-6, 104, 127-37
access assignment, 123-24
activating, 105
ap_addapprole, 129
application, 7, 104-5, 127, 129, 131-32, 141
backups, 193-94
bd_backupoperator, 131-32
db_accessadmin, 128
db_backupoperator, 128
db_datareader, 128
db_datawriter, 128
db_ddladmin, 128, 133-34
db_denydatareader, 128
db_denydatawriter, 128
db_owner, 128-29
db_owner fixed database, 14
db_securityadmin, 128-29
dbcreator, 9-10, 127
diskadmin, 9-10, 127
fixed database, 11-12, 127-28, 133-34
fixed server, 9-10, 127, 135-36
permissions, 121-22, 143-44
processadmin, 127
public, 14, 121-22, 128
public fixed database, 14
push subscriptions, 221-22
roles (continued)
securityadmin, 9-10, 127, 135-36
serveradmin, 127
setupadmin, 9-10, 127
sp_addrole, 129
sp_addrolemember, 128-29
sp_addsrvrolemember, 127
sp_droprolemember, 131
sp_setapprole, 131-32
sysadmin, 107-8, 127
types, 6
user-defined database, 6, 13-14, 104, 127
Web site, 15
rule, 134
S
saving DTS packages, 183-84
scalability, 21-22
schema, transferring, 181-82
Search service. See Microsoft Search service
secondary database files, 17, 21-22, 165-66
security, 103-5
access assignment, 117-24
application roles, 7, 129, 131-32
applications, 5
authentication, 5
components run as NT services, 6
folder, 119-20
linked databases, 164, 239
linked servers, 7, 9-10, 245-46
login accounts, 107-15
permissions, 139-49
planning, 5-16
remote servers, 11-12
roles, 5-6, 127-37
securityadmin role, 135-36
sp_grantbaccess, 110
strategy, 1, 5-15
unauthorized login attempts, 261-62
securityadmin, 9-10, 127, 135-36
SELECT INTO statements, 46, 162, 179, 187
Select Into/Bulk Copy option, 174
Select permission, 369-70
SELECT statement, 143-44
Server Alias Configurations list, 355
serveradmin, 127
server capacity
filegroups, 17
scalability, 21-24
Server Network utility, 88, 361-62
servers
clusters, 32
configuration options, 271
Distributor, 59
failure recovery, 154
fixed roles, 9-10
linked, 7, 9-10, 164, 239-51
Named Pipes, 357-58
names, 303-4
optimization, 270-71
Oracle, 247-48
performance, 255, 270-71
planning capacity, 17-29
Publisher, 59
replication, 53-54
standby, 32, 311-12
Subscriber, 59
Total Server Memory (KB) counters, 270
virtual, 33
Windows 98, 359-60
Services account, installation, 71-72
sessions
closing, 367-68
orphaned, 367-68, 373
SET QUERY_GOVERNOR_COST_LIMIT statement, 285-88, 291-92
SET ROWCOUNT statements, 292
SET statements, 151
setupadmin, 9-10, 127
setup initialization files, 72, 75-76
Setup program, domain user account default, 12
SID (security identifier), 371-72
single-user mode, 85-86
size of databases, 171-72, 175-76
SMS (Systems Management Server), 72
snapshot replication, 59-62, 327-28
sort order
changing, 367-68
installation, 73-74
restoring, 311-12
sp_addlinkedserver, 164, 239, 247-51, 351-52
sp_addlinkedsrvlogin, 164, 239, 245-46, 251, 347-48
sp_addlogin, 120
sp_addmessage, 154
sp_addpublisher70, 329-30
sp_addrole, 129
sp_addrolemember, 128-29
sp_addserver, 304
sp_addsrvrolemember, 127
sp_addumpdevice, 162, 189, 197-98, 201
sp_altermessage, 236
sp_article_validation, 321
sp_catalogs, 239
sp_change_users, 110
sp_column_privileges_ex, 239
sp_columns_ex, 240
sp_configure, 270-71, 285, 289-90
sp_configure Allow Updates option, 86
sp_dboption, 83, 91-92, 161, 165, 190
sp_droplinkedsrvlogin, 164, 239
sp_droprolemember, 131
sp_dropserver, 164, 239, 304
sp_foreignkeys, 240
sp_fulltext_database, 97-98
sp_fulltext_table, 97-98
sp_grantdbaccess, 110, 119-20
sp_grantlogin, 109-10
sp_helprevdatabase, 54
sp_indexes, 240, 243-44
sp_linkedservers, 239
sp_lock, 154, 255
sp_monitor, 154, 255
sp_primarykeys, 240
sp_processmail, 89-90
sp_publication, 321
sp_revokelogin, 110
sp_serveroption, 164, 239, 241-42, 251
sp_setapprole, 131-32, 145-46
sp_spaceused, 255
sp_statistics, 154
sp_table_privileges_ex, 240
sp_tables_ex, 240
sp_who, 153-54, 255, 262
SQLAgentMail, 164, 227, 229, 237
configuring, 70, 83, 89-90
setup, 229
SQL Compilations/sec counters, 257
SQL Distributed Management Objects (SQL-DMO), 95
SQL Mail, 11-12
automated processing, 89-90
configuration, 70, 83, 89-90
SQL Server Agent. See Agent
SQLServerAgent service, 6, 11-12, 83, 337-38
SQL Server logins, 5, 9-10
SQL Server Profiler. See Profiler
SQL Server Statistics object, 257
standby servers, 32
stored procedures. See system stored procedures
stripe sets, 18
striping with parity, 19
Subscribers, 53-54, 163, 215, 221-22
anonymous, 216
registering, 215-16, 329-30
Subscriber servers, 59
subscriptions, 216
pull subscriptions, 223-25
push subscriptions, 221-22
SUSER_NAME() function, 113-14
sysadmin, 127
backups, 193-94
pull subscriptions, 222-24
push subscriptions, 221-22
sysmessages
severity, 236
table, 223-34
sysmoperators table, 223-34
sysnotifications table, 223-34
system files, 17
system stored procedures. See also sp_ entries
permission to execute, 143-44
Transact SQL, 151
User Settable objects, 257
T
tables
Central Subscriber/Multiple Publishers model, 60, 65-66
Drop and Recreate option, 183-84
full-text indexing, 99-100
linked servers, 240
transferring data, 179
tape devices, 31, 37-38
tape method, 45
task, 134
TCP/IP (Transmission Control Protocol/Internet Protocol)
connectivity, 359-60
network library, 19
tempdb, 53-54
threads, total available, 271
Total Server Memory (KB) counters, 257, 270
traces, 255-56, 264-65
deadlocks, 369-70
flags, 86, 373
Transact SQL, 151
transactional replication, 59-64
transaction logs
alerts, 335-36, 339-40
backups, 31, 39-42, 162, 190-91, 197-200, 203,
209-10, 313-14, 317-18
complete database backups, 189-90
creating, 161, 165
files, 17
gap in, 313-14
increasing size, 166
minimize maintenance, 55-56
name, display, 207-8
nonlogged operations, 173-74
not allowed error message, 317-18
not rolled forward error, 313-14
restoring, 204-5, 209-12, 313-16
restoring backups, 163
Stopat option, 211-12
Truncate Log on Checkpoint option, 55-56
truncation, 190, 199-200
transaction-log space, 158
transactions, objects for monitoring, 257
Transact-SQL, 95, 151, 157-58, 260
DBCC (database consistency checker), 151
functions, 151
SET statements, 151
system stored procedures, 151
trace flags, 151
transferring data. See data, loading
troubleshooting, 297-98
6.x, upgrading from, 297, 299-307
access problems, 365-73
alerts, 333-41
backups, 309-19
Client Network utility, 298
connections, 298, 355-63
deadlocks, 369-70
jobs, 333-41
linked servers, 345-52
orphaned sessions, 367-68
queries, 343-53
replication, 321-31
restoring, 309-19
upgrading checklist, 299
Truncate Log On Checkpoint option, 55-56, 173-74,
317-18
tuning. See optimization
U
unattended installation, 69, 71-72, 75-76
Unicode
data types, 53-54
installing, 69, 71
Upgrade log files, 297, 299-300, 305-6
Upgrade Wizard, 45, 49-50, 54, 70, 72, 77-80, 299-304
upgrading 6.x to 7.0, 45, 49-52, 297, 299-307
@@SERVICENAME function, 303-4
backups during, 301-4
checklist, 299
consolidate databases, 79-80
cross-database dependencies, 51-52
custom sort orders, 53-54
Executive settings, 77-78
log files, 297, 299-300, 305-6
Publishers and Subscribers, 53-54
replication servers, 53-54
replication settings, 77-80
Replicator Distributor server, 53-54
tape backup during, 301-2
tempdb, 53-54
troubleshooting, 301-7
Unicode data types, 53-54
Web sites, 57
Wizard, 49-50, 72, 77-80, 299-304
Use a Query option, 183-84
User Connections counters, 257
User Settable objects, 257
user-defined database roles. See roles, user-defined database
user-defined error message storage, 233-34
user-defined filegroups, 18, 167
users
account assignment, 123-24
accounts, 117, 119-20
auditing, 153-54
upgrades, 303-6
V
verifying backups, 203
view data, 12
W
warm spares, 32
warm-standby state, 311-12
warnings, ANSI, 84
Windows 95. See Microsoft Windows 95
Windows 98. See Microsoft Windows 98
Windows NT. See Microsoft Windows NT
Working Set counters, 269
write-caching mechanisms, 27-28
X
xp_logevent, 154
xp_trace_setqueryhistory, 153-54
Forewords & Introductions
Chapter 5.|Monitoring and Optimization- Objective Domain 5: Monitoring and Optimization
- Tested Skills and Suggested Practices
- Objective 5.2: Tune and optimize SQL Server.
- Key Terms
- Further Reading
Objective Domain 5: Monitoring and Optimization
The Monitoring and Optimization domain examines how you can monitor system performance and optimize system resources for SQL Server. You can use SQL Server Profiler and Windows NT Performance Monitor to monitor system performance and determine whether performance problems exist. If they do, you can resolve them by tuning and optimizing SQL Server or by limiting resources used by queries.
Tested Skills and Suggested Practices
- Monitoring SQL Server performance. Be able to use SQL Server Profiler and the counters SQL Server adds to Windows NT Performance Monitor to monitor SQL Server components.
- Practice 1: Learn the purpose of SQL Server Profiler. Use SQL Server Profiler to monitor performance by setting up traces to collect information. Learn how the Index Tuning Wizard uses a workload file captured by SQL Server Profiler to analyze trace results.
- Practice 2: Learn the purpose of the counters SQL Server adds to Performance Monitor. Use Performance Monitor to monitor performance. Learn the purpose of SQL Server objects, object instances, and counters. View the results of the counters on the Performance Monitor chart.
- Tuning and optimizing SQL Server. Be able to tune and optimize SQL Server memory and CPU usage.
- Practice 1: Familiarize yourself with the counters used in Windows NT Performance Monitor to indicate SQL Server memory and CPU usage.
- Practice 2: Familiarize yourself with the configuration options used to tune SQL Server memory and CPU usage.
- Limiting resources used by queries. Be able to use the query cost value to control resources used by queries.
- Practice 1: Set the query cost value for the local server. Set the query cost value for the current session.
Objective 5.2: Tune and optimize SQL Server.
After monitoring your SQL Server, you may find the need to tune and optimize SQL Server memory and CPU usage. SQL Server 7.0 is designed to tune many server configuration options automatically, requiring little tuning by a system administrator. It is recommended that configuration options be left at their default values, which allows SQL Server to auto-tune based on run-time conditions.
You must periodically monitor and analyze the computer running SQL Server to confirm that memory usage is within normal ranges. Analyze the following Performance Monitor counters to determine memory usage:
- Memory: Available Bytes: Number of bytes of memory currently available for use by processes. Low values may indicate an overall shortage of memory or that an application is not releasing memory.
- Memory: Pages/sec: Number of pages that were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults. A high rate may indicate excessive paging.
- Memory: Page Faults/sec: Number of page faults caused by hard page faults when pages must be retrieved from disk or by writes to disk to free space in the working set. May indicate if disk activity is caused by paging.
- Process: Page Faults/sec: Number of page faults for the SQL Server process instance. May indicate if SQL Server rather than another process is causing excessive paging.
- Process: Working Set: Memory used by a process. May indicate that SQL Server is configured for too much memory if consistently below memory configuration amount. May indicate that a change is necessary in working set size.
- SQL Server: Buffer Manager: Buffer Cache Hit Ratio: Percentage of pages found in the buffer cache without having to read from disk. A rate of 90 percent or higher is desirable and indicates that more than 90 percent of all requests for data were satisfied from the data cache.
- SQL Server: Buffer Manager: Free Buffers: Number of free buffers available. Low values may indicate that more memory is required.
- SQL Server: Memory Manager: Total Server Memory (KB): Total amount of dynamic memory that the server is currently using. High values (compared with the amount of physical memory) may indicate that more memory is required.
You can also manage and optimize memory resources through configuration options using SQL Server Enterprise Manager or the sp_configure system stored procedure. Although it is recommended that configuration options be left at their default values, the following options can be configured to optimize server performance in response to SQL Server memory usage issues:
- Min Server Memory: Specifies the minimum amount of memory (in MB) SQL Server can use.
- Max Server Memory: Specifies the maximum amount of memory (in MB) SQL Server can use.
- Max Worker Threads: Specifies the total number of threads available to SQL Server. Generally, this configuration value should be set to the number of concurrent connections, but it cannot exceed 1,024.
- Index Create Memory: Specifies the maximum amount of memory (in KB) SQL Server can use when creating indexes.
- Min Memory Per Query: Specifies the minimum amount of memory (in KB) SQL Server can use for the execution of a query.
You should periodically monitor and analyze the computer running SQL Server to determine if CPU utilization rates are within normal ranges. Analyze the following Performance Monitor counters to determine CPU usage:
- Processor: % Processor Time: Amount of time the CPU spends processing a non-idle thread. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors.
- System: % Total Processor Time: Average amount of time that all CPUs in the system spend processing non-idle threads. A consistent state of 80 to 90 percent may indicate the need for CPU upgrades or the addition of more processors.
- Processor: % Privileged Time: Percentage of time the CPU spends executing Windows NT kernel commands. If consistently high in conjunction with the Physical Disk counters, SQL Server could be I/O-bound. Consider upgrading the disk subsystem.
- Processor: % User Time: Percentage of time the CPU spends executing user processes such as SQL Server. If consistently high, may indicate the need for a CPU upgrade or the addition of more processors.
- System: Processor Queue Length: Number of threads waiting for processor time. If more than a few program processes are contending for most of the processor’s time, may indicate the need for a CPU upgrade or the addition of more processors.
You can also manage and optimize CPU resources through configuration options using SQL Server Enterprise Manager or the sp_configure system stored procedure. Although it is recommended that configuration options be left at their default values, the following options can be configured to optimize server performance in response to SQL Server CPU usage issues:
- Affinity Mask: Specifies processors on which SQL Server threads can run.
- Cost Threshold for Parallelism: Specifies the cost threshold for creating parallel queries.
- Max Async IO: Specifies the maximum number of asynchronous I/O requests that the server can issue against a file. Generally, leave the Max Async IO server configuration option at the default value of 32. For very sophisticated I/O subsystems with many disks and controllers, the value can be increased to 64 or possibly higher.
- Max Degree of Parallelism: Specifies the maximum number of threads used to execute parallel queries.
- Max Worker Threads: Specifies the total number of threads available to SQL Server processes.
- Priority Boost: Allows SQL Server to run at a higher priority than other processes running under Windows NT.
Questions related to this objective are designed to determine if you have an awareness of these issues. To successfully answer the questions for this objective, you need a firm understanding of several key terms. For definitions of these terms, refer to the Glossary in this book.
- Cache
- Configuration options
- Input/output (I/O)
- Optimize
- Page faults
- Paging file
- Performance Monitor
- Thread
- Tuning
- Working set
70-028.05.02.001
You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. You find that the Available Bytes counter value is very low. Which condition could be indicated by this value?
- The Max Server Memory option is set too low.
- There is a surplus of memory for SQL Server.
- The system is experiencing an overall memory shortage.
- SQL Server is consuming too much of the system’s available memory.
70-028.05.02.001
You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. You find that the Available Bytes counter value is very low. Which condition could be indicated by this value?
Correct Answer: C
- Incorrect: The low setting of the Max Server Memory option does not cause the Available Bytes counter to be low.
- Incorrect: The Available Bytes counter indicates the number of bytes of memory currently available for use by system processes. A low number of available bytes does not indicate a surplus of memory for SQL Server.
- Correct: The Available Bytes counter indicates the number of bytes of memory currently available for use by system processes. A low number of available bytes may indicate an overall shortage of memory or that an application is not releasing memory.
- Incorrect: A low number of available bytes does not necessarily indicate that SQL Server is consuming too much of the system’s available memory. To monitor the amount of memory being used by SQL Server, examine the following performance counters: Process: Working Set, SQL Server: Buffer Manager: Buffer Cache Hit Ratio, SQL Server: Buffer Manager: Free Buffers, SQL Server: Memory Manager: Total Server Memory (KB).
70-028.05.02.002
You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. What should you do if the Buffer Cache Hit Ratio value is 60 percent?
- Reduce the size of the working set.
- Decrease the Max Server Memory option.
- Add more memory until the value is 90 percent or higher.
- Determine which applications are not releasing memory.
- Monitor the Available Bytes counter, and increase memory if its value is too high.
70-028.05.02.002
You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. What should you do if the Buffer Cache Hit Ratio value is 60 percent?
Correct Answer: C
- Incorrect: Reducing the size of the working set reduces the physical memory space available for SQL Server. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk and it cannot be set by reducing the size of the working set.
- Incorrect: Decreasing the Max Server Memory option decreases the maximum amount of memory (in MB) in the buffer pool that SQL Server uses (not the amount of memory used by the entire process). The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk and it cannot be set by decreasing the Max Server Memory option.
- Correct: The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk. A value of 90 percent or higher is desirable. If the value is 60 percent, you should add more memory until the value is consistently greater than 90 percent.
- Incorrect: Determining which applications are not releasing memory does not affect the value of the buffer cache hit ratio, which is the percentage of pages found in the buffer cache without having to read from disk.
- Incorrect: The Available Bytes counter indicates how many bytes of memory are currently available for use by processes. High values for the Available Bytes counter indicate that there is a memory surplus and you do not need to increase memory. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk and it is not set by evaluating the number of available bytes.
70-028.05.02.003
SQL Server is running on a multiprocessor system. You need to determine if the CPU usage rates are within an acceptable range. Which Performance Monitor counter should you use to determine the average amount of time all of the processors spend processing non-idle threads?
- Processor: % User Time
- Processor: % Privileged Time
- System: % Total Processor Time
- System: Processor Queue Length
- Processor: % Processor Time
70-028.05.02.003
SQL Server is running on a multiprocessor system. You need to determine if the CPU usage rates are within an acceptable range. Which Performance Monitor counter should you use to determine the average amount of time all of the processors spend processing non-idle threads?
Correct Answer: C
- Incorrect: The Processor: % User Time counter indicates the percentage of time the processor spends executing user processes such as SQL Server.
- Incorrect: The Processor: % Privileged Time counter indicates the percentage of time the processor spends executing Windows NT kernel commands such as processing SQL Server I/O requests.
- Correct: The System: % Total Processor Time counter combines the average processor usage of all processors into a single counter.
- Incorrect: The System: Processor Queue Length counter indicates the number of threads waiting for processor time.
- Incorrect: The Processor: % Processor Time counter indicates the processor usage of one processor.
70-028.05.02.004
You are using Performance Monitor to monitor the CPU usage for the computer running SQL Server.
The required result is to determine the percentage of time the processor spends processing SQL Server I/O requests.
The first optional result is to determine the percentage of time the processor spends executing user processes.
The second optional result is to determine the number of threads waiting for processor time.
The proposed solution is to use Performance Monitor to activate the SQL Server: Buffer Manager: Buffer Cache Hit Ratio, the Processor: % User Time, and the System: Processor Queue Length counters.
What does the proposed solution provide?
- The required result and all optional results.
- The required result and one optional result.
- The required result but none of the optional results.
- The proposed solution does not provide the required result.
70-028.05.02.004
You are using Performance Monitor to monitor the CPU usage for the computer running SQL Server.
The required result is to determine the percentage of time the processor spends processing SQL Server I/O requests.
The first optional result is to determine the percentage of time the processor spends executing user processes.
The second optional result is to determine the number of threads waiting for processor time.
The proposed solution is to use Performance Monitor to activate the SQL Server: Buffer Manager: Buffer Cache Hit Ratio, the Processor: % User Time, and the System: Processor Queue Length counters.
What does the proposed solution provide?
Correct Answer: D
- Incorrect: See the explanation for answer D.
- Incorrect: See the explanation for answer D.
- Incorrect: See the explanation for answer D.
- Correct: The Buffer Cache Hit Ratio, the % User Time, and the Processor Queue Length counters cannot determine the percentage of time the processor spends processing SQL Server I/O requests. To determine the percentage of time the processor spends processing SQL Server I/O requests, use the Processor: % Privileged Time counter. To determine the percentage of time the processor spends executing user processes, use the Processor: % User Time counter. To determine the number of threads waiting for processor time, use the System: Processor Queue Length counter.
70-028.05.02.005
Why should you monitor the Performance Monitor Memory: Page Faults/sec counter?
- To ensure that disk activity is not caused by paging
- To determine if SQL Server is consuming too much memory
- To determine the average number of pages read from memory each second
- To ensure that pages are not being stolen from SQL Server by the Virtual Memory Manager
70-028.05.02.005
Why should you monitor the Performance Monitor Memory: Page Faults/sec counter?
Correct Answer: A
- Correct: The Performance Monitor Memory: Pages/sec counter indicates the number of page faults caused by hard page faults when pages must be retrieved from disk or by writes to disk to free space in the working set. Since a high rate for the Pages/sec counter may indicate excessive paging, you should monitor the Memory: Page Faults/sec counter to make sure that paging does not cause the disk activity.
- Incorrect: To monitor the amount of memory being used by SQL Server, you should examine the following performance counters: Process: Working Set, SQL Server: Buffer Manager: Buffer Cache Hit Ratio, SQL Server: Buffer Manager: Free Buffers, SQL Server: Memory Manager: Total Server Memory (KB).
- Incorrect: The Performance Monitor Memory: Page Reads/sec is used to determine the average number of pages read from memory each second.
- Incorrect: It is normal for Windows NT Virtual Memory Manager (VMM) to steal pages from SQL Server and other processes as it trims the working set sizes of those processes, causing page faults. A low level of page faults is normal, even if the computer has plenty of available memory.
The Microsoft SQL Server 7.0 System Administration Training volume of the Microsoft SQL Server 7.0 System Administration Training Kit, Chapter 14, Lesson 2 discusses the use of the Windows NT Performance Monitor for SQL Server performance tuning.
Use Microsoft SQL Server Books Online (free download available at http://support.microsoft.com/download/support/mslfiles/sqlbol.exe) to search for “monitoring memory usage,” “optimizing server performance using memory configuration options,” “monitoring CPU use,” and “server memory options” for details on the Windows NT Performance Monitor counters used for SQL Server performance tuning.
Download the White Paper “Microsoft SQL Server 7.0 Performance Tuning Guide” available under “support” at http://www.microsoft.com/sql for information about SQL Server 7.0 index selection, disk I/O subsystem tuning, and performance tuning tools.
The Microsoft Windows NT Workstation 4.0 Resource Kit, Chapter 10, contains detailed information about using Windows NT Performance Monitor counters.
SQL Server System Administration. New Riders, Indianapolis, IN, 1999. ISBN 1-56205-955-6. Chapter 11 contains a detailed discussion of performance tuning and optimization.
Using Microsoft SQL Server 7.0. Que, Indianapolis, IN, 1999. ISBN 0-7897-1628-3. Chapter 18 contains a detailed discussion of SQL Server optimization and tuning fundamentals.
loading...
Related EssentiaListsuiAction=GetRelatedListsByEanFromProductPage&page=List&pageType=list&ean=9780735606722&productCode=BK&maxCount=5&threshold=10
loading...
Fans of this BookuiAction=GetProfileBlurbsWithEANinFavorites&page=UserProfile&pageType=profile&eanInput=9780735606722
loading...