SQL Server Performance and Tuning

Duration: 5 Days

Objectives

The objective of this course is to provide senior database analysts, developers or database administrators with a good understanding of SQL Server Architecture and Query Performance so that applications and their supporting environment can be tuned for optimal performance. The course will provide delegates with the knowledge required to size, plan, monitor, tune and troubleshoot a SQL Server application for either a OLTP live environment or a OLAP data warehousing environment. Everything from Operating System and SQL Server service tuning through to query and index tuning will be covered. It is a hands on course with performance and analysis tools being put to use throughout. Delegates will learn how to capture and analyse performance data to enable troubleshooting of performance problems using tools such as Performance Monitor, SQL Profiler, Extended Events and Dynamic Management Views. They will learn how to interpret query execution plans to determine where the cause of bottlenecks lies and how to tune the queries for best performance. Covers SQL Server 2005, SQL Server 2008, SQL Server 2012 and SQL Server 2014.

Pre-requisites

This course assumes no prior knowledge of SQL Server Reporting Services. This course does assume prior knowledge of the SQL language to the level of the SQL Server Database Querying course.

Course includes

A comprehensive set of reference notes covering course topics, follow-up telephone support and, for training on Transmedia premises, lunch and refreshments.

Course outline

Introducing SQL Reporting Services (SSRS)

  • Who Uses SSRS?
  • Dashboards, Reports and Applications
  • Enterprise Reporting
  • Solution Types
  • Business Intelligence Reporting Solutions

Reporting Services Installation & Architecture

  • The Basic Installation
  • The Enterprise Deployment
  • The Reporting Lifecycle
  • SSRS Tools: Report Designer, Power View, Report Builder, Report manager, SharePoint Libraries, Reporting Services Configuration Manager, SQL Server Management Applications
  • SSRS Windows Service
  • SSRS Processors & Extensions: Processing & Rendering Extensions (HTML, CSV, XML, Image, PDF, Excel, Word)
  • SSRS Delivery Extensions
  • SSRS Databases: ReportServer, ReportServerTempDB

Overview of Configuring SharePoint Integration

  • SharePoint Technologies
  • Installation & Configuration
  • Architecture

Basic Report Design

  • Creating Reports with Wizards
  • Manual Report Design
  • Report Components: Data Source, Data Set, Report Layout, Regions
  • Shared Data Sources & Data Sets
  • Report Builder 30
  • SQL Server Data Tools (SSDT - 2012 to 2014)
  • Business Intelligence Development Studio (BIDS - 2008)
  • Properties Window
  • TextBox Properties
  • Selected Text Properties
  • Toolbars

Report Layout & Formatting

  • Report Layout Types: Table, Matrix, List, Chart, Gauge and Dashboard, Maps
  • Tablix Regions
  • Tablix Properties
  • Table Groups
  • Group Expressions
  • Interactive Sort
  • Page Breaks
  • Drill Down & Dynamic Visibility
  • Formatting Report Data
  • Dynamic Formatting
  • Multicolumn Reports
  • Gauge Reports

Designing Data Access

  • Reporting for Relational Data
  • Data Sources
  • Data Sets
  • Filtering Techniques: Filtered Data Sets, Filtered Regions
  • Report Parameters: Single Value, Mulitvalue, Drop Down Lists
  • Cascading Parameters
  • Using Stored Procedures
  • Filtering with Parameters
  • Non SQL Server Database Data Sources: Access, Oracle, Excel, Sybase
  • Building Queries in Strings

Advanced Report Design

  • Headers & Footers: First Page, Every Page, Last Page, Group Headers & Footers
  • Table Groups and Matrix Groups
  • Adding Totals to Table and Matrix Reports
  • Creating Report Templates
  • Creating Composite Reports
  • Text boxes, Padding & Embedded Formatting
  • Reporting on Recursive Relationships: Recursive Parent
  • Designing Master/Detail Reports
  • Drill down Reports
  • Sub Reports
  • Drill Through & Link Action Reports
  • Nested Table & Chart Regions
  • Navigating Reports: Bookmarks, URLs, Document Maps
  • Expression Builder
  • Fields!, Globals!, Parameters! And ReportItems! Collections
  • SSRS Operators: string concatenation, arithmetic, comparison
  • SSRS Functions: Maths, String, Date, IIf, Switch, and many more
  • Conditional Formatting
  • Using List Regions to Combine Report Items

Chart Reports

  • Chart Types: Column, Stacked, Area & Line, Pie & Doughnut, Bubble & Stock
  • Anatomy of a Chart: Configuring Charts
  • Creating a Multiseries Chart
  • Secondary Axis
  • Multiple Chart Areas
  • Label Rotation
  • Useful Properties and Settings

Content Management

  • Navigating Around Report Manager
  • Organising Reports, Data Sources and Datasets
  • Adding Data Cells
  • Displaying or Hiding Report Manager Details
  • Running Reports
  • Managing Folders and Files
  • Creating a Standard Subscription
  • Creating Data- Driven Subscriptions
  • Creating the Data- Driven Query
  • Managing Generic Site Settings
  • Report Caching
  • Report Snapshots
  • Report History
  • Creating Subscriptions
  • Parts of a Subscription
  • Managing Reports and Data Sources
  • Other Things You Can Do with Report Manager
  • Creating Linked Reports

Native Mode Server Administration

  • Reporting Services Security Architecture
  • Web Server Authentication
  • Reporting Services Authentication
  • Data Source Security
  • Managing Data Source Security from Visual Studio
  • Four Choices for Managing Credentials
  • Which SQL Server Permissions are Needed for a Report?
  • Managing Data Source Security from Report Manager
  • Backup & Recovery
  • Encryption Keys
  • Monitoring
  • Configuration

Integrating Reports Into Custom Applications

  • URL Access
  • Accessing Reporting Services Objects
  • Reporting Services URL Parameters
  • Passing Report Information Through the URL
  • Programmatic Rendering
  • Using The ReportViewer Control

Using Embedded & Referenced Code

  • Using Expression Builder
  • Calculated Fields
  • Conditional Expressions
  • Using Custom Code

Course Dates

London CentralLondon GreenwichBristol

London Greenwich

Dates Price Book Now
23 - 27 Oct £1495 Book Now
20 - 24 Nov £1495 Book Now
18 - 22 Dec £1495 Book Now
See more dates...
All our courses run in London and Bristol, or can be tailored to requirements at your own offices. For alternative dates, please email or call us on 0208 269 4260
All prices are per person and are subject to VAT
15% discount for groups of four delegates.
10% discount for groups of three delegates.
(This offer applies for delegates on the same public scheduled course on the same date.)

Related Courses


"Fantastic! Everything was explained very clearly."

Sothebys

"Excellent. The best training I have ever had."

National Housing Federation

"I have nearly thirty years experience as an information systems manager and I cannot recall meeting a trainer with the combination of teaching skill and subject knowledge that you displayed over the last two days. I am self employed and so time spent on a course is time spent not earning. This was worth the money and my time!"

Trinitas Consulting



 

 

© Transmedia . 277 Greenwich High Road London SE10 8NB  UK . 0208 269 4260
Designed by Transmedia's Web Team