Name: Learn Microsoft SQL Server 2016 Best Practices for VLDB
Rating: 4.3 (139 reviews)

Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Created byJohn Hall

Last updated 12/2021

English

What you'll 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.

Course content

5 sections • 18 lectures • 2h 4m total length

Introduction4:03
Introduction to your trainer.
Storage and Trace flags10:20
A quick history of some important trace flags.

T1117 & T1118
Storage types.
What is a very large databases.
Creating a database8:08
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.
Creating a database part 25:30
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.
Learning Test

Memory and Compression5:26
Theory of memory and compression.
Uncompressed information vs Row and Page level compression and why that is helpful.
Memory and Compression Part 13:01
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 29:11
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 310:44
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
Learning Test

Improving file groups6:09
Altering an existing database, to add file groups and move data to the new file groups.
Making use of the DROP_EXISTING = ON statement
Adding more datafiles than drive letter4:22
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.
Partitioning Tables Theory5:27
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 Part 113:37
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 29:04
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.
Learning Test

Server Side Trace9:03
How to create server side trace script from SQL profiler
Server Side Trace Part 28:20
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
TSQL Scripts0:06
The scripts used during the course.
Learning Test

Requirements

SQL 2008 or later knowledge.
Downloaded SQL Server 2016 Developer or Enterprise.
Windows 8.1 or Windows Server 2012 R2 and higher
Recommend running VirtualBox but this is optional.

Description

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 this course is for:

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

What you'll learn

Explore related topics

Course content

Creating a database and Memory Management4 lectures • 28min

Memory and Compression4 lectures • 28min

Optimizing existing database5 lectures • 39min

Server Profiling3 lectures • 17min

Disaster Recovery2 lectures • 12min

Requirements

Description

Who this course is for: