Creating Efficient Database Applications

Description

To create an efficient database application one needs the knowledge of T-SQL language, must build an appropriate logical and physical database model, be aware of mechanisms specific to SQL Server, be able to use given server functions properly, and comply with both general, as well as SQL Server-specific, design patterns. Testing, including performance and load tests, is a crucial stage of a database project. Not only should such tests  simulate the expected load of a database, but, most importantly, their results should be the basis for introducing changes in the application project. The course prepares programmers to create efficient and scalable database applications working in Microsoft SQL Server environment. At the heart of this training there are verified standards and best programming practices, as well as methods of their application which we have successfully put to practical use in database projects ourselves. In effect, we have designed a training which will prevent you from making common mistakes which often result in coming up with applications whose proper performance is limited to the programmer’s computer, or ones whose performance decreases substantially as the number of users and/or the amount of data stored in the database grows.

Options

  • Course level: 400
  • Start date: as required
  • Duration time: 4 days

Price: 4100 zł

  • Open training
  • On-site (costumer's premises)
  • Customer’s computer

Class

The course has been designed for database programmers, testers and all those who would like to learn to diagnose and troubleshoot performance issues. Its participants will acquire the practical skills crucial for designing scalable databases, writing efficient queries and  identifying and optimizing ineffective code modules. The training is available in 3 versions, for SQL Server 2005, 2008 and 2008 R2, 2012 and 2014 respectively.

Plan

The basic training is comprised of 12 modules. Just like all the other courses on our offer, it has been designed by our experts, therefore we can freely customize it to suit the individual needs of the participants.  We would like to encourage you not only to choose modules which you find relevant for your needs, but also to feel free to contact us with your suggestions and questions you would like to hear answered during the course.
Our trainings are unique also because we limit the time set aside for laboratory type classes customarily conducted after the completion of each module, to the benefit of exercises done with the assistance of the trainer and demonstrations. We believe in this manner we can equip you with substantially more practical information and tips, and focus on dealing with the issues directly within your scope of interest. However, each day of the training is finished with a laboratory class of approximately 1hr, so that you can do self-practice and do exercises of your choice.

Modules

Duration: Level:
Module 1

Development Tools
  • SQL Server Management Studio and SQL Server Data Tools
  • Online Development
  • Database Projects
  • Source Code Versioning
  • Debugging Stored Procedures and Functions
  • Code Versioning and Synchronizing Changes
  • Refactoring T-SQL Code
  • Deploying and Publishing Database Project Data-Tier Applications

60 minutes 400
Module 2

Logical Database Design
  • Database Designing Process
  • Identifying and Documenting Business Requirements
  • Guidelines for the Logical Model
  • Entities and Attributes
  • Choosing Primary Keys
  • OLTP Database Modeling
  • Data Warehouse Modeling
  • Model Evaluation

90 minutes 300
Module 3

Physical Database Design
  • Naming Conventions
  • Choosing Right Data Types (Simple SQL Server Data Types, Non-Relational Types, Large Binary Objects, Filestream Attribute and Filetables, SQL CLR Types)
  • Designing Tables (Row Length, Sparse Attribute, Computed Columns)
  • Data Integrity (Constraints vs. Triggers)
  • Evaluation of the Physical Model

120 minutes 400
Module 4

Indexing Strategies
  • Heaps and Balanced Trees (HOBTS)
  • Index Internals
  • Clustered Index Best Practices
  • Non-Clustered Index Design for Optimal Performance
  • Indexing Computed Columns
  • Filtered Indexes
  • Columnstore Indexes

240 minutes 400
Module 5

Database Scalability
  • Scaling Up vs. Scaling Out
  • Data Distribution and Partitioning Views
  • Partitioning Tables And Indexes
  • Data Compression
  • Occasionally Connected Systems
  • Data Synchronization Techniques (Replication, Database Mirroring, Always On)

120 minutes 300
Module 6

Data Access Strategies
  • SQLCLR.NET Code In SQL Server (Architecture, Assemblies, Scalar and Tabular Functions , Stored Procedures, DML and DDL Triggers, User Types, Group Functions)
  • Recommendations for Creating and Indexing Views
  • SQLCLR Best Practices
  • Recommendations for Creating Stored Procedures
  • Recommendations for Creating User Functions
  • Exception Handling

120 minutes 400
Module 7

Query Execution
  • SQL Server Query Processing
  • Reading Query Plans
  • Analyzing Query Plans (Scans and Seeks, Seekable Predicates, Joins and Unions, Aggregations, Subquery Plans, Parallelism)

90 minutes 400
Module 8

Writing Effective Queries
  • Data Types and Conversions
  • Query Best Practices
  • Row Constructors
  • Table-Valued Parameters
  • Common Table Expressions
  • Ad-Hoc Query Paging
  • GROUPING SETS Clause
  • OVER Clause (Ranging, Grouping and Analytic Functions, Grouping, Ordering and Selecting Rows)
  • “Top N Per Group” Query Pattern
  • Sequence Objects
  • MERGE Statement

150 minutes 400
Module 9

Query Optimizer
  • How Does Query Optimizer Work?
  • Statistics
  • Hints
  • Plan Guides

90 minutes 400
Module 10

Plan Caching and Recompilation
  • Plan Caching and Reuse
  • What Is in the Procedure Cache?
  • Managing the Procedure Cache
  • Controlling Plan Reuse
  • Recompilation Triggers

60 minutes 400
Module 11

Transactions and Concurrency Control: Locking, Blocking and Row Level Versioning
  • SQL Server Locking Architecture
  • Isolation Levels Internals
  • Nesting Transactions
  • Distributed Transactions
  • Managing Locks
  • Lock Escalation and Partitioning
  • Row-Level Versioning
  • Managing Version Store

60 minutes 400
Module 12

Detecting Performance Problems
  • Performance Methodology (Baselining and Benchmarking)
  • SQL Server Wait Types
  • Detecting Resource Bottlenecks
  • Detecting Inefficient Queries
  • Detecting Concurrency Problems
  • SQL Server Tools (Perfmon, SSMS Reports, Database Tuning Advisor, Management Data Warehouse, Dynamic Management Views, SQL Server Profiler & Trace, Extended Events)
  • Additional Tools (SQLDIAG, SQL Diag Configuration Tool, Performance Analysis of Logs, RML Utilities for SQL Server, SQL Nexus, ClearTrace)

240 minutes 400

Form

Ask Question

Register