Collection 2784: Tuning and Optimizing Queries Using Microsoft® SQL Server™ 2005
Duration (hr): 12h
Collection 2784: Tuning and Optimizing Queries Using Microsoft®
SQL Server™ 2005
These six workshops will teach database developers working in
enterprise environments how to analyze and address performance
bottlenecks that occur within their Microsoft® SQL Server™ 2005
database solutions.
Topics covered within the collection include:
Measuring database performance
Optimization through physical design
Query optimization
Refactoring cursors in queries
Index optimization
Managing concurrency and locking
These courses will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
Workshop 4370: Measuring Database Performance Using Microsoft®
SQL Server™ 2005
In this 2-hour workshop, you will learn about how to monitor performance
in your Microsoft® SQL Server™ 2005 Database solutions.
Topics include performance monitoring, tools for monitoring performance,
and causes of locking and blocking.
In the lab, you will analyze tables and SQL scripts, as well as use SQL
scripts to simulate user activity for capturing baseline and concurrency
performance. You will also prioritize discovered bottlenecks and
improvements to the database. As a workshop, most of the learning
will be done within the lab itself.
Having this experience will enable you to address performance issues
that arise within deployed database solutions.
This course will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
At the end of the course, students will be able to:
Benchmark and identify key performance measures
Use Performance Monitor and SQL Server Profiler
Identify Locking and Blocking
Workshop 4371: Optimizing Physical Database Design Using Microsoft®
SQL Server™ 2005
In this 2-hour workshop, you will learn about how to optimize your
schema design to meet the performance requirements of your Microsoft®
SQL Server™ 2005 database solutions.
In the lab, you will optimize various schema objects, as well as measure
the performance after the objects have been optimized. As a workshop,
most of the learning will be done within the lab itself.
Having this experience will enable you to both prevent performance issues
form occuring, as well as address issues that arise during deployed
database solutions.
This course will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
At the end of the course, students will be able to:
Describe strategies for optimizing physical database design.
Create an optimized database design.
Workshop 4372: Optimizing Queries for Performance Using Microsoft®
SQL Server™ 2005
In this 2-hour workshop, you will learn how to optimize database queries
to enhance performance in your Microsoft® SQL Server™ 2005 Database
solutions. Topics include the importance of query optimization, the role
of the query optimizer, and the guidelines for building efficient queries.
In the lab, you will optimize stored procedures and views, as well as
measure the performance after these database have been optimized.
As a workshop, most of the learning will be done within the lab itself.
Having this experience will enable you to address performance issues that
arise from poorly performing queries within your database solutions.
This course will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
At the end of the course, students will be able to:
Describe how queries are processed by SQL Server
Identify the role of the query optimizer
Use subqueries
Build efficient queries
Workshop 4373: Refactoring Cursors into Queries Using Microsoft®
SQL Server™ 2005
In this 2-hour workshop you will learn how to refactor row-cursor based
operations into queries in order to enhance performance in your Microsoft®
SQL Server™ 2005 Database solutions. Within the workshop, you will learn
several different methods for refactoring, as well as guidelines for when
to use cursors.
In the lab, you will refactor several different types of cursors into
queries.As a workshop, most of the learning will be done within the lab itself.
Having this experience will enable you to address performance issues that
arise from poorly performing queries within your database solutions.
This course will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
At the end of the course, students will be able to:
Identify guidelines for using cursors
Describe how cursors impact performance
Refactor cursors in queries
Use Common Table Expressions and Pivot
Workshop 4374: Optimizing an Indexing Strategy Using Microsoft®
SQL Server™ 2005
In this 2-hour workshop you will learn how to apply various indexing
strategies in order to enhance performance in your Microsoft® SQL
Server™ 2005 Database solutions. Topics include index types, guidelines
for designing indexes, and using clustered vs nonclustered indexes.
Within the workshop, you will analyze bottlenecks and apply indexes to
various tables in order to improve performance. As a workshop, most of
the learning will be done within the lab itself.
Having this experience will enable you to address performance issues
that arise from poorly performing queries within your database solutions.
This course will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
At the end of the course, students will be able to:
Describe types of indexes
Describe index design guidelines
Describe guidelines for using Clustered and Nonclustered indexes
Workshop 4375: Managing Concurrency Using Microsoft®
SQL Server™ 2005
In this 2-hour workshop, you will learn how to apply a locking strategy
to your Microsoft® SQL Server™ 2005 Database solutions. Topics include
transaction isolation levels and effective techniques to reduce locking
and blocking.
Within the workshop, you will analyze database locking and blocking
issues and apply techniques to reduce concurrency issues. As a workshop,
most of the learning will be done within the lab itself.
Having this experience will enable you to address bottlenecks that occur
in multiuser databases due to locking.
This course will also prepare you for exam 70-442 PRO: Designing and
Optimizing Data Access by Using Microsoft® SQL Server™ 2005.
At the end of the course, students will be able to:
Use Transaction Isolation Levels
Reduce locking and blocking
Resolve deadlocks
