queries, 47–96
check and foreign keys for improving query performance, 206–208
distributed queries, 51–54
enabling ad hoc queries, 352
exercise comparing query execution with/without MARS, 175–183
foreign keys used with subqueries, 205
full-text queries, 63–64
inefficient or slow-running, 384
MARS (Multiple Active Result Sets). See MARS (Multiple Active Result Sets)
querying data with ADOMD.NET, 157–158
ranking functions, 58
reference materials, 95
results with _NUMBER function, 59
SELECT statements. See SELECT statements
Transact-SQL and, 364
user-defined functions used with subqueries, 202–204
queries, XML data, 80–93
exercise generating XML, 92
exercise writing XQuery expression, 91
overview, 80
parameters, 84–85
returning data as XML, 86–89
updating data using XML, 89
XQuery, 81–84
query cost
indexed views and, 322
metrics for analyzing query performance, 285
query engine, 454–462
components, 454, 455
execution context, 458
hardware trends, 406
overview of, 454
procedure cache, 459–462
processing, 455–458
query plan, 458
steps in operation of, 457
query hints
for optimizing performance, 71–73
plan guides, 73
query notifications
benefits of, 357
caching and, 105–108
query optimizer
benefits of, 457
check and foreign key constraints for improving query performance, 206
cost-basis of, 456
DML triggers and, 210
execution order and, 286
FORCE ORDER, 78
in SQL Server 2005 query engine, 455
query plan as output, 455
reliance on distribution statistics, 457
searchable arguments and, 74
query performance, 70–79, 285–304. See also index performance
case scenario creating plan guide, 93
correlated and uncorrelated subqueries, 292–294
costs, 285
covered indexes and, 306
cursors and, 297
execution order, 286–289
execution plan and, 70
execution time, 286
exercise creating performance baseline, 324–325
exercise creating plan guide, 78
exercise optimizing example query, 302–304
exercise rewriting query, 76
exercise testing large result set, 300–302
exercise testing small result set, 298–300
exercise using clustered index for optimizing query, 325–327
exercise using indexed view for optimizing query, 328–330
exercise using non-clustered index for optimizing query, 327–328
graphical execution plan for optimizing, 289
joins for optimizing, 291
overview of, 70, 285
page reads, 285
plan guides for injecting query hints, 73
query hints for optimizing, 71–73
SARGs (search arguments) for optimizing, 290
scalar user-defined functions (UDFs), 294–296
scenario optimizing, 338
searchable arguments, 74–76
table-valued UDFs, 296
query plan
as component of query engine, 458
as output of query optimizer, 455
caching, 462
condition requiring new plan, 459
optimizing index structures, 467
read-ahead contrasted with parallel query plans, 454