Learn Microsoft SQL Server 2016 Best Practices for VLDB
4.1 (7 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
54 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Learn Microsoft SQL Server 2016 Best Practices for VLDB to your Wishlist.

Add to Wishlist

Learn Microsoft SQL Server 2016 Best Practices for VLDB

Learning best practices for very large databases in SQL 2016
4.1 (7 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
54 students enrolled
Created by John Hall
Last updated 11/2016
Current price: $10 Original price: $20 Discount: 50% off
5 hours left at this price!
30-Day Money-Back Guarantee
  • 2 hours on-demand video
  • 1 Article
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • At the end of this you will understand how to find performance issues and understand the best practices for large databases.
  • How you can optimism disk subsystems and memory management including row and page compression.
View Curriculum
  • SQL 2008 or later knowledge.
  • Downloaded SQL Server 2016 Developer or Enterprise.
  • Windows 8.1 or Windows Server 2012 R2
  • Recommend running VirtualBox but this is optional.

The at the end of this course you will know how use Microsoft SQL Server 2016 with Very Large Databases, how its been improved from earlier version and get years of experience in this short course.

  • Understand the Best Practices for databases for growth across files.
  • Create file groups and move Data between file groups.
  • Partition tables and create Memory optimized tables.
  • How to use Mount Points.
  • Use SQL Profiler to find improvements in database designs.
Who is the target audience?
  • This course is for New Database Administrators that have not worked with Very Large Databases on Microsoft SQL Server 2016
  • This course is for Database Administrators that want to have a deeper understanding of features that help VLDB
  • This course is for Developers that want a better understanding of performance features.
  • Knowledge level intermediate
Compare to Other Microsoft SQL Courses
Curriculum For This Course
16 Lectures
Creating a database and Memory Management
4 Lectures 28:01

Introduction to your trainer.

Preview 04:03

A quick history of some important trace flags.

  • T1117 & T1118
  • Storage types.
  • What is a very large databases.
Preview 10:20

Creating database from the beginning, as per best practice, we will do this with more than one file group and enable the auto grow all files. 

  • How to create file groups and set default one for use.
Preview 08:08

Continuation of database creation, enabling the database settings to make use of T1117 and T1118 behavior. 

  • How to enable Auto Grow All Files and Set Mixed Page Allocation.
  • This will improve the performance of the database use of the data files.
Creating a database part 2

Section 1 knowledge quiz

Learning Test
8 questions
Memory and Compression
4 Lectures 28:22

Theory of memory and compression.

Uncompressed information vs Row and Page level compression and why that is helpful.

Memory and Compression

Server Memory Management.

  • Its important not to use all the system memory for SQL Server.
  • its good to be able to know how much memory SQL Server is using as this can effect performance.
Memory and Compression Part 1

Row and page level compression and how to enable them.

  • Understand that Page level offers the best compression and also uses the highest CPU.
  • Row level uses less CPU than page level and still offers some compression.
Memory and Compression Part 2

In memory tables.

  • How to enable them and what are the limitations.
  • In Memory tables do not use disk I/O unless data is updated all read queries take place in memory.
  • In Memory tables are limited to 2 TB in SQL Server 2016
Memory and Compression Part 3

Section 2 knowledge quiz

Learning Test
7 questions
Optimizing existing database
5 Lectures 38:39

Altering an existing database, to add file groups and move data to the new file groups.

Making use of the DROP_EXISTING = ON statement 

Improving file groups

Creating and using Mount Points and how they are useful given limited driver lettering in Windows.

Remember that you only have A-z options for drive letters and at least one of those is already used by system disk C.

Adding more datafiles than drive letter

Why to use a Partition on a table.

What is improved by partitioning a table and what is not improved.  For example UPDATE, INSERT and DELETE however read only quires do not improve in more case.

Partitioning Tables Theory

How to Partitioning table by date and why we need to think about Indexes when we do this.

Remember that indexes are created on tables and when tables are changed the Index will need to be changed or recreated as well.

Partitioning Tables Part 1

How to Partitioning table by name or range so that you can see how else this could be done depending on data types in your table.

  • Partitions can be create in number groups like 1-15000, 15001-30000 or in name groups, "Hardware" "Sales" "Audit" etc so check the kind of data in the table and use the logic that best fits the table.
Partitioning Tables Part 2

Section 3 knowledge quiz

Learning Test
4 questions
Server Profiling
3 Lectures 17:29

How to create server side trace script from SQL profiler

Server Side Trace

Running the profiler script and then using the profiler trace to improve the database using database engine turning.

  • Replaying the trace file can show you what was the most CPU and Disk intensive statements.
  • Database engine turning.can suggest new indexes and partition schema, however it up to you what to use.

If you want to capture windows performance counters to compare with SQL Server Trace files I recommend setting the ones below.  A complete list can be found on Microsoft site.

  • Memory
    • Available MBytes
  • Physical Disk
    • Avg. Disk sec/Read
    • Avg. Disk sec/Write
  • Processor
    • % Processor Time
  • SQL Server: Buffer Manager
    • Page Life Expectancy
    • Buffer cache hit ratio
  • SQL Server: SQL Statistics
    • Batch Requests/sec
    • Compilations/sec
Server Side Trace Part 2

The scripts used during the course.

TSQL Scripts

Section 4 knowledge quiz

Learning Test
1 question
About the Instructor
John Hall
4.1 Average rating
8 Reviews
60 Students
2 Courses
Cloud Service Architect

John Hall is an IT professional with knowledge of Linux, Windows and Networking.

Spend large part of his career working for blue chip multinationals developing Private and Public cloud solutions for applications such as Sharepoint, SAP, J2EE and PaaS. 

Working in IT since 1999 he is an Microsoft Certified IT Professional in SQL Server and Windows Server however most of his time has been spend hunting down performance issue and improving overall customer service.