Start up

1
Start up

• Log on to the network
• Start Management Studio
• Connect to Reliant\sql2k5 and your SalesOrders
  database
• Start Books Online

2
MIS 431Dr. Steve RossSpring 2007

• Improving Query Performance

Material for this lecture is drawn from SQL
Server 2005 Database Essentials, SQL Server 2005
Applied Techniques, and the professors
experience.
3
SQL Server Data Storage

• 8KB page blocks
• 8 contiguous pages is an extent
• Mixed and uniform extents
• Up to 8 pages of an object in mixed extent
• More than 8 pages in uniform extents
• Most efficient read entire extents
• IAM Index Allocation Map
• Data stored in a heap

4
Query Plans

• Estimated
• Ctrl-L or icon before execution
• Based on available indexes and statistics
• Actual
• Ctrl-M or icon before execution
• Based on actual execution

5
Benefits of Indexes

• Exact-match (point) queries
• Range queries
• Join based on a unique indexed field
• Join based on an ordered indexed field
• All data for a query
• Avoiding duplicates
• Sorting data

6
Clustered Index

• Contains all the data
• Ordered in some way
• Only one (1) per table
• CREATE UNIQUE CLUSTERED INDEX name ON
  object(column)

7
Nonclustered Indexes

• Contain key values and pointers to record numbers
• Can have many (249) per table
• CREATE INDEX name ON object(column)

8
Covered Indexes

• A type of nonclustered index
• Index contains all the data necessary to answer a
  query
• CREATE INDEX name ON object(column) INCLUDE
  (column)

9
Indexes on Computed Columns

• Useful when computed column is used in WHERE
  clause
• Computed column must be deterministic and precise
• CREATE INDEX name ON object(column)

10
Indexes on Views

• When created view is executed and resulting
  data is stored in a file just like a clustered
  index
• Updated every time underlying data is changed
• Use when
• View processes many rows
• Data in base tables changed infrequently

11
Indexes and Joins

• Nested loop join
• Inner input table scanned one row at a time
• Seek match in outer input table
• Most efficient if
• Outer table indexed on join column
• Few rows in inner table

12
Indexes and Joins

• Merge join
• When join inputs are sorted on join columns
• Very efficient but requires advance sort
  (i.e., index)
• Hash join
• Large, unsorted, non-indexed inputs
• SQL Server computes hash values

13
Data Distribution and Statistics

• SQL Server chooses type of join operation based
  on input size of tables
• Statistics are normally updated automatically

14
Index Maintenance

• Required on every INSERT, UPDATE, or DELETE
  command
• Full pages must be split
• FILLFACTOR to compress or expand pages
• Compress for reading efficiency
• Expand for writing efficiency

15
What Do We Need to Know about Indexes?

• Two types
• Clustered
• Nonclustered
• Can be created on
• Fields
• Concatenated fields
• Computed columns
• Views
• Impact on read and write operations

16
Database Engine Tuning Advisor

• Requires either
• Workload file with statements to be optimized
• Trace file of past activity
• Suggests changes in design
• Create, change, or remove indexes
• See Database Essentials, chapter 3, for more
  information on traces and tuning

17
Next Lecture

• Dynamic Queries