Scheduled Dates (sort by: location | date) |
Request availability or book by selecting a date: Central London
City
Prices exclude VAT.
Have questions or need a better city/date? Ask now.
On-site/international quote? Ask now.
|
Course Overview To fully exploit the powerful features of Microsoft SQL Server, developers and designers must acquire an in-depth knowledge of SQL Server's storage engine, query optimiser and lock manager. This course provides the details of SQL Server's internal architecture. Through hands-on exercises, you gain the skills needed to achieve a highperformance SQL Server database solution. Audience This course is valuable for those responsible for increasing the performance and efficiency of SQL Server databases. Course 532, " SQL Server 2000 Transact-SQL Programming ", or Course 133, " SQL Server 2005 Server-Side Programming ", is assumed. Familiarity with logical database design is also assumed. Skills Gained - Design and implement high-performance databases for SQL Server 2005 and 2000
- Create indexes that optimise different types of queries
- Design transactions that maximise concurrency and minimise contention
- Interpret the data access plans produced by the query optimiser
- Minimise I/O by designing efficient physical data structures
- Improve response time by introducing controlled redundancy
- Analyse and cure performance problems using SQL Server's tools
Course Outline Fundamental Concepts Analysing performance - Selecting an appropriate monitoring tool
- Investigating plans with SHOWPLAN_ALL
- Interpreting STATISTICS output
- Pinpointing performance problems with aggregated Profiler data
Developing a monitoring plan - Establishing a performance baseline
- Tracking changes over time
- Creating server-side Profiler traces
- Monitoring SQL Server and the operating system with System Monitor
Managing Storage Database architecture - Page and extent allocation
- Controlling data placement with file groups
Defining tables - Selecting the correct data types
- Specifying text and image locations
- Examining internal page structures
Creating and managing indexes - Clustered vs. non-clustered
- Defining indexed views
- Analysing and repairing fragmentation
Memory and Locking Managing memory - Buffer pool
- Buffer manager
- Lazywriter
- Checkpointing
- Log writer
Designing transactions - Consistency vs. concurrency
- Investigating lock types and their compatibility
- Choosing isolation levels
- Designing transactions to limit lock duration
- Supporting optimistic concurrency
Optimising Queries Query optimiser architecture - Phases
- Strategies
- Data access plans
- Auto-parameterisation
- Avoiding continuous recompilation of dynamic queries
Maintaining up-to-date statistics - Index vs. column
- Automatic vs. manual
- Full-scan vs. sample
Distinguishing among query types - Point
- Multipoint
- Range
- Prefix match
- Extremal
- Ordering
- Grouping
- Join
Designing effective indexes - Providing alternate access paths
- Improving join performance
- Increasing sort efficiency
- Reducing I/O with covering indexes
- Getting design advice from built-in tuning tools
Designing a Physical Data Model Storing summarised data for faster retrieval - Defining roll-up tables
- Materialising aggregated data with indexed views
Minimising response time by introducing redundant data - Maintaining redundant data with triggers
- Enhancing performance with surrogate keys
Solving performance problems by redefining the database structure - Partitioning data
- Creating a read-only query database
Scaling out with distributed partitioned views - Designing a federated database
- Implementing a routing strategy for updates
How to make a booking for the MSQ-DH course
|
