Q


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




MCITP Self-Paced Training Kit Exam 70-442  .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
ISBN: 073562383X
EAN: N/A
Year: 2007
Pages: 162

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net